Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
irxn
Starting Member
13 Posts |
 Posted - 2012-03-09 : 08:16:58
|
| Hi,I have installed two instances of MS SQL Server 2005. On Instance A, I have three Databases orig, test, staging.The orig database contains lots of views where tables of the staging DB where accessed (like Select * from staging.dbo.table1).Now I have moved database test to another instance on a foreign server. Now I have the problem to access these views. I have created a linked Server from B to a, so I can access the views like [instanceA].staging.dbo.., this works. But there are about 200 view containg the syntax staging.dbo.table1. How could i simple change all of these? Or is there another solution, so on Instance B I can access the database on A likw staging.dbo.table?Best regards,Peter |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-03-12 : 18:42:30
|
| Would Synonyms be of use here? There would be a one time cost to change your code to use them but once they were in place you could move the database locations and then simply recreate the synonyms to reference the new location.=================================================Men shout to avoid listening to one another. -Miguel de Unamuno |
 |
|
|
irxn
Starting Member
13 Posts |
Posted - 2012-03-15 : 03:51:50
|
| Hi, Synonyms didn't help much.I solved the problem by changing all of the views with including the server name. Thanks for your help.Best regards,Peter |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-03-15 : 19:15:16
|
| Out of curiousity, what prevernted the Synonyms from solving the issue? I'm glad that you were able to resolve the immediate problem, but it seems that you'll run into this again; possibly soon.=================================================Men shout to avoid listening to one another. -Miguel de Unamuno |
|
|
|
|
|
|
|
|
Access remote Database after moving Database