SQL Server Performance

2005 insert bug

Discussion in 'SQL Server 2005 General Developer Questions' started by merrillaldrich, Sep 26, 2006.

  1. merrillaldrich New Member

    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?
  2. merrillaldrich New Member

    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.
  3. merrillaldrich New Member

    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=':)' />
  4. Adriaan New Member

    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).
  5. merrillaldrich New Member

    Ooo. Good call. I will check into that.

    I wonder if the proc has the wrong connection settings from its creation.
  6. merrillaldrich New Member

    You ROCK <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />That was it. The error message was just not really precise.<br /><br />It's a subtle thing:<br /><br />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.<br /><br />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.<br /><br />THANK YOU!
  7. Roji. P. Thomas New Member

    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
  8. majodio New Member

    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!
  9. merrillaldrich New Member

    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.
  10. majodio New Member

    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.
  11. kiwidude New Member

    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 :)

Share This Page