Problem with ALTER COLUMN
Brian Donahue
Posts: 6,590 Bronze 1
Hi Yanick,
SQL Compare will only rebuild a table in certain circumstances. Usually
specifying a 'force column order to be identical' will do the trick, as
almost nobody ever has column orders that match! I suppose you could only
get away with that once, though...
Is rebuilding the table the only alternative? Seems a bit harsh.
Regards,
Brian Donahue
Red Gate Technical Support
"Yanick Mongeau" <ymongeau@chca.ca> wrote in message
news:hySFCqyREHA.3156@server53...
> Hi.
>
> When I alter an existing VARCHAR column in a table on a SQL Server 2000
SP3
> database, the script generated by SQL Compare is composed of the following
> line :
> "ALTER TABLE [dbo].[Test_Yanick] ALTER COLUMN [cTest_yanick] [varchar]
(100)
> COLLATE Latin1_General_CI_AI NULL".
>
> However, this way of changing the table structure causes a problem,
because
> the "TrimTrailingBlanks" property of the "cTest_yanick" field is set to NO
> even though I added the "SET ANSI_PADDING OFF" statement before the ALTER
> TABLE. Microsoft recorded this behavior in the KB article # 296559 and
wrote
> that this behavior is by design and the workaround is to re-create the
table
> and transfer the existing data.
>
> With my previous version of SQL Compare (version 1.6), the generated
script
> did the following in order to propagate this change :
> - created a temporary table with the new structure,
> - transferred the content of the existing table to the temporary
table
> - deleted the existing table
> - renamed the temporary table
> This was fine, because we didn't have the problem with the
> "TrimTrailingBlanks" set to NO.
> I then upgraded to version 3.16 of SQL Compare and now it generates an
ALTER
> TABLE.
>
> Is there an option I can set to force the table to be re-created or
anything
> else I can do ?
>
> Thanks for any help.
>
> Best regards,
>
> Yanick Mongeau
> Analyst
> CHCA Computer Systems Inc.
>
>
SQL Compare will only rebuild a table in certain circumstances. Usually
specifying a 'force column order to be identical' will do the trick, as
almost nobody ever has column orders that match! I suppose you could only
get away with that once, though...
Is rebuilding the table the only alternative? Seems a bit harsh.
Regards,
Brian Donahue
Red Gate Technical Support
"Yanick Mongeau" <ymongeau@chca.ca> wrote in message
news:hySFCqyREHA.3156@server53...
> Hi.
>
> When I alter an existing VARCHAR column in a table on a SQL Server 2000
SP3
> database, the script generated by SQL Compare is composed of the following
> line :
> "ALTER TABLE [dbo].[Test_Yanick] ALTER COLUMN [cTest_yanick] [varchar]
(100)
> COLLATE Latin1_General_CI_AI NULL".
>
> However, this way of changing the table structure causes a problem,
because
> the "TrimTrailingBlanks" property of the "cTest_yanick" field is set to NO
> even though I added the "SET ANSI_PADDING OFF" statement before the ALTER
> TABLE. Microsoft recorded this behavior in the KB article # 296559 and
wrote
> that this behavior is by design and the workaround is to re-create the
table
> and transfer the existing data.
>
> With my previous version of SQL Compare (version 1.6), the generated
script
> did the following in order to propagate this change :
> - created a temporary table with the new structure,
> - transferred the content of the existing table to the temporary
table
> - deleted the existing table
> - renamed the temporary table
> This was fine, because we didn't have the problem with the
> "TrimTrailingBlanks" set to NO.
> I then upgraded to version 3.16 of SQL Compare and now it generates an
ALTER
> TABLE.
>
> Is there an option I can set to force the table to be re-created or
anything
> else I can do ?
>
> Thanks for any help.
>
> Best regards,
>
> Yanick Mongeau
> Analyst
> CHCA Computer Systems Inc.
>
>
This discussion has been closed.
Comments