Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    6

    Unanswered: Scheduled DTS job to run a cmd file not working

    Hi,

    I have created a DTS job that contains one 'Execute SQL Task' job. This SQL task runs a cmd file. The cmd file runs a few windows commands and then runs a Micorosoft Access Macro. Once finished both access and the cmd screen close down.

    If I open up the DTS job and execute it manually, it works fine (takes about 1/2 an hour to run). My problem is that when I schedule the DTS job, the job starts up at the correct time but it never actually starts running the cmd file and it gives no error. It says Executing until I actually stop the job manually.

    The Job details are:

    Type: Operating System Command [CmdExec]

    Command: DTSRun /~Z0x2F3FF84472BB6E7FF356EB006BA1AEC62C95AB3BF506F3 4A241F228CE148AB09DBC66B8651A450B725E6C4E6A1D328E4 EC2F2C0F8E323F1C7D501FD5B8FD00E25656514AF2224407DB 1C569163CBE383A8E7D8BE4974A0911F5CEB

    The DTS details are:

    C:\Batches\DTSrunofUpdatesqlpmi.cmd

    The cmd program details are:

    echo on
    tart

    if not exist "c:\apps\CI_Databases\pmi\pmiload.mdb" exit

    rem cleanup unfinsihed runs
    if exist "c:\apps\CI_Databases\pmi\pmiload.mdb" if exist "c:\apps\CI_Databases\pmi\pmiloadold.mdb" del "c:\apps\CI_Databases\pmi\pmiloadold.mdb"

    rem main file locked assume its being work on so don't run this job.
    if exist "c:\apps\CI_Databases\pmi\pmiload.ldb" exit

    rem if a tmp exits then the last job never completed - continue to add to tmp file

    if exist "c:\apps\CI_Databases\pmi\pmiloadtmp.mdb" goto Load

    copy "c:\apps\CI_Databases\pmi\pmiload.mdb" "c:\apps\CI_Databases\pmi\pmiloadtmp.mdb"

    :Load
    "C:\Program Files\microsoft office 2003\OFFICE11\msaccess.exe" "c:\apps\CI_Databases\pmi\pmiloadtmp.mdb"/x updatedata
    rem Compact databases
    "C:\Program Files\microsoft office 2003\OFFICE11\msaccess.exe" "c:\apps\CI_Databases\pmi\pmiloadtmp.mdb"/compact

    ren "c:\apps\CI_Databases\pmi\pmiload.mdb" "pmiloadold.mdb"
    ren "c:\apps\CI_Databases\pmi\pmiloadtmp.mdb" "pmiload.mdb"
    if exist "c:\apps\CI_Databases\pmi\pmiload.mdb" if exist "c:\apps\CI_Databases\pmi\pmiloadold.mdb" del "c:\apps\CI_Databases\pmi\pmiloadold.mdb"


    All help is greatly appreciated as this has been bugging me for some time now.

    Thanks
    Sam

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    If all your DTS package is doing is running a .cmd (batch) file, why not run it instead from a scheduled task. In the schedule task step, select task type of Operating system command and type in the path and name of the batch file.

    This may not resolve the problem, however. I suspect that the problem lies within permissions. What account is the SQL Agent user set up to run under? Then think about what permissions your account has that the SQL Agent account might not have.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Jul 2002
    Posts
    6
    Thanks hmscott.
    That works when i change the SQL Server Agent startup account username and password.

Posting Permissions

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