Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2007
    Posts
    5

    Unanswered: DTS Scheduling Issue

    I have a DTS package that runs without any problems when I run it manually, however, when I schedule the package at a certain time no output is produced.

    I have checked the Agent and it says the job is executing, however, it seems to have hung.

    The log tells me DTSStep CreateProcessTask3 started at the correct time but there is nothing after that.

    How do I check this? or find out what is causing it to hang?

    Thanks!

  2. #2
    Join Date
    Apr 2007
    Posts
    5
    Try running profiler at the time the job start starts executing to see how far the job get's. What user account is the job run under?

  3. #3
    Join Date
    Apr 2007
    Posts
    5
    Hey tr1na,

    Many thanks for your response it is appreciated. SQL server is new to me so apologies for the lack of knowledge

    I have never run profiler before, I have had a look and it doesnt look that simple to run and also, I couldnt see where I could call my DTS package.

    Anyway, in the package log file it states DTSStep CreateProcessTask3 started at 07.32 and well thats it! I have read somewhere that this could be a security issue and I should be logged in as SYSADMIN? However, I am currently Remote Desktop to the SERVER, login through NT, the package owner is my USER ID, how can I log into EM as SYSADMIN and try scheduling a DTS package under SYSADMIN, do you think this could be the problem?

    I am not sure how to check the LOGIN ID.

  4. #4
    Join Date
    Apr 2007
    Posts
    5
    To view which user the job is running as right click the job and select "view job history" then tick "show step details". You should then see

    "Executed as user: SYD\blah" etc

  5. #5
    Join Date
    Apr 2007
    Posts
    5
    Thanks!

    Well, unfortunately, the job does not complete because it hangs so there is no job history, however, when I cancelled to job it tells me that the job was cancelled prior to completion by my "USER ID". So I suspect this is the USER ID being used to start the job?

    Perhaps I need someway to remote desktop but login as SYSADMIN?

  6. #6
    Join Date
    Apr 2007
    Posts
    5
    when you run the job manually are you logged in as your userid. if so check the job runs under your user id.

  7. #7
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    there are differences of environment when u execute a dts by right-clicking and thru jobs. for example if u refer a server name as (local) in connection, during right-clicked execution the database will be selected from the server where EM is installed but during execution thru job it will connect to server from where the job is running. similarly any disk file path/reference to COM component also changes with the execution mode. and this is often the source of problem reported by u...

  8. #8
    Join Date
    Apr 2007
    Posts
    5
    OK. The job did not run manually when logged in by me, it does run manually when logged in to the database as "sa"

    upalsen:

    Thank you for your response, it is appreciated, how can I get round this problem, is it a matter of logging in as SYSADMIN, hopefully it is as something as simple as this?

  9. #9
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    there r a few settings under properties context menu (right-click) of SQL Server Agent. by default users other than sysadmin r not allowed to run cmdexec etc command. check the Job-System tab and Connection tab for such user specific issues. also a job can be saved under user other than sa. i am not sure exactly where your job is hanging but u can try the above combinations and check the result.

  10. #10
    Join Date
    Apr 2007
    Posts
    5
    Thank you.

    OK. Well the check box is checked "Only users with SysAdmin priviledges can run CmdExec etc..."

    So I uncheck the box, then it asks me to "Enter the proxy account SQL agent will use to run jobs owned by non-administrators"

    Does this mean I need to create a "dummy" account and does it need to be a member of the sysadmin group or have specific priviledges?



    Thank you.

  11. #11
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    u give the name & password of the local m/c administrator and enter the machine name as domain. if u r having a domain controller in your network u can specify the details of a domain account as well.

Posting Permissions

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