January 4, 2010 at 11:31 pm
Hi All,
I'm a developer working on PHP - MSSQL 2008 combined platform. While running a procedure from a webpage I'm encountering the error as below.
My Query:
DECLARE @ReturnValue INT
EXEC @ReturnValue = S_AccountsBatchActivation @FirstName='abc', @LastName='abc', @Address='abc', @City='abc', @State='abc', @Country='India', @Phone='1', @BatchId='6502'
SELECT ReturnValue=@ReturnValue
Error
Cannot insert the value NULL into column 'TariffId', table 'tempdb.dbo.#tbl________________________________________________________________________________________________________________000000000219'; column does not allow nulls. INSERT fails.
Though the same query when run from SSMS runs fine:
Execute S_AccountsBatchActivation
@FirstName='abc',
@LastName='abc',
@Address='abc',
@City='abc',
@State='abc',
@Country='India',
@Phone='11',
@BatchId=6502
Issue is perhaps in this piece of code:
Create Table #tbl(
AccountId varchar(16) COLLATE DATABASE_DEFAULT,
RatesheetId int,
TariffId int
)
Insert Into #tbl(AccountId,RatesheetId)
SelectAccountId,RatesheetId
FromUsr_Accounts
WhereBatchId=@BatchId AND Status is null
If @@Error<>0
Begin
Drop Table #tbl
Return 0
End
On my end I've tried out with ANSI_NULL AND ANSI_NULLS settings being both ON and OFF but it didn't worked.
Kindly help I've been searching for this for quite some time but am unable to debug this issue.
Thanks
Ankit Mathur
January 4, 2010 at 11:41 pm
nothing seems to be the issue with the create table.
can you post the stored procedure code?
January 4, 2010 at 11:49 pm
As desired,
Complete procedure attached.
I hope it helps.
Ankit
January 5, 2010 at 12:05 am
I am stumped. Stored procedure looks good.
Dont want to ask a dumb question so apologize for that.
Ok. Are you sure, the stored procedure being called from PHP is the same stored procedure when you run from SMSS?
Could it be stored procedures are on 2 different databases or servers and slightly different?
I had once burnt quite some time troubleshooting a stored procedure call from ASP.net application, when I checked the connection string it was pointing to the wrond instance of SQL Server. Just saying.
January 5, 2010 at 12:09 am
Perhaps the same issue can be regenerated using the following commands
CREATE PROCEDURE sp_testing as
CREATE TABLE #test (testcol1 int,testcol2 int)
INSERT INTO #test VALUES (1,NULL)
SELECT * FROM #test
---------------------------------------------------
DECLARE @ReturnValue INT EXEC @ReturnValue = sp_testing SELECT ReturnValue=@ReturnValue
When I am running this test SP I'm getting "The statement has been terminated." error when run on a webpage. But when run on SSMS the following results come.
testcol1testcol2
1NULL
ReturnValue
0
If this lil' R&D stuff is of any help to anybody looking into this issue.
Ankit Mathur
January 5, 2010 at 12:14 am
Though I was very sure that I'm pointing to correct SP on correct server I have once again checked and it's correctly pointed.
This whole thing was working perfectly well in MSSQL 2000. But as soon as we migrated to MSSQL 2008 it started throwing this error.
Wonder which setting is responsible for it.
Ankit
January 5, 2010 at 12:34 am
* On the same connection do u have any other temporary table with
the name #tbl.
A likely scenario can be this:
Your stored proc would be called by another sp. The outer sp having a temp table #tbl. So this sp would refer to other #tbl structure.
just rename ur #tbl into #tbl_2 or smthng it shd work.
Regards,
Raj
January 5, 2010 at 12:46 am
Hi Nagaraj,
MSSQL internally gives a different name for each temp table which it can uniquely identify. I'm very sure of this that same name #tbl shouldn't affect.
Still I tried it out by giving a different name I'm sure is not being used and got the same error.
DECLARE @ReturnValue INT EXEC @ReturnValue = S_AccountsBatchActivation @FirstName='abc', @LastName='abc', @Address='abc', @City='abc', @State='abc', @Country='India', @Phone='1', @BatchId='6502' SELECT ReturnValue=@ReturnValue
Cannot insert the value NULL into column 'TariffId', table 'tempdb.dbo.#tbl_ART____________________________________________________________________________________________________________00000000023D'; column does not allow nulls. INSERT fails.
This has got something to do with some setting somewhere specific to NULL treatment.
Ankit
January 5, 2010 at 1:04 am
Just to set things straight, MSSQL doesnt create unique temp table names WITHIN the SAME CONNECTION automatically.
This piece of code is for your ref
Create proc inner_sp
as
create table #tbl(col1 int )
Insert into #tbl select 1
drop table #tbl
GO
create proc outer_sp
as
create table #tbl(col1 int , col2 int )
Insert into #tbl select 1,1
Exec inner_sp
drop table #tbl
GO
EXEC outer_sp
Regards,
Raj
January 5, 2010 at 1:04 am
Ankit,
Can you try explicitly setting the column datatype to NULL and try?
Create Table #tbl(
AccountId varchar(16) COLLATE DATABASE_DEFAULT,
RatesheetId int,
TariffId int NULL
)
And if that doesnt work...
You could also try setting the TarriffId column to NOT NULL and insert 0 into the column instead of NULL when adding rows. This will require some changes to stored proc logic. You get the idea. I hate to do this, but it will be interesting to see what happens.
Interesting problem indeed.!!!!
January 5, 2010 at 1:06 am
On your problem, the issue looks awefully to close to the scenario I explained. Anyways will think of other possiblities as well.
Regards,
Raj
January 5, 2010 at 1:58 am
Thanks vstitte
I had already tried your second suggestion but didn't wanted to change SP that way.
Your suggestion to explicitly put NULL seems to have worked. With this suggestion even the test code where I was getting "statement terminated" error also started working.
Though still unsure what we had done really for SQL Server to start behaving differently this suggestion has worked and my problem sorted out.
Thanks to you and Nagaraj also for your time and suggestions.
Ankit Mathur
January 5, 2010 at 2:23 am
I've seen this issue quite a few times before... unless you specify the nullability of a column during your table creation script, you are at the mercy of the software making your connection, and the way it sets the "ANSI_NULL_DEFAULT" setting.
Some will set it to ON, others will set it to OFF.
I first saw this where Query Analyzer (on SQL 7) had a different default to ADO (or RDO... can't remember which), so I make sure I specifically define NULL or NOT NULL for every column.
January 5, 2010 at 4:04 am
Try the same SP after changing the scope of temp table(#tab)
May 11, 2011 at 8:41 pm
Well, not to dredge up an old topic but this fits my question perfectly. There was never a resolution to the OP's original question posted so I thought I'd reuse the thread.
I have a very similar problem except that I'm calling a procedure from a web page. This is happening in SQL Server (SSMS) itself.
I'm simply trying to insert a value from a column in one table into a column in another. Both columns already exist and are of the same type and length. My SQL statement is very simple:
INSERT INTO CurAccounts (Member2_First)
SELECT CurProfile.Spouse
FROM CurProfile
I get this error: Cannot insert the value NULL into column 'Pri_Org_Code4', table 'MH_Statgin01.dbo.CurAccounts'; column does not allow nulls. INSERT fails.
(yes, I mispelled ".._Staging01.. but no matter)
I don't get this. I am not even referencing the column Pri_Org_Code4 in the query. So my first thought was that since this query is (supposed to be) updating a lot of records in the "CurAccounts" table that, perhaps, there are existing NULLs in the Pri_Org_Code4 field. But there are not. After running this:
SELECT Pri_Org_Code4 FROM CurAccounts WHERE
Pri_Org_Code4 = 'NULL'
I get 0 rows affected. So, I can't figure it out.
Any Ideas?
-Michael.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply