Updating Oracle database from SQL Server as linked table

From: Garrett Fitzgerald <SarekOfVulcan_at_gmail.com>
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

Original text of this message