Monitoring and debugging large insert statements

[spangeman]

I will be doing a data transfer at some point and I don't want to use DTS for many reasons.

Is there any way of monitoring a large insert statement that may take up to an hour to complete? So you could quickly see that it had only transfered one row after 10 minutes and there was therefore something wrong.

Also if you wait for that hour only to find an extrmely unhelpful error message like - error converting varcahr value ?@?"£?$" to ?"£$@" is there an easy method of finding which row caused the error in the source table?

Cheers
AH

 

[mrdenny]

When doing a large insert via T/SQL you won't see any rows transfered until the entir batch has completed. Records aren't written one by one.

To find the problem row you'd need to insert row by row using a cursor. Which isn't recommend as it's much slower.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[tc3596]

You could try it via a quick vb app. On error, do a debug.print

Also, run it in query analyzer and it should tell you the number of rows affected before the error i.e. 5263 records affected, 5264 caused your error.

 

[mattyboy2000]

What we have done in our company for things like this is find a field that splits the result set quite well (like MatterStatus/OrderStatus).

We then use a cursor to loop through these and then record in a progress table how far we have gotten.