Sql Exception: data type to numeric conversion error

Question

We have a very strange problem with a database that has been moved from production to production.

The first time the database was moved, it was disconnected, copied and reconnected, the second time we tried to restore the backup copy of the stage.

Both SQL servers are the same version of MS SQL 2008 running on 64-bit hardware.

The database access code is the same assembly built using the .net 2.0 framework.

Here is the error message and some stack trace:

Exception Details: System.Data.SqlClient.SqlException: Error converting data type numeric to numeric. Stack Trace: [SqlException (0x80131904): Error converting data type numeric to numeric.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1953274 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4849707 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137 Version Information: Microsoft .NET Framework Version:2.0.50727.4200; ASP.NET Version:2.0.50727.4016 

If I use Red Gate SQL Compare to compare the staging database and live, they will be the same.

Here is the stored procedure that causes the error on the real server:

 ALTER PROCEDURE [dbo].[File_Files_InsertUpdateSingleItem] ( @FileId numeric(18,0) output, @Filename nvarchar(255), @Guid uniqueidentifier, @Name nvarchar(100), @ContentType nvarchar(50), @Size numeric(18, 0), @Description nvarchar(2000), @DateCreated datetime, @DateModified datetime, @IsUserUploaded bit, @UploadedByUserID numeric(18, 0) ) AS SET NOCOUNT ON IF @FileId > 0 BEGIN UPDATE [dbo].[File_Files] SET [Filename] = @Filename, [GUID] = @Guid, [Name] = @Name, [ContentType] = @ContentType, [Size] = @Size, [Description] = @Description, [DateCreated] = @DateCreated, [DateModified] = @DateModified, [IsUserUploaded] = @IsUserUploaded, [UploadedByUserID] = @UploadedByUserID WHERE [FileID] = @FileId END ELSE BEGIN INSERT INTO [dbo].[File_Files]( [Filename], [GUID], [Name], [ContentType], [Size], [Description], [DateCreated], [DateModified], [IsUserUploaded], [UploadedByUserID]) VALUES( @Filename, @Guid, @Name, @ContentType, @Size, @Description, @DateCreated, @DateModified, @IsUserUploaded, @UploadedByUserID) SET @FileId = scope_identity() END 

Answer 1

You probably need to increase the scale and accuracy of the column.

Take a look at this

 declare @n decimal(8,2) select @n = 123456.12 declare @n2 decimal(7,2) select @n2 = @n -- will blow up 

Msg 8115, Level 16, State 8, Line 5 An arithmetic overflow error converting a numeric value to a data type.

Based on the fact that you are sending proc code, take a look at this

 CREATE PROCEDURE [dbo].[File_Files_InsertUpdateSingleItem2] @FileId numeric(18,0) output AS SELECT @FileId GO 

call it 18-digit numbers

 exec [File_Files_InsertUpdateSingleItem2] 123456789012345678 

call it with 19 digits

 exec [File_Files_InsertUpdateSingleItem2] 1234567890123456789 

Msg 8114, Level 16, State 1, Procedure File_Files_InsertUpdateSingleItem2, Line 0 Error converting data types to numeric.

Your proc call probably caused this problem, this is line 0, then the proc call itself is a problem if it is greater than 0, and then look at the line number in proc to see where it definitely fails

Answer 2

Are you sure this is due to database recovery?

I saw this error before with numeric when DB says numeric(5,2) and you are trying to insert a numeric value of 1000.00

The maximum value of numeric(5,2) may be 999.99

UPDATE:

I think I know what it is.

What is the value of Scope_Identity ()? I guess this is more than 18.

See this test code below:

 create table #t ( -- Note that the identity seed is 3 numbers ColId int identity(100,100), Col varchar(10) ) -- Note that this is 2 numbers declare @fileId numeric(2,0) Insert Into #t Values ('test') -- errors here set @fileId = scope_identity() select @fileId drop table #t 

Answer 3

This means that you pass a greater value to a numeric data type that it cannot have

ex

 declare @d decimal(18,8) set @d=98723498345.456