ANSI_PADDING woes !!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tim Greenwood - 15 Mar 2008 00:19 GMT

I've got a table that I've added a computed column to. That column is part
of an index. As soon as I attempt to insert a row I get the infamous:

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

I've tried creating the table with ansi_padding on and off... and with the
session doing the insert on and off....nothing seems to work...what is
causing this?

Thanks for any insight...really stuck and need to get through this to lock
down some input issues...

Reply to this Message

John Bell - 15 Mar 2008 08:21 GMT

Hi Tim

I think that the connection that you are trying to insert does not have the
correct ANSI_PADDING setting rather than anything else.

If Ansi_Padding is on when you create the index and insert everything is ok.

create database TESTDB
go
use TESTDB
gO
SET ANSI_PADDING ON
go
create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
ID2 )
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
insert into tST1 ( ID1, ID2 )
select 1, 3
go
select * from TST1
go

If it is off when you try to insert but on otherwise, you get your error
message

SET ANSI_PADDING Off
go
insert into tST1 ( ID1, ID2 )
select 2, 3
go
/*
Msg 1934, Level 16, State 1, Line 1
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.
*/

If it was off when you create the table and index it will give an error
creating the index

use master
go
drop database TESTDB
go
create database TESTDB
go
use TESTDB
gO

SET ANSI_PADDING Off
go
create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
ID2 )
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
/*
Msg 1934, Level 16, State 1, Line 1
CREATE INDEX 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.
*/

If it is on when creating the index you can insert if ANSI_PADDING is on:
SET ANSI_PADDING On
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
insert into tST1 ( ID1, ID2 )
select 3, 3
go

But not if it is off

SET ANSI_PADDING Off
go
insert into tST1 ( ID1, ID2 )
select 4, 3
go
/*
Msg 1934, Level 16, State 1, Line 1
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.
*/

-- Checking the user options when off and on
-- When off
SELECT @@OPTIONS
-- 5480
SET ANSI_PADDING ON
go
SELECT @@OPTIONS
-- 5496
-- 16 is the USER_OPTION value to set!!

HTH

John

> I've got a table that I've added a computed column to. That column is part
> of an index. As soon as I attempt to insert a row I get the infamous:
[quoted text clipped - 10 lines]
> Thanks for any insight...really stuck and need to get through this to lock
> down some input issues...

Reply to this Message

Tim Greenwood - 17 Mar 2008 23:21 GMT

Yes the connection was it...thank you!! I wasn't aware that the connection
had properties different from the session itself...I mean if my connection
did not have ANSI_PADDING enabled and I entered SET ANSI_PADDING ON I would
have thought that would change everything for the existing
session/connection both.

> Hi Tim
>
[quoted text clipped - 122 lines]
>> lock
>> down some input issues...

Reply to this Message

John Bell - 21 Mar 2008 19:06 GMT

Hi Tim

The SET command would only set the ANSI_PADDING for the current connection
you would need to use sp_configure to set the USER_OPTIONS value, which will
change the properties for any subsequent new connection.

John

> Yes the connection was it...thank you!! I wasn't aware that the
> connection had properties different from the session itself...I mean if my
[quoted text clipped - 133 lines]
>>> lock
>>> down some input issues...

Reply to this Message

Mike C# - 16 Mar 2008 04:05 GMT

> I've got a table that I've added a computed column to. That column is
> part of an index. As soon as I attempt to insert a row I get the
[quoted text clipped - 11 lines]
> Thanks for any insight...really stuck and need to get through this to lock
> down some input issues...

What was the ANSI_PADDING setting when you created the table and when you
later created the computed column?

Reply to this Message

Tim Greenwood - 17 Mar 2008 15:14 GMT

I stated in the original message I have created the table both ways....and
in each instance tried with it on /off for the session doing the insert.

>> I've got a table that I've added a computed column to. That column is
>> part of an index. As soon as I attempt to insert a row I get the
[quoted text clipped - 14 lines]
> What was the ANSI_PADDING setting when you created the table and when you
> later created the computed column?

Reply to this Message

John Bell - 17 Mar 2008 15:50 GMT

Hi

If you check my script out in the other reply it seems to be the session
where you do the insert.

John

>I stated in the original message I have created the table both ways....and
>in each instance tried with it on /off for the session doing the insert.
[quoted text clipped - 17 lines]
>> What was the ANSI_PADDING setting when you created the table and when you
>> later created the computed column?

Reply to this Message

Mike C# - 17 Mar 2008 22:59 GMT

>I stated in the original message I have created the table both ways....and
>in each instance tried with it on /off for the session doing the insert.

Ahh missed that part. So change it in your session/connection where you're
trying to perform the INSERT.

Reply to this Message