FYI - Oracle Linked Server - OpenQuery Deletes

I thought this may be very helpful for those of you using SQL7 linked server to Oracle 7.3.4 Database. After much research and no answers when opening a case with Microsoft I finally figured out how to delete using Openquery. There are a few missing pieces of information that would of been a great help in BOL. The first is, the Microsoft OLE DB for Oracle is not the correct choice for the data provier as one might think. The correct choice is the Oracle Provider for OLE DB. The next important thing is that the Oracle table you are querying MUST have a unique index on at least one column not necessarily the column in your WHERE clause. Thirdly, you get much better performance and use of indexes if you put the WHERE clause inside the OPENQUERY statement. Here is the syntax that I found to work in my application:

DECLARE @ins_id varchar(7)
DECLARE @sqlstring nvarchar(2000)
Select @ins_id = '123456'
Select @ins_id = convert(int, @ins_id)
select @ins_id

select @sqlstring = 'DELETE FROM OPENQUERY(LinkedServerName,"SELECT * FROM OracleTableName WHERE I_ID = '
+ '' + @ins_id + '' + '")'
select @sqlstring

EXEC sp_executesql @sqlstring

*Note...I had an additional data conversion from varchar to integer in my statement.

I hope this information helps any others who are having trouble performing this type of task.

Does the Oracle Provider for OLE DB ship with SQL Server 7? What service pack are you running on your SQL Server machine? I am having many problems getting an Update to run with OPENQUERY. I am using the Microsoft OLEDB Provider for ODBC (MSDASQL). Do you know if there are any issues with this provider that would prevent me from updating an Oracle table?

Thanks

Jen


------------
Jan at 8/7/00 10:56:26 AM

I thought this may be very helpful for those of you using SQL7 linked server to Oracle 7.3.4 Database. After much research and no answers when opening a case with Microsoft I finally figured out how to delete using Openquery. There are a few missing pieces of information that would of been a great help in BOL. The first is, the Microsoft OLE DB for Oracle is not the correct choice for the data provier as one might think. The correct choice is the Oracle Provider for OLE DB. The next important thing is that the Oracle table you are querying MUST have a unique index on at least one column not necessarily the column in your WHERE clause. Thirdly, you get much better performance and use of indexes if you put the WHERE clause inside the OPENQUERY statement. Here is the syntax that I found to work in my application:

DECLARE @ins_id varchar(7)
DECLARE @sqlstring nvarchar(2000)
Select @ins_id = '123456'
Select @ins_id = convert(int, @ins_id)
select @ins_id

select @sqlstring = 'DELETE FROM OPENQUERY(LinkedServerName,"SELECT * FROM OracleTableName WHERE I_ID = '
+ '' + @ins_id + '' + '")'
select @sqlstring

EXEC sp_executesql @sqlstring

*Note...I had an additional data conversion from varchar to integer in my statement.

I hope this information helps any others who are having trouble performing this type of task.

Jen,

We are running SQL 7 enterprise Edition with SP1 and Running Oracle 7.3.4 on the other side.

We have tried unsuccessfully to get the OPENQUERY to work with Updates and Deletes using the Microsoft OLEDB Provider for ODBC or the Microsoft OLEDB Provider for Oracle. When we came across the Oracle Provider for OLEDB which by the way comes on the Oracle 8i CD but is written for Oracle Version 7.3.4 and above. It loads when you do a Oracle client install of the ODBC (Not marked on the install screen as OLEDB). You can also download the Oracle Provider for OLEDB on the Oracle Technet site "so they say" but we could never get the 41MB download to work even trying the ftp method. We did make Oracle aware that it wasn't working so they may have fixed the problem by now.

http://technet.oracle.com/software/tech/nt/ole_db/software_index.htm

Once we created a link server using the Oracle Provider for OLEDB we could get the Deletes to work and use the indexes.



------------
Jen at 8/15/00 3:39:51 PM

Does the Oracle Provider for OLE DB ship with SQL Server 7? What service pack are you running on your SQL Server machine? I am having many problems getting an Update to run with OPENQUERY. I am using the Microsoft OLEDB Provider for ODBC (MSDASQL). Do you know if there are any issues with this provider that would prevent me from updating an Oracle table?

Thanks

Jen


------------
Jan at 8/7/00 10:56:26 AM

I thought this may be very helpful for those of you using SQL7 linked server to Oracle 7.3.4 Database. After much research and no answers when opening a case with Microsoft I finally figured out how to delete using Openquery. There are a few missing pieces of information that would of been a great help in BOL. The first is, the Microsoft OLE DB for Oracle is not the correct choice for the data provier as one might think. The correct choice is the Oracle Provider for OLE DB. The next important thing is that the Oracle table you are querying MUST have a unique index on at least one column not necessarily the column in your WHERE clause. Thirdly, you get much better performance and use of indexes if you put the WHERE clause inside the OPENQUERY statement. Here is the syntax that I found to work in my application:

DECLARE @ins_id varchar(7)
DECLARE @sqlstring nvarchar(2000)
Select @ins_id = '123456'
Select @ins_id = convert(int, @ins_id)
select @ins_id

select @sqlstring = 'DELETE FROM OPENQUERY(LinkedServerName,"SELECT * FROM OracleTableName WHERE I_ID = '
+ '' + @ins_id + '' + '")'
select @sqlstring

EXEC sp_executesql @sqlstring

*Note...I had an additional data conversion from varchar to integer in my statement.

I hope this information helps any others who are having trouble performing this type of task.