• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

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

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.
0
dmoss123
Asked:
dmoss123
1 Solution
 
twoboatsCommented:
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

linkedservername.Payroll.dbo.Employees

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now