Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using views and stored procedures that reference a linked server:

SELECT Id, Name
FROM [LinkedServer].Database.dbo.FooView

I would like to hide the [LinkedServer].Database or at least the [LinkedServer] part of my views and stored procedures. This way I would be able to have "server-agnostic" code, that would be easily compared between production and dev servers.

This could be done at the server level (like the linked server) or at the database level (where the views and the stored procedures using it are).

share|improve this question
add comment

1 Answer 1

up vote 8 down vote accepted
CREATE SYNONYM dbo.FooView FOR [LinkedServer].Database.dbo.FooView

Then just use dbo.FooView in your SPs and they can be the same between production and dev.

Note that this is only for SQL 2005 and up.

A solution for SQL 2000 might be to use views that ONLY SELECT * FROM [LinkedServer].Database.dbo.FooView with no other code in them. You could have a meta-table with the list of these view names and corresponding named sources. Then, write an SP that loops through the table and (re)creates the views as needed. Then, if you want to change where the views point, you run your SP with the new linked server name as a parameter (or keep it in the meta-table and update that) and all your dependent objects will silently point to the new location now. As long as the column names, positions, and data types are the same, it will work just fine.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.