When I execute the SQL Statement in the SQL Query Analyzer, I get the result
s
returned in 4 seconds but when I use the same query from a stored procedure
and execute it from SQL Analyzer, it is taking too long about 50-55 seconds.
Am I missing something? I have a join between external and internal table.
Thanks!!
Vibs
SELECT
AAPSUser.[ID]
, AAPSUser.First_Name
, AAPSUser.Last_Name
, AAPSUser.Second_Sections,
[UserExtraInfo].[title],
[UserExtraInfo].[profile1_text],
[UserExtraInfo].[profile2_text],
[UserExtraInfo].[profile3_text]
FROM
AAPS.AAPS_Web.DBO.iMIS_Data as AAPSUser
INNER JOIN UserExtraInfo ON AAPSUser.[ID] = UserExtraInfo.Id
WHERE
AAPSUser.[ID] = 003479Are you running the query and the proc containg the query locally i.e, same
SQL Server? (Same security creditials as well?) I.e, log into QA and run
the query then directly below (same window) exectue the proc (same query in
proc right?). You can also compare the execution plans as well for any
visable differences.
HTH
Jerry
"vibs" <vibs@.discussions.microsoft.com> wrote in message
news:6DC8F102-2C0F-4CCE-8831-1F228BAF81AC@.microsoft.com...
> When I execute the SQL Statement in the SQL Query Analyzer, I get the
> results
> returned in 4 seconds but when I use the same query from a stored
> procedure
> and execute it from SQL Analyzer, it is taking too long about 50-55
> seconds.
> Am I missing something? I have a join between external and internal table.
> Thanks!!
> Vibs
> SELECT
> AAPSUser.[ID]
> , AAPSUser.First_Name
> , AAPSUser.Last_Name
> , AAPSUser.Second_Sections,
> [UserExtraInfo].[title],
> [UserExtraInfo].[profile1_text],
> [UserExtraInfo].[profile2_text],
> [UserExtraInfo].[profile3_text]
> FROM
> AAPS.AAPS_Web.DBO.iMIS_Data as AAPSUser
> INNER JOIN UserExtraInfo ON AAPSUser.[ID] = UserExtraInfo.Id
> WHERE
> AAPSUser.[ID] = 003479|||Yes the query and the proc is running from the same query analyzer window on
the same SQL Server. I'll look into the execution plans
Thanks
"Jerry Spivey" wrote:
> Are you running the query and the proc containg the query locally i.e, sam
e
> SQL Server? (Same security creditials as well?) I.e, log into QA and run
> the query then directly below (same window) exectue the proc (same query i
n
> proc right?). You can also compare the execution plans as well for any
> visable differences.
> HTH
> Jerry
> "vibs" <vibs@.discussions.microsoft.com> wrote in message
> news:6DC8F102-2C0F-4CCE-8831-1F228BAF81AC@.microsoft.com...
>
>
Showing posts with label stored. Show all posts
Showing posts with label stored. Show all posts
Friday, March 30, 2012
linked server problem with @@SERVERNAME
Hey all,
In a stored procedure that retrieves data from a linked server, I want to
do a simple 'test' for availability before attempting to carry out the SP's
tasks: The linked server is 'defined' as its IP.
DECLARE @.serverUP varchar(100)
SELECT @.serverUP = ( SELECT @.@.SERVERNAME
FROM [xxx.xxx.xxx.xxx] )
BEGIN
IF LEN(@.serverUP) > 0
I can use the IP like this in SELECTs when a four-part name is used, like
[xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
"Invalid object name 'xxx.xxx.xxx.xxx'."
If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME for
the linked server. So I must be missing something here about the reference
in the FROM clause; or can I not even use @.@.SERVERNAME like this?
Any ideas, or suggestions for a better way to check for 'availability'?
Thanks.
Message posted via http://www.droptable.comThe brackets should be used t identify a single expression in the four part
name becasue sql server will else assume that thi is a local object which
name is [xxx.xxx.xxx.xxx], so you hould go for that:
xxx.xxx.xxx.xxx or [xxx].[xxx].[xxx].[xxx]
Furthmore I would write the query as below:
SELECT @.serverUP = @.@.SERVERNAME FROM [xxx.xxx.xxx.xxx]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:4c9f835ab5bd47dabc4d2b22996d8564@.SQ
droptable.com...
> Hey all,
> In a stored procedure that retrieves data from a linked server, I want to
> do a simple 'test' for availability before attempting to carry out the
> SP's
> tasks: The linked server is 'defined' as its IP.
> DECLARE @.serverUP varchar(100)
> SELECT @.serverUP = ( SELECT @.@.SERVERNAME
> FROM [xxx.xxx.xxx.xxx] )
> BEGIN
> IF LEN(@.serverUP) > 0
> I can use the IP like this in SELECTs when a four-part name is used, like
> [xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error
:
> "Invalid object name 'xxx.xxx.xxx.xxx'."
> If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME
> for
> the linked server. So I must be missing something here about the reference
> in the FROM clause; or can I not even use @.@.SERVERNAME like this?
> Any ideas, or suggestions for a better way to check for 'availability'?
> Thanks.
> --
> Message posted via http://www.droptable.com|||Thanks, Jens. With some more trial and error I found that this works to get
what I was after:
SELECT @.serverUP = ( SELECT SRVNAME
FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
I appreciate your feedback.
Message posted via http://www.droptable.com|||Ah ok, the xxx.xxx.xxx.xxx defined the ip adress, i jst thought that would
be the fout-partname of the object.
Nevertheless i would perfer the pattern as i mentioned in my previous post
with the inline set off the variable.
Jens.
"The Gekkster via droptable.com" <forum@.droptable.com> schrieb im
Newsbeitrag news:1c50dd0567b84192aea0ff47adf7d99a@.SQ
droptable.com...
> Thanks, Jens. With some more trial and error I found that this works to
> get
> what I was after:
> SELECT @.serverUP = ( SELECT SRVNAME
> FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
> I appreciate your feedback.
> --
> Message posted via http://www.droptable.com
In a stored procedure that retrieves data from a linked server, I want to
do a simple 'test' for availability before attempting to carry out the SP's
tasks: The linked server is 'defined' as its IP.
DECLARE @.serverUP varchar(100)
SELECT @.serverUP = ( SELECT @.@.SERVERNAME
FROM [xxx.xxx.xxx.xxx] )
BEGIN
IF LEN(@.serverUP) > 0
I can use the IP like this in SELECTs when a four-part name is used, like
[xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
"Invalid object name 'xxx.xxx.xxx.xxx'."
If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME for
the linked server. So I must be missing something here about the reference
in the FROM clause; or can I not even use @.@.SERVERNAME like this?
Any ideas, or suggestions for a better way to check for 'availability'?
Thanks.
Message posted via http://www.droptable.comThe brackets should be used t identify a single expression in the four part
name becasue sql server will else assume that thi is a local object which
name is [xxx.xxx.xxx.xxx], so you hould go for that:
xxx.xxx.xxx.xxx or [xxx].[xxx].[xxx].[xxx]
Furthmore I would write the query as below:
SELECT @.serverUP = @.@.SERVERNAME FROM [xxx.xxx.xxx.xxx]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:4c9f835ab5bd47dabc4d2b22996d8564@.SQ
droptable.com...
> Hey all,
> In a stored procedure that retrieves data from a linked server, I want to
> do a simple 'test' for availability before attempting to carry out the
> SP's
> tasks: The linked server is 'defined' as its IP.
> DECLARE @.serverUP varchar(100)
> SELECT @.serverUP = ( SELECT @.@.SERVERNAME
> FROM [xxx.xxx.xxx.xxx] )
> BEGIN
> IF LEN(@.serverUP) > 0
> I can use the IP like this in SELECTs when a four-part name is used, like
> [xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error
:
> "Invalid object name 'xxx.xxx.xxx.xxx'."
> If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME
> for
> the linked server. So I must be missing something here about the reference
> in the FROM clause; or can I not even use @.@.SERVERNAME like this?
> Any ideas, or suggestions for a better way to check for 'availability'?
> Thanks.
> --
> Message posted via http://www.droptable.com|||Thanks, Jens. With some more trial and error I found that this works to get
what I was after:
SELECT @.serverUP = ( SELECT SRVNAME
FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
I appreciate your feedback.
Message posted via http://www.droptable.com|||Ah ok, the xxx.xxx.xxx.xxx defined the ip adress, i jst thought that would
be the fout-partname of the object.
Nevertheless i would perfer the pattern as i mentioned in my previous post
with the inline set off the variable.
Jens.
"The Gekkster via droptable.com" <forum@.droptable.com> schrieb im
Newsbeitrag news:1c50dd0567b84192aea0ff47adf7d99a@.SQ
droptable.com...
> Thanks, Jens. With some more trial and error I found that this works to
> get
> what I was after:
> SELECT @.serverUP = ( SELECT SRVNAME
> FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
> I appreciate your feedback.
> --
> Message posted via http://www.droptable.com
linked server problem with @@SERVERNAME
Hey all,
In a stored procedure that retrieves data from a linked server, I want to
do a simple 'test' for availability before attempting to carry out the SP's
tasks: The linked server is 'defined' as its IP.
DECLARE @.serverUP varchar(100)
SELECT @.serverUP = ( SELECT @.@.SERVERNAME
FROM [xxx.xxx.xxx.xxx] )
BEGIN
IF LEN(@.serverUP) > 0
I can use the IP like this in SELECTs when a four-part name is used, like
[xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
"Invalid object name 'xxx.xxx.xxx.xxx'."
If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME for
the linked server. So I must be missing something here about the reference
in the FROM clause; or can I not even use @.@.SERVERNAME like this?
Any ideas, or suggestions for a better way to check for 'availability'?
Thanks.
--
Message posted via http://www.sqlmonster.comThe brackets should be used t identify a single expression in the four part
name becasue sql server will else assume that thi is a local object which
name is [xxx.xxx.xxx.xxx], so you hould go for that:
xxx.xxx.xxx.xxx or [xxx].[xxx].[xxx].[xxx]
Furthmore I would write the query as below:
SELECT @.serverUP = @.@.SERVERNAME FROM [xxx.xxx.xxx.xxx]
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"The Gekkster via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:4c9f835ab5bd47dabc4d2b22996d8564@.SQLMonster.com...
> Hey all,
> In a stored procedure that retrieves data from a linked server, I want to
> do a simple 'test' for availability before attempting to carry out the
> SP's
> tasks: The linked server is 'defined' as its IP.
> DECLARE @.serverUP varchar(100)
> SELECT @.serverUP = ( SELECT @.@.SERVERNAME
> FROM [xxx.xxx.xxx.xxx] )
> BEGIN
> IF LEN(@.serverUP) > 0
> I can use the IP like this in SELECTs when a four-part name is used, like
> [xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
> "Invalid object name 'xxx.xxx.xxx.xxx'."
> If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME
> for
> the linked server. So I must be missing something here about the reference
> in the FROM clause; or can I not even use @.@.SERVERNAME like this?
> Any ideas, or suggestions for a better way to check for 'availability'?
> Thanks.
> --
> Message posted via http://www.sqlmonster.com|||Thanks, Jens. With some more trial and error I found that this works to get
what I was after:
SELECT @.serverUP = ( SELECT SRVNAME
FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
I appreciate your feedback.
--
Message posted via http://www.sqlmonster.com|||Ah ok, the xxx.xxx.xxx.xxx defined the ip adress, i jst thought that would
be the fout-part´name of the object.
Nevertheless i would perfer the pattern as i mentioned in my previous post
with the inline set off the variable.
Jens.
"The Gekkster via SQLMonster.com" <forum@.SQLMonster.com> schrieb im
Newsbeitrag news:1c50dd0567b84192aea0ff47adf7d99a@.SQLMonster.com...
> Thanks, Jens. With some more trial and error I found that this works to
> get
> what I was after:
> SELECT @.serverUP = ( SELECT SRVNAME
> FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
> I appreciate your feedback.
> --
> Message posted via http://www.sqlmonster.com
In a stored procedure that retrieves data from a linked server, I want to
do a simple 'test' for availability before attempting to carry out the SP's
tasks: The linked server is 'defined' as its IP.
DECLARE @.serverUP varchar(100)
SELECT @.serverUP = ( SELECT @.@.SERVERNAME
FROM [xxx.xxx.xxx.xxx] )
BEGIN
IF LEN(@.serverUP) > 0
I can use the IP like this in SELECTs when a four-part name is used, like
[xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
"Invalid object name 'xxx.xxx.xxx.xxx'."
If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME for
the linked server. So I must be missing something here about the reference
in the FROM clause; or can I not even use @.@.SERVERNAME like this?
Any ideas, or suggestions for a better way to check for 'availability'?
Thanks.
--
Message posted via http://www.sqlmonster.comThe brackets should be used t identify a single expression in the four part
name becasue sql server will else assume that thi is a local object which
name is [xxx.xxx.xxx.xxx], so you hould go for that:
xxx.xxx.xxx.xxx or [xxx].[xxx].[xxx].[xxx]
Furthmore I would write the query as below:
SELECT @.serverUP = @.@.SERVERNAME FROM [xxx.xxx.xxx.xxx]
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"The Gekkster via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:4c9f835ab5bd47dabc4d2b22996d8564@.SQLMonster.com...
> Hey all,
> In a stored procedure that retrieves data from a linked server, I want to
> do a simple 'test' for availability before attempting to carry out the
> SP's
> tasks: The linked server is 'defined' as its IP.
> DECLARE @.serverUP varchar(100)
> SELECT @.serverUP = ( SELECT @.@.SERVERNAME
> FROM [xxx.xxx.xxx.xxx] )
> BEGIN
> IF LEN(@.serverUP) > 0
> I can use the IP like this in SELECTs when a four-part name is used, like
> [xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
> "Invalid object name 'xxx.xxx.xxx.xxx'."
> If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME
> for
> the linked server. So I must be missing something here about the reference
> in the FROM clause; or can I not even use @.@.SERVERNAME like this?
> Any ideas, or suggestions for a better way to check for 'availability'?
> Thanks.
> --
> Message posted via http://www.sqlmonster.com|||Thanks, Jens. With some more trial and error I found that this works to get
what I was after:
SELECT @.serverUP = ( SELECT SRVNAME
FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
I appreciate your feedback.
--
Message posted via http://www.sqlmonster.com|||Ah ok, the xxx.xxx.xxx.xxx defined the ip adress, i jst thought that would
be the fout-part´name of the object.
Nevertheless i would perfer the pattern as i mentioned in my previous post
with the inline set off the variable.
Jens.
"The Gekkster via SQLMonster.com" <forum@.SQLMonster.com> schrieb im
Newsbeitrag news:1c50dd0567b84192aea0ff47adf7d99a@.SQLMonster.com...
> Thanks, Jens. With some more trial and error I found that this works to
> get
> what I was after:
> SELECT @.serverUP = ( SELECT SRVNAME
> FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
> I appreciate your feedback.
> --
> Message posted via http://www.sqlmonster.com
linked server problem with @@SERVERNAME
Hey all,
In a stored procedure that retrieves data from a linked server, I want to
do a simple 'test' for availability before attempting to carry out the SP's
tasks: The linked server is 'defined' as its IP.
DECLARE @.serverUP varchar(100)
SELECT @.serverUP = ( SELECT @.@.SERVERNAME
FROM [xxx.xxx.xxx.xxx] )
BEGIN
IF LEN(@.serverUP) > 0
I can use the IP like this in SELECTs when a four-part name is used, like
[xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
"Invalid object name 'xxx.xxx.xxx.xxx'."
If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME for
the linked server. So I must be missing something here about the reference
in the FROM clause; or can I not even use @.@.SERVERNAME like this?
Any ideas, or suggestions for a better way to check for 'availability'?
Thanks.
Message posted via http://www.droptable.com
The brackets should be used t identify a single expression in the four part
name becasue sql server will else assume that thi is a local object which
name is [xxx.xxx.xxx.xxx], so you hould go for that:
xxx.xxx.xxx.xxx or [xxx].[xxx].[xxx].[xxx]
Furthmore I would write the query as below:
SELECT @.serverUP = @.@.SERVERNAME FROM [xxx.xxx.xxx.xxx]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:4c9f835ab5bd47dabc4d2b22996d8564@.droptable.co m...
> Hey all,
> In a stored procedure that retrieves data from a linked server, I want to
> do a simple 'test' for availability before attempting to carry out the
> SP's
> tasks: The linked server is 'defined' as its IP.
> DECLARE @.serverUP varchar(100)
> SELECT @.serverUP = ( SELECT @.@.SERVERNAME
> FROM [xxx.xxx.xxx.xxx] )
> BEGIN
> IF LEN(@.serverUP) > 0
> I can use the IP like this in SELECTs when a four-part name is used, like
> [xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
> "Invalid object name 'xxx.xxx.xxx.xxx'."
> If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME
> for
> the linked server. So I must be missing something here about the reference
> in the FROM clause; or can I not even use @.@.SERVERNAME like this?
> Any ideas, or suggestions for a better way to check for 'availability'?
> Thanks.
> --
> Message posted via http://www.droptable.com
|||Thanks, Jens. With some more trial and error I found that this works to get
what I was after:
SELECT @.serverUP = ( SELECT SRVNAME
FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
I appreciate your feedback.
Message posted via http://www.droptable.com
|||Ah ok, the xxx.xxx.xxx.xxx defined the ip adress, i jst thought that would
be the fout-partname of the object.
Nevertheless i would perfer the pattern as i mentioned in my previous post
with the inline set off the variable.
Jens.
"The Gekkster via droptable.com" <forum@.droptable.com> schrieb im
Newsbeitrag news:1c50dd0567b84192aea0ff47adf7d99a@.droptable.co m...
> Thanks, Jens. With some more trial and error I found that this works to
> get
> what I was after:
> SELECT @.serverUP = ( SELECT SRVNAME
> FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
> I appreciate your feedback.
> --
> Message posted via http://www.droptable.com
sql
In a stored procedure that retrieves data from a linked server, I want to
do a simple 'test' for availability before attempting to carry out the SP's
tasks: The linked server is 'defined' as its IP.
DECLARE @.serverUP varchar(100)
SELECT @.serverUP = ( SELECT @.@.SERVERNAME
FROM [xxx.xxx.xxx.xxx] )
BEGIN
IF LEN(@.serverUP) > 0
I can use the IP like this in SELECTs when a four-part name is used, like
[xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
"Invalid object name 'xxx.xxx.xxx.xxx'."
If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME for
the linked server. So I must be missing something here about the reference
in the FROM clause; or can I not even use @.@.SERVERNAME like this?
Any ideas, or suggestions for a better way to check for 'availability'?
Thanks.
Message posted via http://www.droptable.com
The brackets should be used t identify a single expression in the four part
name becasue sql server will else assume that thi is a local object which
name is [xxx.xxx.xxx.xxx], so you hould go for that:
xxx.xxx.xxx.xxx or [xxx].[xxx].[xxx].[xxx]
Furthmore I would write the query as below:
SELECT @.serverUP = @.@.SERVERNAME FROM [xxx.xxx.xxx.xxx]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:4c9f835ab5bd47dabc4d2b22996d8564@.droptable.co m...
> Hey all,
> In a stored procedure that retrieves data from a linked server, I want to
> do a simple 'test' for availability before attempting to carry out the
> SP's
> tasks: The linked server is 'defined' as its IP.
> DECLARE @.serverUP varchar(100)
> SELECT @.serverUP = ( SELECT @.@.SERVERNAME
> FROM [xxx.xxx.xxx.xxx] )
> BEGIN
> IF LEN(@.serverUP) > 0
> I can use the IP like this in SELECTs when a four-part name is used, like
> [xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
> "Invalid object name 'xxx.xxx.xxx.xxx'."
> If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME
> for
> the linked server. So I must be missing something here about the reference
> in the FROM clause; or can I not even use @.@.SERVERNAME like this?
> Any ideas, or suggestions for a better way to check for 'availability'?
> Thanks.
> --
> Message posted via http://www.droptable.com
|||Thanks, Jens. With some more trial and error I found that this works to get
what I was after:
SELECT @.serverUP = ( SELECT SRVNAME
FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
I appreciate your feedback.
Message posted via http://www.droptable.com
|||Ah ok, the xxx.xxx.xxx.xxx defined the ip adress, i jst thought that would
be the fout-partname of the object.
Nevertheless i would perfer the pattern as i mentioned in my previous post
with the inline set off the variable.
Jens.
"The Gekkster via droptable.com" <forum@.droptable.com> schrieb im
Newsbeitrag news:1c50dd0567b84192aea0ff47adf7d99a@.droptable.co m...
> Thanks, Jens. With some more trial and error I found that this works to
> get
> what I was after:
> SELECT @.serverUP = ( SELECT SRVNAME
> FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
> I appreciate your feedback.
> --
> Message posted via http://www.droptable.com
sql
Wednesday, March 28, 2012
Linked Server problem when querying
I have a linked SQL Server on another machine which is created using a stored procedure that executes the following:
EXEC sp_addlinkedserver @.server = 'Achilles\Mixed', @.srvproduct = ' ', @.provider = 'SQLNCLI', @.datasrc = 'Archilles\Mixed', @.catalog = 'DB_INTRANET'
The stored procedure executes successfully and I can also succesfully DROP the linked server. However when I try to query tables in linked databases using:
SELECT * FROM DB_INTRANET...Employees
I get the following error:
OLE DB provider "SQLNCLI" for linked server "DB_Intranet" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0 Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
The logins are Windows Authentication and even if I use sp_addlinkedsrvlogin to map logins it still gives me the same error.
I have no problems linking and querying linked Access Databases but can't do it for the SQL Server DBs.
Does anyone have any suggestions please.
Hi,
the mapping of the logins did not work. What command did you use to use the WIndows login on the other server ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Please check two things:
1. make sure SQL 2005 allow remote access
2. make sure you set right userid/password for linked server.
Thanks
|||Hi,
I have the same problem. But the distributed query seems to work on the management studio of the server but bot accross the network on other management studio with the same impersonated logins.
|||I had to create a SQL authenticated login on the remote server (loginname) and then all local logins are mapped to the one remote SQL login using the Security page of the Linked Servers Properties dialogue.
I could not get it to pass Windows authenticated logins to the remote server at all.
In the Security Page of Linked Servers Properties dialogue.
For a Login not defined in the above list Connections will:
Be made using this security context:
Remote Login: Loginname
With Password: LoginPassword
Linked server problem
Hi,
I am connecting two sql servers together as linked server, the servers are running windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connection on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
my transaction details:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) ro prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller), and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connection! In case more cocurrent connections are
issued, the connection are blocked almost immediately and never returns till the execution times out. The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conduct separately from any other activity on the SQL servers. i.e. they was the only connections on the servers.
2) When we moved the the Linked Server database to the local server, i.e. we removed the linked server, all connections completed their commands execution successfully and without timeouts. Also, note that I have ran the test successfully, on the local server, with 256 and 512 connections.
I do highly appreciate any help or support on this.
Regards,Any one can help?|||This is beyond my ability to test. I've never seen the problem with any configuration that I use, so I'd suspect that it is either an interaction between the software versions that you are using or something specific to your code.
My advice is to open an incident with Microsoft Premier Support. If the problem is in your code, it will cost you roughly $1000 US to prove it. If the problem is in their code, it won't cost you a thing and you'll have had some pretty valuable review of your code and configuration, plus you'll get a solution (usually a patch) for the problem too!
-PatP|||You could set a max on the Query Timeout for the linked server. It would still take awhile to resolve. The bigger questions is what's causing the locking in the first place? Is it always one process that's causing this or one area such as a particular table?
I am connecting two sql servers together as linked server, the servers are running windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connection on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
my transaction details:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) ro prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller), and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connection! In case more cocurrent connections are
issued, the connection are blocked almost immediately and never returns till the execution times out. The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conduct separately from any other activity on the SQL servers. i.e. they was the only connections on the servers.
2) When we moved the the Linked Server database to the local server, i.e. we removed the linked server, all connections completed their commands execution successfully and without timeouts. Also, note that I have ran the test successfully, on the local server, with 256 and 512 connections.
I do highly appreciate any help or support on this.
Regards,Any one can help?|||This is beyond my ability to test. I've never seen the problem with any configuration that I use, so I'd suspect that it is either an interaction between the software versions that you are using or something specific to your code.
My advice is to open an incident with Microsoft Premier Support. If the problem is in your code, it will cost you roughly $1000 US to prove it. If the problem is in their code, it won't cost you a thing and you'll have had some pretty valuable review of your code and configuration, plus you'll get a solution (usually a patch) for the problem too!
-PatP|||You could set a max on the Query Timeout for the linked server. It would still take awhile to resolve. The bigger questions is what's causing the locking in the first place? Is it always one process that's causing this or one area such as a particular table?
linked server problem
Hi all, I am hitting up against a wall when trying to execute a stored proc
running on a remote linked sql server. I have verified that stored proc
executes fine locally on the remote server. The remote server name is
"brserver-i". Here is what I get:-
exec BRSERVER-I.master.dbo.sp_dothework
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'I'.
Any ideas?Try this.
exec [BRSERVER-I].master.dbo.sp_dothework
<param@.community.nospam> wrote in message
news:u8BANJsOFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Hi all, I am hitting up against a wall when trying to execute a stored
> proc running on a remote linked sql server. I have verified that stored
> proc executes fine locally on the remote server. The remote server name is
> "brserver-i". Here is what I get:-
> exec BRSERVER-I.master.dbo.sp_dothework
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'I'.
>
> Any ideas?
>|||Do not use "-" to name databases, I have found errors with other application
s
trying to access a database like this one.
exec [BRSERVER-I].master.dbo.sp_dothework
go
AMB
"param@.community.nospam" wrote:
> Hi all, I am hitting up against a wall when trying to execute a stored pro
c
> running on a remote linked sql server. I have verified that stored proc
> executes fine locally on the remote server. The remote server name is
> "brserver-i". Here is what I get:-
> exec BRSERVER-I.master.dbo.sp_dothework
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'I'.
>
> Any ideas?
>
>|||Sure. Qualify the object using the four-part name.
insert into server_name.database_name.owner.object_name
select ...
AMB
"param@.community.nospam" wrote:
> Hi all, I am hitting up against a wall when trying to execute a stored pro
c
> running on a remote linked sql server. I have verified that stored proc
> executes fine locally on the remote server. The remote server name is
> "brserver-i". Here is what I get:-
> exec BRSERVER-I.master.dbo.sp_dothework
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'I'.
>
> Any ideas?
>
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Sure. Qualify the object using the four-part name.
> insert into server_name.database_name.owner.object_name
> select ...
>
> AMB
> "param@.community.nospam" wrote:
>|||Looks like this worked too:-
exec "BRSERVER-I".master.dbo.sp_dothework
thanks!
"ykchakri" <ykchakri@.hotmail.com> wrote in message
news:O22TQPsOFHA.1096@.tk2msftngp13.phx.gbl...
> Try this.
> exec [BRSERVER-I].master.dbo.sp_dothework
> <param@.community.nospam> wrote in message
> news:u8BANJsOFHA.3380@.TK2MSFTNGP15.phx.gbl...
>|||But in order it to work you should have the setting of SET QUOTED_IDENTIFIER
set to ON.
Example:
SET QUOTED_IDENTIFIER OFF
go
use "master"
go
SET QUOTED_IDENTIFIER ON
go
AMB
"param@.community.nospam" wrote:
> Looks like this worked too:-
> exec "BRSERVER-I".master.dbo.sp_dothework
> thanks!
> "ykchakri" <ykchakri@.hotmail.com> wrote in message
> news:O22TQPsOFHA.1096@.tk2msftngp13.phx.gbl...
>
>|||You are correct. thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:928D7DC3-D605-429E-AF7E-BA3921057E7F@.microsoft.com...
> But in order it to work you should have the setting of SET
> QUOTED_IDENTIFIER
> set to ON.
> Example:
> SET QUOTED_IDENTIFIER OFF
> go
> use "master"
> go
> SET QUOTED_IDENTIFIER ON
> go
>
> AMB
>
> "param@.community.nospam" wrote:
>
running on a remote linked sql server. I have verified that stored proc
executes fine locally on the remote server. The remote server name is
"brserver-i". Here is what I get:-
exec BRSERVER-I.master.dbo.sp_dothework
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'I'.
Any ideas?Try this.
exec [BRSERVER-I].master.dbo.sp_dothework
<param@.community.nospam> wrote in message
news:u8BANJsOFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Hi all, I am hitting up against a wall when trying to execute a stored
> proc running on a remote linked sql server. I have verified that stored
> proc executes fine locally on the remote server. The remote server name is
> "brserver-i". Here is what I get:-
> exec BRSERVER-I.master.dbo.sp_dothework
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'I'.
>
> Any ideas?
>|||Do not use "-" to name databases, I have found errors with other application
s
trying to access a database like this one.
exec [BRSERVER-I].master.dbo.sp_dothework
go
AMB
"param@.community.nospam" wrote:
> Hi all, I am hitting up against a wall when trying to execute a stored pro
c
> running on a remote linked sql server. I have verified that stored proc
> executes fine locally on the remote server. The remote server name is
> "brserver-i". Here is what I get:-
> exec BRSERVER-I.master.dbo.sp_dothework
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'I'.
>
> Any ideas?
>
>|||Sure. Qualify the object using the four-part name.
insert into server_name.database_name.owner.object_name
select ...
AMB
"param@.community.nospam" wrote:
> Hi all, I am hitting up against a wall when trying to execute a stored pro
c
> running on a remote linked sql server. I have verified that stored proc
> executes fine locally on the remote server. The remote server name is
> "brserver-i". Here is what I get:-
> exec BRSERVER-I.master.dbo.sp_dothework
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'I'.
>
> Any ideas?
>
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Sure. Qualify the object using the four-part name.
> insert into server_name.database_name.owner.object_name
> select ...
>
> AMB
> "param@.community.nospam" wrote:
>|||Looks like this worked too:-
exec "BRSERVER-I".master.dbo.sp_dothework
thanks!
"ykchakri" <ykchakri@.hotmail.com> wrote in message
news:O22TQPsOFHA.1096@.tk2msftngp13.phx.gbl...
> Try this.
> exec [BRSERVER-I].master.dbo.sp_dothework
> <param@.community.nospam> wrote in message
> news:u8BANJsOFHA.3380@.TK2MSFTNGP15.phx.gbl...
>|||But in order it to work you should have the setting of SET QUOTED_IDENTIFIER
set to ON.
Example:
SET QUOTED_IDENTIFIER OFF
go
use "master"
go
SET QUOTED_IDENTIFIER ON
go
AMB
"param@.community.nospam" wrote:
> Looks like this worked too:-
> exec "BRSERVER-I".master.dbo.sp_dothework
> thanks!
> "ykchakri" <ykchakri@.hotmail.com> wrote in message
> news:O22TQPsOFHA.1096@.tk2msftngp13.phx.gbl...
>
>|||You are correct. thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:928D7DC3-D605-429E-AF7E-BA3921057E7F@.microsoft.com...
> But in order it to work you should have the setting of SET
> QUOTED_IDENTIFIER
> set to ON.
> Example:
> SET QUOTED_IDENTIFIER OFF
> go
> use "master"
> go
> SET QUOTED_IDENTIFIER ON
> go
>
> AMB
>
> "param@.community.nospam" wrote:
>
Linked server Problem
Hi,
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain databases. All settings and
users are same in all databases.
For eg: My query is
'select * from sqlserver1.db1.dbo.table1'.
For some database, I getting following error
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLSERVER1' does not contain
table '"db1"."dbo"."table1"'. The table either does not
exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SQLSERVER1',
TableName='"db1"."dbo"."table1"'].
Any help would very much appreciated.
Thanks in advance.
Rajah V.Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx
.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain databases. All settings and
> users are same in all databases.
> For eg: My query is
> 'select * from sqlserver1.db1.dbo.table1'.
> For some database, I getting following error
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'SQLSERVER1' does not contain
> table '"db1"."dbo"."table1"'. The table either does not
> exist or the current user does not have permissions on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider
> does not contain the table: ProviderName='SQLSERVER1',
> TableName='"db1"."dbo"."table1"'].
> Any help would very much appreciated.
> Thanks in advance.
> Rajah V.|||Also make sure that SA is the owner in all of the DB's. I have seen issues
like this when you have different users owning the different DB's
Jeff
MCDBA, MCSE+I|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.
>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in
>message news:1874a01c422f6$2d04e050$a001280a@.phx
.gbl...
key[vbcol=seagreen]
and[vbcol=seagreen]
not[vbcol=seagreen]
provider[vbcol=seagreen]
>
>.
>|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.
>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>sql
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain databases. All settings and
users are same in all databases.
For eg: My query is
'select * from sqlserver1.db1.dbo.table1'.
For some database, I getting following error
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLSERVER1' does not contain
table '"db1"."dbo"."table1"'. The table either does not
exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SQLSERVER1',
TableName='"db1"."dbo"."table1"'].
Any help would very much appreciated.
Thanks in advance.
Rajah V.Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx
.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain databases. All settings and
> users are same in all databases.
> For eg: My query is
> 'select * from sqlserver1.db1.dbo.table1'.
> For some database, I getting following error
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'SQLSERVER1' does not contain
> table '"db1"."dbo"."table1"'. The table either does not
> exist or the current user does not have permissions on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider
> does not contain the table: ProviderName='SQLSERVER1',
> TableName='"db1"."dbo"."table1"'].
> Any help would very much appreciated.
> Thanks in advance.
> Rajah V.|||Also make sure that SA is the owner in all of the DB's. I have seen issues
like this when you have different users owning the different DB's
Jeff
MCDBA, MCSE+I|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.
>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in
>message news:1874a01c422f6$2d04e050$a001280a@.phx
.gbl...
key[vbcol=seagreen]
and[vbcol=seagreen]
not[vbcol=seagreen]
provider[vbcol=seagreen]
>
>.
>|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.
>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>sql
Linked server Problem
Hi,
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain databases. All settings and
users are same in all databases.
For eg: My query is
'select * from sqlserver1.db1.dbo.table1'.
For some database, I getting following error
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLSERVER1' does not contain
table '"db1"."dbo"."table1"'. The table either does not
exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SQLSERVER1',
TableName='"db1"."dbo"."table1"'].
Any help would very much appreciated.
Thanks in advance.
Rajah V.
Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain databases. All settings and
> users are same in all databases.
> For eg: My query is
> 'select * from sqlserver1.db1.dbo.table1'.
> For some database, I getting following error
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'SQLSERVER1' does not contain
> table '"db1"."dbo"."table1"'. The table either does not
> exist or the current user does not have permissions on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider
> does not contain the table: ProviderName='SQLSERVER1',
> TableName='"db1"."dbo"."table1"'].
> Any help would very much appreciated.
> Thanks in advance.
> Rajah V.
|||Also make sure that SA is the owner in all of the DB's. I have seen issues like this when you have different users owning the different DB's
Jeff
MCDBA, MCSE+I
|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.
>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in[vbcol=seagreen]
>message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
key[vbcol=seagreen]
and[vbcol=seagreen]
not[vbcol=seagreen]
provider
>
>.
>
|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.
>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain databases. All settings and
users are same in all databases.
For eg: My query is
'select * from sqlserver1.db1.dbo.table1'.
For some database, I getting following error
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLSERVER1' does not contain
table '"db1"."dbo"."table1"'. The table either does not
exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SQLSERVER1',
TableName='"db1"."dbo"."table1"'].
Any help would very much appreciated.
Thanks in advance.
Rajah V.
Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain databases. All settings and
> users are same in all databases.
> For eg: My query is
> 'select * from sqlserver1.db1.dbo.table1'.
> For some database, I getting following error
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'SQLSERVER1' does not contain
> table '"db1"."dbo"."table1"'. The table either does not
> exist or the current user does not have permissions on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider
> does not contain the table: ProviderName='SQLSERVER1',
> TableName='"db1"."dbo"."table1"'].
> Any help would very much appreciated.
> Thanks in advance.
> Rajah V.
|||Also make sure that SA is the owner in all of the DB's. I have seen issues like this when you have different users owning the different DB's
Jeff
MCDBA, MCSE+I
|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.
>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in[vbcol=seagreen]
>message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
key[vbcol=seagreen]
and[vbcol=seagreen]
not[vbcol=seagreen]
provider
>
>.
>
|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.
>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>
Monday, March 26, 2012
Linked server Problem
Hi,
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain databases. All settings and
users are same in all databases.
For eg: My query is
'select * from sqlserver1.db1.dbo.table1'.
For some database, I getting following error
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLSERVER1' does not contain
table '"db1"."dbo"."table1"'. The table either does not
exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SQLSERVER1',
TableName='"db1"."dbo"."table1"'].
Any help would very much appreciated.
Thanks in advance.
Rajah V.Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain databases. All settings and
> users are same in all databases.
> For eg: My query is
> 'select * from sqlserver1.db1.dbo.table1'.
> For some database, I getting following error
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'SQLSERVER1' does not contain
> table '"db1"."dbo"."table1"'. The table either does not
> exist or the current user does not have permissions on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider
> does not contain the table: ProviderName='SQLSERVER1',
> TableName='"db1"."dbo"."table1"'].
> Any help would very much appreciated.
> Thanks in advance.
> Rajah V.|||Also make sure that SA is the owner in all of the DB's. I have seen issues like this when you have different users owning the different DB'
Jef
MCDBA, MCSE+I|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.
>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in
>message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
>> Hi,
>> We linked 2 sql servers and executing stored procedures
>> between the two servers.
>> We are using the 4 part key to reference a table.
>> We can able to execute sql statements with four part
key
>> from sqlserver1 to sqlserver2 for certain databases and
>> cannot able to do on certain databases. All settings
and
>> users are same in all databases.
>> For eg: My query is
>> 'select * from sqlserver1.db1.dbo.table1'.
>> For some database, I getting following error
>> Server: Msg 7314, Level 16, State 1, Line 1
>> OLE DB provider 'SQLSERVER1' does not contain
>> table '"db1"."dbo"."table1"'. The table either does
not
>> exist or the current user does not have permissions on
>> that table.
>> OLE DB error trace [Non-interface error: OLE DB
provider
>> does not contain the table: ProviderName='SQLSERVER1',
>> TableName='"db1"."dbo"."table1"'].
>> Any help would very much appreciated.
>> Thanks in advance.
>> Rajah V.
>
>.
>|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.
>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>
We linked 2 sql servers and executing stored procedures
between the two servers.
We are using the 4 part key to reference a table.
We can able to execute sql statements with four part key
from sqlserver1 to sqlserver2 for certain databases and
cannot able to do on certain databases. All settings and
users are same in all databases.
For eg: My query is
'select * from sqlserver1.db1.dbo.table1'.
For some database, I getting following error
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLSERVER1' does not contain
table '"db1"."dbo"."table1"'. The table either does not
exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SQLSERVER1',
TableName='"db1"."dbo"."table1"'].
Any help would very much appreciated.
Thanks in advance.
Rajah V.Does the user that you use to connect to the linked server actually have
permissions and access to this database?
Check this on the linked server user acounts.
Bojidar Alexandrov
"Rajah Venkata Krishnan" <anonymous@.discussions.microsoft.com> wrote in
message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
> Hi,
> We linked 2 sql servers and executing stored procedures
> between the two servers.
> We are using the 4 part key to reference a table.
> We can able to execute sql statements with four part key
> from sqlserver1 to sqlserver2 for certain databases and
> cannot able to do on certain databases. All settings and
> users are same in all databases.
> For eg: My query is
> 'select * from sqlserver1.db1.dbo.table1'.
> For some database, I getting following error
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'SQLSERVER1' does not contain
> table '"db1"."dbo"."table1"'. The table either does not
> exist or the current user does not have permissions on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider
> does not contain the table: ProviderName='SQLSERVER1',
> TableName='"db1"."dbo"."table1"'].
> Any help would very much appreciated.
> Thanks in advance.
> Rajah V.|||Also make sure that SA is the owner in all of the DB's. I have seen issues like this when you have different users owning the different DB'
Jef
MCDBA, MCSE+I|||Yes. User has permissions and access to the database.
Infact, user has db_owner permission.
>--Original Message--
>Does the user that you use to connect to the linked
server actually have
>permissions and access to this database?
>Check this on the linked server user acounts.
>Bojidar Alexandrov
>"Rajah Venkata Krishnan"
<anonymous@.discussions.microsoft.com> wrote in
>message news:1874a01c422f6$2d04e050$a001280a@.phx.gbl...
>> Hi,
>> We linked 2 sql servers and executing stored procedures
>> between the two servers.
>> We are using the 4 part key to reference a table.
>> We can able to execute sql statements with four part
key
>> from sqlserver1 to sqlserver2 for certain databases and
>> cannot able to do on certain databases. All settings
and
>> users are same in all databases.
>> For eg: My query is
>> 'select * from sqlserver1.db1.dbo.table1'.
>> For some database, I getting following error
>> Server: Msg 7314, Level 16, State 1, Line 1
>> OLE DB provider 'SQLSERVER1' does not contain
>> table '"db1"."dbo"."table1"'. The table either does
not
>> exist or the current user does not have permissions on
>> that table.
>> OLE DB error trace [Non-interface error: OLE DB
provider
>> does not contain the table: ProviderName='SQLSERVER1',
>> TableName='"db1"."dbo"."table1"'].
>> Any help would very much appreciated.
>> Thanks in advance.
>> Rajah V.
>
>.
>|||Actually,
System administrator is owner in all databases. But
primary owner is different for different databases.
ie., name shown dbo in user accounts are different from
SA.
Thanks .
Rajah V.
>--Original Message--
>Also make sure that SA is the owner in all of the DB's.
I have seen issues like this when you have different
users owning the different DB's
>Jeff
>MCDBA, MCSE+I
>.
>
Linked Server Performance with Stored Procedure
I have set up a stored procedure to do a quick lookup similar to
dbo.get_Value_for_Id @.id int
When I invoke the stored procedure from a database on the same server instance as the stored procedure lives, it processes 10K lookups in about 4 seconds.
When I invoke the stored procedure from a different server (via MSSM with a Linked server), it took about 4 minutes to perform 10K lookups. Not good. My question is what can I do to get back to the much faster response times.
I set up a little .Net app to do the remote calls, and it was back to the 4 second range for the 10K calls, in my mind eliminating the network as the primary bottleneck.
Items I've tried:
SET XACT_ABORT is OFF on both ends. No visible results.
SET REMOTE_PROC_TRANSACTIONS OFF . This appears to have put the distributed transactions issue to bed.
Run SP_SERVEROPTION "collation compatible" option to true for the server link. Ran this on both ends. This setting supposedly tells SQL Server the collation sequence and character sets match on both ends. Probably a good idea, even though it didn't make much difference here.
Run the stored procedure remotely using OpenQuery. Took some creativity, given OpenQuery doesn't accept variables as arguments. Got this one working, with run times down to the 90-135 second range (1:30-1:45).
I'm still an order of magnitude slower than I want to be. I suppose I could implement a .NET solution (select the key out of a database on server x, lookup the key on server y), but that sounds more like a workaround than actually using SQL Server capabilities. Any suggestions?
try
exec linkedserver.mydb.dbo.sp_executesql N'exec mysp'
|||Thanks for the suggestion, Nigel.
I setup two variations of the sp_executesql call, one calling the stored procedure, the second invoking the main select in the stored procedure. In both cases, when run during an MSSM session that was logged into the server that was doing the work, I could loop through 10000 calls in 4-5 seconds. And, in both cases, when logged into my desktop, it took about 4 and a half minutes to run the same script. So this didn't resolve the issue. Interestingly enough, I did see some surging in the activity of my desktop msdtc.exe (in windows task manager), so I'm guessing this process is somehow getting the distributed transaction server involved.
To confirm the involvement of the distributed transaction server, I went into my desktop and turned off the msdtc service. With this service turned off, the script would not run (MSG 8501 MSDTC on server xxx not available). So that seems to confirm I have not evaded the overhead of MSDTC.
To minimize confusion, following is the test script I am using...
set nocount on
set XACT_Abort off
set REMOTE_PROC_TRANSACTIONS off
declare @.Encrypt_id int
declare @.c int
declare @.t table (UnEncrypted_Card varchar(19))
declare @.sql nvarchar(500)
declare @.ParamDef nvarchar(500)
set @.c = 0
while (@.c < 10000)
Begin
set @.Encrypt_id = Rand() * 100000
set @.sql = N'exec dbo.p_Get_Card_Number_By_Id_inst @.Enc_id , ''123456'' ';
-- set @.sql = N'select Value_Encrypted as Value from dbo.tb_Encrypted_Store where Encrypt_Id = @.Enc_Id';
set @.ParamDef = N'@.Enc_Id int';
-- print @.sql
insert into @.t exec linkservername.dbname.dbo.sp_executesql @.sql, @.ParamDef, @.Enc_id = @.Encrypt_Id
set @.c = @.c + 1;
End
Linked Server Performance
I call a stored proc via a linked server like:
EXEC LINKEDSERVER.dbname.dbo.usp_myproc
The proc is very basic and executes immediately when called from the local
box. But when it is called as a linked server call, it takes 30-40 seconds
to execute.
The newtork traffic is not heavy. What are some things I should look at to
improve this performance?
Thanks
DaveDoes usp_myproc only work with data on the remote server ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"DaveF" <davef@.comcast.net> wrote in message
news:%23ekAsrpbDHA.3248@.tk2msftngp13.phx.gbl...
I call a stored proc via a linked server like:
EXEC LINKEDSERVER.dbname.dbo.usp_myproc
The proc is very basic and executes immediately when called from the local
box. But when it is called as a linked server call, it takes 30-40 seconds
to execute.
The newtork traffic is not heavy. What are some things I should look at to
improve this performance?
Thanks
Dave
EXEC LINKEDSERVER.dbname.dbo.usp_myproc
The proc is very basic and executes immediately when called from the local
box. But when it is called as a linked server call, it takes 30-40 seconds
to execute.
The newtork traffic is not heavy. What are some things I should look at to
improve this performance?
Thanks
DaveDoes usp_myproc only work with data on the remote server ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"DaveF" <davef@.comcast.net> wrote in message
news:%23ekAsrpbDHA.3248@.tk2msftngp13.phx.gbl...
I call a stored proc via a linked server like:
EXEC LINKEDSERVER.dbname.dbo.usp_myproc
The proc is very basic and executes immediately when called from the local
box. But when it is called as a linked server call, it takes 30-40 seconds
to execute.
The newtork traffic is not heavy. What are some things I should look at to
improve this performance?
Thanks
Dave
Friday, March 23, 2012
linked server msdb stored procedures
exec [ltd-gln-db2].msdb..sp_help_job
exec [ltd-gln-db2].msdb..sp_help_alert
Neither line fails.
The first returns 0 rows.
The second returns > 0 rows.
Both stored procedures allow 'public' to 'exec'.
Question: Why does sp_help_job return no rows?
Note: SQLServer 2000 SP2 on all db servers.Hi
Are there any jobs on the remote system?
As sp_help_job does not call sp_help_alert therefore I am not sure why you
should think sp_help_job would return anything if sp_help_alert does?
John
"Larry Storm" <larry.storm@.ltd.org> wrote in message
news:0c1901c5362c$251ac720$a401280a@.phx.gbl...
> exec [ltd-gln-db2].msdb..sp_help_job
> exec [ltd-gln-db2].msdb..sp_help_alert
> Neither line fails.
> The first returns 0 rows.
> The second returns > 0 rows.
> Both stored procedures allow 'public' to 'exec'.
> Question: Why does sp_help_job return no rows?
> Note: SQLServer 2000 SP2 on all db servers.
>
>|||
>--Original Message--
>Hi
>Are there any jobs on the remote system?
>As sp_help_job does not call sp_help_alert therefore I am
not sure why you
>should think sp_help_job would return anything if
sp_help_alert does?
>John
>"Larry Storm" <larry.storm@.ltd.org> wrote in message
>news:0c1901c5362c$251ac720$a401280a@.phx.gbl...
>
>.
>When I am connected to the remote system, sp_help_job
displays info for multiple jobs.
The reason I included information for sp_help_alert was to
show the remote server is accessable as a linked server and
permissions on stored procedures allow sp_help_alert to
return results.
My question remains:
Why does sp_help_job return zero rows?|||Hi
Try profiling the procedure and then see what the steps do when you log in
locally as that user.
John
"Larry Storm" <larry.storm@.ltd.org> wrote in message
news:1a0001c5392e$f9f0e4c0$a401280a@.phx.gbl...
>
> not sure why you
> sp_help_alert does?
> displays info for multiple jobs.
> The reason I included information for sp_help_alert was to
> show the remote server is accessable as a linked server and
> permissions on stored procedures allow sp_help_alert to
> return results.
> My question remains:
> Why does sp_help_job return zero rows?
>
>|||Larry Storm wrote:
> exec [ltd-gln-db2].msdb..sp_help_job
> exec [ltd-gln-db2].msdb..sp_help_alert
> Neither line fails.
> The first returns 0 rows.
> The second returns > 0 rows.
> Both stored procedures allow 'public' to 'exec'.
> Question: Why does sp_help_job return no rows?
> Note: SQLServer 2000 SP2 on all db servers.
>
From BOL: "A user who is not a member of the sy
min fixed role can use sp_
help_job to view only the jobs he/she owns."
HTH,
Igor
exec [ltd-gln-db2].msdb..sp_help_alert
Neither line fails.
The first returns 0 rows.
The second returns > 0 rows.
Both stored procedures allow 'public' to 'exec'.
Question: Why does sp_help_job return no rows?
Note: SQLServer 2000 SP2 on all db servers.Hi
Are there any jobs on the remote system?
As sp_help_job does not call sp_help_alert therefore I am not sure why you
should think sp_help_job would return anything if sp_help_alert does?
John
"Larry Storm" <larry.storm@.ltd.org> wrote in message
news:0c1901c5362c$251ac720$a401280a@.phx.gbl...
> exec [ltd-gln-db2].msdb..sp_help_job
> exec [ltd-gln-db2].msdb..sp_help_alert
> Neither line fails.
> The first returns 0 rows.
> The second returns > 0 rows.
> Both stored procedures allow 'public' to 'exec'.
> Question: Why does sp_help_job return no rows?
> Note: SQLServer 2000 SP2 on all db servers.
>
>|||
>--Original Message--
>Hi
>Are there any jobs on the remote system?
>As sp_help_job does not call sp_help_alert therefore I am
not sure why you
>should think sp_help_job would return anything if
sp_help_alert does?
>John
>"Larry Storm" <larry.storm@.ltd.org> wrote in message
>news:0c1901c5362c$251ac720$a401280a@.phx.gbl...
>
>.
>When I am connected to the remote system, sp_help_job
displays info for multiple jobs.
The reason I included information for sp_help_alert was to
show the remote server is accessable as a linked server and
permissions on stored procedures allow sp_help_alert to
return results.
My question remains:
Why does sp_help_job return zero rows?|||Hi
Try profiling the procedure and then see what the steps do when you log in
locally as that user.
John
"Larry Storm" <larry.storm@.ltd.org> wrote in message
news:1a0001c5392e$f9f0e4c0$a401280a@.phx.gbl...
>
> not sure why you
> sp_help_alert does?
> displays info for multiple jobs.
> The reason I included information for sp_help_alert was to
> show the remote server is accessable as a linked server and
> permissions on stored procedures allow sp_help_alert to
> return results.
> My question remains:
> Why does sp_help_job return zero rows?
>
>|||Larry Storm wrote:
> exec [ltd-gln-db2].msdb..sp_help_job
> exec [ltd-gln-db2].msdb..sp_help_alert
> Neither line fails.
> The first returns 0 rows.
> The second returns > 0 rows.
> Both stored procedures allow 'public' to 'exec'.
> Question: Why does sp_help_job return no rows?
> Note: SQLServer 2000 SP2 on all db servers.
>
From BOL: "A user who is not a member of the sy
min fixed role can use sp_help_job to view only the jobs he/she owns."
HTH,
Igor
Linked Server loses login-mapping
I use linked serve with (Security :be made with specific security conterxt) to connect to my production server from a Data Archive server.
Stored Procedures loop throught the prodcution data to insert records into Archive tables...Howerver after few hundreds of iteration I get the following error...
"Access to the remote server is denied because no login-mapping exists."
I am confused with the error message as it happens only after some successful insertions....
Any help is apprecaited...
Thanks
Please refer to BOL. A couple of links that hopefully will help you to get started:
· sp_addlinkedsrvlogin http://msdn2.microsoft.com/en-us/library/ms189811(SQL.90).aspx
· Distributed Queries Stored Procedures http://msdn2.microsoft.com/en-us/library/ms174359(SQL.90).aspx
If you have additional questions we will be glad to help.
-Raul Garcia
SDE/T
SQL Server Engine
Linked Server loses login-mapping
I use linked serve with (Security :be made with specific security conterxt) to connect to my production server from a Data Archive server.
Stored Procedures loop throught the prodcution data to insert records into Archive tables...Howerver after few hundreds of iteration I get the following error...
"Access to the remote server is denied because no login-mapping exists."
I am confused with the error message as it happens only after some successful insertions....
Any help is apprecaited...
Thanks
Please refer to BOL. A couple of links that hopefully will help you to get started:
· sp_addlinkedsrvlogin http://msdn2.microsoft.com/en-us/library/ms189811(SQL.90).aspx
· Distributed Queries Stored Procedures http://msdn2.microsoft.com/en-us/library/ms174359(SQL.90).aspx
If you have additional questions we will be glad to help.
-Raul Garcia
SDE/T
SQL Server Engine
Linked Server Locks!
Hi,
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali Salem
Could you reduce the code within the "transaction" which could hence reduce
the locks faster than keep it till the end of the transaction.
thanks.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali Salem
Could you reduce the code within the "transaction" which could hence reduce
the locks faster than keep it till the end of the transaction.
thanks.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Linked Server Locks!
Hi,
I am connecting two sql servers together as linked servers, the servers are
running on windows 2003.
I call a stored procedure defined on the linked server as part of a transact
ion and it works properly.
However, my problem appeared when I tried to stress test my application, the
connections on the linked server
started to block and never returns, however sql server didn't show any deadl
ock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock)
to prevent other transactions from retireving the same value till the curre
nt transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the tra
nsaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections
! In case more cocurrent connections are issued, the connections are blocked
almost immediately and never returns till the execution times out, note tha
t it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL serv
ers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e.
we removed the linked server, all connections completed their commands' exec
ution successfully and without timeouts. Also, note that I ran the test succ
essfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached
by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemCould you reduce the code within the "transaction" which could hence reduce
the locks faster than keep it till the end of the transaction.
thanks.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.sql
I am connecting two sql servers together as linked servers, the servers are
running on windows 2003.
I call a stored procedure defined on the linked server as part of a transact
ion and it works properly.
However, my problem appeared when I tried to stress test my application, the
connections on the linked server
started to block and never returns, however sql server didn't show any deadl
ock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock)
to prevent other transactions from retireving the same value till the curre
nt transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the tra
nsaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections
! In case more cocurrent connections are issued, the connections are blocked
almost immediately and never returns till the execution times out, note tha
t it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL serv
ers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e.
we removed the linked server, all connections completed their commands' exec
ution successfully and without timeouts. Also, note that I ran the test succ
essfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached
by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemCould you reduce the code within the "transaction" which could hence reduce
the locks faster than keep it till the end of the transaction.
thanks.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.sql
Linked Server Locks!
Hi,
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local server, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemCould you reduce the code within the "transaction" which could hence reduce
the locks faster than keep it till the end of the transaction.
thanks.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local server, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemCould you reduce the code within the "transaction" which could hence reduce
the locks faster than keep it till the end of the transaction.
thanks.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Linked Server Lockes - StressTesting
Hi,
I am connecting two sql servers together as linked servers, the servers are
running on windows 2003.
I call a stored procedure defined on the linked server as part of a transact
ion and it works properly.
However, my problem appeared when I tried to stress test my application, the
connections on the linked server
started to block and never returns, however sql server didn't show any deadl
ock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock)
to prevent other transactions from retireving the same value till the curre
nt transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the tra
nsaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections
! In case more cocurrent connections are issued, the connections are blocked
almost immediately and never returns till the execution times out, note tha
t it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL serv
ers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e.
we removed the linked server, all connections completed their commands' exec
ution successfully and without timeouts. Also, note that I ran the test succ
essfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached
by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemDo you run into blocking problems only on linked server queries? I'm
wondering if you are using any query hints. Another thing - please update
statistics. Also, you might want to keep your transactions smaller. For
example, how about having a separate trasnaction for the bit within the
stored proc where you are updating that row and commiting it immediately?
thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
I am connecting two sql servers together as linked servers, the servers are
running on windows 2003.
I call a stored procedure defined on the linked server as part of a transact
ion and it works properly.
However, my problem appeared when I tried to stress test my application, the
connections on the linked server
started to block and never returns, however sql server didn't show any deadl
ock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock)
to prevent other transactions from retireving the same value till the curre
nt transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the tra
nsaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections
! In case more cocurrent connections are issued, the connections are blocked
almost immediately and never returns till the execution times out, note tha
t it were not blocking for
a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL serv
ers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e.
we removed the linked server, all connections completed their commands' exec
ution successfully and without timeouts. Also, note that I ran the test succ
essfully, on the local serv
er, with 256 and 512 connections, whoch is alot more than the limit reached
by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemDo you run into blocking problems only on linked server queries? I'm
wondering if you are using any query hints. Another thing - please update
statistics. Also, you might want to keep your transactions smaller. For
example, how about having a separate trasnaction for the bit within the
stored proc where you are updating that row and commiting it immediately?
thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Linked Server Lockes - StressTesting
Hi,
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local server, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemDo you run into blocking problems only on linked server queries? I'm
wondering if you are using any query hints. Another thing - please update
statistics. Also, you might want to keep your transactions smaller. For
example, how about having a separate trasnaction for the bit within the
stored proc where you are updating that row and commiting it immediately?
thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
I am connecting two sql servers together as linked servers, the servers are running on windows 2003.
I call a stored procedure defined on the linked server as part of a transaction and it works properly.
However, my problem appeared when I tried to stress test my application, the connections on the linked server
started to block and never returns, however sql server didn't show any deadlock cases. Below are more details of
the applied scenario:
1) A transaction is opened on Server A
2) Server A executes a stored procedure on Server B (Linked Server)
3) The Linked Server SP performs the following:
a) Selects a number from a certain row in table X.
Note: Here an XLOCK is requested explicitly on this row (KEY Exclusive lock) to prevent other transactions from retireving the same value till the current transaction is committed and the next update takes place.
b) Updates the same Row queries in previous step.
c) Returns the read number to caller.
4) The execute continues on Server A (Caller) to perform the rest of the transaction, and then
5) The transaction is committed.
The above scenario is working properly for 53 and less cocurrent connections! In case more cocurrent connections are issued, the connections are blocked almost immediately and never returns till the execution times out, note that it were not blocking for a while and then the execution resumes, it seems to block forever.
The following was noticed on the Locks/Process ID list on EM:
1) One SPID (SPID 100) was displayed with status blocking.
2) SPID 101 was blocked by SPID 100.
3) All other SPIDs were blocked by SPID 101.
Please note that:
1) the test was conducted separately from any other activity on the SQL servers. i.e. the test copnnections were the only connections on the servers.
2) When we moved the the Linked Server's database to the local server, i.e. we removed the linked server, all connections completed their commands' execution successfully and without timeouts. Also, note that I ran the test successfully, on the local server, with 256 and 512 connections, whoch is alot more than the limit reached by linked server.
I do highly appreciate any help or support on this.
Regards,
Ali SalemDo you run into blocking problems only on linked server queries? I'm
wondering if you are using any query hints. Another thing - please update
statistics. Also, you might want to keep your transactions smaller. For
example, how about having a separate trasnaction for the bit within the
stored proc where you are updating that row and commiting it immediately?
thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Subscribe to:
Posts (Atom)