Re: Updating Oracle database from SQL Server as linked table

"Garrett Fitzgerald" <SarekOfVulcan@[EMAIL PROTECTED]
> wrote in message 
news:cefcd14e-1ffa-4c85-9fa3-6dab448fc3c4@[EMAIL PROTECTED]
>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
> re****ted 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