Opendatasource query with parameter not working

Question

Post reply

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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 · Answer 5

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 · Answer 6

ColdCoffee

SSC-Dedicated

Points: 39972

May 6, 2010 at 7:33 am

#1163195

Welcome, Henri!! 🙂