Copy records from 2005 Express to SQL2000 database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Copy records from 2005 Express to SQL2000 database

Author

Topic

rodney.fetterolf
Starting Member

4 Posts

Posted - 2007-07-09 : 10:31:38

I have an application which stores records on a local SQL Express and I need to move the records to a SQL 2000 database. I have the SQL 2000 server linked in the Express Management Console (under Linked Servers). I'm trying to use a stored procedure to accomplish this, but get an error "Invalid object name 'ngtxa4-rsmsz-01.newpurchase.tblRequest'." Express uses a table named tblTRequest in the TempPurchase database, while 2000 uses a table named tblRequest in a NewPurchase database. Here is the stored procedure I'm using:
USE [tempPurchase]
GO
/****** Object: StoredProcedure [dbo].[InsertRequestToMain] Script Date: 07/09/2007 08:54:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertRequestToMain]
AS
BEGIN
INSERT INTO [ngtxa4-rsmsz-01].newpurchase.tblRequest
(fldRequestDate, fldRequiredBy, fldUserID, fldWorkAreaID, fldVendorID, fldClassID, fldEstimate, fldMemo, fldStatusID, fldStatusDate, fldSystemID,
fldtmpRequestID, fldUpdateCode)
SELECT fldRequestDate, fldRequiredBy, fldUserID, fldWorkAreaID, fldVendorID, fldClassID, fldEstimate, fldMemo, fldStatusID, fldStatusDate, fldSystemID,
tmpRequestID, UpdateCode
FROM tblTRequest
WHERE (fldHold = 0)
END

Any assistance with this would greatly be helpful. Thank you.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-09 : 11:09:22

You need specify table owner, or use .. if owner is dbo. Like:
[ngtxa4-rsmsz-01].newpurchase..tblRequest

rodney.fetterolf
Starting Member

4 Posts

Posted - 2007-07-09 : 11:25:01

Thanks, fixed the problem perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources