DTS Error that I can't figure out... 1

[bessebo]

I am simply trying to copy one table on one server to the same table name on another server using DTS. It is very straightforward but I'm getting the cryptic error below:
------------------------------------------------------------
General error -2147217887 (80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
------------------------------------------------------------

I can do this with 4 other tables without a problem but am running into this problem with 3 other tables. Any idea where I go from here?

Regards,
Bessebo

 

[bessebo]

I found the answer in the KnowledgeBook on SQLServer on the Microsoft site (should've gone there first I guess). My problem was that I was copying from and to a table that had a timestamp data type. What I did was change the destination field to a binary(8) field and the DTS job worked great. Below is a cut and paste from the Knowledgebook...

BUG: DTS Fails When Importing Table with a Timestamp Column

--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft SQL Server version 7.0

--------------------------------------------------------------------------------
BUG #: 53386 (SQLBUG_70)

SYMPTOMS
When you use the "Copy table(s) from the source database" option of the Data Transformation Services (DTS) Import wizard to transfer a table with a timestamp column, DTS fails with the following error:

General error -2147217887 (80040E21). Errors occurred



CAUSE
This error occurs if the destination table is also defined with a timestamp column. The error occurs regardless of whether the transfer has the "Drop destination objects first" or "Append data" option enabled.



WORKAROUND

Use DTS and specify "Use a query to specify the data to transfer". The query should exclude the timestamp column.

-or-


Use DTS and specify "Transfer objects and data between SQL Server 7.0 databases" and then specify which table you want to transfer.

-or-


Define the destination table to use binary(8) for the datatype for the column which will contain the timestamp.


Regards,
Bessebo

 

[foxdev]

You know, Bessebo, a lot of SQLS users don't realize the depth of material there is on MSDN online. A fair number of times, people will ask a question (usually relating to an error) here on Tek-Tips, and I'm able to find the answer in a couple of minutes on MSDN (but don't tell anyone - I want people to think I'm that smart).

Thanks for checking MSDN, and double-thanks for following up by posting the solution. Robert Bradley

http://www.foxdev.com/

Coming Soon:

http://www.lowcostprints.com

http://www.agrainofhope.org

 

[Jenns]

I have the same error -2147217887(80040e21). I checked Microsoft's site and found the same article. My problem is I don't have a timestamp field. I am simply importing a text file into a table with all character fields. The DTS package runs fine by itself, but fails with the above error when scheduled.

Does anyone have any other ideas?
Thanks!
Jenn

 

[tlbroadbent]

Jenn,

I recommend starting a new thread rather than posting to a thread that has not been active for over a year.

Check the article, "How to Run a DTS Package as a Scheduled Job" at ...
[ignore]

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q269074

[/ignore] Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.