0

I have a table that is filled using a stored procedure. This stored procedure uses a view that calls attributes from another databases.

To illustrate, it is something like:

ALTER PROCEDURE theSp
AS BEGIN
     INSERT INTO dbo.theTable (attr1, att2, amount, attr4)
         SELECT attr1, attr2, amount, attr4
         FROM theView
END

The view is defined this way:

select attr1, attr2, amount, attr4
from db1.theTable
where date >='anyDate'

and the values are correctly inserted, but if the view is used this way:

select attr1, attr2, amount, attr4
from db2.theTable
where date >='anyDate'

this message is shown:

Checking identity information: current identity value '1252'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Msg 515, Level 16, State 2, Procedure theSp, Line 16
Cannot insert the value NULL into column 'amount', table 'db2.dbo.theTable'; column does not allow nulls. INSERT fails.

Note: the 'amount' attribute for db1 and db2 tables allows null but I never insert null, instead, I insert 0.

So I filtered to check whether the amount attribute is null and I did not get results, meaning there are not nulls value in the amount attribute.

Does anyone know a possible solution?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Steve
  • 29
  • 1
  • 8
  • Have you tried ISNULL([your_column],0) ? – briskovich May 13 '19 at 22:00
  • *"Note: The 'amount' attribute for db1 and db2 dbs tables allows null but I never insert null, instead, I insert 0."* Maybe you always make sure you insert `0` instead of `NULL`, but *someone* has inserted a `NULL`. If the column really shouldn't include `NULL` values, then it should be declared as `NOT NULL`. I would suggest that if you tried to change that attribute of `db1.theTable.amount` to `NOT NULL` the change would fail, due to rows have the value `NULL`. – Thom A May 13 '19 at 22:04
  • I think you might have removed essential information in your attempt to make the question as concise as possible (which is a good goal). Because there are 2 things clear from the error you have posted; 1) You are definitely trying to insert into a column which **doesn't** allow nulls - there is not other way you can get that error, 2) You are definitely getting a null result from your insert query, again there is no other way you can get that error. So really its a case of investigation to find why/how those 2 events are happening. – Dale K May 13 '19 at 22:12
  • Thanks the problem was solved by using ISNULL([theColumn], 0), I see it is not possible to know which record had the NULL attribute this way: select * from table where amount=NULL – Steve May 13 '19 at 22:39

2 Answers2

0

By looking at the error message that you have posted

Cannot insert the value NULL into column 'amount', table 'db2.dbo.theTable'; column does not allow nulls. INSERT fails. The statement has been terminated.

there is a Null value for amount column in `db2.dbo.theTable'. You can use ISNULL() to get rid of this issue.

If you want to see the NULL values, you gotta use query like

select * from db2.dbo.theTable where amount IS NULL

Have a look at is Null vs =Null to see why you weren't seeing those null records in your previous query.

Bharathi
  • 1,015
  • 13
  • 41
0

Good to have a logger to validate the condition when it's going wrong, like printing the values before inserting

Yugansh
  • 365
  • 3
  • 9