Error Capturing

[I_Server_Whale]

Hi All,

If I have a batch file which runs a MSSQL script by calling a utility program called 'osql', Will datastage capture if there is any error in the MSSQL script file?

I did execute the batch file. It runs fine in both conditions, i.e, it runs even when the MSSQL script is correct AND it also runs when it is filled with junk.

I just wanted to confirm this. Thanks!

Naveen.

[chulett]

That depends on how you script is written, so I would venture to say that the answer is 'no' right now.

All DataStage knows is the exit status of your wrapper script. If the script runs fine but there are problems in the sql, DataStage won't know unless you pass that failure out. Meaning, you'll need to do something to specifically check the success or failure of the MSSQL script and pass that back as the exit status of the script.

For example, I do something similar with Oracle and sqlplus. The output of the sqlplus session is redirected out to a filename and then that output is grepped for certain error messages we see when there are problems. If those are found, a non-zero exit status is passed back by the wrapper script.

[I_Server_Whale]

Thank you Craig!

I was thinking in the same lines. The MSSQL utility program 'osql.exe' can return a exit code when run from the DOS command line. I can capture this exit code by redirecting it to a text file, something like this:

Code: Select all

osql.exe -S DWDEV64 -U dataviewer -P guest -i e:\datastage\int400\input\POS_TML\myscript.sql -b

echo %ERRORLEVEL% > c:\project\job\status.txt

And then read this file 'status.txt' to check if it is a zero or non-zero code, and proceed accordingly. Is there any way that I can check this file through DataStage and abort the job if the exit code is non-zero?

Please let me know your valuable opinion and also if you have any suggestions. Thanks!

Naveen.

[elavenil]

You can read the file using OpenSeq/ReadSeq functions. Search the forum/help how to use these functions. DO the error handling based on the obtained exit code.

Regards
Saravanan

[chulett]

You'd need to combine what you are doing with the batch file into a routine if you want to go the 'read the results file' route. Use 'DSExecute' to run the bat file and then the sequential file operators to read the file. If the sql session echoes anything usable to the screen, it can be captured by the DSExecute command and processed without having to drop it to a file. Lastly, all you'd need to do is drop out a DSLogWarn for the 'calling' job to notice.

Or find a way to pass the exit status of the sql session back from the batch file. Simple in UNIX but my DOS days are too far in the past for me to have a clue how to do it in Windows.