Updating Oracle database from SQL Server as linked table
Date: Mon, 12 May 2008 13:42:07 -0700 (PDT)
Message-ID: <cefcd14e-1ffa-4c85-9fa3-6dab448fc3c4@c65g2000hsa.googlegroups.com>
I have a medical records system where parts live in Oracle and parts
live in SQL Server. I'm trying to inactivate a lot of patients who
haven't been seen since the conversion by updating them on the SQL
Server side and then updating the Oracle side to match. I'd like to be
able to use the following query:
UPDATE server..user.table
SET OraField1 = 'I'
WHERE OraField1 = 'A'
AND OraField2 IN ( SELECT SQLfield2 FROM SQLTable WHERE SQLField1 = 1 )
However, when I do this, I get an error saying that the field "was reported to have a DBTYPE of 130 at compile time and 5 at run time". This appears to mean that there's a field on the Oracle side that was defined as Number without any precision information. How can I rewrite this query to actually work? OpenQuery doesn't seem like it will do what I want, and I don't want to accidentally inactivate everyone in the database...
Thanks! Received on Mon May 12 2008 - 15:42:07 CDT