Problem With Remotely Executing Stored Procedures

• EvilPostIT

  SSCrazy

  Points: 2578

  More actions

  January 25, 2006 at 5:39 am

  #91393

  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.

  ---

  
  Nuke the site from orbit, its the only way to be sure... :w00t:

• January 30, 2006 at 8:00 am

  This was removed by the editor as SPAM

• TriggerMe

  SSChampion

  Points: 11752

  More actions

  January 30, 2006 at 3:58 pm

  #617847

  Any reason why you are using Dynamic SQL to achieve this simple query?

  Although the above may work in my mind it would be simpler if you just used the four part naming convention like this;

  SELECT * FROM [SERVERNAME].master.dbo.sysdatabases

  ---

  Kindest Regards,

• EvilPostIT

  SSCrazy

  Points: 2578

  More actions

  February 6, 2006 at 3:30 am

  #618916

  The reason i am using this is because i am remotely execting stored procedures for example....

  declare @servername varchar(255)

  declare @cmd nvarchar(500)

  set @SERVERNAME = 'SERVERNAME'

  #CREATE A LINKED SERVER#

  set @cmd = 'exec '+@servername+'.master..sp_who2'

  exec sp_executesql @cmd

   

  This has now been fixed due to the following

  This is down to MSDTC having problems with "Use NT Fibres" which is now turned off and has not hung since.

  ---

  
  Nuke the site from orbit, its the only way to be sure... :w00t: