Solved

One SQL query - two SQL instances SQL Server 2005

Posted on 2011-02-19
5
542 Views
Last Modified: 2012-06-21
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
Comment
Question by:rwheeler23
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Incidentally, you may find that query performs better if you re-write it to use a LEFT JOIN instead of using NOT IN.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
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
 

Author Comment

by:rwheeler23
Comment Utility
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
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
Comment Utility
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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