Solved

One SQL query - two SQL instances SQL Server 2005

Posted on 2011-02-19
5
562 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
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Date from a string 4 65
Incremental load example 2 52
MS SQL order by with "over" statement and row_number() 11 48
How to import SQL 2000 database to SQL 2014 5 112
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 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