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?
Caroline_SwartAsked:
Who is Participating?
 
OtanaCommented:
Yes, that's it. You just use it once to add the server to your linked servers, after that you can refer to it as much as you like.
0
 
OtanaCommented:
Can't you add the SQL 2005 server as a linked server on your 2000 server?
0
 
Caroline_SwartAuthor Commented:
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?

0
 
Caroline_SwartAuthor Commented:
Thanks a lot!
Got it to work 100%
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.