Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-03-24 : 07:41:56
|
| tim writes "Hi there,I'm trying to schedule some DTS package execution in SQL 7.0, and I've run into a few different problems, using both ODBC and OLE Db connections to an AS/400 IBM iSeires.First of all, they run fine when I execute them from the server manually using my own machine (right click -> execute), using either the ODBC or OLE DB connections.To schedule them I created a job with the following command line call:dtsrun /sServer /uUsername /pPassword /nPackageNameand at first--using the ODBC connection--I got the following error:DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217887 (80040E21) Error string: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217887 (80040E21); Provider Error: 0 (0) Error string: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.After some initial research, someone suggested trying the OLE DB connection to the source. I tried this, and though it seemed to get me a bit further, this is the error I am now getting:DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147008507 (80074005) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error Detail Records: Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error: -2147467259 (80004005); Provider Error: 8002 (1F42) Error string: CWBSY0002 - Password for user USER on server SERVER2 is not correct Error source: IBMDA400 Session Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.Strangely, every time I open the OLE DB source Connection Properties window in the DTS Package editor, the password field is blank, regardless of the number of times I enter it and click "OK". However, I am never prompted for my password when I execute the packages manually. Also, I can login to the AS/400, so I know my password is still valid.At one point I found some articles saying I might need to update the MDAC version on the server (my own machine is running v 2.71, and the server is only running v 2.5), but the same article said that version 2.5 or above would fix any problems, the same version the server already has.Anyone have suggestions/ideas? Any help would be MUCH appreciated. Thanks in advance,Tim" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-24 : 07:47:37
|
| The first thing you should check is that the OLEDB/ODBC drivers for AS/400 are installed correctly on your SQL Server (not your desktop machine). Once that's done you should double-check the user and password used to connect to the AS/400 to make sure they're correct. Windows Authentication, or some other type of pass-through authentication, will not work because when the job runs on a schedule, it runs under the account used by SQL Agent. If this is LocalSystem, then it won't even be aware of the network and won't be able to connect. You will probably have to use an specific user name and password.Unless you are running your SQL Server in a clustered environment, I'd recommend updating the latest version of MDAC. Even if it doesn't need it, it could fix some other bug you might not be aware of, and improve performance. |
 |
|
|
|
|
|
|
|
DTS scheduling errors