ANSI_PADDING woes !

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...
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
"Tim Greenwood" wrote:

> 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...
>
>
|||"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
> 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...
What was the ANSI_PADDING setting when you created the table and when you
later created the computed column?
|||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.
"Mike C#" <xyz@.xyz.com> wrote in message
news:Ozq7VKxhIHA.5900@.TK2MSFTNGP02.phx.gbl...
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
> What was the ANSI_PADDING setting when you created the table and when you
> later created the computed column?
>
|||Hi
If you check my script out in the other reply it seems to be the session
where you do the insert.
John
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%239lAvkDiIHA.2416@.TK2MSFTNGP04.phx.gbl...
>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.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:Ozq7VKxhIHA.5900@.TK2MSFTNGP02.phx.gbl...
>
|||"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%239lAvkDiIHA.2416@.TK2MSFTNGP04.phx.gbl...
>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.
|||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.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5B951B45-CAEE-4FAB-90AB-4CC90BB024DB@.microsoft.com...[vbcol=seagreen]
> 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
> "Tim Greenwood" wrote:
|||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
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:u7mcB1HiIHA.1164@.TK2MSFTNGP02.phx.gbl...
> 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.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:5B951B45-CAEE-4FAB-90AB-4CC90BB024DB@.microsoft.com...
>