"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

"Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
news:F8284F2D-0D4B-4910-8481-E594FEDB3F40@microsoft.com...
> 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

"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 new column with the value
> of the old column, drop the old column and rename the new column. Column order will not be
> preserved, of course.
>
> Another option is to create a new table. My guess is that this is what Enterprise 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 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" <AlejandroM***@discussions.microsoft.com> wrote in message
> news:F8284F2D-0D4B-4910-8481-E594FEDB3F40@microsoft.com...
> > 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
>
>