Link to home
Start Free TrialLog in
Avatar of Caroline_Swart
Caroline_Swart

asked on

Problem with joining tables in different SQL servers

I have a stored procedure in SQL 2000 but need to join a table in SQL 2005.
Is there a way to use a connection string in a stored procedure?
Avatar of Otana
Otana

Can't you add the SQL 2005 server as a linked server on your 2000 server?
Avatar of Caroline_Swart

ASKER

I got this from the internet:

>EXEC sp_addlinkedserver SERVER_01
>GO
>/* The following command links 'sa' login on SERVER_02 with the 'sa' login of >SERVER_01 */
>EXEC sp_addlinkedsrvlogin @rmtsrvname = 'SERVER_01', @useself = 'false', >@locallogin = 'sa', @rmtuser = 'sa', @rmtpassword = 'sa password of SERVER_01'
>GO
>SELECT a.title_id
>FROM SERVER_01.pubs.dbo.titles a
>INNER JOIN SERVER_02.pubs.dbo.titles b
>ON a.title_id = b.title_id
>GO

Is this what u mean?
Is this a once off procedure or should i add it to every stored procedure?

ASKER CERTIFIED SOLUTION
Avatar of Otana
Otana

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks a lot!
Got it to work 100%