addlogin into linked server

Hi,
I need to develop a stored procedure to add or drop SQL server logins into
SQL servers based on some logics users give to me. My stored procedure is on
one server. I need to add logins to other servers through linked server.
Does anybody know how to run sp_addlogin to add SQL logins into linked serve
r?
Thanks a lot.BF (BF@.discussions.microsoft.com) writes:
> I need to develop a stored procedure to add or drop SQL server logins
> into SQL servers based on some logics users give to me. My stored
> procedure is on one server. I need to add logins to other servers
> through linked server.
> Does anybody know how to run sp_addlogin to add SQL logins into linked
> server?
Did you try
EXEC SOMESERVER.master.dbo.sp_addlogin=
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I just tried
exec wsdev.master.dbo.sp_addlogin 'test', 'test'
After this, I checked and saw the new login has been created. But I got the
following error message:
New login created.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should
be discarded.
I cannot use this in production system with error message like this. Any
further suggestions?
"Erland Sommarskog" wrote:

> BF (BF@.discussions.microsoft.com) writes:
> Did you try
> EXEC SOMESERVER.master.dbo.sp_addlogin=
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||BF (BF@.discussions.microsoft.com) writes:
> I just tried
> exec wsdev.master.dbo.sp_addlogin 'test', 'test'
> After this, I checked and saw the new login has been created. But I got
> the following error message:
> New login created.
> Msg 0, Level 11, State 0, Line 0
> A severe error occurred on the current command. The results, if any,
> should be discarded.
> I cannot use this in production system with error message like this. Any
> further suggestions?
I was not able to repeat this. Exactly which versions of SQL Server do
you have on the two servers? Use serverproperty('ProductVersion') to
determine this.
Anyway, the error message looks like you are running SQL 2005. In such
case you can use EXEC() AT:
EXEC ('EXEC master.dbo.sp_addlogin ''test'', ''test''') AT wsdev
If the target server is also running SQL 2005, you should not use
sp_addlogin at all, but rather CREATE LOGIN:
EXEC ('CREATE LOGIN test WITH PASSWORD = ''test''') AT wsdev
Note however, that this command will fail, because SQL 2005 validates
the password according to Windows policy. This mainly happens on SQL 2003,
but SQL 2005 always frowns at username = password.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I am using SQL Server 2000. The exact version is 8.00.760.
I cannot use EXEC AT on my server. But it's good to know this new feature on
SQL 2005.
Now I am thinking open 2 database connections from my .NET application to
access 2 different servers.
Thanks.
"Erland Sommarskog" wrote:

> BF (BF@.discussions.microsoft.com) writes:
> I was not able to repeat this. Exactly which versions of SQL Server do
> you have on the two servers? Use serverproperty('ProductVersion') to
> determine this.
> Anyway, the error message looks like you are running SQL 2005. In such
> case you can use EXEC() AT:
> EXEC ('EXEC master.dbo.sp_addlogin ''test'', ''test''') AT wsdev
> If the target server is also running SQL 2005, you should not use
> sp_addlogin at all, but rather CREATE LOGIN:
> EXEC ('CREATE LOGIN test WITH PASSWORD = ''test''') AT wsdev
> Note however, that this command will fail, because SQL 2005 validates
> the password according to Windows policy. This mainly happens on SQL 2003,
> but SQL 2005 always frowns at username = password.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||BF (BF@.discussions.microsoft.com) writes:
> I am using SQL Server 2000. The exact version is 8.00.760.
> I cannot use EXEC AT on my server. But it's good to know this new
> feature on SQL 2005.
> Now I am thinking open 2 database connections from my .NET application to
> access 2 different servers.
I assumed that you were on SQL 2005, because I took for granted that
you were running this from a query tool, which in that case would
be Management Studio which uses SqlClient.
Moral: please always be specific with the environment you are using.
As for the solution, this what I have recommended in the first place,
if I had known that you were doing this from an application. Relying
on linked servers is fragile, since the definition of a linked server
could change or disappear completely.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Additional IP for SQL Cluster

