Managing Linked Server in Stored Procedures

[Kamienios]

Hi,

I have a lot of stored procedures, which joins tables from different server.
The question is: what is the elegant way to manage this situation in case of having development environment, and production environment?

I don`t want to enter 20 stored procedures and manually change linked server or database name?

Thanks for You help!

[szlamany]

I am assuming these are linked servers to SQL DB? Otherwise you can force the names to match in production and test (if they are non-sql db's) - right??

If they are SQL DB's then simply put a VIEW in front of the linked server tables so your sprocs reference the VIEW's.

Have one set of VIEW definitions for test and one for production.

I have one here...

Create View StudentSIS_V
As
Select * From [10.25.0.60].dbo.StudentNurse_V

and

Create View StudentSIS_V
As
Select * From Stufiles.dbo.StudentNurse_V

[Kamienios]

Thanks for reply.
I don`t get it 100%...

Maybe I`ll explain the situation better:

I have a stored procedure in which i use a table from different server:

inner join [SERVER\SQLSERVER].Database.dbo.table


And now, I`m copying the whole database including it`s stored procedures to new backup. Which will work as development database.

And i also made a copy of linked database, and name it Database_Test.

Is there any way to fastyly change all stored procedures to have:

[SERVER\SQLSERVER].Database_Test.dbo.table

?

[szlamany]

Do not INNER JOIN to the table.

Instead INNER JOIN to a VIEW - that references the table.

And that VIEW is the only place you have to change the "name" of the linked server/table.

I am trying to give you a simpler place to make the change - a single place.

[Kamienios]

Thanks.

But it`s not a good idea for me.

If i have 2 databases I will also need then 2 views. One for Develop, and second for production.

And the result is the same, I have to change view names in every stored procedure.

Because If i only have one view, and change it to show table from development. I must remember that also production environment use that view...

[szlamany]

Hm - well - that's a shame.

TABLE names are text - typed - they don't "change" themselves.

If you have dozens of stored procedures that are joining to this object then how do you think you can have the object name change?

By hiding the object behind a VIEW - the sprocs all use the view and you only have to change ONE PLACE.

Oh well - good luck on your quest...