dcsimg
Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: dts owner and errors

  1. #1
    Join Date
    Dec 2002
    Posts
    29

    dts owner and errors

    I created a dts job which is run through a vb app. The job runs fine when I run it from my machine but fails when a different user using their machine trys to run the job. I have other vb apps running dts jobs so the code I'm using is the same however I noticed the dts job, created using SQL2000, that is failing for users has my name for the owner while all the other dts jobs have a blank owner. I was able to get around the problem by creating an identical dts job using SQL 7.0 enterprise manager which saved the job with a blank owner. Any ideas on what I can do in the future when I create DTS jobs using SQL2000
    Thanks.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Do you access remote file in dts? If so, ensure sqlagent service account has permission on remote file.

  3. #3
    Join Date
    Dec 2002
    Posts
    29
    That's a good point and I'll make sure but the two dts jobs are identical. They both reference the same remote data file and the user can run the dts package with no owner name but cannot run the dts package with the owner name specified.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Are they on same server? Does owner a member of sysadmin? If not, you should configure sql agent proxy account for them.

  5. #5
    Join Date
    Dec 2002
    Posts
    29
    Yes, both jobs, they're identical in everything but name, are on the same server. The job that has an owner attached to it and is causing me the problems is owned by my user name. However, my user name has all server roles, i.e. System Administrator, Security Administrator, etc.

  6. #6
    Join Date
    Nov 2002
    Location
    DE
    Posts
    246
    Faced a similar problem. Found a knowledge base article which does not match my symptoms at 100%. Anyway the first workaround did solve the issue.
    Look at: KB241643
    Last edited by andi_g69; 01-28-2003 at 04:04 PM.

  7. #7
    Join Date
    Dec 2002
    Posts
    29
    Read the arcticle but how did you change the owner? I tried EXEC sp_changeobjectowner... but received 'does not exist' error.
    I'm still stumped why all the jobs I created earlier on sql7 did not have a name in the owner field but any new jobs I've created using SQL2000 have my name attached and can only be run by me.

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    Can't use sp_changeobjectowner to change job owner since job is not db object. You can change it in em -> management -> jobs -> properties of the job.

  9. #9
    Join Date
    Dec 2002
    Posts
    29
    My mistake I used the word job to refer to a dts package. The problems I'm experiencing are all related to two packages I've created and tried to run through a vb app. They are identical packages except the one that works has a blank owner and the one that doesn't has my user name as owner.

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    You can try change owner by updating system table.

  11. #11
    Join Date
    Nov 2002
    Location
    DE
    Posts
    246
    Or you connect to the Server where the package resides using a SQL Login. Then open the DTS package and save it under a different name. It should have the SQL login you are connected as the owner now.

  12. #12
    Join Date
    Dec 2002
    Posts
    29
    I connected to the sqlserver using a SQL login, saved the package under a different name and the package is still saved with my nt name as the owner. Seems to disregard how I am connected in enterprise manager. I am unable to change the package owner.

  13. #13
    Join Date
    Dec 2002
    Posts
    181
    Check out the stored procedure sp_reassign_dtspackageowner in MSDB. It's fairly self explanatory and I've used it a bunch to change package owner names.

    Jeff

  14. #14
    Join Date
    Dec 2002
    Posts
    29
    I used the sp_reassign_dtspackageowner to save it with the owner ''. Still not able to run it from the user's machine. I had earlier tried saving the package from the Server as the local administrator, then domain administrator, then sa and none have worked. The only way I've had the dts package run properly from the users machine is using the package created on my old SQL 7.0 machine which for some reason saves dts packages with a blank owner name. The dts packages are all exactly the same except for the owner listed. This is very frustrating. I don't want to always have to use my old machine to create dts packages.

  15. #15
    Join Date
    Dec 2002
    Posts
    181
    I'm not sure what your resolution is. We don't have any 7.0 boxes around anymore, but I know I've been managing package owners since 7.0 came out. I'm curious as to how you are getting a blank owner in the first place. What user are you connecting to EM with on your 7.0 machine?

    Jeff

Posting Permissions

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