Right or wrong in index creation......

Hi,

like I said in other posts, I'm new to sql server, worked with informix for a long time. Right now I got into an "argument" with the present "dba", the indexes for all the tables in the database are being rebuilt, he wanted to start a monthly process, I told him that he can't do processing because when an index is built the table is locked, now, since I'm new at sql server I would like to know from the experts.... can you run process in sql server against tables when:

- indexes are being created
- the structure of a table is being modified
- the database is being shrunk

If we had been talking about informix then I would have probably slapped him if he tried to "teach" me about how things work, I'm pretty sure its the same in SQL but I think its better to have complete assureness of what I'm talking about before the slapping starts :)

Thanks in advance

Luis TorresThe ever-popular answer: It depends.

During a schema change, all bets are off. Only the process modifying the table structure has any access to the table at all.

During indexing, read operations are often allowed (depending on session settings), but write operations are usually blocked (although there are some creative ways to work around this too). In general, I recommend that you let the box have its way with the table while indexing, then you can have your turn later.

While shrinking the log, there is no material impact on access to the tables. While shrinking the data device(s), performance goes down a rat-hole, but blocking doesn't often occur. I'd still suggest letting the machine have its way with the tables while this is going on, but that's because I'm paranoid and usually have lots of other things to entertain me... If push came to shove, I'm pretty sure that this isn't a problem.

-PatP|||I know you can not insert or delete (and 99% sure you can not update) a table that is currently being indexed. SQL Server in its present form does not keep a "redo area" like Oracle to keep track of changes.

As for the others, shrinking the database is not really advised for a production environment. It tends to be costly, and since data pages have to be moved around, you are likely to see exclusive locking as a result. The major problem is, you will not be able to predict what tables will be locked.

Edit: Sniped again. Pat, are you on course for 4,000?|||Thanks for your replies, gives me better understanding of what I'm doing and talking about :)|||Edit: Sniped again. Pat, are you on course for 4,000?I might just make it. I've been so busy at the orifice that I don't always know which way is up (and they keep re-definiing just what "up" means constantly). I think I'll make it though!

...gives me better understanding of what I'm doing and talking aboutAlways a good thing in my book. I like it when I've got a strong clue what I'm talking about!

-PatP|||And speaking of books..you should probably go get one asap...did you get the client side tools installed?

Have you looked at books online?

EDIT: And, if the DBA is just reindexing everything, just because, that's not a good idea if they don't need to be done.

They need to check the amount of fragmentation.

I mean, why Reindex a code table?

EDIT AGAIN: You might also want to look at DBCC INDEXDEFRAG|||Brett,

I have definetly started reading books, specially BOL. I started training myself at the beginning of the year and now I'm allowed to "play" with the production database. I've been a dba for many years (close to 10) so I'm more than comfortable around them, just need to get used to the way SQLServer works.

I would have been able to get the answer to the questions that I asked from BOL but something that time hasn't finished teaching me is patience and I when the actual "dba" started "teaching" me (he is new to databases) about things I knew or had a very accurate idea of how they work then I got pissed and wanted to get an answer as fast as I could from the best source I could find (which is of course the experience of other dbas). Something that time HAS taught me is to not open my mouth until I'm completely sure of what's coming out of it (because of my natural impatience I tended to do a lot of goofups when I was younger).

Thanks again for al your coments and have an excelent end of day :)

Luis Torres

revoking all the privileges from public

