Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problem with joining tables in different SQL servers

Posted on 2007-03-23
4
Medium Priority
?
167 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 11

Expert Comment

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

Author Comment

by:Caroline_Swart
ID: 18778112
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 750 total points
ID: 18778253
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
ID: 18778271
Thanks a lot!
Got it to work 100%
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

618 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