SQL Server Performance Forum – Threads Archive

DTS to oracle sporadically bring back 0 rows

SQL SERVER 2000 SP3 Operating System:Windows 2000 A DTS package uses ODBC driver to connect to a oracle data source, on occassions, brings back 0 rows for a specific table ( even though there is data to satisfy the condition on the oracle database). We have tested this extensively with our Oracle DBA’s and the problem doesnt seem to be on the Oracle side. The package runs every half hour and brings data to SQL Server from 7 different tables. Data from the other tables seem to be coming through fine. I have tried using OLE DB / ODBC for connectivity. Fails using both. Also the OLE DB driver does not pass the where caluse and sends a full table scan to Oracle. I have tested several different permutations and combinations with this and there is no consistency in the behaviour. ( with where clause, Without where clause, with Join to another table, without joins, bringing back the whole table). Scheduled the same thing outside of DTS and SQL using VB script and XP scheduler and also oracle SQLPlus and xp Scheduler to eliminate any timing issues and they work fine… This package has 7 or 8 transformations. Has anyone had any experience with more no.of trnasformations being a issue? Thanks. Any input is appreciated.

---

This is what I have done with similiar issues. Ran the query (Select statement) from SQLPlus in Oracle if this brought back data then I know my query was not the problem. In the tranformation task under source tab when you hit preview does it bring back data (If it is a large data set limit the criteria) if it brings back data then I know it is a problem with the tranformations or something else. What kind of tranfromations are you performing? Can you post some code? Raulie
Hewlett-Packard

---

]]>