Tags: collation, column, columns, complate, database, hii, microsoft, mysql, oracle, server, sql, tables, text, thedatabase, type

change collation of a type of text column

On Database » Microsoft SQL Server

4,409 words with 3 Comments; publish: Tue, 06 May 2008 17:43:00 GMT; (250125.00, « »)

Hi!

I have to change a complate database collation. After I had changed the

database collation I go through the tables and its columns and make a DDL

command on collated columns :

"ALTER TABLE table ALTER COLUMN column text COLLATE Hungarian_CI_AS NULL"

It work on varchar and char columns but I receive an error message in case

of text columns :

"Server: Msg 4928, Level 16, State 1, Line 1

Cannot alter column 'Ic_DbFields' because it is 'text'."

I use OLEDB.

Enterprise Manager can change the collation on text columns.

What can I do?

I nedd to use SQL-DMO?

thanks for any help :

Imre

All Comments

Leave a comment...

  • 3 Comments
    • Check if you have a full-text index using that column. if so, then you have

      to remove it before changing the collation.

      AMB

      "Imre Ament" wrote:

      > Hi!

      > I have to change a complate database collation. After I had changed the

      > database collation I go through the tables and its columns and make a DDL

      > command on collated columns :

      > "ALTER TABLE table ALTER COLUMN column text COLLATE Hungarian_CI_AS NULL"

      > It work on varchar and char columns but I receive an error message in case

      > of text columns :

      > "Server: Msg 4928, Level 16, State 1, Line 1

      > Cannot alter column 'Ic_DbFields' because it is 'text'."

      > I use OLEDB.

      > Enterprise Manager can change the collation on text columns.

      > What can I do?

      > I nedd to use SQL-DMO?

      > thanks for any help :

      > Imre

      #1; Tue, 06 May 2008 17:45:00 GMT
    • I don't think you can alter collation for blob columns.

      One option can be to add a new column with desired collation, update the new

      column with the value

      of the old column, drop the old column and rename the new column. Column ord

      er will not be

      preserved, of course.

      Another option is to create a new table. My guess is that this is what Enter

      prise Manager does.

      Imre: pressing "save change script" will show you how EM does this. I doubt

      it is exposed in DMO, as

      the DaVinci tools (the diagramming etc tools) are not exposed in DMO. But yo

      u could have a look, of

      course.

      Tibor Karaszi, SQL Server MVP

      http://www.karaszi.com/sqlserver/default.asp

      http://www.solidqualitylearning.com/

      Blog: http://solidqualitylearning.com/blogs/tibor/

      "Alejandro Mesa" <AlejandroMesa.sqlserver.questionfor.info.discussions.microsoft.com> wrote in message

      news:F8284F2D-0D4B-4910-8481-E594FEDB3F40.sqlserver.questionfor.info.microsoft.com...

      > Check if you have a full-text index using that column. if so, then you hav

      e

      > to remove it before changing the collation.

      >

      > AMB

      > "Imre Ament" wrote:

      >

      #2; Tue, 06 May 2008 17:46:00 GMT
    • You are right. Thanks for the comment.

      AMB

      "Tibor Karaszi" wrote:

      > I don't think you can alter collation for blob columns.

      > One option can be to add a new column with desired collation, update the n

      ew column with the value

      > of the old column, drop the old column and rename the new column. Column o

      rder will not be

      > preserved, of course.

      > Another option is to create a new table. My guess is that this is what Ent

      erprise Manager does.

      > Imre: pressing "save change script" will show you how EM does this. I doub

      t it is exposed in DMO, as

      > the DaVinci tools (the diagramming etc tools) are not exposed in DMO. But

      you could have a look, of

      > course.

      > --

      > Tibor Karaszi, SQL Server MVP

      > http://www.karaszi.com/sqlserver/default.asp

      > http://www.solidqualitylearning.com/

      > Blog: http://solidqualitylearning.com/blogs/tibor/

      >

      > "Alejandro Mesa" <AlejandroMesa.sqlserver.questionfor.info.discussions.microsoft.com> wrote in messag

      e

      > news:F8284F2D-0D4B-4910-8481-E594FEDB3F40.sqlserver.questionfor.info.microsoft.com...

      >

      #3; Tue, 06 May 2008 17:47:00 GMT