I am revoking all the privileges from public role from master and tempdb
database, will that be any thing wrong after that? just want expert idea
because I ran one tool and it gave me suggession to remove public rights
from system objects on master database.
LIke following are some of the commands
USE MASTER
REVOKE EXEC ON dt_droppropertiesbyid FROM PUBLIC
GO
REVOKE EXEC ON dt_checkinobject FROM PUBLIC
GO
REVOKE SELECT ON dtproperties FROM PUBLIC
GO
REVOKE EXEC ON dt_checkoutobject FROM PUBLIC
GO
REVOKE EXEC ON dt_checkoutobject_u FROM PUBLIC
GO
Please give any suggession,
Rogers,
Do not revoke all rights from public for these databases. That will break
many things.
RLF
"Rogers" <naissani@.hotmail.com> wrote in message
news:eLXU7qB4HHA.5804@.TK2MSFTNGP05.phx.gbl...
>I am revoking all the privileges from public role from master and tempdb
>database, will that be any thing wrong after that? just want expert idea
>because I ran one tool and it gave me suggession to remove public rights
>from system objects on master database.
> LIke following are some of the commands
> USE MASTER
> REVOKE EXEC ON dt_droppropertiesbyid FROM PUBLIC
> GO
> REVOKE EXEC ON dt_checkinobject FROM PUBLIC
> GO
> REVOKE SELECT ON dtproperties FROM PUBLIC
> GO
> REVOKE EXEC ON dt_checkoutobject FROM PUBLIC
> GO
> REVOKE EXEC ON dt_checkoutobject_u FROM PUBLIC
> GO
>
> Please give any suggession,
>
|||Thanks alot for your reply,
Is there any Microsoft article on that so I can present this to our Manager
and also refer that Microsoft doesn't recommend that.
Thanks
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eMITWCD4HHA.5776@.TK2MSFTNGP04.phx.gbl...
> Rogers,
> Do not revoke all rights from public for these databases. That will break
> many things.
> RLF
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:eLXU7qB4HHA.5804@.TK2MSFTNGP05.phx.gbl...
>
|||Rogers,
You don't say which version of SQL Server you are running, which may make
some difference.
First of all, you can revoke some rights in master and msdb and still have
the necessary things work, but revoking everything would be a disaster.
Basic system stored procedures that everyone needs would also be revoked.
You need to be more measured in your revoking of rights, which means you
need to understand what is still needed and what can be safely revoke. (If
you are using SQL Server 2005, note that some rights that were exposed in
SQL Server 2000 are now more tightly guarded.)
Here is a PowerPoint SQL Server 2000 oriented from about 3 years ago, which
does suggest some areas for rights to be removed:
http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA5_kburge.ppt#302,43,URLs
Tools such as AuditPro also make suggestions.
So, I do not have a specific list for you, review the things in the BOL (or
do "exec sp_helprotect @.username='public'" in both master and msdb) to see
what is granted, then think about whether you want that or not. (It is
pointless to manipulate rights in tempdb, since it is rebuilt everytime the
SQL Server restarts and it has its own mechanism for privacy.)
I tried finding a real solid article on this, but (alas) could not.
RLF
Note: The following article offers no guidance on what to revoke and what
not to revoke, it merely tells you what you can do, not what it is wise to
do: http://support.microsoft.com/kb/75291
"Rogers" <naissani@.hotmail.com> wrote in message
news:Oqsq$FD4HHA.3900@.TK2MSFTNGP02.phx.gbl...
> Thanks alot for your reply,
> Is there any Microsoft article on that so I can present this to our
> Manager and also refer that Microsoft doesn't recommend that.
> Thanks
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eMITWCD4HHA.5776@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks alot, I am using SQL Server 2000.
Thanks
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:u5hPS6D4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Rogers,
> You don't say which version of SQL Server you are running, which may make
> some difference.
> First of all, you can revoke some rights in master and msdb and still have
> the necessary things work, but revoking everything would be a disaster.
> Basic system stored procedures that everyone needs would also be revoked.
> You need to be more measured in your revoking of rights, which means you
> need to understand what is still needed and what can be safely revoke.
> (If you are using SQL Server 2005, note that some rights that were exposed
> in SQL Server 2000 are now more tightly guarded.)
> Here is a PowerPoint SQL Server 2000 oriented from about 3 years ago,
> which does suggest some areas for rights to be removed:
> http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA5_kburge.ppt#302,43,URLs
> Tools such as AuditPro also make suggestions.
> So, I do not have a specific list for you, review the things in the BOL
> (or do "exec sp_helprotect @.username='public'" in both master and msdb) to
> see what is granted, then think about whether you want that or not. (It
> is pointless to manipulate rights in tempdb, since it is rebuilt everytime
> the SQL Server restarts and it has its own mechanism for privacy.)
> I tried finding a real solid article on this, but (alas) could not.
> RLF
> Note: The following article offers no guidance on what to revoke and what
> not to revoke, it merely tells you what you can do, not what it is wise to
> do: http://support.microsoft.com/kb/75291
>
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:Oqsq$FD4HHA.3900@.TK2MSFTNGP02.phx.gbl...
>
|||Please send me the list If you find any like which system objects in Master
and MSDB can be revoked from Public Role, and no impact on sql server.
Thanks alot for your help.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:u5hPS6D4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Rogers,
> You don't say which version of SQL Server you are running, which may make
> some difference.
> First of all, you can revoke some rights in master and msdb and still have
> the necessary things work, but revoking everything would be a disaster.
> Basic system stored procedures that everyone needs would also be revoked.
> You need to be more measured in your revoking of rights, which means you
> need to understand what is still needed and what can be safely revoke.
> (If you are using SQL Server 2005, note that some rights that were exposed
> in SQL Server 2000 are now more tightly guarded.)
> Here is a PowerPoint SQL Server 2000 oriented from about 3 years ago,
> which does suggest some areas for rights to be removed:
> http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA5_kburge.ppt#302,43,URLs
> Tools such as AuditPro also make suggestions.
> So, I do not have a specific list for you, review the things in the BOL
> (or do "exec sp_helprotect @.username='public'" in both master and msdb) to
> see what is granted, then think about whether you want that or not. (It
> is pointless to manipulate rights in tempdb, since it is rebuilt everytime
> the SQL Server restarts and it has its own mechanism for privacy.)
> I tried finding a real solid article on this, but (alas) could not.
> RLF
> Note: The following article offers no guidance on what to revoke and what
> not to revoke, it merely tells you what you can do, not what it is wise to
> do: http://support.microsoft.com/kb/75291
>
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:Oqsq$FD4HHA.3900@.TK2MSFTNGP02.phx.gbl...
>

