linked server within if statement

• Robert Klimes

  SSCoach

  Points: 17865

  More actions

  January 4, 2011 at 11:48 am

  #232578

  I have come across a situation where the query within an IF statement seem to be executing even though it should not be (fails the IF). It only seems to be an issue if a linked server is used and that linked server is off-line.

  I am really just looking for an explanation as to why and secondly a possible workaround.

  here is an example.

  create linked server.

  EXEC master.dbo.sp_addlinkedserver @server = N'link', @srvproduct=N'SQL Server'

  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'link',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

  select from linked server within an if statement

  declare @i varchar(10)

  set @i = 'Y'

  print @i

  if @i = 'N'

  begin

  select * from .[database].[dbo].

  end

  print @i

  if you remove the linked server, but leave the rest of the invalid table name, the statement executes as expected.

  declare @i varchar(10)

  set @i = 'Y'

  print @i

  if @i = 'N'

  begin

  select * from [database].[dbo].

  end

  print @i

  Bob
  -----------------------------------------------------------------------------
  How to post to get the best help[/url]

• anuraganu.tiwari

  SSC Enthusiast

  Points: 106

  More actions

  January 4, 2011 at 4:42 pm

  #1269264

  Because you have the same table in local server also. Linked Server Failure Issues[/url]

  -anu

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  January 4, 2011 at 6:23 pm

  #1269272

  I don't know for sure but it seems like SQL server is trying to connect to the linked server before it runs the query.

  IF you enclose the call to the linked server in an EXEC('select * from .[database].[schema].

  ') then the query operates as you would like it to...

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]