Running a job via osql?

Hello All,
A VERY green SQL Server DBA here looking for some help. Our main production environment is Oracle, which utilizes Control-M as a scheduler. At the end of the Oracle batch process, we would like to automate a process to kick off a sql server job (perhaps via osql??) Is this possible?

Thanks in advance,
TonyYes, just create a bat file with the osql commands in it and execute the bat file..I would imagine you would have to have osql on the oracle box...so if it's unix, then I'm not sure...|||Thanks Brad!|||ummmm...That's Brett

Or "x002548"

Or "HEY YOU"

Or "You look like a man who needs a drink"

Or "Coach" (As in hey coach..ummm...can I play forward? 13 voices at once)

Don't forget to redirect the output to a log so you can track what happened...that would be from a second bat file calling the one with the osql commands...

I like this approach, because it kind reminds me of my old JCL home

Weep no more, my lady,
Oh weep no more today!
We will sing one song for the old JCL home,
For the old JCL home far away.|||Brett,
My sincerest apologies!! That's what I get for multi-tasking & not paying attention when I type.

Our scheduling software has a windows client version that would allow us to excute the .bat file on the SQL Server box.

In order to call the job, do I just refer to the Job Name in the /Q [query] portion of the osql "statement"?

Server- PROD01
User- Tony
Password- Test
Job Name- Get1|||See the attachments

The txt file should be the bat, but it didn't let me upload a bat file|||ok, it only allows 1 upload per post..

This is a sample sql that osql executes

Well maybe it would allow more than 1, it didn't like the *.sql extension which is what it was|||Very Cool!! Once again, many thanks. I know that yours was executing a .sql script. How do I get it to recognize a pre-exisiting SQL Server Job?|||Place something like this in the *.sql file

EXECUTE msdb..sp_start_job 'Load_Ledger_Init_sp'|||The job and subsequently the script that calls the job need to have an error trapping mechanism. With current syntax of both batch file and SQL script Control-M will report SUCCESS even if OSQL part fails. At a minimum add -b to OSQL to force it to fail if error occurs.|||The osql failure will show uo in the log.

I usually load the log to a table to interogate what happened.

Whats -b?

yeah, yeah, BOL|||The osql failure will show uo in the log.

I usually load the log to a table to interogate what happened.

Whats -b?

yeah, yeah, BOL

EDIT:

-b

Specifies that osql exits and returns a DOS ERRORLEVEL value when an error occurs. The value returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity of 10 or greater; otherwise, the value returned is 0. Microsoft MS-DOS batch files can test the value of DOS ERRORLEVEL and handle the error appropriately.|||The error WILL show up in the log, but Control-M will not know that the error occurred, unless you use -b.|||Good point.

But I still would interogate the log anyway...but thanks for the info...

Anything with a severity error of less than 10 though still returns a zero...

EXINFO 1 Informational, nonerror.
EXUSER 2 User error.
EXNONFATAL 3 Nonfatal error.
EXCONVERSION 4 Error in DB-Library data conversion.
5 The server has returned an error flag.
EXTIME 6 Time-out period exceeded while waiting for
a response from the server; the DBPROCESS is still alive.
EXPROGRAM 7 Coding error in user program.
EXRESOURCE 8 Running out of resources; the DBPROCESS may be dead.
EXCOMM 9 Failure in communication with server; the DBPROCESS is dead.
EXFATAL 10 Fatal error; the DBPROCESS is dead.
EXCONSISTENCY 11 Internal software error; notify your primary support provider|||Sample fool-proof batch for Control-M:

OSQL...
if errorlevel 1 goto ErrorHandler
goto end

:ErrorHandler
echo OSQL ended with code %ERRORLEVEL%!
exit /B 1

:end|||Fine, but what about the severity errors below 10?

You're not suggesting that they completed. And since the return code will be 0, you still have to check.

No?|||Sample fool-proof SQL script (pseudo):

<sql_statement>
if @.@.error != 0 begin
raiserror ('The last statement failed (<WHAT STATEMENT? BE SPECIFIC!>)', 15, 1)
return
end
...|||are you sure it's not brad?