Re: Updating Oracle database from SQL Server as linked table

"Garrett Fitzgerald" <SarekOfVulcan_at_gmail.com> wrote in message news:cefcd14e-1ffa-4c85-9fa3-6dab448fc3c4_at_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!

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

-- 
Terry Dykstra