Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jonsey54
Starting Member
4 Posts |
 Posted - 2006-12-07 : 19:45:56
|
| Hi,I am writing a Stored Procedure which is using a Linked server so at the moment the name of the linked server is in the actual stored procedure.Is it possible to access some config file from the SP to get the name of the Linked Server rather than hard coding it?What is the best way of doing this? Or can some environment variables be setup in SQL Server?Any ideas / suggestions would be appreciated.Thanks |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-12-08 : 00:08:09
|
I think the only way to do such a thing is to use dynamic sql in your sproc, unless you want to hardcode multiple linked server names in your sproc and switch between them using if/else.Alternatively, you can leave the sproc alone and change the underlying linked server if you want it to point to a new server.
http://www.elsasoft.org |
 |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-12-08 : 01:21:58
|
quote:
Is it possible to access some config file from the SP to get the name of the Linked Server rather than hard coding it?
This is for SQL Server 2005, i cant say will this work for 2000 or not.SELECT * FROM sys.servers Gurpreet S. Gill |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-08 : 01:22:38
|
| We put an SProc on the linked server, and then call it using dynamic SQL. This has minimal impact on permissions (the User has to have permissions to run the SProc on the Linked Server, but that is OK for us - whereas granting SELECT etc. permissions on the linked server would not be so good!)i.e. the difference between:EXEC 'RemoteServer.RemoeDB.dbo.MyRemoteSP'andEXEC 'SELECT * FROM RemoteServer.RemoeDB.dbo.MyRemoteTable'Kristen |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-12-08 : 08:45:18
|
one thing to be aware of with linked servers is that loopback scenarios are not supported. here by loopback I mean where the linked server points to itself. Some things work, but others don't. In particular, this fails:insert into sometable exec Self.Database.Owner.Table
http://www.elsasoft.org |
|
|
|
|
|
|
Configurable variables in a stored procedure