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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 External TABLE access from another SQL Server
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/16/2004 :  08:39:30  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

Garden of Sakuntala
701 Posts

Posted - 09/16/2004 :  09:25:40  Show Profile  Visit Amethystium's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000