Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


One SQL query - two SQL instances SQL Server 2005

Posted on 2011-02-19
Medium Priority
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?
from [gseappserver\sql2000].[gse2000sql].[dbo].[jobs]
where jobnumber not in (
select pacontid from [gseappserver].[gse].[dbo].[pa01101]) and contractcomplete=0

Open in new window

Question by:rwheeler23
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
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34934182
Something like this:
FROM    [OtherDatabaseNameGoesHere].[dbo].[jobs]
WHERE   jobnumber NOT IN (SELECT    pacontid
                          FROM      [OtherDatabaseNameGoesHere].[dbo].[pa01101])
        AND contractcomplete = 0

Open in new window

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.
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.

Author Comment

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.?
LVL 38

Accepted Solution

Jim P. earned 2000 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.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

670 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