Sql ANSI Padding: ansi

显示标签为“ansi_padding”的博文。显示所有博文

2012年2月11日星期六

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...
>> 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?
>|||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: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?
>|||"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...
> 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...
>>|||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...
>> 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...
>>
>

Ansi_Padding Question

Hi all,
How do you determine if ansi_padding was set to on or off when a table
was created? Aside from inserting a record with trailing blanks, I
can't seem to find an answer.
Thanks,
Terryif (@.@.OPTIONS & 16 = 16)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Terry" <wondermutt@.sbcglobal.net> wrote in message
news:6eee4aa9.0310031300.468ccb15@.posting.google.com...
> Hi all,
> How do you determine if ansi_padding was set to on or off when a table
> was created? Aside from inserting a record with trailing blanks, I
> can't seem to find an answer.
> Thanks,
> Terry|||@.@.options only helps you figure out the current value of ANSI_PADDING.
To check the value in effect when a column was created, use COLUMNPROPERTY
SELECT COLUMNPROPERTY(table_id, column_name, 'UsesANSITrim')
Please see Books Online for more details.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Terry" <wondermutt@.sbcglobal.net> wrote in message
news:6eee4aa9.0310031300.468ccb15@.posting.google.com...
> Hi all,
> How do you determine if ansi_padding was set to on or off when a table
> was created? Aside from inserting a record with trailing blanks, I
> can't seem to find an answer.
> Thanks,
> Terry

ANSI_PADDING Deprecated

