December 16, 2008 at 9:40 am
I am trying to connect to multiple servers using openrowset. but i am not able to pass a variable as parameter to the OPENROWSET server name. My query goes like this
select 'PTUS01DS01',name
from openrowset ('SQLOLEDB', @servername ; 'username' ; 'password',
'select name from master.dbo.sysdatabases where name not in (''master'',''model'',''msdb'',''tempdb'',''pubs'',''northwind'',''dba_admin'')')
the error obviously is, "Server: Msg 17, Level 16, State 1, Line 24
SQL Server does not exist or access denied."
is there some way this can be done?
would be grateful if someone can come up with a solution.
December 16, 2008 at 1:51 pm
Hi Arun,
I don't have SQL in front of me so I can't give you the exact syntax, however.....
You can create some dynamic SQL and run it. The dynamic sql will have to include the whole statement - select ... from openrowset(.....)
I have thrown it together below but you will need to check it as it can be quite confusing with all the single quotes!
declare @query varchar(2048)
set @query = 'select ''PTUS01DS01'',name
from openrowset (''SQLOLEDB'', ' + @servername + ' ; ''username'' ; ''password'',
''select name from master.dbo.sysdatabases where name not in ([master],[model],[msdb],[tempdb],[pubs],[northwind],[dba_admin])'')'
print @query
exec(@query)
December 17, 2008 at 5:20 am
oh, thats great.. i used the script this way using a variable and then executing it. and i completed my query and it worked great. But I think we cannot use that [] inside the query. instead i used ''. For instance, ''master'' instead of [master].
SSC- Enthusiastic, thanks for your help 🙂 you are great!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply