How to handle Tempdb.dbo.##XXXXXXX

Question

How to handle Tempdb.dbo.##XXXXXXX

SZARS

SSChasing Mays

Points: 655
More actions
July 1, 2010 at 2:28 pm

#387266

Under my Systems Databases>tempdb>temporary tables; I have there table with dbo. ##XXXXXX. when I run an application I get the following error
Microsoft SQL Server Cannot insert the value NULL into column XXXX, table tempdb.dbo.##XXXXX. Column does not allow nulls. INSERT fails and I can not run the report
How to maneuver this issue ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

It looks like you are using a global temp table. The temp table has a constraint on column XXXX which does not allow null values.

First, why are you using a global temp table? You probably should only be using a local temp table (one # sign) - which will make that table only available to the connection that creates it.

Second, you need to make sure you are inserting the correct data in the correct columns. In other words, you have to have values defined for column XXXX when you insert into this table.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

zeeaay (7/1/2010)
Under my Systems Databases>tempdb>temporary tables; I have there table with dbo. ##XXXXXX. when I run an application I get the following error
Microsoft SQL Server Cannot insert the value NULL into column XXXX, table tempdb.dbo.##XXXXX. Column does not allow nulls. INSERT fails and I can not run the report
How to maneuver this issue ?

I absolutely agree with what Jeffrey wrote above... if you're using a Global Temporary Table, you've most likely done something very wrong.

That notwithstanding, please post the code you're using that creates the GTT so we don't have to guess. Thanks. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SZARS SSChasing Mays Points: 655 More actions · Answer 3

Dear folks,

Thank you very much for replying so quickly to help me out. Reply to your assumptions. I am not doing anything. If you read my message carefully I said, I am using an Application which an ERP ( Microsoft Dynamic GP) and that table is auto created by that application not today.

We never had this issue for the past 3 years until now and the report I run is also a canned report and we do not input values manually into tables. The Application also has the data integrity option of not accepting any NULLS at the time of creation of any record.

Anything and everything that goes into SQL table is fed by the application we do touch it except for reporting.

I hope this will help

Regards,

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

zeeaay (7/1/2010)
Under my Systems Databases>tempdb>temporary tables; I have there table with dbo. ##XXXXXX. when I run an application I get the following error
Microsoft SQL Server Cannot insert the value NULL into column XXXX, table tempdb.dbo.##XXXXX. Column does not allow nulls. INSERT fails and I can not run the report
How to maneuver this issue ?

If you read my message carefully I said, I am using an Application which an ERP ( Microsoft Dynamic GP) and that table is auto created by that application not today.

You said no such thing and even Carnac wouldn't have been able to derive such a description from what you wrote. If you're going to get both testy and inaccurate in your statements on this forum, I suggest you call Microsoft about their product. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Leo.Miller SSChampion Points: 13051 More actions · Answer 5

zeeaay (7/1/2010)
Dear folks,
Thank you very much for replying so quickly to help me out. Reply to your assumptions. I am not doing anything. If you read my message carefully I said, I am using an Application which an ERP ( Microsoft Dynamic GP) and that table is auto created by that application not today.
We never had this issue for the past 3 years until now and the report I run is also a canned report and we do not input values manually into tables. The Application also has the data integrity option of not accepting any NULLS at the time of creation of any record.
Anything and everything that goes into SQL table is fed by the application we do touch it except for reporting.

Talk to the application vendor.

When last did you restart the server or at least the SQL Service? A restart will clear theTempDB objects and allow the app to recreate them, possibly clearing up what may just be a user error.

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.