The Problem
i'm trying to import data into a table using SQL Server Management Studio's Import Data task. It only brings in 26 rows, out of the original 49,325. (Edit: That's where 99.9% comes from: (1-26/49325)*100 = 99.9%
Using DTS in Enterprise Manager correctly brings all 49,325 rows.
Why is SSMS not importing all rows, reporting that it transferred 49,325 successfully, and experienced no errors? Why is Enterprise Manager able to correctly import all 49,325 rows?
Microsoft SQL Server Management Studio version: 10.0.1600.22 (From SQL Server 2008, installed today on a fresh Windows 7 machine, SP1 applied)
Proof - Import using SSMS
The STRTransactions table is initially empty:
Source is the RamaLCTManager database on mango:
Destination is the RTMS database on mango;
i want to copy data from one (or more) tables:
i want to copy the STRTransactions table:
You can append to the existing table, that's fine (it's empty). i want to enable identity inserts. And don't try to import a timestamp (since you'll just complain anyway):
Run immediately, that's fine:
Yup, you're going to do stuff:
i managed to catch it while it was transferring the 49,325 rows, around the 1k mark:
All done. All 49,325 rows copied successfully:
And here's the report:
The execution was successful
Initializing Data Flow Task (Success)
Initializing Connections (Success)
Setting SQL Command (Success)
Setting Source Connection (Success)
Setting Destination Connection (Success)
Validating (Success) Messages Warning 0x80049304: Data Flow Task 1: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console. (SQL Server Import and Export Wizard) Warning 0x80047076: Data Flow Task 1: The output column "timestamp" (158) on output "OLE DB Source Output" (11) and component "Source - STRTransactions" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance. (SQL Server Import and Export Wizard)
Prepare for Execute (Success)
Pre-execute (Success)
Executing (Success)
Copying to [dbo].[STRTransactions] (Success) 49325 rows transferred
Messages Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in "component "Destination - STRTransactions" (163)" has started. (SQL Server Import and Export Wizard) Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion in "component "Destination - STRTransactions" (163)" has ended. (SQL Server Import and Export Wizard)
- Post-execute (Success) Messages Information 0x4004300b: Data Flow Task 1: "component "Destination - STRTransactions" (163)" wrote 49325 rows. (SQL Server Import and Export Wizard)
Excellent. All done. "wrote 49325 rows".
Except that it only wrote 26 rows:
Obviously i'm not crazy. i did everything right. And even if i didn't, SSMS gives no indication of any problems. i've repeated these same steps 8 times:
- 5 times for myself
- twice to demonstrate to two different colleagues
- once to screenshot the process for this SO question
Every time it's exactly 26 rows, no more, no less. But which was destroyed, the master, or the aprentice?
But just to prove that i'm not doing anything wrong, we'll try again with Enterprise Manager. An excellent tool written over ten years ago:
Proof - Import using Enterprise Manager
i've deleted the 26 rows from the STRTransactions table. i could provide a screenshot that i'm starting with an empty table; or you could just trust me on this. And since the wizards are nearly identical, you'll be seeing nearly identical screenshots. Sorry about that; but nobody will believe me without proof.
i want to export from the RamaLCTManager database on mango:
i want to import to the RTMS database on mango:
i want to copy tables:
i want to copy the STR Transactions table:
You can go ahead and append rows to the existing table (it's empty anyway). i want you to insert identity values. And don't try to insert timestamp values, you'll just throw an error anyway:
Run now, sure:
Yup, you're about to do stuff:
i managed to catch it in the middle of the import, around 12k rows:
All done, 49,325 rows successfully copied:
And we select from the table to see the rows:
Why is SSMS, a tool which has been actively developed for 6 years now still not gotten in right? While Enterprise Manager was nearly bug-free by the initial dev team? This is another example of the critical bugs in SSMS. The last critical bug i found was that it does not script all objects.
Can we please get the Enterprise Manager dev team to re-write SSMS?
It's safe to say that the only answers i'll see are
- use a different tool
- have you reported it on Microsoft Connect?
- call PSS
Of course i have a workaround, stop using SQL Server Management Studio. But i'm already having to run Enterprise Manager in an XP Mode 32-bit compatibility thing (as you can see by the Luna themed windows on my Aero themed desktop)
Me: i got it to work
Colleague: How?
Me: i used Enterprise Manager
Colleague: scoff Well.
Colleague: I might have to install that, then.
Edit: Reading my old question on ServerFault, about SSMS silently not scripting some tables in a database, i had at the end, "Honk if you hate SSMS". So:
Honk if you hate SSMS.
The Game
i've created a ticket on Microsoft Connect. Place your bets on when this issue will be Closed as not reproducible. Edit this question adding your name and date. The one closest without going over will win 100 rep! (i will donate my rep) If you don't have enough rep to edit questions, place your guess in a comment, and i'll add it.
The Guesses
- Ian Boyd: 5/1/2011