BUG: ANSI_PADDING Ignores Using Subquery in Insert

• Microsoft SQL Server version 6.5 Service Pack 5a

SYMPTOMS

The ANSI_PADDING attribute of a CHAR column is ignored when you use a subquery in an INSERT statement. The subquery statement uses two tables to copy one table to another. For the source table, ANSI_PADDING is set OFF and for the destination table ANSI_PADDING is set ON. For an example of this behavior, see the MORE INFORMATION section of this article.

WORKAROUND

To work around this problem, use the CONVERT function in the subquery for the ANSI_PADDING attribute to take effect. An example of this would be:

insert into tabpo select convert( char(10), col01 ) from tabpf 

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5 Service Pack 5a.

MORE INFORMATION

The following script reproduces the problem:

set ansi_padding on
go
create table tabpo ( col01 char(10) null )
go
insert into tabpo values ( 'A' )
go

select len(col01), col01+'#' from tabpo
go

set ansi_padding off
go
create table tabpf ( col01 char(10) null )
go
insert into tabpf values ( 'A' )
go

select len(col01), col01+'#' from tabpf
go

insert into tabpo select col01 from tabpf
go

select len(col01), col01+'#' from tabpo
go 

(1 row(s) affected)

                            
----------- ----------- 
10          A         # 

(1 row(s) affected)


(1 row(s) affected)

                            
----------- ----------- 
1           A#          

(1 row(s) affected)


(1 row(s) affected)

                            
----------- ----------- 
10          A         # 
1           A#          

(2 row(s) affected) 

Additional query words: sp sp5a

Keywords : SSrvTran_SQL kbSQLServ650sp5bug kbbug6.50.sp5
Version : winnt:6.5 Service Pack 5a
Platform : winnt
Issue type : kbbug