Updating one DB field from a remote DB

xeroxcanada
xeroxcanada used Ask the Experts™
on
I get no error when I run this but the field Res_Org does not gst updated in the table CSEA from the value on the remote DB.

ALTER PROCEDURE [dbo].[ADD_CSEA_Org]
      -- Add the parameters for the stored procedure here
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON

UPDATE tblTo
SET tblTo.RESP_ORG = tblFrom.RESP_ORG
FROM Tbl_CSEA tblTo

INNER JOIN [Linked Server].ICSS.dbo.ICSSLOGS tblFrom
   ON tblTo.LOG_ID = tblFrom.LOG_ID


END
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
if you get no rows updated, the join condition returns 0 rows.
there is no other explanation.

now, the issue might be that LOG_ID is not the same data type, aka "incompatible" data type, for example 2 CHAR() columns with different length

please double check the 2 tables for data types and data
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Could there possibly be a trigger or something rolling back the change?  Try running just the UPDATE syntax in SQL Management Studio (SSMS) and see if you get an errors or multiple lines of rows affected.  If you do, the second may be the indication of the trigger on Tbl_CSEA running.

UPDATE tblTo
SET tblTo.RESP_ORG = tblFrom.RESP_ORG
FROM Tbl_CSEA tblTo
INNER JOIN [Linked Server].ICSS.dbo.ICSSLOGS tblFrom
   ON tblTo.LOG_ID = tblFrom.LOG_ID

Open in new window

Author

Commented:
Wrong data types..How would I make the conversion?

Conversion failed when converting the nvarchar value 'VGW626588 ' to data type int.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
on the field that is "int", use CAST ...

for example:


INNER JOIN [Linked Server].ICSS.dbo.ICSSLOGS tblFrom
   ON tblTo.LOG_ID = CAST( tblFrom.LOG_ID AS NVARCHAR(100))

or the other way round...

Author

Commented:
Ok got that figured out. Now there are duplicate log_ID on the remote DB, How do I select only one of them.

Msg 512, Level 16, State 1, Procedure Log_created, Line 14
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
that error cannot come from the statement you showed itself.
either you modified it (please post), or it's rather a "trigger" Log_created that has been coded incorrectly, and would only take single-line updates...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial