Pass Parameter to Stored Procedure for Linked Server Name

[mamartin]

I have created a stored procedure that executes 2 select statements against 2 separate linked server databases. The sp works fine when the linked server names are hardcoded directly into the query. However, I receive an error message when I attempt to pass the linked server names to the sp as variables. Message received is: "could not find server 'variable name' in sysservers. Execute sp_addlinkedserver to add the server to syssservers".

Any ideas on how to pass the linked server name into the query (used in the from clause to point to various tables contained within the linked servers).

Thank you,

Michael Martin

 

[skicamel]

Try playing around w/ this...

create proc test
@dbname1 varchar(25),
@dbname2 varchar(25)
as
declare @sql1 nvarchar(1000)
declare @sql2 nvarchar(1000)

set @sql1 = 'select * from ' + @dbname1 + '.dbo.tablename'
set @sql2 = 'select * from ' + @dbname2 + '.dbo.tablename'

exec sp_executesql @sql1
exec sp_executesql @sql2

 

[mamartin]

skicamel,

thanks, that worked quite well. Another question, if you please. Executing the stored procedure directly from the server works, but when I attempt to execute it from my workstation via query analyzer, I receive the message: "login failed for user '\'". I know this has something to do with how I have the security setup for the linked servers, but can't seem to figure out how to access them from other than directly from the server. On the security tab for the linked servers, I have "they will be impersonated" selected. Any ideas?

Thanks again,

Michael Martin

 

[skicamel]

Terry can correct me if I'm wrong on the details here, but the way I understand it, if you have 'they will be impersonated' checked, it uses the login AND password that you used to log in to whatever server you're using. If the login you used in query analyzer is not on the remote server or the password is different it won't let you in to the remote server.

You can map it to the remote user and it should work fine. Though, and again, Terry (or someone else...lot of good folks here), can help me out here, I don't know if that opens up security issues or not.

There were also some things in SQL Books Online concerning sp_addlinkedsrvlogin that might be useful, though I can't speak w/ any real understanding there.

Hope that's something to go on. This isn't really my area of expertise, so if I missed anything, any insight from the power folks here is always welcome.

 

[mamartin]

Thanks, I was able to get it working by mapping to the 'sa' account. Unfortunately, I don't think the 'sa' account's password is standard across the servers I wish to link. Oh well.

 

[mamartin]

Thanks, I was able to get the security resolved using the "mapped" selection with the sa account. Unfortunately, I don't think the sa password is standard throughout my linked servers. Oh well. Thanks again.