• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 597
  • Last Modified:

One SQL query - two SQL instances SQL Server 2005

My client has recently moved a sql database from one server to the same server as another database. Now that both instances are on the same server I can no longer used a linked server. How do I structure this SQL command to access data in both databases?
select JOBNUMBER,CRDATE,CONVERT(CHAR(15),AGENCY) AS AGENCY
from [gseappserver\sql2000].[gse2000sql].[dbo].[jobs]
where jobnumber not in (
select pacontid from [gseappserver].[gse].[dbo].[pa01101]) and contractcomplete=0

Open in new window

0
rwheeler23
Asked:
rwheeler23
  • 2
  • 2
1 Solution
 
Anthony PerkinsCommented:
Something like this:
SELECT  JOBNUMBER,
        CRDATE,
        CONVERT(CHAR(15), AGENCY) AS AGENCY
FROM    [OtherDatabaseNameGoesHere].[dbo].[jobs]
WHERE   jobnumber NOT IN (SELECT    pacontid
                          FROM      [OtherDatabaseNameGoesHere].[dbo].[pa01101])
        AND contractcomplete = 0

Open in new window

0
 
Anthony PerkinsCommented:
Incidentally, you may find that query performs better if you re-write it to use a LEFT JOIN instead of using NOT IN.
0
 
Jim P.Commented:
Now that both instances are on the same server I can no longer used a linked server.

There is no reason that you can't create a linked server to another instance on the same server.
0
 
rwheeler23Author Commented:
I don't  understand why the first time I attempted to create the linked server to the other instance I got the message "You cannot create a local SQL Server as a linked server" and then the second time I did the same thing it worked just fine.

From a performance standpoint, are left outer joins always preferable.?
0
 
Jim P.Commented:
From a performance standpoint, are left outer joins always preferable?

It depends. If your subquery is pulling just a quick few rows
(select pacontid
from [gseappserver].[gse].[dbo].[pa01101]
where orderdate > getdate()-1)

that's fine.  But it looks you are pulling the whole table so a left join is the preferred faster method.
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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