Referencing Linked Server in Queries

[Jeff Dillon]

We have several sprocs where the name of a linked server table is
hard-coded, like [MTLKDEV08-D].CMS_ED_DEV.dbo.Patient. The linked server
name will change as we move our code from dev, test, and onto production.

This is all SQL2000.

We can't use a lookup table/variable name, then use the EXEC function,
because EXEC doesn't inherit the permissions of the stored procedure it is
contained in, but rather uses the native table permissions (per the docs,
and has been our experience). So when we try to use EXEC to join back to a
local table, we get "permission denied" since we have removed SELECT
permissions on our tables. All our data access is via sprocs. Therefore, we
need to hard-code the linked server reference in the queries.

Is there anyway to retrieve/use a linked server name dynamically? Other
ideas? Ideally, we don't want to change any sproc code as we move it to
production.

Jeff

[Aaron Bertrand [MVP]]

In the creation script(s) for the stored procedures, you could create the
script for each procedure dynamically, e.g.

DECLARE @linkedServerName VARCHAR(255)
SET @linkedServerName = 'ProductionLinkedServerName'
GO

EXEC('CREATE PROCEDURE whatever
AS
BEGIN
SELECT * FROM ['+@linkedServerName+']. ...
END
GO')

--
www.aspfaq.com / www.perfhound.com

"Jeff Dillon" <jef...@fidalgo.net.remove> wrote in message
news:#vwypPNNCHA.2224@tkmsftngp09...

[Jeff Dillon]

Ingenious

"Aaron Bertrand [MVP]" <aaronATaspfaq.com> wrote in message
news:u5ppISNNCHA.1776@tkmsftngp12...

[Aaron Bertrand [MVP]]

You could also add logic so you don't have to change the script for each
environment:

DECLARE @servername SYSNAME
SELECT @servername = SERVERPROPERTY('MACHINENAME')
IF @servername = 'ProductionServer'
SET @linkedServerName = 'ProductionLinkedServerName'
IF @servername = 'StagingServer'
SET @linkedServerName = 'StagingLinkedServerName'

Of course this scheme won't work if your primary servers are named
identically in each environment, but if that were the case, you might
explore naming your linked servers the same in each environment also (can I
ask why the linked servers can't be the same in each environment, or why you
can't set up multiple linked servers?)...

--
www.aspfaq.com / www.perfhound.com