Solved

One SQL query - two SQL instances SQL Server 2005

Posted on 2011-02-19
5
583 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
[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
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34934182
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
ID: 34934191
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.
ID: 34935561
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
ID: 34937762
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
ID: 34937982
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

632 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