Re: Updating Oracle database from SQL Server as linked table

On May 12, 5:50 pm, "Terry Dykstra" <tddyks..._at_forestoil.ca> wrote:
> "Garrett Fitzgerald" <SarekOfVul..._at_gmail.com> 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
> >                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".
> > ... OpenQuery doesn't seem like it will do
> > what I want, and I don't want to accidentally inactivate everyone in
> > the database...
>
> OpenQuery is the only way you'll get around that dbtype error.  I've found
> OpenQuery to work very well.

Ok, how do I do that? Would this be the correct syntax?

UPDATE OpenQuery(Server, 'select pid, orafield1, orafield2 from oratable')

   SET OraField1 = 'I'
   WHERE OraField1 = 'A'

        AND OraField2 IN (
           SELECT SQLfield2
               FROM SQLTable
               WHERE SQLField1 = 1
           )