Wait Type OLEDB ... Bad DTS

Question

Post reply

Wait Type OLEDB ... Bad DTS

HelpMeInOhio

Hall of Fame

Points: 3945
More actions
October 11, 2004 at 2:01 pm

#85623

I'm running a DTS Package that hangs at the end. I check its process out ant it is Waiting type OLEDB. It is an extract using OLEDB/ODBC to hit a non SQL Server database. What's up with it?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 1

What is the package doing when it is hanging? Try running it interactively from the designer to see.

The waittype means that the OLEDB conenction is waiting on something.

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 2

The package is copying 2 fields from an ODBC data source.

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 3

When I look at the process initially is shows sleeping and:

select collationname(0x0904D00034)

why is it looking for collationname?

William Millett SSC Enthusiast Points: 147 More actions · Answer 4

Prior to an upgrade of our production management database to Sql Server, we used SQLBase database by Gupta. For reasons we never could resolve, connections to this database would occasionally cease communicating but would stay open. The same state you find. I have also seen this with DTS packages connecting to Access databases. After efforts to fix the source problem were not progressing, we decided to work around the issue. We set up Jobs to run the packages and log the start. When a job had been running too long (yes, that's a trick, 'how long is too long?') we would check its state. This required a second job running whenever one of these quitters was running, and watching for its apparent naptime to start. When we caught a slacker napping, we stopped it, gave it some new underwear and started it up again (now, get out there and vote).

One 'slacker catcher' job and a log table was sufficient for all potential slackers. The first step of each slacker job was to wake up the catcher if it was not running and log its own presence. The middle step is where the slacker actually worked on something (like the DTS package - or midday voting, perhaps). The last step was to check the log table for other slackers and if none present, stop the catcher.

Jeff Burton SSCommitted Points: 1551 More actions · Answer 5

You did not mention the nonSQL db you were accessing. Also, how long is it hanging? Have you tried setting/varying parameter "fetch buffer size"? Have you tried splitting the data pump task into 2 tasks (nonSQL db table -> file -> SQL db table)?

I experienced the same hang/stall at the end of a data pump task from a nonSQL db table (Oracle/Rdb) into a SQL db table. The stall time was more significant in larger nonSQL tables, but as a percentage of time of the task run time it was fairly small and the task did eventually complete.

Jeff

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 6

Pervasive is an SQL database accessed via ODBC Connections.

How long is it hanging? Hours ... eventualy starts ... then hangs again after 1.2 million records of 5 million expected to extract. We have space and 8 processors.

Have you tried setting/varying parameter "fetch buffer size"? Yes.

Have you tried splitting the data pump task into 2 tasks ? No. So I can set up several each with a different date range ... when is this approach normally uses ... high volume situation?

Thanks for your replies and questions and comments.

Jeff Burton SSCommitted Points: 1551 More actions · Answer 7

Here are some other things to try that we have done to get around problems in nonSQL ODBC access:

(1) Also, if you have not done so already, remove any constraints/indexes on the SQL destination table (and add a SQL task to recreate them after the data load). Use an intermediate table if needed.

(2) Use nonSQL utilities for extracting the nonSQL table to an intermediate file.

(3) If possible, configure a read-only connection to your nonSQL data. (This is an ODBC data source setting.)

(4) Investigate upgrading to a newer version of the nonSQL ODBC driver.

Jeff

Michael Girdler Right there with Babe Points: 780 More actions · Answer 8

Isn't Pervasive fun. Are you running your odbc connections to Pervasive using the client engine or the Engine interface? Pervasive also works in interesting ways... it can actually download the records to the local machine and use the local machine to process them....... You will find that there is a pretty substantial temporary file created locally and a fair amount of data being brought down over the network....

You may have to break the task into bits to have it process.

The option of extracting the Pervasive information to an intermediate one is also worth thinking about.

Pervasive also has issues with indexes..... you may need to look at the sql you are using...

Michael

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 9

I've got it running but it is slow. While ops is running tape backups of pervasive I get noting out of it but a 94 lock message. Any idea why? Is there a pervasive/sqlserver url I can jump to?

Thanks

Hans Lindgren SSChampion Points: 10496 More actions · Answer 10

Could it be that your batch size is very large and it is trying to commit the batch? I.e the 'first' million lines are fast when it might be dumping to your tempdb and the wait you are experiencing is when it is actually 'filling' your table?

//Hans

HelpMeInOhio Hall of Fame Points: 3945 More actions · Answer 11

The job ran for a month with no issue then suddently started to hang and the batch size didn't change : /. I'll try pulling 1 day rather than 5 and let you know what happens.