parametrize linked server name

• kob uki

  Mr or Mrs. 500

  Points: 508

  More actions

  October 23, 2008 at 6:52 pm

  #195059

  Hi,

  Is it possible to parametrize a remote procedure through a linked server or a query through a linked server?

  I am trying to parametrize the name of the linked server in something like this:

  select count(*) from REMOTESERVER.database.dbo.table

  How can I do that?

  Thanks,

  Janos

• krayknot

  SSCertifiable

  Points: 7998

  More actions

  October 23, 2008 at 10:41 pm

  #889026

  Janos Horanszky (10/23/2008)

  ---

  Hi,

  Is it possible to parametrize a remote procedure through a linked server or a query through a linked server?

  I am trying to parametrize the name of the linked server in something like this:

  select count(*) from REMOTESERVER.database.dbo.table

  How can I do that?

  Thanks,

  Janos

  yes you can

  kshitij kumar
  kshitij@krayknot.com
  www.krayknot.com

• kob uki

  Mr or Mrs. 500

  Points: 508

  More actions

  October 24, 2008 at 11:07 am

  #889367

  For example:

  declare @remoteserver nvarchar(50)

  declare @cmd nvarchar(255)

  declare @rc int

  declare @nCount int

  select @remoteserver = 'SYED_ALI,1533'

  set @cmd = 'select @nCount = count(*) from ['+@remoteserver+'].chartwellgame.dbo.CountPlaceholder (nolock)'

  exec sp_executesql @cmd, N'@nCount int out', @nCount out

  select @nCount as cnt

  gives me:

  Msg 4122, Level 16, State 1, Line 1

  Remote table-valued function calls are not allowed.

  cnt

  -----------

  NULL

  Why is that?

• kob uki

  Mr or Mrs. 500

  Points: 508

  More actions

  October 24, 2008 at 11:20 am

  #889372

  Oh, okay, it does not support the (nolock) hint.

  If I take it out, it works!