DTS from SQL to Oracle generates a select * from tbl on Oracle srv

[py1rdj]

I have a DTS created to insert data from a table on SQL Server 7 in a table on an Oracle Server. The DTS runs fine and inserts the data correctly.
The Oracle DBA has complained to me that my DTS is running a SELECT * FROM tablename on the Oracle Server every time it runs. The table has more than 800,000 rows and the DTS must run every minute, so he is having a serious performance issue.
My DTS only inserts the data, and I can't find anywhere on its configuration that sends this SELECT to the Oracle Server. I've already checked the ODBC driver log, and I could see that SQL Server is really sending this command. And I'm sure this DTS is the responsible, since when I disable the job, the SELECT doesn't happen.
I've already tried to do the same insert using Linked Server, but the Oracle DBA told me that my insert also creates a SELECT that is worse than the first one.
I'm using MS ODBC Driver for Oracle to make the connection.
This is the code I used with Linked Server:

insert into FPWPRPD..EPADM.ENTRREAL
select * from CMS.dbo.TB_ExpPontoOnLine2

Any help is apreciated.
Thanks.

Andre Silva
MCDBA

 

[SQLSister]

it looks to me like you are inserting the entire contents of the table every time since there is no where clause. Is this what you intended to do?

Since Idon;t know Oracle, it would be hard to say what the problem is. But check withyour Oracle dba ass to how he woud write the insert statement if the he was sending the dat from one of his tables to another of his tables. Maybe there is something differnent inhow Oracle implemtns the insert statement.

 

[py1rdj]

it looks to me like you are inserting the entire contents of the table every time since there is no where clause. Is this what you intended to do?

Yes, that´s correct. Before I insert the data I truncate the table on SQL and populate it again with new data.

I´ve already tried to talk to the Oracle dba, but he told he can´t do anything, and I must stop doing the select * from ENTRREAL on his server. As I wrote, i don´t want to send this command, but SQL Server does. If I don´t get any solution I´ll have to buy Microsoft´s support.

Thanks anyway.

André.

 

[tlbroadbent]

Have you tried using the OLE DB provider for Oracle rather than ODBC?

You might be able to use OpenQuery in the following manner. I don't know if this will work with the Oracle provider. It does work with a linked SQL Server. The trick here is to create a record set with OpenQuery that contains zero rows hence the Where 1=2 clause.

Insert Into
OpenQuery(FPWPRPD, 'Select * From EPADM.ENTRREAL Where 1=2')
Select * from CMS.dbo.TB_ExpPontoOnLine2

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[py1rdj]

I tried using OLE DB provider for Oracle when I did the insert through Linked Servers using the code above. The Oracle dba told me that doing the insert this way generated a even worse select on his server. I'm not familiar with OpenQuery. What does it do?

Thanks in advance.

André

 

[py1rdj]

I checked OpenQuery on Books OnLine and it seems to work on Oracle providers. I'll try it and let you know.

Andre.

 

[tlbroadbent]

My concern isn't whether OpenQuery works for Oracle. I'm just not sure if the OLE DB provider for Oracle allows inserts into OpenQuery. I do believe you will need to use the OLE DB provider in order to use OpenQuery.

OpenQuery "Executes the specified pass-through query on the given linked server, which is an OLE DB data source. The OPENQUERY function can be referenced in the FROM clause of a query as though it is a table name. The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider." Source: SQL BOL Transact-SQL Reference

http://msdn.microsoft.com/library/en-us/tsqlref/ts_oa-oz_5xix.asp

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[py1rdj]

It worked!

Using OPENQUERY forced the Oracle Server to run a controlled SELECT.
I only had to change the WHERE clause to match the PK Index, so the query would have the least cost as possible.
With the original SELECT I had a cost of 5886. With the SELECT suggested by you it lowered to 307, and when I changed the WHERE clause it dropped to 3.

That's the new code:

INSERT INTO
OPENQUERY(FPWPROD, 'SELECT MRSTATUS, MRDATA, MRHORA, MRMATRICULA, MRCOLETOR, MRCODEMPRESA, MRCODAGENCIA, MRCODPAB, MRENTSAI, MRSTATUSENV FROM EPADM.ENTRREAL WHERE MRSTATUS = '' A '' AND MRDATA = ''01/01/2000'' AND MRHORA = '' A '' AND MRMATRICULA = 9999999999 AND MRENTSAI = '' A '' ')
SELECT MRSTATUS, CONVERT(datetime, MRDATA), MRHORA, MRMATRICULA, MRCOLETOR, MRCODEMPRESA, MRCODAGENCIA, MRCODPAB, MRENTSAI, MRSTATUSENV FROM TB_ExpPontoOnLine2

The new WHERE clause contains values that I'm sure don't exist in the table.


Thank you very much for your help!

Andre Almeida

 

[tlbroadbent]

Glad you got it to work.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm