CODEWITHSUNDEEP
sql-servert-sql
simon831
simon831

Reputation: 5476

Referencing remote MS SQL databases from Stored Procedures

I have inherited a large legacy system on MS SQL2008 R2.

There are a large number of stored procedures that reference another database:

select * from MySeparateDatabase_LIVE.dbo.MyTable
select * from MySeparateDatabase_STAGING.dbo.MyTable
select * from MySeparateDatabase_TEST.dbo.MyTable

Unfortunately all the live, staging and test databases are on the same SQL instance and company constraints mean this will not change soon.

The problem comes trying to push changes from test to staging and live. It requires an amount of manual editing or find/replace - which introduces potential errors on a critical system.

What techniques can I employ to use exactly the same SP on each database?

Upvotes: 2

Views: 516

Answers (1)

anon
anon

Reputation:

Create SYNONYMs, e.g. In your test db:

CREATE SYNONYM dbo.MyTable FOR MySeparateDatabase_Test.dbo.MyTable;

And in staging:

CREATE SYNONYM dbo.MyTable FOR MySeparateDatabase_Staging.dbo.MyTable;

This provides a layer of abstraction, and allows you to have the same code in each database. This way you can deploy the different synonyms once but the procedures can be identical (and they would only reference dbo.MyTable without the database prefix, letting the synonym redirect to the right database). A stored procedure in the test db will reference the table in the other test database, a stored procedure in the staging db will reference the table in the other staging database, etc.

Upvotes: 6

Related Questions