Internal Query Processor Error when instering into table with foreign keys - by mrdenny

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


3
0
Sign in
to vote
ID 422368 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 3/10/2009 4:29:26 PM
Access Restriction Public

Description

We on occasion see:
Msg 8624, Level 16, State 1, Procedure ins_Group, Line 37

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

The statement in question that is fails on is:
    INSERT INTO	[Group]	(	AccountId,
							GroupName,
							PropertyStatic,
							MemberStatic,
							SettingStatic,
							IsHidden)
	VALUES	(	@AccountId,
				@GroupName,
				@PropertyStatic,
				@MemberStatic,
				@SettingStatic,
				@IsHidden);

The table [Group] has a single Foreign key on the AccountId column to the [Account] table.

When that foreign key is deleted another statement within the procedure returns the same error.

INSERT INTO	GroupSetting (	GroupId,
							SettingId,
							[Value]	)
SELECT		@GroupId,
			SettingId,
			DefaultValue
FROM		Setting WITH(NOLOCK)
WHERE		SettingId > 0
AND			SettingScopeId = 

The [GroupSetting] table has two foreign keys.  One to the [Setting] table and one to the [Group] table.  I deleted the foreign key to the Setting table and the procedure began running without error.

This problem is intermittent, but does show up sometimes.  It happens on SQL 2005 SP2 and SP3.
Sign in to post a comment.
Posted by mrdenny on 3/13/2009 at 9:33 PM
Campbell,
I don't have any computed columns. I will try settings these settings next week and get back to you.

Thanks,
Denny
Posted by Microsoft on 3/13/2009 at 1:32 PM
Thank you for taking the time to report this issue.

This sounds like a duplicate of an issue we are already aware of, and for which a workaround is present. When you run the insert statement that fails to compile, please make sure that all the SET options required to create the persisted computed column on the primary table are enabled. Ideally, this should not be a requirement when inserting into the foreign table, but I hope it's an acceptable workaround for the time being.
The options in question are:

ANSI_NULLS –> ON
ANSI_PADDING –> ON
ANSI_WARNINGS –> ON
CONCAT_NULL_YIELDS_NULL –> ON
NUMERIC_ROUNDABORT –> OFF
QUOTED_IDENTIFIER –> ON
ARITHABORT –> ON (required only if the database is in 80 compatibility mode)

The issue is tracked by Connect Feedback ID : 228950

Please let us know if this turns out to not be the same issue.

Campbell Fraser,
SQL Development