SP not working "Can not insert value NULL into column 'Docentry'.

[Former Member]

Detailed problem description: This is in SBO2005B PL 39

We have created some store procedures in SP_transactionnotification. To monitor if user are doing something they are not suppose to do. For all other transaction it seems to be working fine. But in Invoice, one part of the sp would not work at all (it will tell me it has been completed successful, but no document is added), and if I disable that part other part which work against objtype 13 works. Which means if something is wrong it will sent me a error message and stop transaction, but when I correct the problem and try to add again. I get MS_SQL error message "Can not insert null in INV11.DOCENTRY, this field doesn't accept null value" I attached the screen shot here.

I have taken everything off the original store procedure and used just the problem SP for Invoice and it is still the same problem. Furthermore when I run the store procedure against another set of similar DB without the DTW loads and it works fine. I am just not sure what's wrong with it... Can anyone help please?

Sincerely Yours

Bo Peng

[Former Member]

Bo,

send here part of SP with objtype = 13.

[Former Member]

Hello:

Thank you for responding. I worked this SP with different database earlier and it works just fine. It's been working fine until very recently... We have a function in there called CC_Get_Messsage. It does nothing but get a corresponding message from a UDT on the database. (We checked the UDT and everything looks ok)

From my guess, it looks like it is something with the db.. But just not sure because if I turn all store procedure off, everything looks ok!

IF @object_type='13' AND (@transaction_type='A' or @transaction_type='U') 
BEGIN 
SELECT  @is1=U_CC_INS1, @is2=U_CC_INS2, @is3=U_CC_INS3, @isp1=U_CC_IPC1, @isp2=U_CC_IPC2, @isp3=U_CC_IPC3 from OINV where docentry= @list_of_cols_val_tab_del 


                IF isnull(@isp1,0)+isnull(@isp2,0)+isnull(@isp3,0) <> 100 
                begin 
                        SET @error = 101 
                        SET @error_message=dbo.CC_Get_Message('CC-0901','JAP') -- 'Industry Segment percentages assigned must sum to 100% - Please change values entered' 
                        goto exitline 
                END 
                ELSE 
                BEGIN 
                        if (isnull(@is3,'') <>'' and isnull(@is2,'') <>'' and @is3 = @is2) or (isnull(@is3,'') <>'' and isnull(@is1,'') <>'' and @is3 = @is1) or (isnull(@is1,'') <>'' and isnull(@is2,'') <>'' and @is1 = @is2) 
                        begin 
                                SET @error = 101 
                                SET @error_message=dbo.CC_Get_Message('CC-0902','JAP') --'The same Industry Segment can not be assigned twice - Please change the Industry Segments fields'         
                                goto exitline 
                END 
END 

....

exitline;

Edited by: Bo Peng on Sep 15, 2008 7:08 AM

[Former Member]

how do you have declared variables @isp1 2 and 3?

[Former Member]

declare @isp1 as smallint

declare @isp2 as smallint

declare @isp3 as smallint

declare @is1 as smallint

declare @is2 as smallint

declare @is3 as smallint

It is pretty standard code I thought

[Former Member]

Bo,

it seems to be ok in case that user fields you have contains numeric values. Did you check other PL?

[Former Member]

Hello Petra:

Yes I did. Every other store procedure works just fine. It was working in preceeding db. Only had problem after DTW>. so I am very worried that it can be data problem. Which means all the stuff that we done have to be reversed!

Sincerely Yours

Bo Peng

[Former Member]

Maybe last thing - the problems occurs with adding document or updating too? Did you check, what is stored in db in your user fields for values in records imported through DTW?