Stored Proc References to Tables on Linked Servers and Server Alias 2

[Auguy]

I’ve seen some discussions on this topic, but I’m just trying to understand it better. I have SQL 2005 and SQL 2000 on my development machine and I have set a linked server form the 2005 to the 2000 server. Some of my stored procs use tables in the 2000 server and I have changed them to use the Linked Server name when referencing the tables. Is there an easier or better way to handle references in the stored procedures to tables in the Linked Servers other than using a Server Alias or having to change my procs when deploying to the production server at the client site? At the client site the 2000 & 2005 instances are on different physical servers if that makes any difference.

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

In my opinion, this is a perfect example where synonyms are useful.

http://msdn.microsoft.com/en-us/library/ms177544(v=sql.90).aspx

Basically, you write all your queries to use the synonym. Then, when you want to deploy on another system, just make sure the synonym is set up correctly.

Synonyms were introduced in SQL2005, so this will only work when writing queries on 2005. What I mean is, you cannot use synonyms in the 2000 database to connect to 2005.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

Thanks George. After checking that link, am I correct in assuming I only have to issue the Create Synomym once? In other words once the synonym is created I don't have to re-create it every time I call a stored proc. Yes, these are procedures in 2005 that reference tables in the 2000 linked server.

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

You are correct. You'll need to create the synonym before you use it (obviously), but you only need to do this once.

If you ever need to change the synonym (to point to another linked server for example), you change the synonym and that's it. You would not need to change any of your stored procedure code.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[starsky51]

First time I've heard of synonymns in SQL Server. Really useful. Thanks George!