March 27, 2007 at 5:43 am
Is there any way to set ansi_padding to on in a stored procedure?
I have a table with a persisted computed column, and this is causing some stored procedures to fail when trying to insert if the connection does not have ansi_padding set to true, but this property isn't saved with the stored procedure the same way ansi_nulls is.
Here's an example that is producing my problem.
Notice, the first insert_A gets the correct error. The second insert_B fails with Internal Query Processor Error, it also doesn't even touch table A so really it should not be failing?
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].') AND type in (N'U'))
DROP TABLE [dbo].
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[A]') AND type in (N'U'))
DROP TABLE [dbo].[A]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[insert_A]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[insert_A]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[insert_B]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[insert_B]
GO
CREATE TABLE [dbo].[A](
[id1] [int] NOT NULL,
[a] [tinyint] NOT NULL,
AS CASE [a] WHEN 1 THEN 1 ELSE 0 END PERSISTED,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[id1] ASC
)
)
GO
CREATE TABLE [dbo].(
[id1] [int] NOT NULL,
[id2] [int] NOT NULL,
CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED
(
[id1] ASC,
[id2] ASC
),
CONSTRAINT [FK_B_A] FOREIGN KEY([id1])
REFERENCES [dbo].[A] ([id1])
)
GO
SET ANSI_PADDING ON
GO
CREATE proc [dbo].[insert_A]
@id1 int,
@a tinyint
AS
INSERT INTO A( id1, a)
VALUES (@id1, @a)
GO
SET ANSI_PADDING ON
GO
CREATE proc [dbo].[insert_B]
@id1 int,
@id2 int
AS
SET ANSI_PADDING ON
INSERT INTO B( id1, id2)
VALUES (@id1, @id2)
GO
SET ANSI_PADDING OFF
GO
EXECUTE [insert_A]
@id1=null
,@a=null
EXECUTE [insert_B]
@id1=null
,@id2=null
March 27, 2007 at 6:48 am
I guess I'm confused by the problem. When I ran the test as presented I got the error you described, but I got the same error even when I commented out the [insert_B] execute statement. This suggests the problem. Some clarification might help or someone brighter than me will figure it out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 27, 2007 at 7:11 am
sorry, the first insert_A shows the error i would have expected. But the insert_B is failing with an internal query error instead of the error I would have expected (this made locating the problem troublesome to say the least).
Here are the two errors I get:
Msg 1934, Level 16, State 1, Procedure insert_A, Line 6
INSERT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
Msg 8624, Level 16, State 1, Procedure insert_B, Line 7
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.
I would have expected insert_B to work since it doesn't even touch table A, but it seems to fail simply because table B has a foreign key constraint on table A.
The second error (insert_B) is the error I was actually getting, the cause of which seems to be related to the error seen in insert_A as removing the foreign key, changing the column A.b to not be persisted, or setting ansi_padding on for the connection all make insert_B (and insert_A) work.
But as I've stated, insert_B has no reliance upon table A and doesn't use, modify or alter table A in anyway (other than the foreign key) so I don't even see why it should fail.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply