May 6, 2010 at 5:39 am
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
May 6, 2010 at 5:45 am
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
May 6, 2010 at 5:57 am
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
May 6, 2010 at 6:20 am
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
May 6, 2010 at 6:35 am
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!
May 6, 2010 at 6:36 am
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
May 6, 2010 at 7:33 am
Welcome, Henri!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy