800a0cb3 Error in DTS Package

I've been working on a DTS Package that runs every 10 minutes to check for new records to a table and sends out notification emails accordingly. After the email is sent, the new record is updated with a flag so it will not be pulled and emailed again.

This package worked fine for a week (and is currently working - exact same code - in another SQL Server).

As of last night, this process started throwing the following error:

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= ADODB.Recordset
Error Description: Current Recordset does not support updating. This may be a limitation of the provider,
or of the selected locktype.

Error on Line 110
(ADODB.Recordset (800a0cb3): Current Recordset does not support updating. This may be a limitation
of the provider, or of the selected locktype.)
Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

Thinking I may have screwed something up, I moved the functioning code from the other SQL Server back to the non-functioning SQL Server, and the problem persists. I stopped and restarted the SQL Server (but have not yet rebooted the server it's on), also with no results.

The package is a single ActiveX object written in VB to open up a recordset of unprocessed records (KeySet and Lock Optomistic), send out notification emails, flag the files, then close. The error occurs at the updating of the flag.

What is even more strange, is that this is happening on our development SQL server. Our production SQL server seems unaffected. I originally developed and tested this code on the dev box, and when I was satisfied, I moved it to production for testing. Then last night the dev server stopped functioning, but the production server shows no sign of problems.

I was curious if anyone else has had an experience with a DTS package throwing a similar error.

Any ideas?I took the ActiveX code and copied it to VB and ran it.. when it's pointing to the server in question, the error occurs... when I change the server name to point to the production server, the same code functions properly... amazing....|||I temporarily managed to resolve the problem by rebuilding the packages from scratch. I do not know if this fixed it, or if it was just coincidence that I did this at the same time something else happened, but it began working again.

Unfortunately, as of this afternoon, the production and staging environments redeveloped the problem and my "solution" did not fix it.

Thinking my transaction log may be filled I checked the settings. The DB and the Transaction log are both set to unlimited growth and to grow automatically.

At this point I'm baffled. I have a DTS package on 3 servers... all three running successfully for days on end... spontaneously two of them stop working giving the error in the original post... very very troubling...|||Would you believe I came across something that may have fixed it right after I posted? It wouldn't be the first time...

I found a reference to the same error code, but different error message, in regard to an Oracle error. The solution was to make the recordset a client-side recordset. I had originally thought of this, but the DTS package is in SQL Server, so my thinking was, when this runs, there is no client, so it has to run as a server-side recordset. When I changed ADO to use a client-side recordset (which is still the SQL Server), it started working again. I changed it back, it failed, put it back in again, it worked.

Very strange... why would you need to declare a recordset to be client-side, even though it's running in the SQL Server itself? Since DTS runs under a separate agent, does that count as a "client"?

64bit problem?

I run SQL Server 2005 on a Windows 2003 64-bit server. I have created a package where I run an execute t-sql statement task. In the t-sql I have the following:

select * from
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\test.mdb";User ID=Admin;Password=').pubs.dbo.data

and I get the following error:

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

There is only 32-bit version of Jet provider.

You access it on 64-bit machine, if you run SSIS packages in 32-bit mode (using 32-bit dtexec from Program Files (x86) or setting appropriate option in project), and access Jet database directly (not through SQL server).|||I am a newbie with SSIS and I am not yet in the post-deployment stage. I am running the package from Visual Studio 2005.
How to choose to run it in 32 bit mode?
And how to run the Jet datbase directly and not through SQL server?

Thanks again!

|||To run package in 32-bit mode in Visual Studio, right click SSIS project and select properties. Set the property Debugging > Run64BitRuntime to false.
To access Jet database (e.g. as a source) without SQL Server:
1) create OLE DB Connection Manager, select "Native OLEDB\Microsoft Jet 4.0" provider in the top drop down box, configure path to database.
2) from Toolbox drop OLE DB Source to the data flow, configure it to use this connection manager.|||

execute the package with the dtsrun.exe found in

c:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn

regards

JG

|||DtsRun is for DTS 2000, it would not be able to run SSIS package.
To run SSIS use DTEXEC from
c:\Program Files\Microsoft SQL Server\90\Dts\Binn or
c:\Program Files (x86)\Microsoft SQL Server\90\Dts\Binn|||

precondition >> I run SQL Server 2005 on a Windows 2003 64-bit server<<

further ssis dosnt run on 2000 but on 2005.

if you on 2005 run a ssis package it will use the 32bit jet driver and your import will work fine

regards

G.

|||But opening an OLEDB source for MS Access will not allow me to use OpenDatasource from SQL, right?

Therefore there is no way to use OpenDataSource from SQL 2005 64 bit?

The idea is that I would like to run a complex stored procedure in the package, and that the stored procedure needs the data in Access as well.

64-bit ODBC issues with DTEXEC

We've hit a weird problem that we can't resolve. We have a package using ODBC to extract from an AS400. When running the package through DTEXECUI, the package executes successfully. However, it chokes when executing through DTEXEC or as a SQL Agent job on our 64-bit machine. Seems like DTEXECUI is using the 32-bit ODBC driver while DTEXEC and SQL Agent is trying to access an non-existent 64 bit ODBC driver. We're using a DSN-less connection string (Driver={Client Access ODBC Driver (32-bit)};system=AS400;....) so that's not the issue. Anyone else seen this weird error? Any possible workarounds?If the 64-bit ODBC driver is not aviailable, you can run the package in 32-bit mode.