I have a few questions regarding the ANSI_PADDING option. I see, according
to MSDN (http://msdn2.microsoft.com/en-us/library/ms187403.aspx), that this
option is deprecated:
> This feature will be removed in a future version of Microsoft SQL
> Server. Avoid using this feature in new development work, and
> plan to modify applications that currently use this feature.
My questions are:
1) What will this setting be fixed to once it is no longer supported?
According to MSDN, their recommendation is that we always have it set to ON,
and it states the default value is also ON. So I would assume that it will
be fixed to ON once it is no longer supported, but I don't see this
explicitly stated anywhere.
2) What will happen to existing databases with columns defined with this
option turned on? Will they be converted such that the columns will have
ANSI_PADDING ON, or will the OFF setting be preserved? I would assume that
they will be converted, but again, this is not explicitly stated anywhere.
If this information is listed somewhere, please direct me to the reference.
Thanks in advance for your time.
JeradJerad,
1)
I suggest you do a feedback on that BOL page. They should document what the setting will be when it
is deprecated.
I can bet a large sum that it will be ON. The Sybase heritage had the OFF behavior, where ANSI SQL
has the ON behavior. So, in some version (possibly 6.0) we could use this setting to change the
behavior. Over time, the ANSI (ON) behavior has become more and more dominating (tools defaulting to
this etc). Considering that some features require ON behavior (indexed views etc), and ANSI SQL is
ON, this will be ON when deprecated. But of course, the text should tell us that, so I suggest you
BOL feedback.
2) I have a feeling that no-one know at this moment what happens to existing databases when the
setting is deprecated. This is probably one of the things that MS need to work out with the
customers during beta. So, this I can understand why they can't comment on at this moment. But I
suggest you mention this in your BOL feedback. You never know, they might already know what will
happen...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jerad Rose" <no@.spam.com> wrote in message news:uRheZ08aHHA.348@.TK2MSFTNGP02.phx.gbl...
>I have a few questions regarding the ANSI_PADDING option. I see, according to MSDN
>(http://msdn2.microsoft.com/en-us/library/ms187403.aspx), that this option is deprecated:
>> This feature will be removed in a future version of Microsoft SQL
>> Server. Avoid using this feature in new development work, and
>> plan to modify applications that currently use this feature.
> My questions are:
> 1) What will this setting be fixed to once it is no longer supported? According to MSDN, their
> recommendation is that we always have it set to ON, and it states the default value is also ON.
> So I would assume that it will be fixed to ON once it is no longer supported, but I don't see this
> explicitly stated anywhere.
> 2) What will happen to existing databases with columns defined with this option turned on? Will
> they be converted such that the columns will have ANSI_PADDING ON, or will the OFF setting be
> preserved? I would assume that they will be converted, but again, this is not explicitly stated
> anywhere.
> If this information is listed somewhere, please direct me to the reference.
> Thanks in advance for your time.
> Jerad
>|||Thanks for your response, Tibor. I will follow your advise, and submit
feedback via BOL.
Thanks again.
Jerad
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$4zIM%23aHHA.2076@.TK2MSFTNGP04.phx.gbl...
> Jerad,
> 1)
> I suggest you do a feedback on that BOL page. They should document what
the setting will be when it
> is deprecated.
> I can bet a large sum that it will be ON. The Sybase heritage had the OFF
behavior, where ANSI SQL
> has the ON behavior. So, in some version (possibly 6.0) we could use this
setting to change the
> behavior. Over time, the ANSI (ON) behavior has become more and more
dominating (tools defaulting to
> this etc). Considering that some features require ON behavior (indexed
views etc), and ANSI SQL is
> ON, this will be ON when deprecated. But of course, the text should tell
us that, so I suggest you
> BOL feedback.
>
> 2) I have a feeling that no-one know at this moment what happens to
existing databases when the
> setting is deprecated. This is probably one of the things that MS need to
work out with the
> customers during beta. So, this I can understand why they can't comment on
at this moment. But I
> suggest you mention this in your BOL feedback. You never know, they might
already know what will
> happen...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jerad Rose" <no@.spam.com> wrote in message
news:uRheZ08aHHA.348@.TK2MSFTNGP02.phx.gbl...
> >I have a few questions regarding the ANSI_PADDING option. I see,
according to MSDN
> >(http://msdn2.microsoft.com/en-us/library/ms187403.aspx), that this
option is deprecated:
> >
> >> This feature will be removed in a future version of Microsoft SQL
> >> Server. Avoid using this feature in new development work, and
> >> plan to modify applications that currently use this feature.
> >
> > My questions are:
> >
> > 1) What will this setting be fixed to once it is no longer supported?
According to MSDN, their
> > recommendation is that we always have it set to ON, and it states the
default value is also ON.
> > So I would assume that it will be fixed to ON once it is no longer
supported, but I don't see this
> > explicitly stated anywhere.
> >
> > 2) What will happen to existing databases with columns defined with this
option turned on? Will
> > they be converted such that the columns will have ANSI_PADDING ON, or
will the OFF setting be
> > preserved? I would assume that they will be converted, but again, this
is not explicitly stated
> > anywhere.
> >
> > If this information is listed somewhere, please direct me to the
reference.
> >
> > Thanks in advance for your time.
> >
> > Jerad
> >
>|||> I can bet a large sum that it will be ON.
Tibor, I hope someone took you up on your bet. In fact, the setting will
always be ON. The topic is unclear (Thanks, Jerad, for submitting your
feedback). What's actually being deprecated is the ability to set the option
to OFF.
> 2) I have a feeling that no-one know at this moment what happens to
> existing databases when the setting is deprecated.
Again, you're correct. The migration/upgrade story for this is likely not
firmly in place given that doesn't go into effect for another two releases.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$4zIM%23aHHA.2076@.TK2MSFTNGP04.phx.gbl...
> Jerad,
> 1)
> I suggest you do a feedback on that BOL page. They should document what
> the setting will be when it is deprecated.
> I can bet a large sum that it will be ON. The Sybase heritage had the OFF
> behavior, where ANSI SQL has the ON behavior. So, in some version
> (possibly 6.0) we could use this setting to change the behavior. Over
> time, the ANSI (ON) behavior has become more and more dominating (tools
> defaulting to this etc). Considering that some features require ON
> behavior (indexed views etc), and ANSI SQL is ON, this will be ON when
> deprecated. But of course, the text should tell us that, so I suggest you
> BOL feedback.
>
> 2) I have a feeling that no-one know at this moment what happens to
> existing databases when the setting is deprecated. This is probably one of
> the things that MS need to work out with the customers during beta. So,
> this I can understand why they can't comment on at this moment. But I
> suggest you mention this in your BOL feedback. You never know, they might
> already know what will happen...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jerad Rose" <no@.spam.com> wrote in message
> news:uRheZ08aHHA.348@.TK2MSFTNGP02.phx.gbl...
>>I have a few questions regarding the ANSI_PADDING option. I see,
>>according to MSDN
>>(http://msdn2.microsoft.com/en-us/library/ms187403.aspx), that this option
>>is deprecated:
>> This feature will be removed in a future version of Microsoft SQL
>> Server. Avoid using this feature in new development work, and
>> plan to modify applications that currently use this feature.
>> My questions are:
>> 1) What will this setting be fixed to once it is no longer supported?
>> According to MSDN, their recommendation is that we always have it set to
>> ON, and it states the default value is also ON. So I would assume that it
>> will be fixed to ON once it is no longer supported, but I don't see this
>> explicitly stated anywhere.
>> 2) What will happen to existing databases with columns defined with this
>> option turned on? Will they be converted such that the columns will have
>> ANSI_PADDING ON, or will the OFF setting be preserved? I would assume
>> that they will be converted, but again, this is not explicitly stated
>> anywhere.
>> If this information is listed somewhere, please direct me to the
>> reference.
>> Thanks in advance for your time.
>> Jerad
>|||> Tibor, I hope someone took you up on your bet.
I'm afraid no-one did... :-)
Thanks for jumping in and clarify, Gail.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:OK7GGNBbHHA.3408@.TK2MSFTNGP03.phx.gbl...
>> I can bet a large sum that it will be ON.
> Tibor, I hope someone took you up on your bet. In fact, the setting will always be ON. The topic
> is unclear (Thanks, Jerad, for submitting your feedback). What's actually being deprecated is the
> ability to set the option to OFF.
>> 2) I have a feeling that no-one know at this moment what happens to existing databases when the
>> setting is deprecated.
> Again, you're correct. The migration/upgrade story for this is likely not firmly in place given
> that doesn't go into effect for another two releases.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e$4zIM%23aHHA.2076@.TK2MSFTNGP04.phx.gbl...
>> Jerad,
>> 1)
>> I suggest you do a feedback on that BOL page. They should document what the setting will be when
>> it is deprecated.
>> I can bet a large sum that it will be ON. The Sybase heritage had the OFF behavior, where ANSI
>> SQL has the ON behavior. So, in some version (possibly 6.0) we could use this setting to change
>> the behavior. Over time, the ANSI (ON) behavior has become more and more dominating (tools
>> defaulting to this etc). Considering that some features require ON behavior (indexed views etc),
>> and ANSI SQL is ON, this will be ON when deprecated. But of course, the text should tell us that,
>> so I suggest you BOL feedback.
>>
>> 2) I have a feeling that no-one know at this moment what happens to existing databases when the
>> setting is deprecated. This is probably one of the things that MS need to work out with the
>> customers during beta. So, this I can understand why they can't comment on at this moment. But I
>> suggest you mention this in your BOL feedback. You never know, they might already know what will
>> happen...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jerad Rose" <no@.spam.com> wrote in message news:uRheZ08aHHA.348@.TK2MSFTNGP02.phx.gbl...
>>I have a few questions regarding the ANSI_PADDING option. I see, according to MSDN
>>(http://msdn2.microsoft.com/en-us/library/ms187403.aspx), that this option is deprecated:
>> This feature will be removed in a future version of Microsoft SQL
>> Server. Avoid using this feature in new development work, and
>> plan to modify applications that currently use this feature.
>> My questions are:
>> 1) What will this setting be fixed to once it is no longer supported? According to MSDN, their
>> recommendation is that we always have it set to ON, and it states the default value is also ON.
>> So I would assume that it will be fixed to ON once it is no longer supported, but I don't see
>> this explicitly stated anywhere.
>> 2) What will happen to existing databases with columns defined with this option turned on? Will
>> they be converted such that the columns will have ANSI_PADDING ON, or will the OFF setting be
>> preserved? I would assume that they will be converted, but again, this is not explicitly stated
>> anywhere.
>> If this information is listed somewhere, please direct me to the reference.
>> Thanks in advance for your time.
>> Jerad
>>
>

Ansi_Padding - how to get rid of

Hello -
I copied and pasted a database (and log) from Sql Server 2000 to Sql Server
2005 (and attached it).
Everything appeared okay until my VB 6 app had problems with its comboboxes.
I realized that Ansi_Padding was automatically put in by Sql Server 2005 (it
wasn't there in Sql Server 2000), thus making the text in the textbox of the
combo display strangely (e.g. not showing the first few letters of a
particular item, etc).
My question is: How do I get the Ansi_Padding out? It is a varchar field.
Any help will be greatly appreciated!
--
SandySandy (Sandy@.discussions.microsoft.com) writes:
> I copied and pasted a database (and log) from Sql Server 2000 to Sql
> Server 2005 (and attached it).
> Everything appeared okay until my VB 6 app had problems with its
> comboboxes.
> I realized that Ansi_Padding was automatically put in by Sql Server
> 2005 (it wasn't there in Sql Server 2000), thus making the text in the
> textbox of the combo display strangely (e.g. not showing the first few
> letters of a particular item, etc).
> My question is: How do I get the Ansi_Padding out? It is a varchar
> field.
If you attached the database file from SQL 2000, the setting of ANSI_PADDING
should not change, as it saved with the column.
You can verify this by running:
select name, is_ansi_padded
from sys.columns
where object_id('usrdictwords') = object_id
Generally I would recommend that you stick with ANSI_PADDING on, since
there are features that require this setting. In SQL 2000 it was
indexed views and indexed computed columns. In SQL 2005 this require-
ment also applies when you use XQuery.
As for the behaviour of your VB app, it does not sound like ANSI_PADDING
to me. What ANSI_PARDDING is about is what happens to trailing blanks
in varchar when you insert it. With ANSI_PADDING off, they are trimmed,
with ANSI_PADDING on, they are retained.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

订阅：博文 (Atom)

2012年2月11日星期六

ANSI_PADDING woes !

ANSI_PADDING woes !

Ansi_Padding Question

ANSI_PADDING Deprecated

ANSI_PADDING Deprecated

ANSI_PADDING Deprecated

Ansi_Padding - how to get rid of

Sql ANSI Padding

Blog Archive

About Me