使用服务器数据库时，算术溢出错误将数字转换为数据类型数字 [英] Arithmetic overflow error converting numeric to data type numeric when working with server database

问题描述

我正在使用存储过程更新表或插入表格。

使用本地数据库（同一系统中的数据库和应用程序也在同一系统中，没有网络或共享数据库）所有工作正常，没有任何错误。

我的应用程序是在Visual C＃中。



当我将应用程序转换为在多用户环境中使用时使用相同的现有数据库与相同的存储过程具有相同的值，并且相同的Sqlserver Express版本，我遇到了错误

算术溢出错误将数字转换为数据类型数字。

我试图通过声明为具有指定长度的十进制来限制变量，并将表列增加一个小于我在存储过程中声明的十进制数。但同样的错误来了，无法找到解决方案。



与本地系统数据库和远程服务器数据库相比，SQL服务器是否存在任何限制。

两者是相同的数据，相同的值，相同的代码，相同的应用程序。除连接字符串外没有任何变化。



使用的存储过程代码如下。

I am using Stored Procedures to update tables or Insert into tables.
When working with local database (database in the same system and application also in the same system, No network or shared Database) all are working well without any errors.
My application is in Visual C#.

When I have converted the application to use in multiuser environment and used the same existing database with same stored Procedures with same values, and the same Sqlserver Express version, I have encountered the error
"Arithmetic overflow error converting numeric to data type numeric".
I have tried to limit the variables by declaring as a decimal with specified lengths and increased the table columns one decimal more than what I have declared in the stored procedure. But same error coming and unable to find the solution.

Is there any limitations with SQL server compared with local system database vs remote server database.
Both are same data, same values, same code, same application. Nothing changed except the connection string.

The stored procedure code am using is as below.

USE [inventoryDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF  -- this is added now because in some place someone said that it will overcome the error.
GO
CREATE PROCEDURE [dbo].[SalesMainInsert]
    (
      @cblno INT=0,
      @bldate DATETIME ='',
      @rname VARCHAR(50)='',
      @custname VARCHAR(50)='',
      @custcode VARCHAR(50)='',
      @smaname VARCHAR(50)='',
      @mblno VARCHAR(50)='',       
      @bldis1per decimal(20,2)=0,
      @bldis1amt decimal(20,2)=0,
      @bldis2 decimal(20,2)=0,
      @bldis3 decimal(20,2)=0,
      @dis3narr VARCHAR(500)='',
      @blcash BIT=0,
      @billnet decimal(20,2)=0,
      @bltax decimal(20,2)=0,
      @addless decimal(20,3)=0,
      @billtot decimal(20,3)= 0,
      @rndval decimal(9,2)=0,
      @blgross decimal(20,2)=0,
      @remarks VARCHAR(50)='',
      @TotalCessAmt decimal(20,3)=0,
      @TotalAddlCessAmt decimal(20,3)=0,
      @cstat AS BIT = 0,
      @bstat AS VARCHAR(10)='',
      @custtype AS VARCHAR(1)='',
      @ReturnValue AS INT = 0 OUT
    )
AS 
    BEGIN
    
    INSERT INTO dbo.salesHDB
(
cblno,
bldate,
rname,
custname,
custcode,
smaname,
mblno,       
bldis1per,
bldis1amt,
bldis2,
bldis3,
dis3narr,
blcash,
billnet,
bltax,
addless,
bltotal,
rndval,
blgross,
remarks,
TotalCessAmt,
TotalAddlCessAmt,
cstatus,
bstatus,
custtype
)
VALUES  ( 
@cblno,
                        @bldate,
                        @rname,
                        @custname,
                        @custcode,
                        @smaname,
                        @mblno,       
                        @bldis1per,
                        @bldis1amt,
                        @bldis2,
                        @bldis3,
                        @dis3narr,
                        @blcash,
                        @billnet,
                        @bltax,
                        @addless,
                        @billtot,
                        @rndval,
                        @blgross,
                        @remarks,
                        @TotalCessAmt,
                        @TotalAddlCessAmt,
                        @cstat,
                        @bstat,
                        @custtype
  )
                        
IF @@ERROR = 0 
    BEGIN
            SET  @ReturnValue = 1
    END
ELSE 
    BEGIN
            SET  @ReturnValue = -1
    END
END

我尝试了什么：



我用谷歌搜索但未能找到任何合适的解决方案

What I have tried:

I Googled but failed to find any suitable solution

推荐答案

不，本地实例与远程实例的代码相同，假设它们是相同的SQL版本。


我首先在调用存储过程的任何东西上使用调试器，并确切地找出你在每个参数中传递给它的内容。可能你没有传递你认为自己的价值，这会导致问题。



我们不能为你做到这一点 - 我们不这样做甚至知道你的代码是用什么语言编写的，更不用说你用它做什么了 - 在应用程序运行时需要查看这类问题，以便准确地确定正在发生的事情。

无论你使用什么语言，调试器都是完美的工具！

No, a local instance is the same code as the remote instance, assuming they are the same version of SQL.

I'd start by using the debugger on whatever is calling the stored procedure, and finding out exactly what you are passing to it in each parameter. It may be that you are not passing the value you think you are, and that causes the problem.

We can't do that for you - we don't even know what language your code is written in, much less what you are doing with it - and this kind of problem needs to be looked at while the app is running in order to "nail down" exactly what is happening.
And the debugger is the perfect tool for that, no matter what language you are using!

这篇关于使用服务器数据库时，算术溢出错误将数字转换为数据类型数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！