This is a hard week to me

Question

This is a hard week to me

Hector Angello Silva Carmona

SSCrazy

Points: 2720
More actions
April 12, 2005 at 8:26 am

#66765

Friends i need help to get information about where occurs that error
Traspaso_Archivo_Bajas_CTC_PASO_5
Executed as user: CALLCENTER\_SQLAdmin. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnError: DTSStep_DTSExecuteSQLTask_3, Error = -2147217900 (80040E14) Error string: Invalid length parameter passed to the substring function. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217900 (80040E14); Provider Error: 536 (218) Error string: Invalid length parameter passed to the substring function. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_3 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
I need to determinate the procedure where happened that error
i dont know if the log of dts can say me the name of store procedure where occurs that errors, because i scanned the first procedure that was called by the dts and i didnt find some function subtring,left,right that were bad definied
mmm i know that some function like substring,left,right if the lenght is negative appears that errors for example substring('Hello',1,-1) , exists other cause that occurs ?
i need to find some fast way to find the name of SP where the error appears because the store procedure tree is very large.....and to do a trace is not an easy task
Thank you for read me and sorry my bad english...

Viewing 7 posts - 1 through 6 (of 6 total)

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

Japie Botma SSCrazy Points: 2990 More actions · Answer 1

If you got the error message by looking at the job history details, then you already know which dts package is causing the problem. The only way to determine which step in the dts package is causing the problem is to execute the dts package directly.

5ilverFox
Consulting DBA / Developer
South Africa

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

And after finding the proc involving the error, if all substring functions look correct, then the problem lies with the data being processed.

When this kind of errors occurs, it mostly is because there are unexpected data fed to the function. Easiest is to find that data, look at it, and then you can decide if you have garbage in there that can be removed (which will fix the problem) or if the code needs to be re-written in order to handle the 'unexpected' values. (which will also fix the problem)

/Kenneth

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

If you have sqk2k, script the dts-package as visual basic and then search for the DTSStep_DTSExecuteSQLTask_3.

This way you may find your way using the gui to repair/intervene the issue.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Teague Byrd Right there with Babe Points: 752 More actions · Answer 4

Hi,

I agree you should try to execute the DTS package directly. A feature on SQL2000 that I love is that you can right click on each task of the DTS package and execute them in sequence; one at a time. That way, you can narrow down the error to one specific step and hopefully narrow down your error. Also, like another poster said, the data may be the problem. You may need to do some pre-processing to eliminate "bad data". For instance if you have data coming in as all char but some is bound for numerically typed fields. You may have to do on isnumeric() check and strip out characters like "A" that have somehow gotten in the data.

Hasta Luego,

Teague

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 5

Thanks to all ..

i know the name of the procedure which the DTS executes, but my problem is that this procedure calls a lot of procedures,it is a great tree, and the error could is inside of any them, so i thought a solution ,i will execute the procedure with isql and to do a log file of the execution

exec master..xp_cmdshell 'isql.exe -Sdesing ................

then i could to seek the error into file

Server: Msg 536, Level 16, State 3, Procedure pb, Line 4

Invalid length parameter passed to the substring function.

thanks to everydody

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

you'll have to look for negative length parameter used in a substring function.

I've encountered this some times when using "dynamic" substrings based on CHARINDEX.

e.g. select substring(mycol, CHARINDEX('xx',mycol), (CHARINDEX('yy',mycol) - CHARINDEX('xx',mycol)) ...

This resulted in negative length parameters when CHARINDEX('yy',mycol) was not found and resulted to 0 (zero)

I hope this gets you on track