Problem in Scheduled Jobs | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Problem in Scheduled Jobs

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
How about permission?. Job was created by sa?
Luis Martin
Moderator All postings are provided “AS IS” with no warranties for accuracy.
Check whether SYBASE server is running or not?
And the login used to connect SYBASE has required privileges. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
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,
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.
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.
what you had to do to resolve this issue.