On May 13, 3:07=A0am, Mladen Gogala <mgog...@[EMAIL PROTECTED]
> wrote:
> On Mon, 12 May 2008 18:46:31 -0700, Garrett =A0Fitzgerald wrote:
> > On May 12, 5:50=A0pm, "Terry Dykstra" <tddyks...@[EMAIL PROTECTED]
> wrote:
> >> "Garrett Fitzgerald" <SarekOfVul...@[EMAIL PROTECTED]
> 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
> >> > =A0 =A0SET OraField1 =3D 'I'
> >> > =A0 =A0WHERE OraField1 =3D 'A'
> >> > =A0 =A0 =A0 =A0 AND OraField2 IN (
> >> > =A0 =A0 =A0 =A0 =A0 =A0SELECT SQLfield2
> >> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM SQLTable
> >> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0WHERE SQLField1 =3D 1
> >> > =A0 =A0 =A0 =A0 =A0 =A0)
>
> >> > 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".
> >> > ... 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.
=A0I'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')
> > =A0 =A0SET OraField1 =3D 'I'
> > =A0 =A0WHERE OraField1 =3D 'A'
> > =A0 =A0 =A0 =A0 AND OraField2 IN (
> > =A0 =A0 =A0 =A0 =A0 =A0SELECT SQLfield2
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM SQLTable
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0WHERE SQLField1 =3D 1
> > =A0 =A0 =A0 =A0 =A0 =A0)
>
> 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 (
> =A0 =A0 =A0select pid, orafield1, orafield2,sqlfield2
> =A0 =A0 =A0from oratable o,sqltable s
> =A0 =A0 =A0where o.orafield2=3Ds.sqlfield2 and
> =A0 =A0 =A0 =A0 =A0 =A0orafield1 =3D 'A' and
> =A0 =A0 =A0 =A0 =A0 =A0sqlfield1 =3D 1)
> update myview set orafield=3D'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 Gogalahttp://mgogala.freehostia.com-
Hide quoted text -
>
> - Show quoted text -
If this is being ran from the SQL Server side the Transparent Gateway
product would not be required.
If may also be possible to get around the ODBC error via the use of an
explict convert function in the code or if that does not work by
placing a view over the Oracle table that incudes the necessary
conversion. I think I have managed to work around one or two errors
using the above.
To run from the Oracle side instead of purchasing the Gateway product
you can try generic connectivity. This requires that you obtain
(purchase) and ODBC driver to SQL Server for the platform you run SQL
server on for all platforms except Windows where I believe the
necessary comes with the platform. I had this working on AIX for
Oracle version 8.1.7. With AIX I believe this feature is broke on ver
9 but it works on other platforms and a patch might have been
available. The developers choose to use a java program so we never
carried the HS forward after the initial tests.
The open query method should also work.
HTH -- Mark D Powell --
|