To do this, edit the SSIS step in Agent. Copy the text from Command Line tab. Switch step to use CmdExec subsystem instead of SSIS subsystem. Specify 32-bit DTEXEC as application ("C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" - including quotes) and add the parameters you've copied from Command Line tab.|||Thank you. This will work until we upgrade Client Access to a version supporting 64 bit drivers. This is our first 64-bit server. Was wondering why there was both "C:\Program Files\" and "C:\Program Files (x86)\" directories and didn't know how to execute dtexec in 32-bit mode.

64-bit issue

I am running into a strange problem when trying to run my package against 64-bit version of SQL Server 2005.

The package was initially developed with a CTP 2 version of SSIS. It has been running fine against a 32-bit bersion of SQL Server 2000.

I created a new SSIS project, with the release version, and added the package mentioned above. Things still work fine against a 32-bit version of SQL Server 2000. However when I run the same exact package against a 64-bit version it doesn't work correctly.

I am using a flat file connector - to pull data from a text file - and loading the data into a database table. Each time I run against the 64-bit version of SQL Server 2005 the number of nulls - for a particular colum - changes.

In other words, the following query returns different counts each time the package is run - even when the underlying flat file data hasn't changed:

select count(*) from some_table where email_address is null

Any help would be much appreciated!

Scott

I now have more information about the above problem. It appears that this is *not* a 64-bit issue. If I run the package using the CTP 2 version of SSIS, everything is correct on 32 or 64 bit versions of SQL Server.

However, if I take the same package and run it within the RTM version of SSIS, the numbers are not only incorrect...the numbers change with each run of the package!

Note that the flat file data source remains the same.

Is there some issue with taking a package built with CTP 2 and runnig it directly with the RTM version of SSIS?

Thanks,

Scott

|||

Wouldn't do it. I would create the package from scratch. There was no supported upgrade from CTP to Beta (that I am aware of) and that includes the packages.

Based on experience, not only remove any signs of the CTPs, but rebuild any machine that any component of a non RTM version on it. It just removes the possibility of error, and I would suspect if not done invalidate any support.

64 bit(part II)

In a recent post, I've read that Jet Provider is only provided with 32-bit.

I wonder, is there any problem if you run a SSIS package 32-bit which read from Access and then call another SSIS package 64 bit? We've got quite 2000 dts which pulling data from MDB and within a time it'll disappear in favour 2005 environment .

Another question, what sort of Providers don't offer us 64-bit versions?

Any link or thought will be as usual welcomed

It will work - you can run first package in 32-bit mode, move data to staging table or flat file, then call second package in 64-bit mode and do further processing of the data.|||The msdaora drivers also don't function in 64bit. This is a bit of a hassle since the oracle provided 64bit drivers don't work correctly. ODBC is also a bit tricky since you have to create dsn's for 32 bit and 64 bit since the desinger is 32 and the runtime is 64.|||Thanks to both for the info provided.

64 bit(part II)

In a recent post, I've read that Jet Provider is only provided with 32-bit.

I wonder, is there any problem if you run a SSIS package 32-bit which read from Access and then call another SSIS package 64 bit? We've got quite 2000 dts which pulling data from MDB and within a time it'll disappear in favour 2005 environment .

Another question, what sort of Providers don't offer us 64-bit versions?

Any link or thought will be as usual welcomed

It will work - you can run first package in 32-bit mode, move data to staging table or flat file, then call second package in 64-bit mode and do further processing of the data.|||The msdaora drivers also don't function in 64bit. This is a bit of a hassle since the oracle provided 64bit drivers don't work correctly. ODBC is also a bit tricky since you have to create dsn's for 32 bit and 64 bit since the desinger is 32 and the runtime is 64.|||Thanks to both for the info provided.

64 Bit OLE vs. 32 Bit OLE

I have a SSIS package that works on Windows Server 2003 32 Bit with SQL Server 2005 32 Bit when I move it to a Windows Server 2003 64 Bit (AMD) with SQL Server 2005 64 Bit it fails with error 80040154 on the connection to an Access 2000 database. The solution maybe Jet 4.0 SP8 however I can not find SP8 for Windows Server 2003 64 Bit AMD (I can find Jet 4.0 SP8 for Windows Server 2003 32 Bit, Windows Server 2003 64 Bit Itanium however).

Sorry, but there is no Jet support for 64 bit so you would have to run this package as 32 bit only.

HTH,

Matt

|||I understand that Access is 32 Bit and the system I am replacing is running on this server using 32 Access (2000), the is also SP8 for Itanium 64 Bit so are you saying that the only 64 Bit OLE connection to an Access database that is supported is the Itanium 64 Bit system?|||

It is my understanding that there is no jet support for 64 bits. However, since this is an SSIS forum there could be new releases of Jet that are addressing this. To find out for sure you should ask on that forum as they would know better than we do.

Matt

|||I posted it here because all other access to these databases work. In fact SSIS Import Wizard will build a project for this, testing the connection works, selecting the desired table works, it all works until I run it. NOTE: If I preview the connection the preview works however it will general a "system out of memory"......|||

The problem is that the wizard uses different access mechanisms to get the information than the actual package. The package uses OLEDB to access jet and that (to the best of my knowledge) does not support 64 bit unless it has changed recently. If it has then you should ask on the jet forum what that specific error code means coming from their OLEDB provider because that is usually the error for class not registered and that has always been the error we get due to non 64 bit support for jet (as the class isn't registered in the 64 bit registry since it doesn't support 64 bit).

Thanks,

Matt

|||Ok, where do I find the jet forum?|||Just to make sure you know this - you can run the package as 32-bit and use 32-bit OLEDB providers on 64-bit system. You need to use 32-bit dtexec.exe from c:\Program Files (x86)\Microsoft Sql Server\...|||Thanks Michael! Based on that I found that there is a property in Visual Studio for a project "Run64BitRuntime" which is set to true by default, I set it to false and the OLEDB connection works!