Hi,
We have a two node failover SQL 2000 cluster setup. These servers are on a
closed netowrk where they do not have access to internet or anything other
network. I now have a need to be able to connect to the SQL server from
another network. I had added the correct gateway etc and can assign a new IP
in the cluster for outside access. Everything work fine except that SQL will
not listen on this IP. I am assigning the new IP on the same NIC as my
cluster's public IP. Does anyone know who do I go about making SQL listen
on both IPs?
Thank you.
Hi
Have you made the IP address available as a resource to SQL Server?
John
"Dragon" wrote:

> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new IP
> in the cluster for outside access. Everything work fine except that SQL will
> not listen on this IP. I am assigning the new IP on the same NIC as my
> cluster's public IP. Does anyone know who do I go about making SQL listen
> on both IPs?
> Thank you.
>
>
|||Open the cluster administrator tool and add an IP Address resource to the
group that SQL has been installed in. You will need to have a different IP
bound to the NIC for this to work, but of course on that same subnet.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Dragon" <baadil_nospam@.hotmail.com> wrote in message
news:OHT8S4NCHHA.4680@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
> IP in the cluster for outside access. Everything work fine except that SQL
> will not listen on this IP. I am assigning the new IP on the same NIC as
> my cluster's public IP. Does anyone know who do I go about making SQL
> listen on both IPs?
> Thank you.
>
|||I read the other two responses, and although they were posted in the SS
cluster forum, they are clearly OS answers. They will not work.
Every Operation DBA knows that you cannot manage SQL Server 2000 Cluster IP
addresses through the Cluster Administrator APIs.
You must rerun the SQL Server 2000 Setup utility, Virtual Server, Advanced
Options, Manager Cluster Resources, and add the additional IP resources from
the Network Dialog.
The only other alternative would be to hack the registry (HKLM\Cluster and
HKLM\SOFTWARE\Microsoft, either Microsoft SQL
Server\MSSQL$InstanceName\MSSQLServer or MSSQLServer\MSSQLServer cluster
keys and resources).
Sincerely,
Anthony Thomas

"Dragon" <baadil_nospam@.hotmail.com> wrote in message
news:OHT8S4NCHHA.4680@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
IP
> in the cluster for outside access. Everything work fine except that SQL
will
> not listen on this IP. I am assigning the new IP on the same NIC as my
> cluster's public IP. Does anyone know who do I go about making SQL listen
> on both IPs?
> Thank you.
>

Additional IP for SQL Cluster

Hi,
We have a two node failover SQL 2000 cluster setup. These servers are on a
closed netowrk where they do not have access to internet or anything other
network. I now have a need to be able to connect to the SQL server from
another network. I had added the correct gateway etc and can assign a new IP
in the cluster for outside access. Everything work fine except that SQL will
not listen on this IP. I am assigning the new IP on the same NIC as my
cluster's public IP. Does anyone know who do I go about making SQL listen
on both IPs?
Thank you.Hi
Have you made the IP address available as a resource to SQL Server?
John
"Dragon" wrote:
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new IP
> in the cluster for outside access. Everything work fine except that SQL will
> not listen on this IP. I am assigning the new IP on the same NIC as my
> cluster's public IP. Does anyone know who do I go about making SQL listen
> on both IPs?
> Thank you.
>
>|||Open the cluster administrator tool and add an IP Address resource to the
group that SQL has been installed in. You will need to have a different IP
bound to the NIC for this to work, but of course on that same subnet.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Dragon" <baadil_nospam@.hotmail.com> wrote in message
news:OHT8S4NCHHA.4680@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
> IP in the cluster for outside access. Everything work fine except that SQL
> will not listen on this IP. I am assigning the new IP on the same NIC as
> my cluster's public IP. Does anyone know who do I go about making SQL
> listen on both IPs?
> Thank you.
>|||I read the other two responses, and although they were posted in the SS
cluster forum, they are clearly OS answers. They will not work.
Every Operation DBA knows that you cannot manage SQL Server 2000 Cluster IP
addresses through the Cluster Administrator APIs.
You must rerun the SQL Server 2000 Setup utility, Virtual Server, Advanced
Options, Manager Cluster Resources, and add the additional IP resources from
the Network Dialog.
The only other alternative would be to hack the registry (HKLM\Cluster and
HKLM\SOFTWARE\Microsoft, either Microsoft SQL
Server\MSSQL$InstanceName\MSSQLServer or MSSQLServer\MSSQLServer cluster
keys and resources).
Sincerely,
Anthony Thomas
"Dragon" <baadil_nospam@.hotmail.com> wrote in message
news:OHT8S4NCHHA.4680@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
IP
> in the cluster for outside access. Everything work fine except that SQL
will
> not listen on this IP. I am assigning the new IP on the same NIC as my
> cluster's public IP. Does anyone know who do I go about making SQL listen
> on both IPs?
> Thank you.
>

