Opendatasource query with parameter not working

• Henrico Bekker

  One Orange Chip

  Points: 27652

  More actions

  May 6, 2010 at 5:39 am

  #143045

  Hi All, I've tried this a million ways around...but nothing...

  I have a table with a list of all our SQL server names populated "dblist"

  I want to run the same query for each server name in the table (list).

  Pointing SQLNCLI directly works 100%, but not when passing the server name as a parameter.

  I've shortened the parameter part by just setting the @servername as a fixed value for testing - just for this forum, but it should work?

  --This works

  SELECT count(name) FROM OPENDATASOURCE('SQLNCLI',

  'Data Source=dev-server;Integrated Security=SSPI')

  .master.dbo.sysdatabases

  --This doesn't

  declare @servername nvarchar(30)

  Set @servername = 'dev-server'

  SELECT count(name) FROM OPENDATASOURCE('SQLNCLI',

  'Data Source=@servername;Integrated Security=SSPI')

  .master.dbo.sysdatabases

  Maybe I am missing something small, but can't see it...any ideas?

  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

• sql_lock

  SSCrazy Eights

  Points: 9375

  More actions

  May 6, 2010 at 5:45 am

  #1163087

  Have you tried?

  declare @servername nvarchar(30)

  Set @servername = 'dev-server'

  SELECT count(name) FROM OPENDATASOURCE('SQLNCLI',

  'Data Source=' + @servername + ' ;Integrated Security=SSPI')

  .master.dbo.sysdatabases

• Henrico Bekker

  One Orange Chip

  Points: 27652

  More actions

  May 6, 2010 at 5:57 am

  #1163092

  Yes I have, doesn't like the '+' s

  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

• Henrico Bekker

  One Orange Chip

  Points: 27652

  More actions

  May 6, 2010 at 6:20 am

  #1163111

  It acts the same when I provide a non-existing servername, which tell's me the @servername is not actually passing its defined value?

  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  May 6, 2010 at 6:35 am

  #1163124

  Try this Henri!

  DECLARE @SQLString nvarchar(500);

  DECLARE @ParmDefinition nvarchar(500);

  DECLARE @Count varchar(30);

  DECLARE @server VARCHAR(128)

  SET @server = 'dev-server'

  SET @SQLString = N'SELECT @CountOUT = count(name) FROM OPENDATASOURCE(''SQLNCLI'',

  ''Data Source='+@Server+';Integrated Security=SSPI'')

  .master.dbo.sysdatabases';

  SET @ParmDefinition = N' @CountOUT varchar(30) OUTPUT';

  EXECUTE sp_executesql @SQLString, @ParmDefinition, @CountOUT=@Count OUTPUT;

  SELECT @Count;

  Did it work for you?

  Cheers!

• Henrico Bekker

  One Orange Chip

  Points: 27652

  More actions

  May 6, 2010 at 6:36 am

  #1163125

  Awesome, thanx ColdCoffee

  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  May 6, 2010 at 7:33 am

  #1163195

  Welcome, Henri!!