MySQL Bugs: #10793: DTS general error : 2147217891 (80040E1D)

Bug #10793	DTS general error : 2147217891 (80040E1D)
Submitted:	23 May 2005 5:02	Modified:	31 May 2013 6:44
Reporter:	Chagh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51	OS:	Windows (win xp)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
When trying to migrate tables from SQLServer2000 to MySQL using DTS, such an error raises on some tables:
" general error : 2147217891 (80040E1D)"

How to repeat:
try to move an SQLServer DB tables to MySQ 5.x or 4.1 by DTS.

Could you write here definition of your tables ?

This is definition of one of my tables in SQLServer 2000, which leads to the error:

CREATE TABLE [dbo].[_Documents] (
	[DocCode] [DocumentCode] IDENTITY (1, 1) NOT NULL ,
	[DocGrpCode] [GroupCode] NOT NULL ,
	[DocDate] [datetime] NULL ,
	[DocSecLevel] [smallint] NOT NULL ,
	[DocWords] [text] COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
	[DocUserCode] [int] NOT NULL ,
	[Creator] [UserCode] NULL ,
	[CreationMoment] [datetime] NOT NULL ,
	[LastModifier] [UserCode] NULL ,
	[LastModificationMoment] [datetime] NULL ,
	[PrevNumbers] [varchar] (300) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
	[Hash] [varchar] (50) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
	[DocKeyWords] [varchar] (1000) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
	[AttachCount] [int] NULL ,
	[FromParadox] [varchar] (200) COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
	[AttachWords] [text] COLLATE SQL_Latin1_General_CP1256_CI_AS NULL ,
	[ReferWords] [text] COLLATE SQL_Latin1_General_CP1256_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Tested on Win 2000 Sp4 , MyODBC 3.51.11, MySQL 5.0.6, SQLServer2000, DTS

Hi Chagh,

please, could you try this again with the newer MyODBC 3.51.19 and let us know, if the error is still exist or if not.

Regards,

Susanne

I've been looking for this error and found it's rather common with MDAC 2.5 and up. Actually I've found a quote saying it did not show at all with MDAC 2.1. Now, in essence, it means SQL server is unable to map types. Most common cause would be storing Visual Basic String (BSTR) in Variant Field in ADO Recordset.If MS can not ensure this error not to be throw from one MS tool to other I don't see how we can cope with it...

I have also found this (so please check your data in MSSQL server too):
"If I understand correctly, the error occurs when you try to save a record, however, you already know the problem is not here because the data (you want to save) is not generated correctly. If this is the case, you need debug your code to find why the data is not generated correctly and from which point it creates the wrong data."

and

http://www.gridview.org/kb/DAC27/If-you-create-an-ActiveX-Data-Objects-(ADO)-or-ADOR-recor...
If you create an ActiveX Data Objects (ADO) or ADOR recordset with a field of type adVariant, and populate that field with a Microsoft Visual Basic string value, then call the Recordset's Save method, specifying ADTG as the persistence format, the Save method appears to work with MDAC 2.1, but fails under MDAC version 2.5 or later with the following error message:
Run time error -2147217891 (80040e1d) Requested conversion is not supported

Tested on MSSQL 2000 SP3, MyODBC 3.51.20 and MySQL Server 5.0.45.

The types such as DocumentCode, GroupCode etc are not defined, so I used [int] instead. DTS displayed the following error message:

Error at Destination for Row number 1. Errors encountered so far in this task: 1.
BLOB inserts or updates are not supported.

I looked through the driver log and found nothing suspicious. Then I repeated the same operation using MS SQL DSN (IMPORTANT NOTE: use ODBC driver connection, not OLE DB provider).

Thus, I suppose the problem is in MS SQL DTS.
However, for data types such as VARCHAR(1000) MyODBC (or DTS) uses LONGTEXT which raises the error above even if remove explicit [text] columns from CREATE TABLE.

This is a common problem with Microsoft SW (like Visual Fox pro etc). They just don't support what they don't have in MSSQL server even though specifications are not to be written for just one SQL server... Maybe we should file a feature request to them?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I had the following error too, I discovered that it generated from a field whose type in mssql was nvarchar but when exporting to mysql  the dts changed in to blob so i ignored that coloumn and ran he dts again and it run perfectly.

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.