Additional IP for SQL Cluster

Hi,
We have a two node failover SQL 2000 cluster setup. These servers are on a
closed netowrk where they do not have access to internet or anything other
network. I now have a need to be able to connect to the SQL server from
another network. I had added the correct gateway etc and can assign a new IP
in the cluster for outside access. Everything work fine except that SQL will
not listen on this IP. I am assigning the new IP on the same NIC as my
cluster's public IP. Does anyone know who do I go about making SQL listen
on both IPs?
Thank you.
Hi
Have you made the IP address available as a resource to SQL Server?
John
"Dragon" wrote:

> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new IP
> in the cluster for outside access. Everything work fine except that SQL will
> not listen on this IP. I am assigning the new IP on the same NIC as my
> cluster's public IP. Does anyone know who do I go about making SQL listen
> on both IPs?
> Thank you.
>
>
|||Open the cluster administrator tool and add an IP Address resource to the
group that SQL has been installed in. You will need to have a different IP
bound to the NIC for this to work, but of course on that same subnet.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Dragon" <baadil_nospam@.hotmail.com> wrote in message
news:OHT8S4NCHHA.4680@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
> IP in the cluster for outside access. Everything work fine except that SQL
> will not listen on this IP. I am assigning the new IP on the same NIC as
> my cluster's public IP. Does anyone know who do I go about making SQL
> listen on both IPs?
> Thank you.
>
|||I read the other two responses, and although they were posted in the SS
cluster forum, they are clearly OS answers. They will not work.
Every Operation DBA knows that you cannot manage SQL Server 2000 Cluster IP
addresses through the Cluster Administrator APIs.
You must rerun the SQL Server 2000 Setup utility, Virtual Server, Advanced
Options, Manager Cluster Resources, and add the additional IP resources from
the Network Dialog.
The only other alternative would be to hack the registry (HKLM\Cluster and
HKLM\SOFTWARE\Microsoft, either Microsoft SQL
Server\MSSQL$InstanceName\MSSQLServer or MSSQLServer\MSSQLServer cluster
keys and resources).
Sincerely,
Anthony Thomas

"Dragon" <baadil_nospam@.hotmail.com> wrote in message
news:OHT8S4NCHHA.4680@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
IP
> in the cluster for outside access. Everything work fine except that SQL
will
> not listen on this IP. I am assigning the new IP on the same NIC as my
> cluster's public IP. Does anyone know who do I go about making SQL listen
> on both IPs?
> Thank you.
>
sql

Additional IP for SQL Cluster

Hi,
We have a two node failover SQL 2000 cluster setup. These servers are on a
closed netowrk where they do not have access to internet or anything other
network. I now have a need to be able to connect to the SQL server from
another network. I had added the correct gateway etc and can assign a new IP
in the cluster for outside access. Everything work fine except that SQL will
not listen on this IP. I am assigning the new IP on the same NIC as my
cluster's public IP. Does anyone know who do I go about making SQL listen
on both IPs?
Thank you.Hi
Have you made the IP address available as a resource to SQL Server?
John
"Dragon" wrote:

> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
IP
> in the cluster for outside access. Everything work fine except that SQL wi
ll
> not listen on this IP. I am assigning the new IP on the same NIC as my
> cluster's public IP. Does anyone know who do I go about making SQL listen
> on both IPs?
> Thank you.
>
>|||Open the cluster administrator tool and add an IP Address resource to the
group that SQL has been installed in. You will need to have a different IP
bound to the NIC for this to work, but of course on that same subnet.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Dragon" <baadil_nospam@.hotmail.com> wrote in message
news:OHT8S4NCHHA.4680@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
> IP in the cluster for outside access. Everything work fine except that SQL
> will not listen on this IP. I am assigning the new IP on the same NIC as
> my cluster's public IP. Does anyone know who do I go about making SQL
> listen on both IPs?
> Thank you.
>|||I read the other two responses, and although they were posted in the SS
cluster forum, they are clearly OS answers. They will not work.
Every Operation DBA knows that you cannot manage SQL Server 2000 Cluster IP
addresses through the Cluster Administrator APIs.
You must rerun the SQL Server 2000 Setup utility, Virtual Server, Advanced
Options, Manager Cluster Resources, and add the additional IP resources from
the Network Dialog.
The only other alternative would be to hack the registry (HKLM\Cluster and
HKLM\SOFTWARE\Microsoft, either Microsoft SQL
Server\MSSQL$InstanceName\MSSQLServer or MSSQLServer\MSSQLServer cluster
keys and resources).
Sincerely,
Anthony Thomas
"Dragon" <baadil_nospam@.hotmail.com> wrote in message
news:OHT8S4NCHHA.4680@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a two node failover SQL 2000 cluster setup. These servers are on a
> closed netowrk where they do not have access to internet or anything other
> network. I now have a need to be able to connect to the SQL server from
> another network. I had added the correct gateway etc and can assign a new
IP
> in the cluster for outside access. Everything work fine except that SQL
will
> not listen on this IP. I am assigning the new IP on the same NIC as my
> cluster's public IP. Does anyone know who do I go about making SQL listen
> on both IPs?
> Thank you.
>

Adding SSIS to sql 2005 cluster

The basics
Win2k Enterprise
SQL 2005 Enterprise
2 node cluster, single instance.
So I have had these servers up for a little while and would like to add SSIS
to them.
I found this document here about running setup from the command line for a
cluster
http://msdn2.microsoft.com/en-gb/library/ms144259.aspx
this document for installing SSIS from the command line (not in a cluster)
http://msdn2.microsoft.com/en-gb/library/ms144291.aspx
and this about installing SSIS (not in a cluster)
http://support.microsoft.com/kb/913967
and this about how to configure SSIS on a cluster.
http://msdn2.microsoft.com/en-gb/library/ms345193.aspx
As you can see from this last document, step three seems trivial,
"3. Install Integration Services on each node of the cluster individually."
Can anyone help me put this all together.
I think the main thing I need is the command line to install SSIS on a
cluster.
Also, if anyone knows of any good books on HA SQL, that would be nice, I
looked at a stack of SQL2005 books last night and was surprised to find that
HA and Clustering are hardly mentioned.
Chris
The Installer for SSIS is "not" cluster aware.
You will need to install SSIS on each node of the cluster.
If you install it on Node 1 while the SQL server is running on that
node, the installer will install SSIS on Node 1 only.
You will have to run the install a second time on Node 2, preferably
while the SQL is running on Node 2
Once you have SSIS installed on both nodes, you can follow the
instructions in the document on configuring SSIS on a cluster,
remembering that after you make any changes to the MsDtsSrvr.ini.xml
file you need to restart the SSIS service . That should work in your
configuration. It gets real tricky when you have an active/active setup
(I am still struggling with that myself)
Sorry I cant help with the command line, perhaps a more command line
savy person can help with that.
CDSuperG wrote:
> The basics
> Win2k Enterprise
> SQL 2005 Enterprise
> 2 node cluster, single instance.
> So I have had these servers up for a little while and would like to add SSIS
> to them.
> I found this document here about running setup from the command line for a
> cluster
> http://msdn2.microsoft.com/en-gb/library/ms144259.aspx
> this document for installing SSIS from the command line (not in a cluster)
> http://msdn2.microsoft.com/en-gb/library/ms144291.aspx
> and this about installing SSIS (not in a cluster)
> http://support.microsoft.com/kb/913967
> and this about how to configure SSIS on a cluster.
> http://msdn2.microsoft.com/en-gb/library/ms345193.aspx
> As you can see from this last document, step three seems trivial,
> "3. Install Integration Services on each node of the cluster individually."
> Can anyone help me put this all together.
> I think the main thing I need is the command line to install SSIS on a
> cluster.
> Also, if anyone knows of any good books on HA SQL, that would be nice, I
> looked at a stack of SQL2005 books last night and was surprised to find that
> HA and Clustering are hardly mentioned.
> Chris
|||You can visit www.jumpstarttv.com. There is a good video on the site. You
will have to register before you can see the site, but I did and it helped a
lot.
"Jim" wrote:

