January 25, 2006 at 7:45 am
Hi,
I am having a problem with a linked server issue.
Every couple of weeks the following type of statement will starting hanging.
exec [SERVERNAME].master..sp_executesql N'select * from sysdatabases'
Where as the following code works fine and returns the results.
exec sp_executesql N'select * from [SERVERNAME].master.dbo.sysdatabases'
If anyone has seen anything like this before or has any ideas where to start looking to try to resolve this help would be much appreciated.
January 25, 2006 at 9:26 am
Hi,
I ran both statements with Profiler open. Both the local server and the linked server was the same SQL Server under different names.
In the first example I ran your first statement with Query Analyzer connected to the local server as SA and Linked Server Security Tab mapped local SA to Remote Login1 (SQL Server Standard Login)
exec [SERVERNAME].master..sp_executesql N'select * from sysdatabases'
The execution produced 2 lines: RPC by Application Microsoft SQL Server in the security context of SQL Server startup account System as Windows login and login1 as SQL login. The second line produced SQL Statement in the security context of my Windows(!!!) login as Windows login and SA as SQL login
RPC:Completed exec "master".."sp_executesql";1 N'select * from sysdatabases' Microsoft SQL Server' SYSTEM login1
SQL:BatchCompleted exec LinkedSERVERNAME.master..sp_executesql N'select * from sysdatabases' SQL Query Analyzer mywindowslogin sa
In the second example I ran your second statement. It returned only one statement line in the Profiler, but it was following many lines of RPC with prepare and uprepare, transaction stuff etc. in the security context of SQL SErver startup account System and Login1.
SQL:BatchCompleted exec sp_executesql N'select * from LinkedSERVERNAME.master.dbo.sysdatabases' SQL Query Analyzer MyWindowsLogin sa
SO I would run Profiler in your particular case and try to figure out what logins and protocols are involved. Maybe you think that you are working using only SQL Credentials, but your Windows credentials are involed too. And if Domain Controller is not available at this time the statement may fail. This is just a suggestion on how to test. But once you run Profiler, you will see that execution is completely different for your two statements.
Regards,Yelena Varsha
January 25, 2006 at 9:35 am
I have run SQL Profiler and i cannot find any useful information. I am using windows pass through authentication for this and in this scenario it works fine for about 2 weeks and then dies.
I then failover the cluster which it sits on and it works fine again.......
Domain controller is available.....
January 26, 2006 at 5:12 am
I am not sure this will be helpful but you might want to consider the data paths:
exec [SERVERNAME].master..sp_executesql N'select * from sysdatabases'
I think this will produce a query plan on [SERVERNAME] and then build the rowset on [SERVERNAME] and then send this down to Query analyser (or whatever) on your local server.
exec sp_executesql N'select * from [SERVERNAME].master.dbo.sysdatabases'
This will Produce a query plan on the local server, then build the rowset on your local server and then display that.
Is there a reason you cannot use the second version?
What happens if you try:
exec [SERVERNAME].master..sp_executesql N'select * from [SERVERNAME].master.dbo.sysdatabases'
(IE this may give you a clue as to whether it is [SERVERNAME].master..sp_executesql or N'select * from [SERVERNAME].master.dbo.sysdatabases that is hanging.
(Marvin)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply