Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    1

    Unanswered: OSQL Programming to create Jobs

    I have a two Jobs in SQL Agent.
    The first job has a TSQL script which checks for the value of a flag in one of the DB table. If the value is "1" it starts the second Job.
    So the first Job is basically a trigger.

    Second Job - It has two steps. First step executes T SQL script and on failure it quits. On success it goes to second step
    Second step executes a DTS package.

    The require ment is :- My new environment will not support any Job scheduling through SQL Agent. So I need to replicate the same logic in BAT file ( This BAT file will be shceduled through TWS)

    The problem :- I have created the BAT file but unable to replicate the 'Onfailure' and "on Success" action from second step.
    My BAT file looks like.
    osql -E -d T_PBM -Q "if (select load_ind as JobRunIndicator from dts_flag where dts_id = '10000') = '1' update dts_flag set load_ind = 0 where dts_id = '10000'"
    DTSRun /S "LSHTST046" /N "pbm_invoice" /E

    My requirement is to exit the first step ( the osql part ) if upate statement does not run ( if value os 0).
    Else it should update the table and execute the package.
    I am new to Batch programming to do not have mucb ideas/
    Please help.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Here's the concept you can use:
    Code:
    osql -S. -d AdventureWorks -E -Q"if (select FirstName from Person.Contact where ContactID = 1001) = 'Terri' update Person.Contact set FirstName = 'Terry' where ContactID = 1001" -oupdated.$$$
    dir updated.$$$ | find " 0 bytes"
    if %errorlevel%==0 (
       echo Nothing updated
    ) else (
       echo Update successful
    )
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by rdjabarov
    Here's the concept you can use:
    Code:
    osql -S. -d AdventureWorks -E -Q"if (select FirstName from Person.Contact where ContactID = 1001) = 'Terri' update Person.Contact set FirstName = 'Terry' where ContactID = 1001" -oupdated.$$$
    dir updated.$$$ | find " 0 bytes"
    if %errorlevel%==0 (
       echo Nothing updated
    ) else (
       echo Update successful
    )
    NEVERMIND misunderstood
    Last edited by PMASchmed; 10-08-09 at 14:05.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    TYPE will display the contents of UPDATED.$$$. I am looking for the size of the file reported by DIR command. So no, it should be DIR.
    "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
  •