Query data in SQL 2000 database from a SQL 2005 stored procedure

Posted on 2007-07-28
Last Modified: 2010-03-19
I have the following 2 existing databases:

SQL 2000 database is named Payroll
SQL 2005 database is named HRData

HRData contains a stored procedure that queries the Payroll database by referencing the Payroll database in the from clause as indicated below:

Select FirstName.LastName
From Payroll.dbo.Employees

When I run the above query from a SQL 20005 database I get the following error:

Invalid Object Name 'Payroll.dbo.Employees'

It works if both databases are SQL 2000.

Please help me.
Question by:dmoss123
    1 Comment
    LVL 14

    Accepted Solution

    Do you mean you have 2 servers, one with SQL 2000 and one with 2005, and that if you put both dbs on 2000 then it works, but if you put HRData on 2005 you get the problem?

    If so, it's because SQL can't "see" databases on other servers, without linking to them first.

    You need to add the SQL 2000 as a linked server on 2005, then reference the table as



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    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.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    759 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