Returns wrong result when query MySQL 4.1

Hi,
When running the following query "select TIMEDIFF('2004-04-28 04:11:00', '2004-04-28 04:58:00')" from Report Server connecting to MySQL 4.1 (using MySQL ODBC 3.51) returns 00:00:00. I ran the same query from MySQL gets the correct result -00:47:00. Why is that?
Your input is appreciated.
JLReporting Services does not modify the query commandtext - it is just passed
on to the ODBC provider.
When you run the query directly on MySQL you did not connect through the
MySQL ODBC provider, did you? Can you just write a minimal application that
uses the MySQL ODBC provider and execute the query - what is the result
there?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"jcl_tw" <jcl_tw@.discussions.microsoft.com> wrote in message
news:F845F342-D356-4534-BDC3-A9D1F6C9F36E@.microsoft.com...
> Hi,
> When running the following query "select TIMEDIFF('2004-04-28 04:11:00',
'2004-04-28 04:58:00')" from Report Server connecting to MySQL 4.1 (using
MySQL ODBC 3.51) returns 00:00:00. I ran the same query from MySQL gets the
correct result -00:47:00. Why is that?
> Your input is appreciated.
> JL|||Hi Robert,
When I ran the command on MySQL, it was running directly on the server, not thruogh MySQL ODBC. I have created few reports on RS connecting through MySQL ODBC and didn't receive bad data, this is the first, and this is the first report I'm using the time function.
JL
"Robert Bruckner [MSFT]" wrote:
> Reporting Services does not modify the query commandtext - it is just passed
> on to the ODBC provider.
> When you run the query directly on MySQL you did not connect through the
> MySQL ODBC provider, did you? Can you just write a minimal application that
> uses the MySQL ODBC provider and execute the query - what is the result
> there?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "jcl_tw" <jcl_tw@.discussions.microsoft.com> wrote in message
> news:F845F342-D356-4534-BDC3-A9D1F6C9F36E@.microsoft.com...
> > Hi,
> >
> > When running the following query "select TIMEDIFF('2004-04-28 04:11:00',
> '2004-04-28 04:58:00')" from Report Server connecting to MySQL 4.1 (using
> MySQL ODBC 3.51) returns 00:00:00. I ran the same query from MySQL gets the
> correct result -00:47:00. Why is that?
> >
> > Your input is appreciated.
> > JL
>
>

