Linked Server problem

Question

Linked Server problem

Ray Cacciatore

Ten Centuries

Points: 1260
More actions
March 22, 2011 at 3:13 pm

#236711

We just migrated one of our databases to a new server. However, another database in the old server still needs to read data from the database that was migrated.
So we added a linked server, then modified our stored procedure by placing the name of the server. i.e. TheServer.TheDatabase.TheSchema.TheTable.
But I just realized that we need to always modify this stored procedure when we work on our development environment and change it back when we put it in production.
Is there a way to have the same code in the stored procedure use both environments?
Thanks !

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

You could create a synonym that points to the table and use that synonym in your stored proc. The only change required between dev and prod is the synonym definition - the sproc itself would be exactly the same

Ray Cacciatore Ten Centuries Points: 1260 More actions · Answer 2

I've never used synonyms before. Does that mean that when I refresh the DEV database I need to modify the synonym every time?

happycat59 One Orange Chip Points: 29389 More actions · Answer 3

If you mean restore a backup from another environment (e.g. PROD), then yes you will need to update the synonym.

Ray Cacciatore Ten Centuries Points: 1260 More actions · Answer 4

I created the synonym but I soon learned that you cannot create a synonym for a server object. Or can you? I tried creating the synonym like so:

CREATE SYNONYM Remote_Server FOR SQLPRD5;

SQLPRD5 is the linked server. So, naturally, I changed my stored procedure code to:

SELECT * FROM Remote_Server.TheDataBase.TheSchema.TheTable

When I did this, I got an error: Could not find server 'Remote_Server'.

From what I read, I have to create a synonym like:

CREATE SYNONYM Remote_Table FOR SQLPRD5.TheDataBase.TheSchema.TheTable;

And then change my TSQL as:

SELECT * FROM Remote_Table

But here's the problem I have with this... I reference many tables in my stored procedure. Does that mean I have to create a synonym for each? I hope not. Please correct me if I'm wrong or if I'm going about this the wrong way. There has to be an easier way to change the name of the remote server when it's a linked server.

Thanks !

Lowell SSC Guru Points: 323518 More actions · Answer 5

Ray synonyms are Per Object, with an object being a Table, typically;

so you need a synonym for each object coming from the linked server.

so you create say , 4 synonyms for each of the 4 joined tables in a query that were refering to linked server objects.

so if you have 30 queries, and they are fiddling with 16 or 17 assorted tables fromt eh linked server...yep that's 16 or 17 synonyms.

once they are in place, your codes very portable, but it's a little work to set up./

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!