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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-09-16 : 08:39:30
|
| Eduard Bitao writes "Team:A DBA has a stored procedure that creates a table view using two (2) concurrent Database (e.g, A_DB and B_DB) originally installed in a single SQL Server. Now, I have to migrate the A_DB only onto another SQL Server (different App. Server) but not the B_DB (stays with the original App. Server) - the problem is how to access those tables indicated originally in the stored procedure which accesses B_DB in the original SQL Server?Thanks and would appreciate your help.Regards,Eduard" |
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2004-09-16 : 09:25:40
|
Hi there,You could try creating linked servers using the special stored procedure, sp_addlinkedserver (you can read more about this in BOL for full syntax and options).To relate this to your question let us assume that both your databases, A_DB and B_DB are held in 'ServerOne' then A_DB is moved to ServeTwo. The sproc does not know that A_DB is no longer in the same server so it will not work.First we create a linked server, something like this :exec sp_addlinkedserver @Server = 'ServerTwo',srvproduct = 'SQLTeam OLEDB Provider' Check to see that your linked server has been added by using sp_linkedservers, like :exec sp_linkedServers All you need to do now is modify the code in your stored procedure so where there is a mention of any object in A_DB you will need to do something like this :select * from ServerTwo.A_DB.dbo.someObject Hope this helps.------------->>> BREAKING NEWS!!! <<<------------- Saddam Hussien has weapons of mass destrcution |
 |
|
|
|
|
|
External TABLE access from another SQL Server