SQL Server Performance Forum – Threads Archive

2005 insert bug

Hi all – I’ve just hit a nasty bug in SQL Server 2005 (I was surprised, because the testing we’ve done did not yield any up to now) Basically I have a specific table into which I would like to perform a single-row insert as insert into myTable values ( blah, blah, blah ) Run as ad-hoc SQL, this works with no problem, but within a stored proc it always fails with this message: "Server: Msg 8624, Level 16, State 116, Line x
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services." This, as you might imagine, turns a simple insert into a pretty ugly problem. So I applied this cumulative hotfix:
http://support.microsoft.com/kb/918222/ hoping that item "531When you run a query or a stored procedure in SQL Server 2005, certain conditions can cause the SQL Server optimizer not to be able to produce an execution plan." was my issue. No dice. The problem still happens. I have temporarily (and with a great deal of unpleasant work) substituted an ad-hoc version of this specific function in my app, and that seems to have at least gotten it running. Anyone else hit this? Tips?

More info: the insert statment is really that simple – no joins, just inserting a single row. I tried rewriting it — though how many ways can you really write a simple insert? — as insert … select, and even as dynamic SQL. All forms of the insert yield the same error, except taking it out of the stored proc altogether.

Also, that error message should, for accuracy, read "Please go get the corporate credit card and then contact Customer Support in case we decide it’s not really our problem" <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

I haven’t worked with SQL 2005 yet, but it reminds me of the funny messages one can get if there is an indexed view that includes the table, and the view was created with the wrong settings (courtesy of EM).

Ooo. Good call. I will check into that. I wonder if the proc has the wrong connection settings from its creation.

You ROCK <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> That was it. The error message was just not really precise. It’s a subtle thing: I have one table to which this insert was adding rows; this table is nothing special — no indexed views or anything. BUT — there is another table in the database linked to this with a foreign key / cascade update relation. That other table does have an indexed persisted computed column, and the rules for connections re: persisted computed columns in 2005 are similar to the rules for indexed views in 2000 — very specific connection settings. The two connection settings that get stored with stored procedure definitions were incorrect for this one stored proc, which is why it would not work while ad-hoc inserts would. And I got thrown off because it’s not the target table that has the ‘special sauce,’ but another one entirely. THANK YOU!

SQL server produces those "Interanal [component] error", when SQL server does not have a precise idea of what went wrong. So to me it still qualifies as a bug. Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

Hi, I’m getting a similar error. Msg 8624, Level 16, State 116, Line 1
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services. The difference in my case is that I’m selecting from complex view. How do I change the connection settings? What exactly did you do to correct the issue? Any help you can provide would be MUCH appreciated!

Hi – I don’t know if it’s the same problem or not, but here’s how to check the connection settings: For many objects including stored procs and views, two of the connection level settings (ANSI Nulls and Quoted Identifier) are stored with the object definition, and get applied when that object is used. In the stored procedure case, that means the procedure’s code runs as if the setting were what it was when the proc was created, even if it’s called by a user on a connection with different settings. I’m not sure why, but there must be a reason. Anyway, a simple way to check is to right-click the object and pick Properties, then look at Options section of the properties window. You can also script out a Create or Alter statement and look at the SET options that show up at the top. If they are wrong, then adjust them and use an ALTER statement to update the object definition.

Thank you very much for your quick reply. After some further reading, I’ve found that turning off ANSI Nulls and Quoted IDetifiers is not recommend. So I decided it would be better to change the connection properties instead. After doing this (for SQL Management Studio), I diconnected from the DB and reconnected, but am having the same problem. I tried to change the values on the view, but being far from a SQL Server expert, I’m not certain how to fomulate the ALTER statement. Also, I’m going to install the Hotfixes from the link above. Maybe that will fix it.

Thx dude. Saved my bacon. REALLY appreciate your posting the solution and not just finding one yourself and then not updating the rest of the world. Keep up the good work

]]>