Bug with insert inside stored proc
-
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
"531 When 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?
-
just run a simple *insert into * in a strore precedure - got no errors
ALTER
PROCEDURE [dbo].[sp1]
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
insert
_check_insert_bug values( 'one ','two','three')
END
-
That's the strange part -- this doesn't happen anywhere else, only in this one specific proc and only inserting into this one specific table. We have other inserts in other stored procs that work just fine.
-
Merrill,
I found this suggestion on another forum:
"I have encountered the same error while executing a simple Insert query - I wouldn't know how to begin to re-write it to make it work. I did, however, discover another way to rectify the situation. If I open the table in Management Console and change something about the data type for any one column, e.g. from Char(10) to Char(11), or uncheck the Allow Nulls checkbox and then save the table the insert will work from then on.
Note: The problem is happening with a database that worked just fine under SQL Server 2000."
The full forum can be found at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=400808&SiteID=1
-
Thanks for that suggestion -- I also found that posting and tried that, but to no avail.
I saw another suggestion indicating that it could be a connection settings issue (two of which are stored with the stored procedure definition)
I wonder if that poster inadvertently modified the connection settings used to create the table, by modifying it with the SSMS interface -- and then just assumed it was the datatype change that really fixed the issue? I'm grasping... 🙂
-
Solved with a tip from forms on sql-server-performance.com
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.
Redefining the proc with correct connection settings appears to fix the issue.
-
I really struggled with this for about a week when we tried to get this working. I tried EVERY thing I found on the web but mostly what I found were other posts from developers who couldn't get it to work with NO solutions to suggest. I was able to get this to work:
1. Create BCP file. Here is a script that I use to create the bcp file from an existing table in a SQL2005 database. What I create are 'seed' data files that are used to initialize a new database with data (they don't change). I don't know if your source data is from SQL database, but if not, use whatever method you need to create a bcp file. Below is a sample script that works for sql2005 source:
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @bcpCommand = 'bcp "select cd.* from sourcedbname.dbo.CustomerDiagnosis cd '
SET @bcpCommand = @bcpCommand + 'inner join sourcedbname.dbo.diagnosisLibrary dl '
SET @bcpCommand = @bcpCommand + 'on dl.diagnosisLibID = cd.diagnosisLibID '
SET @bcpCommand = @bcpCommand + 'where dl.customerID <> '''
SET @bcpCommand = @bcpCommand + '00000000-0000-0000-0000-000000000000'''
SET @bcpCommand = @bcpCommand + '" queryout "'
SET @bcpCommand = @bcpCommand + 'c:\temp\CustomerDiagnosis.bcp' + '" -c -T -S sourcedbname'
EXEC master..xp_cmdshell @bcpCommand
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
gO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
O.K. Now once you have the bcp file created, here's the stored procedure that will make it work - I've had to edit it down to make it more generic, so I've left out error handling. This stored procedure is expecting to be called by a script - so you'll see code that sets local variables to values passed in by scripts - you could set them other ways if you need. Execute this procedure on the server that the target database lives on
use TargetDatabase
go
IF OBJECT_ID ('TargetDatabase.dbo.temp_CustomerDiagnosis', 'U') IS NOT NULL
DROP TABLE TargetDatabase.dbo.temp_CustomerDiagnosis;
CREATE TABLE temp_CustomerDiagnosis (
custDiagnosisID uniqueidentifier,
diagnosisID uniqueidentifier,
diagnosisLibID uniqueidentifier,
custDiagnosisMedicalInd bit,
custDiagnosisActiveInd bit,
InsertTS datetime,
InsertUserID uniqueidentifier,
UpdateTS datetime,
UpdateUserID uniqueidentifier
 
----------------------------------------------------------------------------------------
-- update the serverSettings to allow xp_cmdShell to run
----------------------------------------------------------------------------------------
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
declare @cmd varchar(8000)
declare @result varchar(8000)
declare @TargetServer sysname
--specify the distribution server using script variable
select @TargetServer = $(PublicationServer)
set @cmd = 'bcp TargetDatabase.dbo.temp_CustomerDiagnosis in \\bcpfilelocation root\DbBuildRoot\bcpfolder\SeedData\CustomerDiagnosis_Data.bcp ' + '-T -c -S ' + @TargetServer
-- run the command
exec @result = master.dbo.xp_cmdshell @cmd
----------------------------------------------------------------------------------------
-- update the serverSettings to lock down xp_cmdShell
----------------------------------------------------------------------------------------
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
-
On my last post the end of the temp table declaration was a semi-colon followed by an end parens, which the posting interpreted as a winking happy face (bla!) So replace the little graphic with a semicolon and end parens!
-
Are you sure that's for this thread? I don't understand how what you posted relates to my issue. ?
-
The funny part is the error message. In sql 2000 it says "invalid connection settings" when the conditions that you described happens. It is good to know that I may get a "no plan found" message on 2005
Another thing to point out is that you should try to include the column list on your insert statement to see if it produces a "better" error message
Thanks for all the fish,
* Noel
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply