Hi list,
I'm curious whty I can't change collation with Query Analyzer, but can do
that with Enterprise Manager. Below is what I did,

1. Create a table lockp in which only one column exists, and the comlumn is
set as primary key.
2. Open Query Analyzer, Login, and execute below line
ALTER TABLE LOCKP ALTER  COLUMN FLAG CHAR(1) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL;
go
I got an error,
Server: Msg 5074, Level 16, State 8, Line 1
The object 'SS_LOCKPROMOTION_PK' is dependent on column 'FLAG'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN FLAG failed because one or more objects access this
column.

I find related document as below in MSDN,
ALTER COLUMN cannot have a collation change if one or more of the following
conditions exist:

    * If a CHECK constraint, FOREIGN KEY constraint, or computed columns
reference the column changed.
    * If any index, statistics, or full-text index are created on the
column. Statistics created automatically on the column changed are dropped if
the column collation is changed.
    * If a schema-bound view or function references the column.

3. Open Enterprise Manager, login, navigate to the same table, change the
collation to SQL_Latin1_General_CP1_CI_AS in "design table" window. It works!!

Could anybody explain why I get different results with two tools? How can I
make the change with Query Analyzer? -- I need change it in a script.

Thanks a lot!

William

EM cannot do magic. EM uses a huge transaction to drop all old table
constraints, rename the old table, create a new table with changes you need,
copy the data fro old to new table, recreate the constraints, drop the old
table (ok, might be slightly different algorithm, but this is basically how
EM works). You can catch this with Profiler. If you catch with Profiler what
EM performs, you have your script as well.

Dejan Sarka (dejan_please_reply_to_newsgroups.sa***@avtenta.si) writes:
But watch out that there are several flaws with the script that EM produces.
For instance, it may not be the one big transaction it should be, but
it can be several transactions.

Rather than using Profiler, I think you can get EM to only generate script
for you, but I may be wrong on that point. (I know you can in SQL Server
Management Studio, and what is in Mgmt Studio is very similar to EM -
including all the bugs!)

IIRC in EM you can generate scripts only for existing objects, not for
actions you want to perform, like you can in SSMS. However, I am not 100%
sure anymore either:-)

Hello Dejan,

When making table changes you can generate a script in EM and SSMS


Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons


Show quoteHide quote

Wow! Then I am moving back to 2000. It is not just because of EM, I am going
to be 7 years younger as well:-)

Thanks, Simon!

SQL Server Management Studio - long names in tabs
Help with SQL 2005 and Sourcesafe 6
SSIS and polling
Little Lock Symbols
SQL - Fast LOCALHOST , Slow IP