returns null not cost value in SP

I do not understand what I am doing wrong. I have no problem getting the
value into @.tot but I cannot get the value assigned to @.item_cost. When I
use the debug mode, it says @.item_cost is null before the start of the debug
and after the debugging is done.
CREATE PROCEDURE test
@.id int,
@.item_cost money = NULL out
AS
declare @.tot money
select @.tot = convert(money,(field_value))
from tbl
where field_id = @.id
and field_code = 901
set @.item_cost = @.tot
GO
Grant
Who gives a {censored} if I am wrong.--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Why have the intermediate variable @.tot? Why not just assign the value
in the SELECT statement to the variable @.item_cost. And, why set a
default of NULL on the OUTPUT variable when, if the SELECT command
doesn't pick up anything, it will be NULL anyway?
CREATE PROCEDURE test
@.id int,
@.item_cost money out
AS
select @.item_cost = convert(money, field_value)
from tbl
where field_id = @.id
and field_code = 901
GO
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRFJOv4echKqOuFEgEQJPPwCfcFbZUZ5LjwCf
r6w/ssa5DdXaul8Ani4G
M3i/xn7vb7/mwkxaXsdhqe9B
=nln8
--END PGP SIGNATURE--
Grant wrote:
> I do not understand what I am doing wrong. I have no problem getting the
> value into @.tot but I cannot get the value assigned to @.item_cost. When I
> use the debug mode, it says @.item_cost is null before the start of the deb
ug
> and after the debugging is done.
>
>
> CREATE PROCEDURE test
> @.id int,
> @.item_cost money = NULL out
> AS
>
> declare @.tot money
>
> select @.tot = convert(money,(field_value))
> from tbl
> where field_id = @.id
> and field_code = 901
>
> set @.item_cost = @.tot
> GO
>|||I was struggling on getting the values returned back to me so I added
intermediate variable and I found that I was getting the expected result but
only to @.tot not @.item_cost. I made the changes to the query to match your
example and it still returns null instead of 20.00.
Grant
Who gives a {censored} if I am wrong.
"MGFoster" <me@.privacy.com> wrote in message
news:a9s4g.5073$An2.3038@.newsread2.news.pas.earthlink.net...
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Why have the intermediate variable @.tot? Why not just assign the value
> in the SELECT statement to the variable @.item_cost. And, why set a
> default of NULL on the OUTPUT variable when, if the SELECT command
> doesn't pick up anything, it will be NULL anyway?
> CREATE PROCEDURE test
> @.id int,
> @.item_cost money out
> AS
> select @.item_cost = convert(money, field_value)
> from tbl
> where field_id = @.id
> and field_code = 901
> GO
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBRFJOv4echKqOuFEgEQJPPwCfcFbZUZ5LjwCf
r6w/ssa5DdXaul8Ani4G
> M3i/xn7vb7/mwkxaXsdhqe9B
> =nln8
> --END PGP SIGNATURE--
> Grant wrote:|||Very confuse now. I got it to work but I have to print it, why? Changes are
in ucase.
CREATE PROCEDURE test
@.id int,
@.item_cost money = NULL out
AS
declare @.tot money
select @.tot = convert(money,(field_value))
from tbl
where field_id = @.id
and field_code = 901
set @.item_cost = @.tot
PRINT CONVERT(VARCHAR, @.item_cost))
GO
Grant
Who gives a {censored} if I am wrong.
"Grant" <email@.nowhere.com> wrote in message
news:%23FEYkSuaGHA.4564@.TK2MSFTNGP03.phx.gbl...
>I do not understand what I am doing wrong. I have no problem getting the
>value into @.tot but I cannot get the value assigned to @.item_cost. When I
>use the debug mode, it says @.item_cost is null before the start of the
>debug and after the debugging is done.
>
>
> CREATE PROCEDURE test
> @.id int,
> @.item_cost money = NULL out
> AS
>
> declare @.tot money
>
> select @.tot = convert(money,(field_value))
> from tbl
> where field_id = @.id
> and field_code = 901
>
> set @.item_cost = @.tot
> GO
>
> --
> Grant
> Who gives a {censored} if I am wrong.
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Can you execute the SP from Query Analyzer? E.g.:
declare @.output money
exec test 255, @.item_cost = @.output OUTPUT
print @.output
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA+AwUBRFJnxoechKqOuFEgEQI+tACgljJ1DN4D
4IfLakOmWRWehBNUQBMAl2eH
YynUqeXDsI6xI87T63C9SbM=
=lDfp
--END PGP SIGNATURE--
Grant wrote:
> Very confuse now. I got it to work but I have to print it, why? Changes ar
e
> in ucase.
>
> CREATE PROCEDURE test
> @.id int,
> @.item_cost money = NULL out
> AS
> declare @.tot money
> select @.tot = convert(money,(field_value))
> from tbl
> where field_id = @.id
> and field_code = 901
> set @.item_cost = @.tot
> PRINT CONVERT(VARCHAR, @.item_cost))|||On Fri, 28 Apr 2006 14:00:37 -0400, Grant wrote:

