[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

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?
0
Caroline_Swart
Asked:
Caroline_Swart
  • 2
  • 2
1 Solution
 
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
 
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
 
Caroline_SwartAuthor Commented:
Thanks a lot!
Got it to work 100%
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now