Return Code from OSQL

Question

Return Code from OSQL

imani_technology

SSCrazy Eights

Points: 8200
More actions
September 15, 2016 at 6:03 pm

#149283

I have the following statement in a batch file. However, how can I get a return code to let me know the SQL Agent job ran successfully?
osql -S [Server Name] -U [login] -P [password] -Q”exec msdb.dbo.sp_start_job ‘[SQL JOB NAME]'”

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

imani_technology (9/15/2016)
I have the following statement in a batch file. However, how can I get a return code to let me know the SQL Agent job ran successfully?
osql -S [Server Name] -U [login] -P [password] -Q”exec msdb.dbo.sp_start_job ‘[SQL JOB NAME]'”

There is no return code for the job running successfully. sp_start_job succeeds when the job starts, fails if the job doesn't start. It's done at that point and doesn't know if the job is successful or not. Just (and only) the starting of the job.

Sue

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 2

Sue_H (9/15/2016)
imani_technology (9/15/2016)
I have the following statement in a batch file. However, how can I get a return code to let me know the SQL Agent job ran successfully?
osql -S [Server Name] -U [login] -P [password] -Q”exec msdb.dbo.sp_start_job ‘[SQL JOB NAME]'”
There is no return code for the job running successfully. sp_start_job succeeds when the job starts, fails if the job doesn't start. It's done at that point and doesn't know if the job is successful or not. Just (and only) the starting of the job.
Sue

How can I get some kind of error code from the SQL job (in this case, the job will process an SSAS cube) back to the batch file?

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

imani_technology (9/16/2016)
Sue_H (9/15/2016)
imani_technology (9/15/2016)
I have the following statement in a batch file. However, how can I get a return code to let me know the SQL Agent job ran successfully?
osql -S [Server Name] -U [login] -P [password] -Q”exec msdb.dbo.sp_start_job ‘[SQL JOB NAME]'”
There is no return code for the job running successfully. sp_start_job succeeds when the job starts, fails if the job doesn't start. It's done at that point and doesn't know if the job is successful or not. Just (and only) the starting of the job.
Sue
How can I get some kind of error code from the SQL job (in this case, the job will process an SSAS cube) back to the batch file?

Easy... stop using OSQL... especially with a clear text login and password.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 4

As Jeff indicated, osql is not a good thing to be using. It's on the deprecation list so you shouldn't be writing something new with it. Sqlcmd has been the recommended replacement for the last 10 years or so.

If you need something done after the processing, put it on the next step of the same job. If you want a notification, put it in the job.

Sue

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 5

I replaced the old command with something like this:

SQLCMD -S [Server Name] -U [login] -P [password] -Q”exec msdb.dbo.sp_start_job ‘[SQL JOB NAME]'”

I still haven't figured out how to get a good return code yet, though.

NOTE: it turns out I need more than a simple return code. The requirement is to create a log file in .csv format. I don't think SQL Server Agent can create this type of .csv file. And even if SQL Agent can do this, the requirement is to create log files that are separate from what SQL Agent is doing. No, I did not create the requirements.

Sue_H SSC Guru Points: 90860 More actions · Answer 6

Since you said earlier that this is in a batch file, you need to look at checking the errorlevel in the batch file. See a similar discussion in this thread:

http://www.sqlservercentral.com/Forums/Topic1735929-23-1.aspx

Keep in mind that when calling sp_start_job, the return only tells you if the job started and that's all. If the job starts, it succeeds, if the job doesn't start it fails. If the job itself fails, it will not know.

Sue