SQL Server Performance

Problem in Scheduled Jobs

Discussion in 'SQL Server DTS-Related Questions' started by abhishekkv, Aug 10, 2004.

  1. abhishekkv New Member

    Hi
    I am facing a strange problem.

    There is a DTS which is porting conditional data from a SQL Server 2000 to Sybase SQL Anywhere database using a linked server. The comparison is done using a linked server. The DTS has been scheduled everyday at a specific time. However whenever the job is scheduled it fails giving the following error:

    Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
    Step Error Description:[Sybase][ODBC Driver]Unable to connect to database server: database engine not running
    Step Error code: 80004005
    Step Error Help File: Step Error Help Context ID:0


    There is no problem with the Sybase database which is the destination database in this case and is running fine.

    However when I am executing the same DTS manually on the server it is executed successfully without any kind of error.

    Also when I schedule the job from a test server with the same linked server settings the job executes successfully.

    Can somebody help me out???

    Regards
    Abhi.
  2. Luis Martin Moderator

    How about permission?. Job was created by sa?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  3. satya Moderator

    Check whether SYBASE server is running or not?
    And the login used to connect SYBASE has required privileges.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. abhishekkv New Member

    Hi,

    The job has been created and is scheduled to be executed by a user belonging to "dbo" group.

    As mentioned earlier, there is no user rights problem nor any connectivity problem with the Sybase database as the same DTS gets executed successfully when ran manually on the server but gives the above mentioned error when scheduled through a job.

    I know that the problem is difficult to reproduce elsewhere outside the existing working environment but suggestions are always welcome as you may suggest something which I may not have even thought about. Anyway thanks for your replies.

    Regards,
    Abhi.
  5. Argyle New Member

    Isn't there seperate drivers for SQL Anywhere? Are you connecting with those through a DSN? If it's a User DSN it might not be configured the same for your admin user (that works) and the sql agent user. Try with a System DSN using the SQL Anywhere drivers.

    Or it could be that the sql agent user does not have rights to access the SQL Anywhere odbc drivers but your admin user has.
  6. mpex04 New Member

    Remember that when you run a DTS package locally, it actually executes on your own PC using your security and DSN settings. When the job runs out of the scheduler it actually runs under the specified owner.
  7. bhupinderd New Member

    what you had to do to resolve this issue.

Share This Page