Re: Updating Oracle database from SQL Server as linked table

From: Terry Dykstra <>
Date: Mon, 12 May 2008 21:50:04 GMT
Message-ID: <gY2Wj.2234$KB3.429@edtnps91>

"Garrett Fitzgerald" <> wrote in message
>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
> 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!

OpenQuery is the only way you'll get around that dbtype error. I've found OpenQuery to work very well.

Terry Dykstra 
Received on Mon May 12 2008 - 16:50:04 CDT

Original text of this message