Debug an Insert Statement

Question

Debug an Insert Statement

ross.mason 49698

SSC-Addicted

Points: 468
More actions
December 9, 2013 at 6:39 am

#279775

We have an insert statement crashing about 20,000 records into the insert because of an Arithmetic overflow error converting numeric to data type numeric . How can we determine the row it is stopping on?
The insert statement is similar to:
INSERT INTO Employees2
SELECT
[LastName]
,[FirstName]
,[Title]
,[TitleOfCourtesy]
,[BirthDate]
,[HireDate]
,[Address]
,[City]
,[Region]
,[PostalCode]
,[Country]
,[HomePhone]
,[Extension]
,[Photo]
,[Notes]
,[ReportsTo]
,[PhotoPath] from Employees
thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

andrea.gnemmi Mr or Mrs. 500 Points: 545 More actions · Answer 1

Hi, the data that you are inserting are coming from a query, I suppose, then you can check in the select clause, running it separately, for the aforementioned overflow error. Otherwise you can try to put the data in order and check around the 20000 row...

Let me know!

ross.mason 49698 SSC-Addicted Points: 468 More actions · Answer 2

The actual data is coming from an ODBC link using and OPENROWSET. I will try an order By clause the next time we run it later this week.

We have narrowed it down to about 300 records using a where clause. The table structure has about 30 smallint columns and I am guessing it is one of those columns that is throwing the error.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What are the definitions of the two tables?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 4

I found this article that may help you out.

http://stackoverflow.com/questions/17361097/sql-server-2008-r2-arithmetic-overflow-error-converting-numeric-to-data-type-num

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

ross.mason 49698 SSC-Addicted Points: 468 More actions · Answer 5

SET NUMERIC_ROUNDABORT looks like a plan. We will try it tonight.

Thanks

ross.mason 49698 SSC-Addicted Points: 468 More actions · Answer 6

I tried SET NUMERIC_ROUNDABORT,but I still got the arithmetic error. We finally determined that we were pushing [numeric](7, 2) into a [numeric](6, 2). Once we corrected the column it ran.

Thanks,