>I was struggling on getting the values returned back to me so I added
>intermediate variable and I found that I was getting the expected result bu
t
>only to @.tot not @.item_cost. I made the changes to the query to match your
>example and it still returns null instead of 20.00.
Hi Grant,
How do you call the procedure?
EXEC test @.id = 1,
@.item_cost = @.result_variable
is WRONG!! (And I expect that this is how you currently do it).
The correct call is
EXEC test @.id = 1,
@.item_cost = @.result_variable OUTPUT
Note the addition of OUTPUT to specify that @.item_cost is an output
variable. You have to specify this both in the CREATE PROCEDURE and in
the EXECUTE statement in order to make it work.
Hugo Kornelis, SQL Server MVP

Returning identity field

Hi all i'm trying to get the identity field after inserting into db, what am i doing wrong? thanks a lot

my sproc:

CREATE PROCEDURE ng_AddCotacao
(
...
@.Codigo_cotacao int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO
Negocios_cotacoes
(
...
)
VALUES
(
...
)
SELECT @.Codigo_cotacao=SCOPE_IDENTITY()
SET NOCOUNT OFF
END
GO

class file

public

class Cotacoes

{

publicint codigoCotacao;

}

public class CotacaoAtualiza
{
public Cotacoes cotacoes = new Cotacoes();

public CotacaoAtualiza()
{
}

public void AdicionarCotacao(
...
)
{
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["stringConexao"]);
SqlCommand myCommand = new SqlCommand("ng_AddCotacao", myConnection);

myCommand.CommandType = CommandType.StoredProcedure;

...

SqlParameter paramCodigo_cotacao = new SqlParameter("@.Codigo_cotacao", SqlDbType.Int, 4);
paramCodigo_cotacao.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(paramCodigo_cotacao);

...

myConnection.Open();
SqlDataReader result = myCommand.ExecuteReader();

while(result.Read())
{
this.cotacoes.codigoCotacao = (int) result["@.Codigo_cotacao"];
}

myConnection.Close();
}

calling into code-behind file:

CotacaoAtualiza ca =

new CotacaoAtualiza();

Cotacoes cotacoes = ca.cotacoes;

Response.Redirect("Cotacao_confirma.aspx?cotacao=" + cotacoes.codigoCotacao);

You don't get the result in the resultset but rather in the parameter - ex:

System.Data.SqlClient.

SqlCommand Cmd =new System.Data.SqlClient.SqlCommand("AddSomething", oConn);

Cmd.CommandType =

CommandType.StoredProcedure;

System.Data.SqlClient.

SqlParameter oParam = Cmd.Parameters.AddWithValue("@.zonename", sName);

oParam = Cmd.Parameters.AddWithValue(

"@.desc", sDesc);

oParam = Cmd.Parameters.AddWithValue(

"@.TheNewId", 0);

oParam.Direction =

ParameterDirection.Output;

Cmd.ExecuteNonQuery();

oConn.Close();

int nTheNewId =Convert.ToInt32(Cmd.Parameters["@.TheNewId"].Value);