Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    24

    Unanswered: Problem With SQL Server Job

    Hi,
    We have jobs successfully running on the server. Now we moved the server to a different IP address(still the same machine).
    I modified the DTS which has been scheduled as a job. In the DTS I modified the server IP address. Now when I ran the job I am getting the error message as " DTSRun: Loading... Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."
    If I run the DTS itself it is working fine. But it is not working as a job.
    But if I create a new job with the same DTS, the job runs fine without any error.
    I have logged into the machine with my own user account.
    Please some advise on this.

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Are you trying to run the encrypted job that DTS created originally? If so, the GUID points to the previous version, not the one you modified. If you change the IP and let DTS create a new job, it should be OK. Or, manually create an uncrypted job with DTSRUN (details are in BOL).

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    When you run it it runs under the context of your id, which has authority to right to the drives...

    When it's scheduled it runs under the context of the sql server agents account, which does not have rights...

    And because you made it an ip...it's leaving and coming back in to itself...and has to authenticate...

    Grant the accounts the rights to the drive...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Aug 2004
    Posts
    24
    Yes. I am running the encrypted job. But when I replaced this encrypted string with the new encrypted string (after I created the new job from the modified DTS), the job successfully ran.
    If we use DTSRUN we need to provide the username and password in the code which will be seen by other users when they check the properties of the job.
    So we are using the encrypted method.
    If we want to user the encryption methodology, and if there are 100s of jobs, do we have to recreate each job from the DTS and replaced the encryption string or there is any easier method?
    If we use DTSRUN method and if we provide username and password for sa in that string, will it be seen by otherusers when they try to open the job?

    Thanks

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Is the SQLAgent a Windows user? If so, use the /E switch for a trusted connection.

    Yes, you will have to create a new job for each encrypted string. Or script out one job, gather all the encrypted strings, and mass produce them.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Does it have to be DTS?

    Can't they be sprocs?

    Or is it too late?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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