What are the challenges you face when working across database platforms? Take the survey

Problem with ALTER COLUMN

Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
edited November 25, 2005 11:32AM in SQL Compare Previous Versions
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.


Brian Donahue
Red Gate Technical Support

"Yanick Mongeau" <ymongeau@chca.ca> wrote in message
> Hi.
> When I alter an existing VARCHAR column in a table on a SQL Server 2000
> database, the script generated by SQL Compare is composed of the following
> line :
> "ALTER TABLE [dbo].[Test_Yanick] ALTER COLUMN [cTest_yanick] [varchar]
> COLLATE Latin1_General_CI_AI NULL".
> However, this way of changing the table structure causes a problem,
> 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
> that this behavior is by design and the workaround is to re-create the
> and transfer the existing data.
> With my previous version of SQL Compare (version 1.6), the generated
> 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
> - 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
> Is there an option I can set to force the table to be re-created or
> else I can do ?
> Thanks for any help.
> Best regards,
> Yanick Mongeau
> Analyst
> CHCA Computer Systems Inc.


This discussion has been closed.