Scheduled Jobs

[ianclarke]

Hi

I am having a lot of difficulty setting up scheduled jobs to backup tables from Symposium Call Centre Software. The DTS packages are running fine, when called manually, but the jobs always fail to run.

The following is the error returned:
Executed as user: NHSDW\sqladmin. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

I have checked the ODBCs on the SQL Server and they are all running perfectly.

I hope you can help.

Ian

 

[SQLDenis]

This because when called manually they use the datasource that reside on your local machine
create the data source on the sql server (use terminal services to login to the machine)

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[ianclarke]

Hi Denis

By 'create the data source on the sql server' do you mean create the DTS and job while on the sql server? Or do you mean create the ODBCs on the server? I have checked the Data Sources on the server from Control Panel - Administrative Tool - Data Sources (ODBC), and they are fine.

Ian

 

[SQLDenis]

Well the message is Data source name not found and no default driver specified

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[DrSql]

In order to execute any ODBC connection related DTS pacakge it always looks locally where you are executing the job from. When you execute manually it run from you local PC odbc, when you schedule the job it looking for an ODBC on your SQL server. All jobs are executed by SQL Agent. To ovoid any conflicts in future I would reccomend you should always create DTS on the SQL server. Same goes for Data Trasnfomations to any files.
Hope this clarifies your question.

Dr.Sql
Good Luck.