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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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