Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2003
    Posts
    6

    Unanswered: 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,
    Tony

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes, 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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2003
    Posts
    6
    Thanks Brad!

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2003
    Posts
    6
    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

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    See the attachments

    The txt file should be the bat, but it didn't let me upload a bat file
    Attached Files Attached Files
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Attached Files Attached Files
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Aug 2003
    Posts
    6
    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?

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Place something like this in the *.sql file

    EXECUTE msdb..sp_start_job 'Load_Ledger_Init_sp'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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 data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The error WILL show up in the log, but Control-M will not know that the error occurred, unless you use -b.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...

    Code:
    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
    Last edited by Brett Kaiser; 12-02-04 at 10:01.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •