Post reply

Problems Scheduling DTS ?

  • February 2, 2005 at 3:09 am

    #60228

    I'm haing a problem with a new SQL server 2000

    I have a DTS job that copies a table from an access database, and then a SQL Agent that uses some TSQL to modify some data

     

    the DTS package works perfectly if you run it manually - BUT I don't seem to be able to shedule the package to ru automatically

    The SQL Agent that runs the TSQL is working perfectly

     

    Its driving me NUTS !!

     

    the services are all running as the administrator account and have access to then local desktop

     

    Any suggestions greatly appreciated

     

    Mike.

  • February 2, 2005 at 5:41 am

    #540127

    Sounds like a permission problem.

    Make sure that the account that sql server agent is running under has the right permissions.

    Also check your sql server settings by running dcomcnfg.exe from the command prompt and add the SQL Service account to Default Access Permissions under Default Security tab.

    ------------------------------
    The Users are always right - when I'm not wrong!

  • February 2, 2005 at 6:22 am

    #540136

    Hi Johnathan

     

    thanks for the prompt reply

    I installed a copy of SQL server on my local machine and i can't schedule a dts there either !!

    however - just noticed that we have a copy of SQL server 2000 "Standard" edition - could thios be the problem ?

    is this feature disabled on the std edition ?

     

    Thanks Mike.

  • February 2, 2005 at 6:25 am

    #540138

    Can you schedule the package and it fails or you cannot setup a schedule at all.

  • February 2, 2005 at 6:39 am

    #540142

    It allows me to schedule a package ie every 1 minute

    but when you click on properties - its as if it was never scheduled ! - the last data was not saved ??

  • February 3, 2005 at 2:03 am

    #540311

    Hi all,

    can you check the account who runs the job in job properties!!

    It musnt be a problem with the service accounts. When you design a job with your account, the standard option is "the job runs under your account" you can change this in your job properties. Check the system rights for the account the job runs.

    I hope this helps you.

     

    Kind Regards

    Michael

  • February 3, 2005 at 2:32 am

    #540315

    A simple thing that has tripped me up a few times is that both the job and the schedule need to be enabled to run. These are on 2 separate tabs of the job properties.

    Bill.

  • February 3, 2005 at 5:57 am

    #540334

    I had a similar problem.  The way I got it to work was to create a batch file using the dtsrun utility and schedule it in "scheduled tasks" to go off every 30 minutes.  Try this substituting your database name, the name of the dts package, and the password of your SQL server. 

      dtsrun /SMyDatabase /NMyDTS /Usqlagent /Ppassword 

  • February 3, 2005 at 11:02 am

    #540464

    Thanks for all the help and advice

    I have traced the problem to a simple permissions error on the fileserver where the database existed !!! - the scheduler did not have access to this drive

    I was hopping mad when i found out !!

    oh well its sorted now

    Thanks again

Viewing 9 posts - 1 through 8 (of 8 total)

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