Re: Updating Oracle database from SQL Server as linked table
Date: Mon, 12 May 2008 18:46:31 -0700 (PDT)
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 )Received on Mon May 12 2008 - 20:46:31 CDT