Re: Updating Oracle database from SQL Server as linked table
From: Mladen Gogala <mgogala_at_yahoo.com>
Date: 13 May 2008 07:07:34 GMT
Message-ID: <48293e36$0$11118$834e42db@reader.greatnowhere.com>
>> "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.
update myview set orafield='I'
Date: 13 May 2008 07:07:34 GMT
Message-ID: <48293e36$0$11118$834e42db@reader.greatnowhere.com>
On Mon, 12 May 2008 18:46:31 -0700, Garrett Fitzgerald wrote:
> 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 > )
From Oracle side, in order to participate in a distributed transaction, you would need the corresponding transparent gateway, a separately licensed product. If and when you have that, something like this would probably work:
with myview as (
select pid, orafield1, orafield2,sqlfield2
from oratable o,sqltable s
where o.orafield2=s.sqlfield2 and
orafield1 = 'A' and
sqlfield1 = 1)
update myview set orafield='I'
That will work as long as the view contains the primary key of the oracle table. Essentially, you need to do the update from the Oracle side, not the SQL Server side, you need the GW software and license and you can use the standard Oracle technique for "updating table A from table B".
-- Mladen Gogala http://mgogala.freehostia.comReceived on Tue May 13 2008 - 02:07:34 CDT