##

can i update tables on a linked oracle server from SQL server.
if so do i use tsql or PLSQL?mat
T-SQL
UPDATE OPENQUERY(OracleServer, 'SELECT col1, col3 FROM tbl..')
SET col1 = @.param1
WHERE col2 = param2
"mat" <mat@.discussions.microsoft.com> wrote in message
news:E1C9CA61-006F-4154-A30D-DC1BCD8DFF5E@.microsoft.com...
> can i update tables on a linked oracle server from SQL server.
> if so do i use tsql or PLSQL?|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23vctyn2uEHA.3200@.TK2MSFTNGP14.phx.gbl...
> mat
> T-SQL
> UPDATE OPENQUERY(OracleServer, 'SELECT col1, col3 FROM tbl..')
> SET col1 = @.param1
> WHERE col2 = param2
>
Both, actually.
The UPDATE is TSQL and the argument to OPENQUERY is a passthrough query in
Oracle dialect.
For instance, compare
select * from OPENQUERY(oracle,'select * from scott.emp where
ename=''smith''')
select * from oracle..SCOTT.EMP where ename='smith'
The first query returns 0 rows since the where clause is evaluated by Oracle
and is case-sensative. The second returns 1 row since it's evaluated by SQL
Server as non-case sensative. Second query also returns all rows from
Oracle to Sql Sever and filters them there.
David