November 15, 2007 at 11:58 am
I have a DTS package that is failing on a regular basis but not nightly
meaning sometime it runs fine and sometime it fails. The error I recieved
this morning is 'Error at destination for Row number 8672. Error encountered
so far in this task: 74 unspecified error.' Can I go to row 8672 of the
destination table and assume this is the record providing the error? ]
I have looked at the record in he given row, the record has not been touched since 2002 and I do not see any problems with the data.
I am also curious to know how the DTS package is detecting errors if they are
'unspecified errors'. Can someone explain to me how this works?
The second error from another table gives the follwoing error 'Expected the text
length in data stream for bulk copy of text, ntext, or image data'.?
I am not sure what the package is expecting and the research I have turned up
on this is topic is not really specific to what should be done to correct the problem.
Thanks in advance for any help provided.
James Pettigrew
Systems Manager
November 26, 2007 at 11:07 am
Unspecified means there was an error, but no one wrote a handler for it.
You can try a few things. If you can manually run the DTS package, run it against the source, but limit it to xx rows, like 8670. See what data gets imported. Then up it until you get the error and try to find the row, chances are you have a badly formatted piece of data.
November 27, 2007 at 2:57 am
The message actually means 'the input data that relates to destination row xxx caused an error'. You need to work out what is the input data that would cause row xxx to be changed, then look at the SQL code that makes the change. If necessary, run the process manually so you get the full error text in Query Analyser. This should identify your problem.
How you solve it is another story...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 27, 2007 at 9:56 am
Thanks for the reply. Just to provide more information on what is occurring. The job has multiple steps (5) first three being DTS packages and final two scripts that produce quality assurance alerts to users for missing data in fields used for reporting. The package fails after step 1 and it is sporadic. For example the job was successful 11/24 and 11/25, but failed on 11/26. As I look back at the history the maintenance package has failed about every other day.
When I run the entire job manually it has been successful. Could there be something interfering or interrupting the job or do you still think it is data related?
James Pettigrew
Systems Manager
November 28, 2007 at 2:27 am
You may need to look at the memory available to DTS.
My old shop had a situation where DTS batch jobs sometimes failed for no apparent reason. When we looked at the schedules, the majority of failures were when we ran multiple DTS jobs at the same time. Business requirements prevented us eliminating all of the parallel running.
We looked at SQL max and min memory, and that seemed to allow enough for DTS. This was proved when we reduced max memory but did not reduce the failure rate.
We then added the -g startup parameter and tried a few values. When we got up to about 100 MB more then the default for -g, the DTS jobs ran reliably.
This may not be relevant to your situation, and if it is the value you need for -g may be different to my old place, but if you cannot resolve the problem by other means this is worth trying.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply