Q
Manage Learn to apply best practices and optimize your operations.

The odbc driver is trying to create a SQL package

Can you please look at this error message? What does this mean, and where do I begin? I am a SQL DBA and know little about the iSeries 400. The library set up in the ODBC client is CLOCFILE00, but the error string references CLOCFILE0 - not the library.

 

DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2

DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2

DTSRun OnStart: DTSStep_DTSDataPumpTask_1

DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217865 (80040E37)

Error string: [IBM][Client Access Express ODBC Driver
(32-bit)][DB2/400 SQL]SQL7973 - SQL create package for DTSRUNFBA
in CLOCFILE0 has failed.

Error source: Microsoft OLE DB Provider for ODBC Drivers

Help file: 

Help context: 0

Error Detail Records:

Error: -2147217865 (80040E37); Provider Error: 204 (CC)

Error string: [IBM][Client Access Express ODBC Driver
(32-bit)][DB2/400 SQL]SQL7973 - SQL create package for DTSRUNFBA
in CLOCFILE0 has failed.

Error source: Microsoft OLE DB Provider for ODBC Drivers

Help file: 

Help context: 0

DTSRun OnFinish: DTSStep_DTSDataPumpTask_1

DTSRun: Package execution complete. 

The ODBC driver is trying to create a SQL package. SQL package is an object in the system that keeps the access plan of the statement (so when you run it again the database will have an execution pan ready).

The SQL package is not created, probably because of commitment mode issue. The package creation phase is a step that must run under commitment control. Therefore, if the very first connection to a remote system is with COMMIT NONE, then the packages will not create properly.

In order to check it please remove the package creation from the data source (in the performance tab disable package creation). If you use connection string you should set off dynamic. a good link for working with iSeries from ms env. written for "non-iSeries" can be found here.

==================================
MORE INFORMATION ON THIS TOPIC
==================================

Ask your systems management questions--or help out your peers by answering them--in our live discussion forums.

The Best Web Links: tips, tutorials and more.


Dig Deeper on Remote Access to iSeries

Why does our DTS package fail when executed from a separate server under different permissions? Our DTS package fails to execute when executed from a separate server under different permissions. Here are the specs:
  • Both Server1 and Server2 are on the same domain. Server1 is the SQL Server instance (Enterprise Edition) with SP4.

  • Server2 has a batch script on it that opens DTSrun and executes a DTS package on Server1 to bulkload some XML into a database on Server1 using a trusted connection.

  • Server2 has no instance of SQL Server on it; instead it has the SQL tools installed.

  • The owner executing the batch script (BBB) has appropriate rights in the database as a DBO and is a member of the bulkadmin fixed server role.

  • The permissions (BBB) executing the script are not the DTS package owner (AAA), nor are they members of the SA group.

  • Additionally, Server2 has the necessary files (XBLKLD3.dll) to create the XMLBulkLoad object, with the latest SQLXML and .NET 1.1 installed. However, the execution of the batch script on Server2 fails with the error message that permissions are denied on the createobject SQLXMLBulkLoad when BBB executes the batch script.

  • The executing account (BBB) has permissions to where the files being loaded are kept.

  • When the batch script is executed off a local XP Pro machine in the same domain with BBB logged on, the script executes successfully. The machine only has the SQL tools installed with no running instances.

  • Server2 is a Windows 2000 server.

  • Batch script executes successfully under the DTS package owner (AAA) off of a local XP Pro machine in the same domain. The machine only has the SQL tools installed with no running instances.
Is this a potential case of
cross-database ownership chaining (CDOC). However, the MS literature does not make specific mention that this would affect DTS package execution from another server. Specifically the documentation mentions that CDOC is between databases instances that access objects in another database, and in my case there is only one instance on Server1.

Thanks,
Frustrated with DTS

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close