Solved

Problem with joining tables in different SQL servers

Posted on 2007-03-23
4
158 Views
Last Modified: 2013-11-30
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
Comment
Question by:Caroline_Swart
  • 2
  • 2
4 Comments
 
LVL 11

Expert Comment

by:Otana
Comment Utility
Can't you add the SQL 2005 server as a linked server on your 2000 server?
0
 

Author Comment

by:Caroline_Swart
Comment Utility
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
 
LVL 11

Accepted Solution

by:
Otana earned 250 total points
Comment Utility
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
 

Author Comment

by:Caroline_Swart
Comment Utility
Thanks a lot!
Got it to work 100%
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now