> The Installer for SSIS is "not" cluster aware.
> You will need to install SSIS on each node of the cluster.
> If you install it on Node 1 while the SQL server is running on that
> node, the installer will install SSIS on Node 1 only.
> You will have to run the install a second time on Node 2, preferably
> while the SQL is running on Node 2
> Once you have SSIS installed on both nodes, you can follow the
> instructions in the document on configuring SSIS on a cluster,
> remembering that after you make any changes to the MsDtsSrvr.ini.xml
> file you need to restart the SSIS service . That should work in your
> configuration. It gets real tricky when you have an active/active setup
> (I am still struggling with that myself)
> Sorry I cant help with the command line, perhaps a more command line
> savy person can help with that.
> CDSuperG wrote:
>

Adding INDEX causes DEADLOCKS?

I have a very small table (50 rows, 50 columns).
I have 4 servers using the table:
1 server updates the table ("UPDATE table SET col1='', col2='',
LastUpdate=GetDate()").. about 20 rows updated a second.
3 servers pull data ("SELECT * FROM table Where
LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
times a second each.
Prior to last week, i did not have the lastupdate column. but it
obviously made sense to me to add it (why pull everything every 100ms
when you can only pull what you need via the LastUpdate, maybe 3-4
rows at a time). so i add the lastupdate column and make an index on
it.
now as soon as i run it, every 2-4 minutes i get a deadlock (!).
execution path is showing a bookmark lookup with index seek because i
have two conditions in the where clause: "lastupdate>(time
oflastrequest) and COL3>0". i have tried making an index based upon
both lastUpdate,Col3 but the results are the same (lots of deadlocks)
I have removed the index, kept the lastupdate column, same queries,
and everything works fine -- except, of course, that it is doing a
table scan for every lookup. the table will be growing soon and i
will be in trouble. so i need some help here, any ideas?
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
You update the whole table every time? Why?
|||"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.co m...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
Easiest way to solve these problems is with more locking. I know, it sounds
weird, but more locking will separate the deadlocking processes and properly
serialize them. I suggest in your Update statement add a TABLOCKX hint.
That will exclusively lock the entire table for the duration of the update.
This will also give you more predictable results as the table will only be
read when it is in a completely consistent state between updates.
David
|||The deadlock is due to that for the 3 server pulling data, it needs to get
page/row lock on the non-clustered index first, then get page/row lock on
the base table(while holding the lock on the non-clustered index), while the
update gets lock on base table first, then the lock on page/row in the
non-clustered index. To break the circle, you can either create a clustered
index on the LastUpdate field instead of a non-clustered index, or either
specify TABLOCKX hint for the update or specify readpast lock hint for the
reader.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.co m...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
|||"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:u4Gn6SuQEHA.2408@.tk2msftngp13.phx.gbl...
> The deadlock is due to that for the 3 server pulling data, it needs to get
> page/row lock on the non-clustered index first, then get page/row lock on
> the base table(while holding the lock on the non-clustered index), while
the
> update gets lock on base table first, then the lock on page/row in the
> non-clustered index. To break the circle, you can either create a
clustered
> index on the LastUpdate field instead of a non-clustered index, or either
> specify TABLOCKX hint for the update or specify readpast lock hint for the
> reader.
> --
> Gang He
> SQL Server Storage Engine Development
Good info. Thanks for the post Gang.
David

Adding INDEX causes DEADLOCKS?

I have a very small table (50 rows, 50 columns).
I have 4 servers using the table:
1 server updates the table ("UPDATE table SET col1='', col2='',
LastUpdate=GetDate()").. about 20 rows updated a second.
3 servers pull data ("SELECT * FROM table Where
LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
times a second each.
Prior to last week, i did not have the lastupdate column. but it
obviously made sense to me to add it (why pull everything every 100ms
when you can only pull what you need via the LastUpdate, maybe 3-4
rows at a time). so i add the lastupdate column and make an index on
it.
now as soon as i run it, every 2-4 minutes i get a deadlock (!).
execution path is showing a bookmark lookup with index seek because i
have two conditions in the where clause: "lastupdate>(time
oflastrequest) and COL3>0". i have tried making an index based upon
both lastUpdate,Col3 but the results are the same (lots of deadlocks)
I have removed the index, kept the lastupdate column, same queries,
and everything works fine -- except, of course, that it is doing a
table scan for every lookup. the table will be growing soon and i
will be in trouble. so i need some help here, any ideas?> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
You update the whole table every time? Why?|||"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.com...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
Easiest way to solve these problems is with more locking. I know, it sounds
weird, but more locking will separate the deadlocking processes and properly
serialize them. I suggest in your Update statement add a TABLOCKX hint.
That will exclusively lock the entire table for the duration of the update.
This will also give you more predictable results as the table will only be
read when it is in a completely consistent state between updates.
David|||The deadlock is due to that for the 3 server pulling data, it needs to get
page/row lock on the non-clustered index first, then get page/row lock on
the base table(while holding the lock on the non-clustered index), while the
update gets lock on base table first, then the lock on page/row in the
non-clustered index. To break the circle, you can either create a clustered
index on the LastUpdate field instead of a non-clustered index, or either
specify TABLOCKX hint for the update or specify readpast lock hint for the
reader.
--
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.com...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?|||"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:u4Gn6SuQEHA.2408@.tk2msftngp13.phx.gbl...
> The deadlock is due to that for the 3 server pulling data, it needs to get
> page/row lock on the non-clustered index first, then get page/row lock on
> the base table(while holding the lock on the non-clustered index), while
the
> update gets lock on base table first, then the lock on page/row in the
> non-clustered index. To break the circle, you can either create a
clustered
> index on the LastUpdate field instead of a non-clustered index, or either
> specify TABLOCKX hint for the update or specify readpast lock hint for the
> reader.
> --
> Gang He
> SQL Server Storage Engine Development
Good info. Thanks for the post Gang.
David

Adding INDEX causes DEADLOCKS?

I have a very small table (50 rows, 50 columns).
I have 4 servers using the table:
1 server updates the table ("UPDATE table SET col1='', col2='',
LastUpdate=GetDate()").. about 20 rows updated a second.
3 servers pull data ("SELECT * FROM table Where
LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
times a second each.
Prior to last week, i did not have the lastupdate column. but it
obviously made sense to me to add it (why pull everything every 100ms
when you can only pull what you need via the LastUpdate, maybe 3-4
rows at a time). so i add the lastupdate column and make an index on
it.
now as soon as i run it, every 2-4 minutes i get a deadlock (!).
execution path is showing a bookmark lookup with index seek because i
have two conditions in the where clause: "lastupdate>(time
oflastrequest) and COL3>0". i have tried making an index based upon
both lastUpdate,Col3 but the results are the same (lots of deadlocks)
I have removed the index, kept the lastupdate column, same queries,
and everything works fine -- except, of course, that it is doing a
table scan for every lookup. the table will be growing soon and i
will be in trouble. so i need some help here, any ideas?> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
You update the whole table every time? Why?|||"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.com...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
Easiest way to solve these problems is with more locking. I know, it sounds
weird, but more locking will separate the deadlocking processes and properly
serialize them. I suggest in your Update statement add a TABLOCKX hint.
That will exclusively lock the entire table for the duration of the update.
This will also give you more predictable results as the table will only be
read when it is in a completely consistent state between updates.
David|||The deadlock is due to that for the 3 server pulling data, it needs to get
page/row lock on the non-clustered index first, then get page/row lock on
the base table(while holding the lock on the non-clustered index), while the
update gets lock on base table first, then the lock on page/row in the
non-clustered index. To break the circle, you can either create a clustered
index on the LastUpdate field instead of a non-clustered index, or either
specify TABLOCKX hint for the update or specify readpast lock hint for the
reader.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.com...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?|||"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:u4Gn6SuQEHA.2408@.tk2msftngp13.phx.gbl...
> The deadlock is due to that for the 3 server pulling data, it needs to get
> page/row lock on the non-clustered index first, then get page/row lock on
> the base table(while holding the lock on the non-clustered index), while
the
> update gets lock on base table first, then the lock on page/row in the
> non-clustered index. To break the circle, you can either create a
clustered
> index on the LastUpdate field instead of a non-clustered index, or either
> specify TABLOCKX hint for the update or specify readpast lock hint for the
> reader.
> --
> Gang He
> SQL Server Storage Engine Development
Good info. Thanks for the post Gang.
David

adding fields

Win 2K & SQL 2K
I have several SQL servers replicating to one server and
I need to add a field to a replicated table can I....
Add the field to the subscriber, then via the Properties-
>Filter Columns->Add Column to Table add the same field
to the publisher?
Will this cause a problem with the other publishers when
they attempt to replicate and there is an addition al
field in the destination table, but has not been added on
all the publishers?
Logically, this seems like it will work. I really do not
want to drop all the publications, alter the tables, then
recreate all the publications with no sync.
HELP!!!
the preferred way to add a column to articles in an existing publication is
to use sp_repladdcolumn.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"larry" <anonymous@.discussions.microsoft.com> wrote in message
news:757d01c4765b$2188ee20$a401280a@.phx.gbl...
> Win 2K & SQL 2K
> I have several SQL servers replicating to one server and
> I need to add a field to a replicated table can I....
> Add the field to the subscriber, then via the Properties-
> to the publisher?
> Will this cause a problem with the other publishers when
> they attempt to replicate and there is an addition al
> field in the destination table, but has not been added on
> all the publishers?
> Logically, this seems like it will work. I really do not
> want to drop all the publications, alter the tables, then
> recreate all the publications with no sync.
> HELP!!!

Adding column to sp_helpuser

I am working on creating a configuration database. This will connect to many databases on many different servers. What I would like to do is run sp_MSForEachDB then store the name of the current server and database the package is connected to. Then add both of these into a column before running sp_helpuser. I don't know how confusing this is, but I think I may have the code worked out pretty well. If you have any questions, please post them and I will try to answer as best as I can. What I would like the ending result to look like is...

<server1> <database1> <userID1> <datareader>
<server1> <database1> <userID1> <datawriter>
<server1> <database1> <userID2> <datawriter>
<server1> <database2> <userID1> <datawriter>

I am at a loss, and I can't use the sys.<table> tables because it has to be backward compatible. Thanks for any help in advance.
sp_helplogins is the answer.
-Kyle

Adding column to sp_helpuser

I am working on creating a configuration database. This will connect to many databases on many different servers. What I would like to do is run sp_MSForEachDB then store the name of the current server and database the package is connected to. Then add both of these into a column before running sp_helpuser. I don't know how confusing this is, but I think I may have the code worked out pretty well. If you have any questions, please post them and I will try to answer as best as I can. What I would like the ending result to look like is...

<server1> <database1> <userID1> <datareader>
<server1> <database1> <userID1> <datawriter>
<server1> <database1> <userID2> <datawriter>
<server1> <database2> <userID1> <datawriter>

I am at a loss, and I can't use the sys.<table> tables because it has to be backward compatible. Thanks for any help in advance.
sp_helplogins is the answer.
-Kyle

Adding an additional Subscription to Merge Replication

I have two servers in a merge replication setup (linked every night using an ISDN line). I wish to add a new subscriber to this but I am not sure if I need to do a new snapshot.
I was thinking of using the latest backup from the publisher and recover this on the new subscriber - if I do this will i need
to run the snapshot again ? or will they all simply carry on with the merge...
Thanks
Gerry
you have to create a no-sync subcription which will run the snapshot only
for necessary merge replication metadata.
"Gerry" <anonymous@.discussions.microsoft.com> wrote in message
news:3490FE65-DF3F-401D-A6EE-78236E508677@.microsoft.com...
> I have two servers in a merge replication setup (linked every night using
an ISDN line). I wish to add a new subscriber to this but I am not sure if
I need to do a new snapshot.
> I was thinking of using the latest backup from the publisher and recover
this on the new subscriber - if I do this will i need
> to run the snapshot again ? or will they all simply carry on with the
merge...
> Thanks
> Gerry
|||How do I create/define a no-sync subscription ? Does it get defined when I run the
Push Subscription Wizard in the usual way?
Thanks
Gerry
|||You have to select an option that no data needs to be synchronized
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Gerry" <anonymous@.discussions.microsoft.com> wrote in message
news:0709ECB3-8396-4593-A423-93761B4137EE@.microsoft.com...
> How do I create/define a no-sync subscription ? Does it get defined when I
run the
> Push Subscription Wizard in the usual way?
> Thanks
> Gerry
>

Adding an additional CPU SQL 2005 Enterprise

Hi,
I have 2 SQL 2005 Enterprise servers currently running one a single CPU
license. I have now purchased an additional enterprise CPU license and would
like to `activate` an additional CPU.
How would I do this?
Many thanks
RichardIf you had more than 1 processor on the machine you were violating the
license agreement to begin with. If you have 2 then you need two licenses
regardless of how many you intended to use. The license does not prohibit or
enable the use of the processors.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Richard Thompson" <sbc@.thompson.co.za> wrote in message
news:8378184B-64EB-473B-81AE-DD54AE2A6CAF@.microsoft.com...
> Hi,
> I have 2 SQL 2005 Enterprise servers currently running one a single CPU
> license. I have now purchased an additional enterprise CPU license and
> would like to `activate` an additional CPU.
> How would I do this?
> Many thanks
> Richard|||Ok, thanks for that. So I now have a 2 CPU license I can just add the second
CPU and it will be fine no need to worry about changing settings in SQL at
all.
Thanks a lot,
Richard
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9Ipsq3$HHA.5652@.TK2MSFTNGP05.phx.gbl...
> If you had more than 1 processor on the machine you were violating the
> license agreement to begin with. If you have 2 then you need two licenses
> regardless of how many you intended to use. The license does not prohibit
> or enable the use of the processors.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Richard Thompson" <sbc@.thompson.co.za> wrote in message
> news:8378184B-64EB-473B-81AE-DD54AE2A6CAF@.microsoft.com...
>> Hi,
>> I have 2 SQL 2005 Enterprise servers currently running one a single CPU
>> license. I have now purchased an additional enterprise CPU license and
>> would like to `activate` an additional CPU.
>> How would I do this?
>> Many thanks
>> Richard
>|||By adding the 2nd CPU do you mean physically installing one? If so then
yes, the OS & SQL Server should detect the 2nd CPU and it will know what to
do without changing any settings unless you already changed some from the
defaults.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Richard Thompson" <sbc@.thompson.co.za> wrote in message
news:31A8B061-7F53-4EB2-AA6A-F96AB2103149@.microsoft.com...
> Ok, thanks for that. So I now have a 2 CPU license I can just add the
> second CPU and it will be fine no need to worry about changing settings in
> SQL at all.
> Thanks a lot,
> Richard
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9Ipsq3$HHA.5652@.TK2MSFTNGP05.phx.gbl...
>> If you had more than 1 processor on the machine you were violating the
>> license agreement to begin with. If you have 2 then you need two licenses
>> regardless of how many you intended to use. The license does not prohibit
>> or enable the use of the processors.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Richard Thompson" <sbc@.thompson.co.za> wrote in message
>> news:8378184B-64EB-473B-81AE-DD54AE2A6CAF@.microsoft.com...
>> Hi,
>> I have 2 SQL 2005 Enterprise servers currently running one a single CPU
>> license. I have now purchased an additional enterprise CPU license and
>> would like to `activate` an additional CPU.
>> How would I do this?
>> Many thanks
>> Richard
>