Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 External TABLE access from another SQL Server

Author  Topic 

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.


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
Go to Top of Page

- Advertisement -