Sql Exception: Error converting data type numeric to numeric

Question

Hello

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

The first time the database was moved it was by detaching, copying and reattaching, the second time we tried restoring from a backup of the staging.

Both SQL Servers are the same version of MS SQL 2008, running on 64 bit hardware.

The code accessing the database is the same build, built using the .net 2.0 framework.

Here is the error message and some of the 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 are the same.

Here is the Stored Procedure that causes the error on the live 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

It means you are passing larger value to the numeric datatype that it cant have

ex

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

Answer 2

You probably need to increase the scale and or precision of your 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 Arithmetic overflow error converting numeric to data type numeric.

Based on you posting the proc code, now take a look at this

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

SELECT @FileId
GO

call it with 18 digits

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 type numeric to numeric.

Probably your call to the proc caused this problem, it it is line 0 then the proc call itself is the problem if it is greater than 0 then look at the line number in the proc to see where it exactly fails

Answer 3

Are you sure it's to do with restoring the DB?

I've seen this error before with numeric when the DB is say numeric(5,2) and you try to insert a numeric value of 1000.00

The maximum value numeric(5,2) can hold would be 999.99

UPDATE:

I think I know what this is.

What is the value of Scope_Identity() ? My guess is that is is bigger 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