Update field using value from linked 2000 sql data

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)

Update field using value from linked 2000 sql data

Author

Topic

rodney.fetterolf
Starting Member

4 Posts

Posted - 2007-07-11 : 16:15:22

I'm trying to update a field (tmpRequestID) in a SQL Express table (tblTSubRequest) using a value in a SQL 2000 database field (fldtmpRequestID) in the dbo.NewPurchase table.

The procedure I thought would work is:
USE [tempPurchase]
GO
/****** Object: StoredProcedure [dbo].[UpdateSubfldRequestID] Script Date: 07/11/2007 15:01:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UpdateSubfldRequestID]
AS
BEGIN
-- Insert statements for procedure here
UPDATE tblTSubRequest
SET tmpRequestID = tblRequest.fldRequestID
FROM tblTSubRequest INNER JOIN
[NGTXA4-RSMSZ-01].NewPurchase.dbo.tblRequest ON tblTSubRequest.fldRequestID = tblRequest.fldRequestID
WHERE (tblRequest.fldUpdateCode = 99)
END

It appears that the procedure cannot read the field value on the 2000 server and the 2000 server is linked to the 2005 express edition.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-11 : 16:21:16

You need to use alias for the remote server. But your UPDATE statement doesnt make sense. IF the records match on fldRequestID, that means the value is same in both servers. So, its like updating 1=1 where 1=1 ?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rodney.fetterolf
Starting Member

4 Posts

Posted - 2007-07-12 : 08:15:38

Thank you dinakar for pointing out my mistake. I missed typing three little letters and realized I don't need the Where clause.

UPDATE tblTSubRequest
SET tmpRequestID = MainTable.fldRequestID
FROM tblTSubRequest INNER JOIN
[ngtxa4-rsmsz-01].NewPurchase.dbo.tblRequest AS MainTable ON tblTSubRequest.fldRequestID = MainTable.fldtmpRequestID

Now things work fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources