Query Error

ryvarz · 2015-08-21 13:37:48 UTC

Hi
I writ a query but when I execute it, prompt me:

Msg 248, Level 16, State 1, Line 13
The conversion of the nvarchar value '7111000101' overflowed an int column.

The statement has been terminated.

Msg 547, Level 16, State 0, Line 15
The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_tblAccount_tblAccount". The conflict occurred in database "Accountingdb", table "dbo.tblAccount".

The statement has been terminated.

The source is:

declare @vendor_id varchar(50)
DECLARE vendor_cursor CURSOR FOR
select code from publicdata.dbo.tblcompany where code <> 1

--ORDER BY s1
--HAVING s1=4
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor INTO @vendor_id

WHILE @@FETCH_STATUS = 0
begin
insert into Accountingdb.dbo.tblAccount(AccCode, CompanyID, AccName, AccGroup, AccLevel, Lowest, Kind, debit, NumberCtl, ValueCtl, CompanyCtl, CostCenterCtl, TimeCtl, EntityCtl, ProfitLooseType, CostCenter2Ctl, remainCycle)
Select AccCode,@vendor_id, AccName, AccGroup, AccLevel, Lowest, Kind, debit, NumberCtl, ValueCtl, CompanyCtl, CostCenterCtl, TimeCtl, EntityCtl, ProfitLooseType, CostCenter2Ctl, remainCycle from Accountingdb.dbo.tblAccount where CompanyID=1 and AccCode=0
insert into Accountingdb.dbo.tblAccount(AccCode, CompanyID, AccName, AccGroup, AccLevel, Lowest, Kind, debit, NumberCtl, ValueCtl, CompanyCtl, CostCenterCtl, TimeCtl, EntityCtl, ProfitLooseType, CostCenter2Ctl, remainCycle)
Select AccCode, @vendor_id, AccName, AccGroup, AccLevel, Lowest, Kind, debit, NumberCtl, ValueCtl, CompanyCtl, CostCenterCtl, TimeCtl, EntityCtl, ProfitLooseType, CostCenter2Ctl, remainCycle from Accountingdb.dbo.tblAccount where CompanyID=1 and LEN(AccCode)=1 and AccCode>0
insert into Accountingdb.dbo.tblAccount(AccCode, CompanyID, AccName, AccGroup, AccLevel, Lowest, Kind, debit, NumberCtl, ValueCtl, CompanyCtl, CostCenterCtl, TimeCtl, EntityCtl, ProfitLooseType, CostCenter2Ctl, remainCycle)
Select AccCode, @vendor_id, AccName, AccGroup, AccLevel, Lowest, Kind, debit, NumberCtl, ValueCtl, CompanyCtl, CostCenterCtl, TimeCtl, EntityCtl, ProfitLooseType, CostCenter2Ctl, remainCycle from Accountingdb.dbo.tblAccount where CompanyID=1 and LEN(AccCode)=3
insert into Accountingdb.dbo.tblAccount(AccCode, CompanyID, AccName, AccGroup, AccLevel, Lowest, Kind, debit, NumberCtl, ValueCtl, CompanyCtl, CostCenterCtl, TimeCtl, EntityCtl, ProfitLooseType, CostCenter2Ctl, remainCycle)
Select AccCode, @vendor_id, AccName, AccGroup, AccLevel, Lowest, Kind, debit, NumberCtl, ValueCtl, CompanyCtl, CostCenterCtl, TimeCtl, EntityCtl, ProfitLooseType, CostCenter2Ctl, remainCycle from Accountingdb.dbo.tblAccount where CompanyID=1 and LEN(AccCode)=6
insert into Accountingdb.dbo.tblAccount(AccCode, CompanyID, AccName, AccGroup, AccLevel, Lowest, Kind, debit, NumberCtl, ValueCtl, CompanyCtl, CostCenterCtl, TimeCtl, EntityCtl, ProfitLooseType, CostCenter2Ctl, remainCycle)
Select AccCode, @vendor_id, AccName, AccGroup, AccLevel, Lowest, Kind, debit, NumberCtl, ValueCtl, CompanyCtl, CostCenterCtl, TimeCtl, EntityCtl, ProfitLooseType, CostCenter2Ctl, remainCycle from Accountingdb.dbo.tblAccount where CompanyID=1 and LEN(AccCode)=8
insert into Accountingdb.dbo.tblAccount(AccCode, CompanyID, AccName, AccGroup, AccLevel, Lowest, Kind, debit, NumberCtl, ValueCtl, CompanyCtl, CostCenterCtl, TimeCtl, EntityCtl, ProfitLooseType, CostCenter2Ctl, remainCycle)
Select AccCode, @vendor_id, AccName, AccGroup, AccLevel, Lowest, Kind, debit, NumberCtl, ValueCtl, CompanyCtl, CostCenterCtl, TimeCtl, EntityCtl, ProfitLooseType, CostCenter2Ctl, remainCycle from Accountingdb.dbo.tblAccount where CompanyID=1 and LEN(AccCode)=10

print @vendor_id
FETCH NEXT FROM vendor_cursor INTO @vendor_id

END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

mmkrishna1919 · 2015-08-20 07:46:28 UTC

The conversion issue is because, the maximum limit that integer data type support is "2,147,483,647". Here the value exceed this limit. Better to use either Bigint or Numeric data type to support these kind of large values.

Kristen · 2015-08-20 10:17:07 UTC

You need to find out which column is causing the problem. Looking at the definition of the Foreign Key ("FK_tblAccount_tblAccount") should answer that

You are doing:

AccCode=0
LEN(AccCode)=1 and AccCode>0
LEN(AccCode)=3 etc

Perhaps AccCode is a numeric column? (if so it would be better to explicitly CAST the parameter to LEN rather than letting it use an implicit conversion), but IF that column IS a String datatype then

AccCode=0
AccCode>0

will fail for any string value which cannot be (implicitly, again) converted to an integer.

If that is not the problem then it will be one of the values in the SELECT list that is INSERTED INTO Accountingdb.dbo.tblAccount

I would check the datatypes for the INSERT INTO column list, and compare them to the datatypes of the SELECT list and put an implicit CAST on any that are different. Currently you are relying on an implicit Cast from NVarchar to INT

Other point: Could you not combine the various INSERT/SELECTs into a single statement? It would be far more efficient than 6 separate statements.

e.g.

insert into Accountingdb.dbo.tblAccount(AccCode, ...)
Select AccCode, ...
from Accountingdb.dbo.tblAccount 
where	    CompanyID=1 
	and
	(
		   AccCode=0
		OR (LEN(AccCode)=1 and AccCode>0)
		OR LEN(AccCode)=3
		OR LEN(AccCode)=6
		OR LEN(AccCode)=8
		OR LEN(AccCode)=10
	)

ScottPletcher · 2015-08-20 12:50:59 UTC

Edit: mmk has identified the core issue, that 7 billion+ is too large a value for an int column.

But also, you don't need separate statements for those INSERTs. If you need the rows to be inserted in that order, you can add an order by clause to the select.

insert into Accountingdb.dbo.tblAccount(AccCode, CompanyID, AccName, AccGroup, AccLevel, Lowest, Kind, debit, NumberCtl, ValueCtl, CompanyCtl, CostCenterCtl, TimeCtl, EntityCtl, ProfitLooseType, CostCenter2Ctl, remainCycle)
Select AccCode,@vendor_id, AccName, AccGroup, AccLevel, Lowest, Kind, debit, NumberCtl, ValueCtl, CompanyCtl, CostCenterCtl, TimeCtl, EntityCtl, ProfitLooseType, CostCenter2Ctl, remainCycle 
from Accountingdb.dbo.tblAccount where CompanyID=1 and 
    ((AccCode=REPLICATE('0', LEN(AccCCode))) or
     (LEN(AccCode)=1 and AccCode>REPLICATE('0', LEN(AccCCode))) or
     (LEN(AccCode)=3) or
     (LEN(AccCode)=6) or
     (LEN(AccCode)=8) or
     (LEN(AccCode)=10))
/*
Order by case when (AccCode=REPLICATE('0', LEN(AccCCode)) then 1
     when (LEN(AccCode)=1 and AccCode>REPLICATE('0', LEN(AccCCode))) then 2
     when (LEN(AccCode)=3) then 3
     when (LEN(AccCode)=6) then 4
     when (LEN(AccCode)=8) then 5
     when (LEN(AccCode)=10) then 6
     else 7 end
*/

Kristen · 2015-08-20 14:01:46 UTC

Good point, hadn't considered that as the reason for the RBAR approach.

I wonder if that is the cause, per se? The error message is "The conversion of the nvarchar value '7111000101' overflowed an int column" and as that is implicit conversion of NVarchar to INT it suggests to me that this might be from an "uncontrolled" field - so it might be just as likely to encounter some non-numeric data in that field, which would also blow up of course, and maybe this "large number" is just an example of uncontrolled data, hence my suggestion that explicitly casting the field would at least mean that the DEV had to think about what the difference between the Source and the Destination was ... e.g. NVarchar(20) isn't going to fit into INT.

However, could easily be "Value has become too large" too.

Perhaps for this sort of data import it would be wise to have a test for any numeric limits being exceeded? I don't think we do that in ANY of our Data Integration code, but maybe we should do. We DO have Housekeeping Code that checks if any IDENTITY columns are approaching MAX values, and also we have a Version Number on records in a SMALLINT and we check the MAX value of those ... if we were importing, say, and OrderNumber from a 3rd party system perhaps we should check that Max(OrderNumber) does not exceed the MAX Numeric Datatype that we have assigned. I'd much prefer our APP blew up with a meaningful message - and I doubt very much that we would be told if the 3rd party vendor upgrading their APP and in the process changed their OrderNumber from INT to BigInt

(We DO check whether any column value is too wide for the target column, and a bunch of other such things, just no test for Max Permitted Numeric Value Exceeded)