Capture osql error message in a batch file

Question

Post reply

Capture osql error message in a batch file

simone_s

Old Hand

Points: 330
More actions
April 28, 2004 at 10:32 am

#111066

Hi everyone. I need HELP
Suppose that we have this situation. I want to run on different machines a batch file consisiting from a series of SQL script. I use Visual studio .NET and make the command file. All it's Ok till now.
But. At the end of the script, I need to write in a specific table a record with the succes or failure of the entire job. All this is done. But, in the same table I also must write the error message in case of failure and the "problem script " if I can say so.
Please anybody can tell me how can I read, in what variable, the body of the error message.
To be more specific, I write down the final part of my script, where I want to insert my variables.
osql -S %1 -d %2 -U %3 -P %4 -n -b -r 1 -i "6 Proceduri\STC_RaportStocFizicLista.PRC"
if %ERRORLEVEL% NEQ 0 goto errors
========== the last valid osql command==========
osql -S %1 -d %2 -U %3 -P %4 -n -b -r 1 -Q "exec ADM18_VersiuniIntroducere '2003','2003.0.1','2004-06-01',1,Null, Null,'%COMPUTERNAME%',%USERNAME%"
if %ERRORLEVEL% NEQ 0 goto errors
===== if everithing is Ok, I record the succes of the operation, togheters with some other informations
goto finish
REM: How to use screen
:usage
echo.
echo Usage: MyScript Server Database User [Password]
echo Server: the name of the target SQL Server
echo Database: the name of the target database
echo User: the login name on the target server
echo Password: the password for the login on the target server (optional)
echo.
echo Example: MyScript.cmd MainServer MainDatabase MyName MyPassword
echo.
echo.
goto done
REM: error handler
:errors
osql -S %1 -d %2 -U %3 -P %4 -n -b -r 1 -Q "exec ADM18_VersiuniIntroducere '2003','2003.0.1','2004-06-01',0,'The name of the script who generated the error', 'The error message','%COMPUTERNAME%',%USERNAME%"
if %ERRORLEVEL% NEQ 0 goto errors1
:errors1
echo.
echo ATENTIE! Au fost detectate erori!
echo --------------------------------
echo Please evaluate the situation and, if needed,
echo restart this command file. You may need to
echo supply command parameters when executing
echo this command file.
echo.
pause
goto done
REM: finished execution
:finish
echo.
echo Script execution is complete!
pause
:done
@echo on

Viewing 4 posts - 1 through 3 (of 3 total)

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

Alamir SSCertifiable Points: 6085 More actions · Answer 1

I think the best thing is to make try

{

}

catch();

and in this you can try to run the SQL cammand that you may write in in a text file

and then catch the errors and print it..

I hope this help u.

Alamir Mohamed
Alamir_mohamed@yahoo.com

simone_s Old Hand Points: 330 More actions · Answer 2

Thanks, but..... a text file is useless for me, I need to update a record in database based on the state of running the script,

gljjr SSCrazy Eights Points: 9970 More actions · Answer 3

simone,

You should be able to set a variable to the script name before each osql call. Then use the variable to insert the script name into your logging table. I'm not sure you will be able to get the error text. I've never tried to do that.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.