Sql Case sensitivity: collation

Showing posts with label collation. Show all posts

Thursday, March 22, 2012

Change default server collation

Hi,
How do I change the default server collation of my SQL server ?
I would hate to do a complete reinstall :-)
Thanks in advance...
Kind regards
Soren
Default collation for what?
When you do CREATE DATABASE, default collation is the collation you have for the system databases.
This you picked when you installed SQL Server. You change it using rebuildm.exe (which scratches
everything in the system databases).
When you CREATE TABLE, default collation for the string columns is the default collation you have
for the database (see above). You can change default column for a database using ALTER Database,
which doesn't change collation for existing tables. For that you need to use ALTER TABLE ... ALTER
COLUMN.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How do I change the default server collation of my SQL server ?
> I would hate to do a complete reinstall :-)
>
> Thanks in advance...
> Kind regards
> Soren
>
|||> Default collation for what?
For my server (of course) !!
When first installing the server you are asked for a default collation which
is used when creating new databases (when you are not able to specify
perferred collation for the database you are creating) - say SAP Business
One creates it's own databases in the server, and the collation on each
alfanumeric field will be the default *server* collation, as its not
possible to specify the perferred collation for the database !!!!
I'll have a look at rebuildm.exe, thanks...
Soren
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
> Default collation for what?
> When you do CREATE DATABASE, default collation is the collation you have
> for the system databases. This you picked when you installed SQL Server.
> You change it using rebuildm.exe (which scratches everything in the system
> databases).
> When you CREATE TABLE, default collation for the string columns is the
> default collation you have for the database (see above). You can change
> default column for a database using ALTER Database, which doesn't change
> collation for existing tables. For that you need to use ALTER TABLE ...
> ALTER COLUMN.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
>
|||Hi
As Tibor pointed out you will be able to change a collation on database
level or even column level.
Please take a look at ALTER DATABASE ... and ALTER TABLE ...ALTER COLUMN
commands in the BOL.
"Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
news:%23U5kohIgFHA.3940@.tk2msftngp13.phx.gbl...
> For my server (of course) !!
> When first installing the server you are asked for a default collation
which[vbcol=seagreen]
> is used when creating new databases (when you are not able to specify
> perferred collation for the database you are creating) - say SAP Business
> One creates it's own databases in the server, and the collation on each
> alfanumeric field will be the default *server* collation, as its not
> possible to specify the perferred collation for the database !!!!
> I'll have a look at rebuildm.exe, thanks...
> Soren
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
> en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
system
>
|||Hi Uri,
Yes - I know I need to run these scripts to change collations on the
objects. But to change default collation for the server I need something
like the rebuildm.exe tool (as Tibor also pointed out)
My problem was not collation on my objects, but default collation for the
server!!
Soren
"Uri Dimant" <urid@.iscar.co.il> skrev i en meddelelse
news:OqcUqlIgFHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi
> As Tibor pointed out you will be able to change a collation on database
> level or even column level.
> Please take a look at ALTER DATABASE ... and ALTER TABLE ...ALTER COLUMN
> commands in the BOL.
>
>
> "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> news:%23U5kohIgFHA.3940@.tk2msftngp13.phx.gbl...
> which
> system
>
|||Hi
You can look it up in Books On Line. Search for Collation or look under
Installing SQL Sever ->Collation Options for International Support ->
Changing Collation Settings after setup.
Regards
Steen
Soeren S. Joergensen wrote:
> Hi,
> How do I change the default server collation of my SQL server ?
> I would hate to do a complete reinstall :-)
>
> Thanks in advance...
> Kind regards
> Soren

Change default server collation

Hi,
How do I change the default server collation of my SQL server '
I would hate to do a complete reinstall :-)
Thanks in advance...
Kind regards
SorenDefault collation for what?
When you do CREATE DATABASE, default collation is the collation you have for the system databases.
This you picked when you installed SQL Server. You change it using rebuildm.exe (which scratches
everything in the system databases).
When you CREATE TABLE, default collation for the string columns is the default collation you have
for the database (see above). You can change default column for a database using ALTER Database,
which doesn't change collation for existing tables. For that you need to use ALTER TABLE ... ALTER
COLUMN.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How do I change the default server collation of my SQL server '
> I would hate to do a complete reinstall :-)
>
> Thanks in advance...
> Kind regards
> Soren
>|||> Default collation for what?
For my server (of course) !!
When first installing the server you are asked for a default collation which
is used when creating new databases (when you are not able to specify
perferred collation for the database you are creating) - say SAP Business
One creates it's own databases in the server, and the collation on each
alfanumeric field will be the default *server* collation, as its not
possible to specify the perferred collation for the database !!!!
I'll have a look at rebuildm.exe, thanks...
Soren
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
> Default collation for what?
> When you do CREATE DATABASE, default collation is the collation you have
> for the system databases. This you picked when you installed SQL Server.
> You change it using rebuildm.exe (which scratches everything in the system
> databases).
> When you CREATE TABLE, default collation for the string columns is the
> default collation you have for the database (see above). You can change
> default column for a database using ALTER Database, which doesn't change
> collation for existing tables. For that you need to use ALTER TABLE ...
> ALTER COLUMN.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> How do I change the default server collation of my SQL server '
>> I would hate to do a complete reinstall :-)
>>
>> Thanks in advance...
>> Kind regards
>> Soren
>|||Hi
As Tibor pointed out you will be able to change a collation on database
level or even column level.
Please take a look at ALTER DATABASE ... and ALTER TABLE ...ALTER COLUMN
commands in the BOL.
"Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
news:%23U5kohIgFHA.3940@.tk2msftngp13.phx.gbl...
> > Default collation for what?
> For my server (of course) !!
> When first installing the server you are asked for a default collation
which
> is used when creating new databases (when you are not able to specify
> perferred collation for the database you are creating) - say SAP Business
> One creates it's own databases in the server, and the collation on each
> alfanumeric field will be the default *server* collation, as its not
> possible to specify the perferred collation for the database !!!!
> I'll have a look at rebuildm.exe, thanks...
> Soren
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
> en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
> > Default collation for what?
> >
> > When you do CREATE DATABASE, default collation is the collation you have
> > for the system databases. This you picked when you installed SQL Server.
> > You change it using rebuildm.exe (which scratches everything in the
system
> > databases).
> >
> > When you CREATE TABLE, default collation for the string columns is the
> > default collation you have for the database (see above). You can change
> > default column for a database using ALTER Database, which doesn't change
> > collation for existing tables. For that you need to use ALTER TABLE ...
> > ALTER COLUMN.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> > news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
> >> Hi,
> >>
> >> How do I change the default server collation of my SQL server '
> >>
> >> I would hate to do a complete reinstall :-)
> >>
> >>
> >> Thanks in advance...
> >>
> >> Kind regards
> >> Soren
> >>
> >
>|||Hi Uri,
Yes - I know I need to run these scripts to change collations on the
objects. But to change default collation for the server I need something
like the rebuildm.exe tool (as Tibor also pointed out)
My problem was not collation on my objects, but default collation for the
server!!
Soren
"Uri Dimant" <urid@.iscar.co.il> skrev i en meddelelse
news:OqcUqlIgFHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi
> As Tibor pointed out you will be able to change a collation on database
> level or even column level.
> Please take a look at ALTER DATABASE ... and ALTER TABLE ...ALTER COLUMN
> commands in the BOL.
>
>
> "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> news:%23U5kohIgFHA.3940@.tk2msftngp13.phx.gbl...
>> > Default collation for what?
>> For my server (of course) !!
>> When first installing the server you are asked for a default collation
> which
>> is used when creating new databases (when you are not able to specify
>> perferred collation for the database you are creating) - say SAP Business
>> One creates it's own databases in the server, and the collation on each
>> alfanumeric field will be the default *server* collation, as its not
>> possible to specify the perferred collation for the database !!!!
>> I'll have a look at rebuildm.exe, thanks...
>> Soren
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev
>> i
>> en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
>> > Default collation for what?
>> >
>> > When you do CREATE DATABASE, default collation is the collation you
>> > have
>> > for the system databases. This you picked when you installed SQL
>> > Server.
>> > You change it using rebuildm.exe (which scratches everything in the
> system
>> > databases).
>> >
>> > When you CREATE TABLE, default collation for the string columns is the
>> > default collation you have for the database (see above). You can change
>> > default column for a database using ALTER Database, which doesn't
>> > change
>> > collation for existing tables. For that you need to use ALTER TABLE ...
>> > ALTER COLUMN.
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> > Blog: http://solidqualitylearning.com/blogs/tibor/
>> >
>> >
>> > "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
>> > news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
>> >> Hi,
>> >>
>> >> How do I change the default server collation of my SQL server '
>> >>
>> >> I would hate to do a complete reinstall :-)
>> >>
>> >>
>> >> Thanks in advance...
>> >>
>> >> Kind regards
>> >> Soren
>> >>
>> >
>>
>|||Hi
You can look it up in Books On Line. Search for Collation or look under
Installing SQL Sever ->Collation Options for International Support ->
Changing Collation Settings after setup.
Regards
Steen
Soeren S. Joergensen wrote:
> Hi,
> How do I change the default server collation of my SQL server '
> I would hate to do a complete reinstall :-)
>
> Thanks in advance...
> Kind regards
> Sorensql

Change default server collation

change default collation for user database

Hi Folks,
Is there another way to change the default collation for a
user database without using the SQL cmd:
ALTER DATABASE myDatase COLLATE Latin1_General_CI_AS '
With the ALTER DATABASE it is not possible to change the
database collation whenever there are Check Constraint
which are depending on the database collation.
A go lead for me would also be if someone can tell me in
which master table this is recorded'
Cheers Jack.You have to drop the constraints, then change the database collation, and
recreate the constraints. Don't try to change anything in system table
directly, you can make your database completely unusable.
I have an (unsupported) script from Microsoft that you can use to recollate
a database. Drop me a mail if you want it.
--
Jacco Schalkwijk
SQL Server MVP
"Jack" <jbonapart@.dicon.nl> wrote in message
news:051301c3c2f8$46849d80$a301280a@.phx.gbl...
> Hi Folks,
> Is there another way to change the default collation for a
> user database without using the SQL cmd:
> ALTER DATABASE myDatase COLLATE Latin1_General_CI_AS '
> With the ALTER DATABASE it is not possible to change the
> database collation whenever there are Check Constraint
> which are depending on the database collation.
> A go lead for me would also be if someone can tell me in
> which master table this is recorded'
> Cheers Jack.|||Jacco,
What e-mail adres can I reach you ? I have tried
nospamjaccos@.eurostop.co.uk but i receive a Delivery
Status Notification Failure.
You can e-mail me the collate script to jbonapart@.dicon.nl
cheers.
Delivery to the following recipients failed.
>--Original Message--
>You have to drop the constraints, then change the
database collation, and
>recreate the constraints. Don't try to change anything in
system table
>directly, you can make your database completely unusable.
>I have an (unsupported) script from Microsoft that you
can use to recollate
>a database. Drop me a mail if you want it.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Jack" <jbonapart@.dicon.nl> wrote in message
>news:051301c3c2f8$46849d80$a301280a@.phx.gbl...
>> Hi Folks,
>> Is there another way to change the default collation
for a
>> user database without using the SQL cmd:
>> ALTER DATABASE myDatase COLLATE Latin1_General_CI_AS '
>> With the ALTER DATABASE it is not possible to change the
>> database collation whenever there are Check Constraint
>> which are depending on the database collation.
>> A go lead for me would also be if someone can tell me in
>> which master table this is recorded'
>> Cheers Jack.
>
>.
>|||sorry, i had put a wrong sender's E-mail adres.
>--Original Message--
>Jacco,
>What e-mail adres can I reach you ? I have tried
>nospamjaccos@.eurostop.co.uk but i receive a Delivery
>Status Notification Failure.
>You can e-mail me the collate script to jbonapart@.dicon.nl
>cheers.
>Delivery to the following recipients failed.
>
>>--Original Message--
>>You have to drop the constraints, then change the
>database collation, and
>>recreate the constraints. Don't try to change anything
in
>system table
>>directly, you can make your database completely unusable.
>>I have an (unsupported) script from Microsoft that you
>can use to recollate
>>a database. Drop me a mail if you want it.
>>--
>>Jacco Schalkwijk
>>SQL Server MVP
>>
>>"Jack" <jbonapart@.dicon.nl> wrote in message
>>news:051301c3c2f8$46849d80$a301280a@.phx.gbl...
>> Hi Folks,
>> Is there another way to change the default collation
>for a
>> user database without using the SQL cmd:
>> ALTER DATABASE myDatase COLLATE Latin1_General_CI_AS '
>> With the ALTER DATABASE it is not possible to change
the
>> database collation whenever there are Check Constraint
>> which are depending on the database collation.
>> A go lead for me would also be if someone can tell me
in
>> which master table this is recorded'
>> Cheers Jack.
>>
>>.
>.
>sql

Tuesday, March 20, 2012

Change database collation - quickly

There have been several threads about changing a database's collation but none have come up with an easy answer before.
The suggestion before was to create an empty database with the correct collation and then copy the data across.
However this is hard work as you have to populate tables in a specific order in order not to violate foreign keys etc. You can't just dts the whole data.

There follows scripts we have written to do the job. If people use them, please could you add to this thread whether they worked successfully or not.

Firstly we change the default collation, then change all the types in the database to match the new collation.

===================
--script to change database collation - James Agnini
--
--Replace <DATABASE> with the database name
--Replace <COLLATION> with the collation, eg SQL_Latin1_General_CP1_CI_AS
--
--After running this script, run the script to rebuild all indexes

ALTER DATABASE <DATABASE> COLLATE <COLLATION>

exec sp_configure 'allow updates',1
go
reconfigure with override
go
update syscolumns
set collationid = (select top 1 collationid from systypes where systypes.xtype=syscolumns.xtype)
where collationid <> (select top 1 collationid from systypes where systypes.xtype=syscolumns.xtype)
go
exec sp_configure 'allow updates',0
go
reconfigure with override
go
===================

As we have directly edited system tables, we need to run a script to rebuild all the indexes. Otherwise you will get strange results like comparing strings in different table not working.
The indexes have to actually be dropped and recreated in separate statements.
You can't use DBCC DBREINDEX or create index with the DROP_EXISTING option as they won't do anything(thanks to SQL Server "optimization").
This script loops through the tables and then loops through the indexes and unique constraints in separate sections. It gets the index information and drops and re-creates it.
(The script could probably be tidied up with the duplicate code put into a stored procedure).

====================
--Script to rebuild all table indexes, Version 0.1, May 2004 - James Agnini
--
--Database backups should be made before running any set of scripts that update databases.
--All users should be out of the database before running this script

print 'Rebuilding indexes for all tables:'
go

DECLARE @.Table_Name varchar(128)
declare @.Index_Name varchar(128)
declare @.IndexId int
declare @.IndexKey int

DECLARE Table_Cursor CURSOR FOR
select TABLE_NAME from INFORMATION_SCHEMA.tables where table_type != 'VIEW'

OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor
INTO @.Table_Name

--loop through tables
WHILE @.@.FETCH_STATUS = 0

BEGIN
print ''
print @.Table_Name

DECLARE Index_Cursor CURSOR FOR
select indid, name from sysindexes
where id = OBJECT_ID(@.Table_Name) and indid > 0 and indid < 255 and (status & 64)=0 and
not exists(Select top 1 NULL from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_NAME = @.Table_Name AND (CONSTRAINT_TYPE = 'PRIMARY KEY' or CONSTRAINT_TYPE = 'UNIQUE') and
CONSTRAINT_NAME = name)
order by indid

OPEN Index_Cursor
FETCH NEXT FROM Index_Cursor
INTO @.IndexId, @.Index_Name

--loop through indexes
WHILE @.@.FETCH_STATUS = 0
begin

declare @.SQL_String varchar(256)
set @.SQL_String = 'drop index '
set @.SQL_String = @.SQL_String + @.Table_Name + '.' + @.Index_Name

set @.SQL_String = @.SQL_String + ';create '

if( (select INDEXPROPERTY ( OBJECT_ID(@.Table_Name) , @.Index_Name , 'IsUnique')) =1)
set @.SQL_String = @.SQL_String + 'unique '

if( (select INDEXPROPERTY ( OBJECT_ID(@.Table_Name) , @.Index_Name , 'IsClustered')) =1)
set @.SQL_String = @.SQL_String + 'clustered '

set @.SQL_String = @.SQL_String + 'index '
set @.SQL_String = @.SQL_String + @.Index_Name
set @.SQL_String = @.SQL_String + ' on '
set @.SQL_String = @.SQL_String + @.Table_Name

set @.SQL_String = @.SQL_String + '('

--form column list
SET @.IndexKey = 1

-- Loop through index columns, INDEX_COL can be from 1 to 16.
WHILE @.IndexKey <= 16 and INDEX_COL(@.Table_Name, @.IndexId, @.IndexKey)
IS NOT NULL
BEGIN

IF @.IndexKey != 1
set @.SQL_String = @.SQL_String + ','

set @.SQL_String = @.SQL_String + index_col(@.Table_Name, @.IndexId, @.IndexKey)

SET @.IndexKey = @.IndexKey + 1
END

set @.SQL_String = @.SQL_String + ')'

print @.SQL_String
EXEC (@.SQL_String)

FETCH NEXT FROM Index_Cursor
INTO @.IndexId, @.Index_Name
end

CLOSE Index_Cursor
DEALLOCATE Index_Cursor

--loop through unique constraints
DECLARE Contraint_Cursor CURSOR FOR
select indid, name from sysindexes
where id = OBJECT_ID(@.Table_Name) and indid > 0 and indid < 255 and (status & 64)=0 and
exists( Select top 1 NULL from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_NAME = @.Table_Name AND CONSTRAINT_TYPE = 'UNIQUE' and CONSTRAINT_NAME = name)
order by indid

OPEN Contraint_Cursor
FETCH NEXT FROM Contraint_Cursor
INTO @.IndexId, @.Index_Name

--loop through indexes
WHILE @.@.FETCH_STATUS = 0
begin

set @.SQL_String = 'alter table '
set @.SQL_String = @.SQL_String + @.Table_Name
set @.SQL_String = @.SQL_String + ' drop constraint '
set @.SQL_String = @.SQL_String + @.Index_Name

set @.SQL_String = @.SQL_String + '; alter table '
set @.SQL_String = @.SQL_String + @.Table_Name
set @.SQL_String = @.SQL_String + ' WITH NOCHECK add constraint '
set @.SQL_String = @.SQL_String + @.Index_Name
set @.SQL_String = @.SQL_String + ' unique '

if( (select INDEXPROPERTY ( OBJECT_ID(@.Table_Name) , @.Index_Name , 'IsClustered')) =1)
set @.SQL_String = @.SQL_String + 'clustered '

set @.SQL_String = @.SQL_String + '('

--form column list
SET @.IndexKey = 1

-- Loop through index columns, INDEX_COL can be from 1 to 16.
WHILE @.IndexKey <= 16 and INDEX_COL(@.Table_Name, @.IndexId, @.IndexKey)
IS NOT NULL
BEGIN

IF @.IndexKey != 1
set @.SQL_String = @.SQL_String + ','

set @.SQL_String = @.SQL_String + index_col(@.Table_Name, @.IndexId, @.IndexKey)

SET @.IndexKey = @.IndexKey + 1
END

set @.SQL_String = @.SQL_String + ')'

print @.SQL_String
EXEC (@.SQL_String)

FETCH NEXT FROM Contraint_Cursor
INTO @.IndexId, @.Index_Name
end

CLOSE Contraint_Cursor
DEALLOCATE Contraint_Cursor

FETCH NEXT FROM Table_Cursor
INTO @.Table_Name
end

CLOSE Table_Cursor
DEALLOCATE Table_Cursor

print ''
print 'Finished, Please check output for errors.'
====================

Any comments are very welcome.The error "Cannot resolve collation conflict for equal to operation" may be caused by you doing a query between 2 databases with different collations.

It can also happen if your database has a different collation to the server and you are using system stored procedures or tempory tables.
See above for how to change your database's collation.

change database collation

Hello there
Is there a way to change database collation?Roy
Take a look at this script I found on an internet
Changing the collation of the existing tables is unfortunately not very
straightforward.
You might consider using SEM to script out the objects in the database,
change the
COLLATE clauses in the script to match the new collation, run this script in
a new
database with the new desired default collation, and finally use DTS to
transfer your
data to the new database.
However, if you wish to evaluate whether you can change the collation of
existing tables
in place, you might want to check out the helper procs in the attached
script. This
includes the following procs:
1. sp_check_for_common_collation__change_pr
oblems
- makes an assessment of how difficult it would be to change
collation in-place
2. sp_generate_index_constraint_s_cript_for
_collation_change
- outputs a CREATE INDEX/ALTER TABLE script to recreate indexes &
constraints
3. sp_generate_drop_cmds_for_coll_ation_cha
nge
- outputs ALTER TABLE DROP CONSTRAINT and DROP INDEX commands
4. sp_generate_alter_cmds_for_col_lation_ch
ange
- outputs ALTER TABLE commands to change the collation
You use #1 to see if this path is practical for you. If so you run #2, #3,
and #4.
It the obstacles reported by #1 are not insurmountable, you run the drop
index/constraint
script (output of #3), the alter collation script (output of #4), and
finally the create
index/constraint script (output of #2).
If you do attempt this be sure to back up the database first. This script
is not
extremely well tested and isn't an official part of SQL; it's just something
that might
help get you started. If your schema is simple enough it may work as-is
without much
additional work. For more complex schemas there is still a considerable
amount of manual
work involved with doing a collation change in-place.
--_-
The T-SQL command ALTER TABLE...ALTER COLUMN provides a way to change a
column's
collation without exporting and re-importing the data. However, there are a
number
of things that will prevent a column's collation from being altered,
including:
- The column may not be used in any indexes or statistics.
- No check/foreign key/primary key constraints may reference the column.
- The column may not use the text or ntext datatype.
- The column may not be a computed column or be referenced by a computed
column.
- The column may not be replicated.
- No full-text index may exist on the column.
- No SCHEMABOUND views or functions may reference the column.
This script is intended to make it somewhat easier to change a database's
collation
without transferring all data to a new database.
CAUTION: This script relies on ALTER TABLE...ALTER COLUMN commands to change
the
collation of existing columns in the database. If the original and new
collations
are based on different code pages, and if you are not using Unicode data
types, SQL
Server will perform code page translation from the original collation's code
page to
the new code page as part of the ALTER COLUMN command. This is desirable in
most cases,
but it assumes that the data was stored in the correct code page to begin
with. If you
have been storing, for example, code page 850 data in a code page 1252
database, SQL
Server will interpret the data using the column's cp850-based collation and
this may
cause data loss. In general, if you are storing code page X data in a code
page Y
column, you should use a transfer-based approach to move the data to a
schema that uses
the correct collation.
How to Perform an In-Place Collation Change of a User Database:
1. Run this script, which will create the following four procedures in your
master
database:
sp_check_for_common_collation__change_pr
oblems
sp_generate_index_constraint_s_cript_for
_collation_change
sp_generate_drop_cmds_for_coll_ation_cha
nge
sp_generate_alter_cmds_for_col_lation_ch
ange
2. Change to the database with the collation that is to be modified, and run
the proc
sp_check_for_common_collation__change_pr
oblems. Read the output carefully to
evaluate
what potential problems you might encounter. Some schema elements will
require you to
make manual changes to the database before you can change a column's
collation. Other
schema elements (e.g. text/ntext columns) simply cannot have their collation
changed
in-place.
USE Northwind
EXEC sp_check_for_common_collation__change_pr
oblems
3. Another method for changing the collation of a database is to transfer
everything
from the source database to a new database that uses the desired destination
collation.
Based on the output of sp_check_for_common_collation__change_pr
oblems,
evaluate whether
it would be easier to use this approach or the transfer approach. If you
are able to
work around the potential pitfalls of an in-place collation change, continue
with the
next step after backing up your database and ensuring that you can restore
it if
necessary.
4. Run a command like the following to script out indexes and contraints
that would
prevent an in-place collation change. Pass the proc the full path to the
.SQL script
file you want the proc to create.
USE Northwind
EXEC sp_generate_index_constraint_s_cript_for
_collation_change
@.scriptfilename = 'C:\IDX_CREATE.SQL'
5. Run the command below to geneate T-SQL commands to drop indexes and
contraints
(these objects will prevent an in-place collation change). Save the output
as
a SQL script, for example 'C:\IDX_DROP.SQL'.
USE Northwind
EXEC sp_generate_drop_cmds_for_coll_ation_cha
nge
6. Run the command below to generate ALTER TABLE/ALTER COLUMN commands that
will
change the collation of all char/varchar/nchar/nvarchar columns in the
database.
Specify the desired new collation name via the @.newcollationname parameter.
Save
the output as a SQL script, for example, 'C:\ALTER_COLUMN.SQL'.
USE Northwind
EXEC sp_generate_alter_cmds_for_col_lation_ch
ange
@.newcollationname = 'SQL_Latin1_General_CP437_CI_A_S'
7. Address any problems identified by
sp_check_for_common_collation__change_pr
oblems.
For example, if the proc identified any schemabound views or functions you
must drop
these before continuing to the next step.
8. Use the scripts generated in steps 4, 5, and 6 to change the default
database
collation and the collation of any varchar/char/nvarchar/nchar columns in
the database:
- Run 'C:\IDX_DROP.SQL' to drop all indexes and constraints.
- Run 'C:\ALTER_COLUMN.SQL' to change column collations.
- Run 'C:\IDX_CREATE.SQL' to recreate indexes and constraints.
9. If your database contained schema elements that you manually dropped in
step 7,
recreate them.
*/
-- proc params and views in syscolumns still retain the old collation --
effect?
USE master
GO
IF OBJECT_ID ('sp_check_for_common_collatio_n_change_
problems') IS NOT NULL
DROP PROC sp_check_for_common_collation__change_pr
oblems
GO
CREATE PROCEDURE sp_check_for_common_collation__change_pr
oblems AS
DECLARE @.bProblemsFound int -- BOOL. 0=false, 1=true
SET @.bProblemsFound = 0
SET NOCOUNT ON
IF (DB_ID() < 5 OR DB_NAME() IN ('master', 'tempdb', 'msdb', 'distribution',
'model'))
BEGIN
RAISERROR ('The ALTER TABLE approach to changing collation is not valid
for system
databases. Search for REBUILDM in Books Online or run this procedure from
a user database.',16,1)
RETURN -1
END
PRINT 'Checking database "' + DB_NAME()
+ '" for objects that could prevent a collation change...'
PRINT ''
-- Check for text/ntext columns in user tables and warn user.
IF EXISTS (SELECT *
FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
WHERE c.xtype IN (99, 35) AND o.type = 'U' AND OBJECTPROPERTY (o.id,
'IsMSShipped')=0)
BEGIN
SET @.bProblemsFound = 1
PRINT '**** WARNING: TEXT OR NTEXT COLUMNS FOUND ****'
PRINT 'You have one or more text or ntext columns on user tables. The
collation '
PRINT 'of text and ntext columns cannot be modified in-place with ALTER
TABLE. '
PRINT 'These columns WILL BE SKIPPED by this script, and their collation
will '
PRINT 'not be changed. If you wish to change the collation of text/ntext
columns '
PRINT 'you must export the data, drop and add the column with the new
collation '
PRINT 'specified, and then import the data. text/ntext columns are listed
below: '
PRINT ''
SELECT LEFT (o.name, 60) AS [Table Name], LEFT (c.name, 60) AS [Column
Name]
FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
WHERE c.xtype IN (99, 35) AND o.type = 'U' AND OBJECTPROPERTY (o.id,
'IsMSShipped')=0
PRINT ''
PRINT ''
END
-- Check for computed columns in user tables and warn user.
IF EXISTS (SELECT * FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
WHERE c.iscomputed = 1 AND o.type = 'U' AND OBJECTPROPERTY (o.id,
'IsMSShipped')=0)
BEGIN
SET @.bProblemsFound = 1
PRINT '**** WARNING: COMPUTED COLUMNS FOUND ****'
PRINT 'You have one or more computed columns defined on user tables. If
any '
PRINT 'of these columns reference varchar/char/nvarchar/ntext columns it
will '
PRINT 'prevent the referenced column''s collation from being changed. Any
of '
PRINT 'these columns that reference character columns must be dropped
before '
PRINT 'this script will run without errors. '
PRINT ''
PRINT 'This script does NOT preserve the computed columns'' definitions,
so be '
PRINT 'sure to script out the definition of these columns *before* running
the '
PRINT 'ALTER TABLE DROP COLUMN commands below. You will need to manually
add '
PRINT 'any columns that you dropped back to the tables after changing the
'
PRINT 'collation of the referenced column. This may modify the computed
columns'''
PRINT 'ordinal position in the table. If any indexes reference a computed
'
PRINT 'column you must manually drop that index before you can drop the
computed '
PRINT 'column. '
PRINT ''
SET NOCOUNT ON
SELECT
'-- ALTER TABLE [' + o.name + '] DROP COLUMN [' + c.name + ']' AS [Drop
Command],
'-- ' + t.text AS [Definition (May be Truncated)]
FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
INNER JOIN syscomments t ON c.id = t.id AND c.colid = t.number
WHERE c.iscomputed = 1 AND o.type = 'U' AND OBJECTPROPERTY (o.id,
'IsMSShipped')=0
PRINT ''
PRINT ''
END
-- Check to see if database is involved in replication.
DECLARE @.replfound int
SET @.replfound = 0
-- merge publisher
IF DATABASEPROPERTY (DB_NAME(), 'IsMergePublished') = 1 SET @.replfound = 1
-- tran subscriber
IF DATABASEPROPERTY (DB_NAME(), 'IsSubscribed') = 1 SET @.replfound = 1
-- transactional publisher
IF OBJECT_ID ('sysarticles') IS NOT NULL SET @.replfound = 1
-- transactional subscriber
IF OBJECT_ID ('MSreplication_subscriptions'_) IS NOT NULL SET @.replfound = 1
IF @.replfound = 1
BEGIN
SET @.bProblemsFound = 1
PRINT '**** WARNING: DATABASE INVOLVED IN REPLICATION ****'
PRINT 'This database appears to be involved in replication. A replicated
column '
PRINT 'may not have its collation changed. You must drop replication
before '
PRINT 'running the script and redefine articles afterward. This script
WILL NOT '
PRINT 'script out publication properties for you. '
PRINT ''
PRINT ''
END
-- Check to see if database may have full text catalogs defined.
IF DATABASEPROPERTY (DB_NAME(), 'IsFulltextEnabled') = 1
BEGIN
SET @.bProblemsFound = 1
PRINT '**** WARNING: DATABASE FULLTEXT ENABLED ****'
PRINT 'Full text catalogs must be dropped before the collation of a column
used '
PRINT 'in a fulltext index may be changed. You can script out the
definition of a '
PRINT 'fulltext catalog in Enterprise Manager, and recreate it after the
columns'' '
PRINT 'collations have been changed. The catalog will require a full
population '
PRINT 'after it is recreated, and this may take a while. This script WILL
NOT '
PRINT 'script out your catalog properties for you.'
PRINT ''
PRINT ''
END
-- Check for schemabinding.
IF EXISTS (
SELECT *
FROM sysdepends d
INNER JOIN sysobjects o1 on d.id = o1.id
INNER JOIN sysobjects o2 on d.depid = o2.id
WHERE
(OBJECTPROPERTY (d.id, 'IsMSShipped')=0 OR OBJECTPROPERTY (d.depid,
'IsMSShipped')=0)
AND deptype > 0 -- an enforced dependency
-- Eliminate computed columns which show up as tables dependent on
themselves.
-- These are reported separately.
AND (o1.type <> 'U' OR o2.type <> 'U')
-- Eliminate CHECK constraints. These are handled separately.
AND o1.type <> 'C'
)
BEGIN
SET @.bProblemsFound = 1
PRINT '**** WARNING: POSSIBLE SCHEMABINDING DETECTED ****'
PRINT 'Views and User Defined Functions may be created WITH SCHEMABINDING,
which '
PRINT 'means that they must disallow any schema changes on the objects
they are '
PRINT 'dependent on. Before attempting to change a column''s collation
you must '
PRINT 'drop any views or UDFs that are schemabound to the column''s table.
This '
PRINT 'script WILL NOT script out these objects for you, so be sure to
script out '
PRINT 'their definition before dropping them. The objects that are schema
bound '
PRINT 'to another object are listed below: '
PRINT ''
SELECT DISTINCT
LEFT (OBJECT_NAME (d.id), 50) AS [Dependent Object],
CASE o1.type WHEN 'V' THEN 'VIEW' WHEN 'FN' THEN 'FUNCTION'
ELSE o1.type END AS [Dependent Obj Type],
LEFT (OBJECT_NAME (depid), 50) AS [Referenced Object],
CASE o2.type WHEN 'V' THEN 'VIEW' WHEN 'FN' THEN 'FUNCTION'
WHEN 'U' THEN 'USER TABLE'
ELSE o2.type END AS [Referenced Object Type]
FROM sysdepends d
INNER JOIN sysobjects o1 on d.id = o1.id
INNER JOIN sysobjects o2 on d.depid = o2.id
WHERE (OBJECTPROPERTY(d.id,'IsMSShip_ped')=0 OR
OBJECTPROPERTY(d.depid,'IsMSSh_ipped')=0)
AND deptype > 0 -- an enforced dependency
-- Eliminate computed columns which show up as tables dependent on
themselves.
-- These are reported separately.
AND (o1.type <> 'U' OR o2.type <> 'U')
-- Eliminate CHECK constraints. These are handled separately.
AND o1.type <> 'C'
PRINT ''
PRINT ''
END
IF @.bProblemsFound = 1
BEGIN
PRINT 'One or more potential problems were identified. These issues must
be '
PRINT 'manually addressed before performing an in-place collation change.
'
PRINT ''
PRINT ''
END
ELSE
BEGIN
PRINT 'No blocking issues were identified that would prevent an in-place
collation '
PRINT 'change. '
PRINT ''
PRINT ''
END
-- Report on constraints and indexes that will need to be dropped.
IF EXISTS (SELECT *
FROM sysobjects
WHERE OBJECTPROPERTY (id, 'IsMSShipped') = 0
AND xtype IN ('F','D','C','UQ','PK'))
OR EXISTS (SELECT *
FROM sysindexes i
WHERE indid BETWEEN 1 AND 254 AND OBJECTPROPERTY (id, 'IsMSShipped') = 0
AND NOT INDEXPROPERTY (id, name, 'IsAutoStatistics') = 1
AND NOT INDEXPROPERTY (id, name, 'IsHypothetical') = 1
-- not part of a UNIQUE or PK constraint
AND NOT EXISTS (SELECT * FROM sysobjects o WHERE o.xtype IN ('PK', 'UQ')
AND i.id = o.parent_obj AND o.name = i.name))
BEGIN
DECLARE @.objcount int
SELECT @.objcount = COUNT (*) FROM sysobjects
WHERE OBJECTPROPERTY (id, 'IsMSShipped') = 0 AND xtype IN
('F','D','C','UQ','PK')
PRINT '**** SUMMARY OF INDEXES & CONSTRAINTS ****'
PRINT 'Constraint count (UNIQUE, FK/PK, CHECK, DEFAULT): '
+ CONVERT (varchar(20), @.objcount)
SELECT @.objcount = COUNT (*) FROM sysindexes i
WHERE indid BETWEEN 1 AND 254 AND OBJECTPROPERTY (id, 'IsMSShipped') = 0
AND NOT INDEXPROPERTY (id, name, 'IsAutoStatistics') = 1
AND NOT INDEXPROPERTY (id, name, 'IsHypothetical') = 1
AND NOT EXISTS (SELECT * FROM sysobjects o WHERE o.xtype IN ('PK', 'UQ')
AND i.id = o.parent_obj AND o.name = i.name)
PRINT 'Index count: ' + CONVERT (varchar(20), @.objcount)
PRINT 'Before a column''s collation may be modified, any indexes or
constraints on '
PRINT 'that column must be dropped. The following two stored procedures
may help by '
PRINT 'creating a script that will drop indexes/constraints, and another
script to '
PRINT 'recreate them after the collation is changed: '
PRINT ' sp_generate_index_constraint_s_cript_for
_collation_change'
PRINT ' sp_generate_drop_cmds_for_coll_ation_cha
nge '
PRINT ''
PRINT ''
END
GO
IF OBJECT_ID ('sp_generate_index_constraint__script_f
or_collation_change')
IS
NOT NULL
DROP PROC sp_generate_index_constraint_s_cript_for
_collation_change
GO
CREATE PROC sp_generate_index_constraint_s_cript_for
_collation_change
-- Output file to create (default:
'C:\<DBNAME>_IDX_CONSTRAINT_TR_ANSFER_SCRIPT.SQL')
@.scriptfilename sysname=NULL,
@.trustedconnection bit=1, -- Use a trusted connection to connect to the
server
@.username sysname='sa', -- Name of the user to connect as (defaults to
'sa')
@.password sysname=NULL, -- User's password
@.servername sysname=NULL -- Only used to handle incorrect @.@.SERVERNAME
AS
-- SQLDMO_SCRIPT_TYPE vars
DECLARE @.SQLDMOScript_Default int
DECLARE @.SQLDMOScript_Drops int
DECLARE @.SQLDMOScript_ObjectPermission_s int
DECLARE @.SQLDMOScript_PrimaryObject int
DECLARE @.SQLDMOScript_ClusteredIndexes int
DECLARE @.SQLDMOScript_Triggers int
DECLARE @.SQLDMOScript_DatabasePermissi_ons int
DECLARE @.SQLDMOScript_Permissions int
DECLARE @.SQLDMOScript_ToFileOnly int
DECLARE @.SQLDMOScript_Bindings int
DECLARE @.SQLDMOScript_AppendToFile int
DECLARE @.SQLDMOScript_NoDRI int
DECLARE @.SQLDMOScript_UDDTsToBaseType int
DECLARE @.SQLDMOScript_IncludeIfNotExis_ts int
DECLARE @.SQLDMOScript_NonClusteredInde_xes int
DECLARE @.SQLDMOScript_Indexes int
DECLARE @.SQLDMOScript_Aliases int
DECLARE @.SQLDMOScript_NoCommandTerm int
DECLARE @.SQLDMOScript_DRIIndexes int
DECLARE @.SQLDMOScript_IncludeHeaders int
DECLARE @.SQLDMOScript_OwnerQualify int
DECLARE @.SQLDMOScript_TimestampToBinar_y int
DECLARE @.SQLDMOScript_SortedData int
DECLARE @.SQLDMOScript_SortedDataReorg int
DECLARE @.SQLDMOScript_TransferDefault int
DECLARE @.SQLDMOScript_DRI_NonClustered int
DECLARE @.SQLDMOScript_DRI_Clustered int
DECLARE @.SQLDMOScript_DRI_Checks int
DECLARE @.SQLDMOScript_DRI_Defaults int
DECLARE @.SQLDMOScript_DRI_UniqueKeys int
DECLARE @.SQLDMOScript_DRI_ForeignKeys int
DECLARE @.SQLDMOScript_DRI_PrimaryKey int
DECLARE @.SQLDMOScript_DRI_AllKeys int
DECLARE @.SQLDMOScript_DRI_AllConstrain_ts int
DECLARE @.SQLDMOScript_DRI_All int
DECLARE @.SQLDMOScript_DRIWithNoCheck int
DECLARE @.SQLDMOScript_NoIdentity int
DECLARE @.SQLDMOScript_UseQuotedIdentif_iers int
-- SQLDMO_SCRIPT2_TYPE vars
DECLARE @.SQLDMOScript2_Default int
DECLARE @.SQLDMOScript2_AnsiPadding int
DECLARE @.SQLDMOScript2_AnsiFile int
DECLARE @.SQLDMOScript2_UnicodeFile int
DECLARE @.SQLDMOScript2_NonStop int
DECLARE @.SQLDMOScript2_NoFG int
DECLARE @.SQLDMOScript2_MarkTriggers int
DECLARE @.SQLDMOScript2_OnlyUserTrigger_s int
DECLARE @.SQLDMOScript2_EncryptPWD int
DECLARE @.SQLDMOScript2_SeparateXPs int
-- SQLDMO_SCRIPT_TYPE values
SET @.SQLDMOScript_Default = 4
SET @.SQLDMOScript_Drops = 1
SET @.SQLDMOScript_ObjectPermission_s = 2
SET @.SQLDMOScript_PrimaryObject = 4
SET @.SQLDMOScript_ClusteredIndexes = 8
SET @.SQLDMOScript_Triggers = 16
SET @.SQLDMOScript_DatabasePermissi_ons = 32
SET @.SQLDMOScript_Permissions = 34
SET @.SQLDMOScript_ToFileOnly = 64
SET @.SQLDMOScript_Bindings = 128
SET @.SQLDMOScript_AppendToFile = 256
SET @.SQLDMOScript_NoDRI = 512
SET @.SQLDMOScript_UDDTsToBaseType = 1024
SET @.SQLDMOScript_IncludeIfNotExis_ts = 4096
SET @.SQLDMOScript_NonClusteredInde_xes = 8192
SET @.SQLDMOScript_Indexes = 73736
SET @.SQLDMOScript_Aliases = 16384
SET @.SQLDMOScript_NoCommandTerm = 32768
SET @.SQLDMOScript_DRIIndexes = 65536
SET @.SQLDMOScript_IncludeHeaders = 131072
SET @.SQLDMOScript_OwnerQualify = 262144
SET @.SQLDMOScript_TimestampToBinar_y = 524288
SET @.SQLDMOScript_SortedData = 1048576
SET @.SQLDMOScript_SortedDataReorg = 2097152
SET @.SQLDMOScript_TransferDefault = 422143
SET @.SQLDMOScript_DRI_NonClustered = 4194304
SET @.SQLDMOScript_DRI_Clustered = 8388608
SET @.SQLDMOScript_DRI_Checks = 16777216
SET @.SQLDMOScript_DRI_Defaults = 33554432
SET @.SQLDMOScript_DRI_UniqueKeys = 67108864
SET @.SQLDMOScript_DRI_ForeignKeys = 134217728
SET @.SQLDMOScript_DRI_PrimaryKey = 268435456
SET @.SQLDMOScript_DRI_AllKeys = 469762048
SET @.SQLDMOScript_DRI_AllConstrain_ts = 520093696
SET @.SQLDMOScript_DRI_All = 532676608
SET @.SQLDMOScript_DRIWithNoCheck = 536870912
SET @.SQLDMOScript_NoIdentity = 1073741824
SET @.SQLDMOScript_UseQuotedIdentif_iers = -1
-- SQLDMO_SCRIPT2_TYPE values
SET @.SQLDMOScript2_Default = 0
SET @.SQLDMOScript2_AnsiPadding = 1
SET @.SQLDMOScript2_AnsiFile = 2
SET @.SQLDMOScript2_UnicodeFile = 4
SET @.SQLDMOScript2_NonStop = 8
SET @.SQLDMOScript2_NoFG = 16
SET @.SQLDMOScript2_MarkTriggers = 32
SET @.SQLDMOScript2_OnlyUserTrigger_s = 64
SET @.SQLDMOScript2_EncryptPWD = 128
SET @.SQLDMOScript2_SeparateXPs = 256
DECLARE @.dbname sysname,
@.srvobject int, -- SQL Server object
@.object int, -- Work variable for accessing COM objects
@.hr int, -- Contains HRESULT returned by COM
@.tfobject int, -- Stores pointer to Transfer object
@.scriptoptions int, -- Options bitmask for Transfer
@.res int
SET @.res=0
IF (DB_ID() < 5 OR DB_NAME() IN
('master','tempdb','msdb','dis_tribution
','model'))
BEGIN
RAISERROR ('The ALTER TABLE approach to changing collation is not valid
for system
databases. Search for REBUILDM in Books Online or run this procedure from
a user database.',16,1)
RETURN -1
END
SET @.dbname=DB_NAME()
IF (@.scriptfilename IS NULL)
SET @.scriptfilename = 'C:' + DB_NAME() + '_INDEX_CONSTRAINT_SCRIPT.SQL'
RAISERROR ('', 0, 1) WITH NOWAIT
RAISERROR ('-- Starting
sp_generate_index_constraint_s_cript_for
_collation_change',
0, 1) WITH NOWAIT
RAISERROR ('-- Current (source) Database: %s', 0, 1, @.dbname) WITH
NOWAIT
RAISERROR ('', 0, 1) WITH NOWAIT
-- @.servername is NULL by default
IF @.servername IS NULL
SET @.servername = @.@.SERVERNAME
ELSE
RAISERROR ('-- Override of local @.@.SERVERNAME with @.servername (sp
param).
-- Using local server name: %s', 0, 1, @.servername) WITH NOWAIT
RAISERROR ('-- Script w/cmds to recreate indexes/constraints will be
saved to: %s',
0, 1, @.scriptfilename) WITH NOWAIT
RAISERROR ('-- This may run for a while if there are many tables in the
database.',
0, 1) WITH NOWAIT
RAISERROR ('', 0, 1) WITH NOWAIT
-- Create a SQLServer object
EXEC @.hr=sp_OACreate 'SQLDMO.SQLServer', @.srvobject OUTPUT
IF (@.hr <> 0) BEGIN
RAISERROR ('Error creating SQLDMO.SQLServer object (%d).', 15, 1, @.hr)
EXEC sp_displayoaerrorinfo @.srvobject, @.hr
RETURN -1
END
-- Connect to the server
IF (@.trustedconnection=1) BEGIN
EXEC @.hr = sp_OASetProperty @.srvobject, 'LoginSecure', 1
IF (@.hr <> 0) BEGIN
RAISERROR ('Error setting SQLServer.LoginSecure property (%d).', 15,
1, @.hr)
EXEC sp_displayoaerrorinfo @.srvobject, @.hr
GOTO ServerError
END
EXEC @.hr = sp_OAMethod @.srvobject, 'Connect', NULL, @.servername
END
ELSE BEGIN
IF (@.password IS NOT NULL)
BEGIN
EXEC @.hr = sp_OAMethod @.srvobject, 'Connect', NULL, @.servername,
@.username, @.password
END
ELSE BEGIN
EXEC @.hr = sp_OAMethod @.srvobject, 'Connect', NULL, @.servername,
@.username
END
END
IF (@.hr <> 0) BEGIN
PRINT 'Failed to connect to server ''' + @.servername
+ '''. If this is not the name of the local SQL Server instance, '
PRINT 'pass in the optional @.servername parameter to provide the correct
local '
+ 'server name as follows: '
PRINT ''
PRINT ' EXEC
sp_generate_index_constraint_s_cript_for
_collation_change '
+ '@.scriptfilename=''...'', '''
+ DB_NAME() + ''', @.servername = ''<correct_SQL_Server_name>'' '
PRINT ''
PRINT 'Or, use sp_dropserver and sp_addserver w/the ''LOCAL'' parameter
to '
+ 'correct the local server name.'
PRINT ''
RAISERROR ('Error calling SQLServer.Connect method (%d).', 15, 1, @.hr)
EXEC sp_displayoaerrorinfo @.srvobject, @.hr
GOTO ServerError
END
-- Create a Transfer object
EXEC @.hr=sp_OACreate 'SQLDMO.Transfer', @.tfobject OUTPUT
IF (@.hr <> 0) BEGIN
RAISERROR ('Error creating SQLServer.Transfer object (%d).', 15, 1, @.hr)
EXEC sp_displayoaerrorinfo @.tfobject, @.hr
GOTO FreeSrv
END
-- Set Transfer's CopyData property
EXEC @.hr = sp_OASetProperty @.tfobject, 'CopyData', 0
IF (@.hr <> 0) BEGIN
RAISERROR ('Error setting Transfer.CopyData property (%d).', 15, 1, @.hr)
EXEC sp_displayoaerrorinfo @.tfobject, @.hr
GOTO FreeAll
END
-- Tell Transfer to copy the schema
EXEC @.hr = sp_OASetProperty @.tfobject, 'CopySchema', 1
IF (@.hr <> 0) BEGIN
RAISERROR ('Error setting Transfer.CopySchema property (%d).', 15, 1,
@.hr)
EXEC sp_displayoaerrorinfo @.tfobject, @.hr
GOTO FreeAll
END
-- Tell Transfer not to copy all objects -- we only want constraints,
indexes, etc.
EXEC @.hr = sp_OASetProperty @.tfobject, 'CopyAllObjects', 0
IF (@.hr <> 0) BEGIN
RAISERROR ('Error setting Transfer.CopyAllObjects property (%d).', 15,
1, @.hr)
EXEC sp_displayoaerrorinfo @.tfobject, @.hr
GOTO FreeAll
END
-- Must tell Transfer to consider all tables even though we aren't scripting
the base
-- table itself.
EXEC @.hr = sp_OASetProperty @.tfobject, 'CopyAllTables', 1
IF (@.hr <> 0) BEGIN
RAISERROR ('Error setting Transfer.IncludeGroups property (%d).', 15, 1,
@.hr)
EXEC sp_displayoaerrorinfo @.tfobject, @.hr
GOTO FreeAll
END
-- Include object dependencies or script won't create parent table PKs
before trying
-- to create child FKs.
EXEC @.hr = sp_OASetProperty @.tfobject, 'IncludeDependencies', 1
IF (@.hr <> 0) BEGIN
RAISERROR ('Error setting Transfer.IncludeDependencies property (%d).',
15, 1, @.hr)
EXEC sp_displayoaerrorinfo @.tfobject, @.hr
GOTO FreeAll
END
IF (@.scriptoptions IS NULL)
BEGIN
SET @.scriptoptions=@.SQLDMOScript_O_wnerQuali
fy | @.SQLDMOScript_Default
| @.SQLDMOScript_Bindings | @.SQLDMOScript_DRI_All |
@.SQLDMOScript_Indexes
-- Don't script table creates, just indexes and constraints.
SET @.scriptoptions=@.scriptoptions ^ @.SQLDMOScript_PrimaryObject
END
-- Set Transfer's ScriptType property
EXEC @.hr = sp_OASetProperty @.tfobject, 'ScriptType', @.scriptoptions
IF (@.hr <> 0) BEGIN
RAISERROR ('Error setting Transfer.ScriptType property (%d).', 15, 1,
@.hr)
EXEC sp_displayoaerrorinfo @.tfobject, @.hr
GOTO FreeAll
END
-- Get a pointer to the SQLServer object's Databases collection
EXEC @.hr = sp_OAGetProperty @.srvobject, 'Databases', @.object OUT
IF @.hr <> 0 BEGIN
RAISERROR ('Error getting SQLServer.Databases property (%d).', 15, 1,
@.hr)
EXEC sp_displayoaerrorinfo @.srvobject, @.hr
GOTO FreeAll
END
-- Get a pointer from the Databases collection for the current database (the
one
-- we are going to script indexes for).
EXEC @.hr = sp_OAMethod @.object, 'Item', @.object OUT, @.dbname
IF @.hr <> 0 BEGIN
RAISERROR ('Error getting Databases.Item property (%d).', 15, 1, @.hr)
EXEC sp_displayoaerrorinfo @.object, @.hr
GOTO FreeAll
END
-- Call the Database object's Transfer method to create a script file with
the object
-- definitions.
EXEC @.hr = sp_OAMethod @.object, 'ScriptTransfer', NULL, @.tfobject, 2,
@.scriptfilename
IF @.hr <> 0 BEGIN
RAISERROR ('Error calling Database.ScriptTransfer property (%d).', 15,
1, @.hr)
EXEC sp_displayoaerrorinfo @.object, @.hr
GOTO FreeAll
END
PRINT ''
PRINT 'SQL script with commands to recreate indexes and constraints saved
to: '
PRINT ' ' + @.scriptfilename
PRINT ''
GOTO FreeAll
ServerError:
SET @.res=-1
RAISERROR ('Error transferring schema.', 15, 1)
FreeAll:
EXEC sp_OADestroy @.tfobject
FreeSrv:
EXEC sp_OADestroy @.srvobject
RETURN @.res
GO
IF OBJECT_ID ('sp_generate_drop_cmds_for_co_llation_c
hange') IS NOT NULL
DROP PROC sp_generate_drop_cmds_for_coll_ation_cha
nge
GO
CREATE PROCEDURE sp_generate_drop_cmds_for_coll_ation_cha
nge AS
/*
This generates DROP commands for all indexes, constraints, and user-created
statistics in
the current database. In theory only a subset of these items (those that
referenced a
varchar/char/nchar/nvarchar column either directly or indirectly) would need
to be dropped.
However, there are a number of problems with doing a more selective drop:
- A FK may be defined on a non-"string" column, but may reference a string
PK column.
In this case the FK constraint must be dropped even though it is not on a
column that
will be targeted for a collation change (because the PK must be dropped,
and it can't
be dropped while a FK references it). A similar potential problem exists
for CHECK
constraints. Would have to untangle these sort of constraint
dependencies to reliably
identify exactly those constraints that had to be dropped.
- In the case of a clustered index or PK on a string column, you would want
to drop all
nonclustered indexes (even those on non-string columns) before dropping
the clustered
index to avoid two unnecessary rebuilds of each NC index when the
clustered index was
first dropped and then recreated. However, when the clustered index was
on a non-
string column you would ideally do a more selective drop of the NC
indexes.
- There is no way to tell DMO to only script out just the relevant
constraints or
indexes.
For simplicity's sake, this script is designed to drop and all constraints
and indexes.
The complementary proc
sp_generate_index_constraint_s_cript_for
_collation_change generates
a script that will recreate all indexes and constraints.
*/
IF (DB_ID() < 5 OR DB_NAME() IN ('master', 'tempdb', 'msdb', 'distribution',
'model'))
BEGIN
RAISERROR ('The ALTER TABLE approach to changing collation is not valid
for system
databases. Search for REBUILDM in Books Online or run this procedure from
a user database.',16,1)
RETURN -1
END
PRINT '/**** Generating DROP commands for constraints, statistics, and
indexes ****/'
PRINT '/* '
PRINT 'Save this output to a .SQL script and run it to drop constraints and
indexes '
PRINT 'that might prevent a column''s collation from being changed. Back up
the '
PRINT 'database before running this, and be sure that you have scripted out
the index, '
PRINT 'constraint, and statistics definitions so you can recreate these
objects after '
PRINT 'changing the collation of the underlying columns.'
PRINT '*/'
PRINT ''
DECLARE @.sql nvarchar (4000)
DECLARE @.obj_type varchar (30)
DECLARE @.itemcount int
DECLARE @.currentitem int
SET NOCOUNT ON
-- 1. Drop UNIQUE, CHECK, DEFAULT, and FOREIGN KEY constraints (sysobjects)
-- 2. Drop nonclustered indexes and statistics (sysindexes)
-- 3. Drop PRIMARY KEY constraints (sysobjects)
-- 4. Drop non-PK clustered indexes (sysindexes)
IF OBJECT_ID ('tempdb..#idx_const_drop') IS NOT NULL DROP TABLE
#idx_const_drop
SELECT *
INTO #idx_const_drop
FROM (
-- non-PK constraints
SELECT 1 AS obj_type, CONVERT (nvarchar (4000), 'ALTER TABLE ['
+ OBJECT_NAME (parent_obj) + '] DROP CONSTRAINT [' + name + ']') AS [sql]
FROM sysobjects
WHERE OBJECTPROPERTY (id, 'IsMSShipped')=0 AND xtype IN ('F','D','C','UQ')
UNION
-- nonclustered indexes and statistics
SELECT 2, 'DROP '
+ CASE INDEXPROPERTY (id, name, 'IsStatistics')
WHEN 1 THEN 'STATISTICS ' WHEN 0 THEN 'INDEX ' END
+ '[' + OBJECT_NAME (id) + '].[' + name + ']'
FROM sysindexes i
WHERE indid BETWEEN 2 AND 254 AND OBJECTPROPERTY (id, 'IsMSShipped') = 0
AND NOT INDEXPROPERTY (id, name, 'IsAutoStatistics') = 1
AND NOT INDEXPROPERTY (id, name, 'IsHypothetical') = 1
-- Not part of a constraint
AND NOT EXISTS (SELECT * FROM sysobjects o
WHERE o.xtype IN ('PK', 'UQ') AND i.id = o.parent_obj AND o.name
= i.name)
UNION
-- primary key constraints
SELECT 3, 'ALTER TABLE [' + OBJECT_NAME (parent_obj) + '] DROP CONSTRAINT
['
+ name + ']'
FROM sysobjects
WHERE OBJECTPROPERTY (id, 'IsMSShipped') = 0 AND xtype IN ('PK')
UNION
-- clustered indexes
SELECT 4, 'DROP INDEX [' + OBJECT_NAME (id) + '].[' + name + ']'
FROM sysindexes i
WHERE indid = 1 AND OBJECTPROPERTY (id, 'IsMSShipped') = 0
AND INDEXPROPERTY (id, name, 'IsAutoStatistics') = 0
AND INDEXPROPERTY (id, name, 'IsHypothetical') = 0
-- Not part of a constraint
AND NOT EXISTS (SELECT * FROM sysobjects o
WHERE o.xtype IN ('PK', 'UQ') AND i.id = o.parent_obj AND o.name
= i.name)
) AS t
SET @.itemcount = @.@.ROWCOUNT
SET @.currentitem = 1
PRINT 'USE ' + DB_NAME()
PRINT 'GO'
DECLARE c CURSOR LOCAL READ_ONLY FOR
SELECT obj_type, [sql] FROM #idx_const_drop ORDER BY obj_type
OPEN c
FETCH NEXT FROM c INTO @.obj_type, @.sql
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
PRINT 'RAISERROR (''Executing DROP cmd ' + CONVERT (varchar (20),
@.currentitem)
+ ' of ' + CONVERT (varchar (20), @.itemcount) + ': ' + @.sql
+ ''', 0, 1) WITH NOWAIT'
PRINT @.sql
PRINT 'GO'
SET @.currentitem = @.currentitem + 1
FETCH NEXT FROM c INTO @.obj_type, @.sql
END
CLOSE c
DEALLOCATE c
PRINT ''
PRINT ''
GO
IF OBJECT_ID ('sp_generate_alter_cmds_for_c_ollation_
change') IS NOT NULL
DROP PROC sp_generate_alter_cmds_for_col_lation_ch
ange
GO
CREATE PROCEDURE sp_generate_alter_cmds_for_col_lation_ch
ange
@.newcollationname sysname AS
DECLARE @.sql nvarchar (4000)
DECLARE @.objname sysname
DECLARE @.columnname sysname
DECLARE @.type sysname
DECLARE @.size int
DECLARE @.isnullable int -- BOOL. 0=false, 1=true
DECLARE @.itemcount int
DECLARE @.currentitem int
IF (DB_ID() < 5 OR DB_NAME() IN ('master', 'tempdb', 'msdb', 'distribution',
'model'))
BEGIN
RAISERROR ('The ALTER TABLE approach to changing collation is not valid
for system
databases. Search for REBUILDM in Books Online or run this procedure from
a user database.',16,1)
RETURN -1
END
IF COLLATIONPROPERTY (@.newcollationname, 'CodePage') IS NULL
BEGIN
RAISERROR ('%s is not a valid collation. See the "SQL Collation Name"
and
"Windows Collation Name" articles in the SQL Server Books Online.',
16, 1, @.newcollationname)
RETURN -1
END
SET NOCOUNT ON
PRINT '/**** Generating ALTER TABLE ALTER COLUMN commands for string columns
****/'
PRINT '/*'
PRINT 'Save this output as a .SQL script and run it to change the collation
of '
PRINT 'char, varchar, nchar, and nvarchar columns to ' + @.newcollationname +
'.'
PRINT '*/'
PRINT ''
-- Change default collation of the database to the system default
-- (same as tempdb). Note that this does *not* change the collation
-- of existing tables or columns.
PRINT ''
PRINT 'USE ' + DB_NAME()
PRINT 'GO'
PRINT 'RAISERROR (''Changing database default collation (requires exclusive
access)...'',
0, 1) WITH NOWAIT'
SET @.sql = 'ALTER DATABASE [' + DB_NAME() + '] COLLATE '
+ @.newcollationname
PRINT @.sql
PRINT 'GO'
PRINT ''
PRINT ''
-- Change collation of each (var)char/n(var)char/(n)text column to match
-- the database collation.
-- Open a cursor on a list of all char/varchar/nchar/nvarchar columns
-- on user tables.
IF OBJECT_ID ('tempdb..#col_list') IS NOT NULL DROP TABLE #col_list
SELECT object_name (o.id) AS objname, c.name AS colname,
t.name AS [type], c.prec AS [size], isnullable
INTO #col_list
FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
INNER JOIN systypes t ON c.xtype = t.xtype
WHERE o.type = 'U' AND t.name IN ('varchar', 'nvarchar', 'char', 'nchar')
AND OBJECTPROPERTY (o.id, 'IsMSShipped') = 0
SET @.itemcount = @.@.ROWCOUNT
SET @.currentitem = 1
DECLARE col INSENSITIVE CURSOR FOR
SELECT objname, colname, [type], [size], isnullable
FROM #col_list
ORDER BY objname, colname
FOR READ ONLY
OPEN col
FETCH NEXT FROM col INTO @.objname, @.columnname, @.type, @.size,
@.isnullable
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
-- Generate ALTER TABLE ... ALTER COLUMN stmt.
SET @.sql = 'ALTER TABLE [' + @.objname + '] ' + 'ALTER COLUMN ['
+ @.columnname + '] ' + @.type + ' '
-- Add column length if type is not text/ntext.
IF @.type IN ('varchar', 'nvarchar', 'char', 'nchar')
SET @.sql = @.sql + '(' + CAST (@.size AS varchar) + ') '
SET @.sql = @.sql + 'COLLATE ' + @.newcollationname + ' '
IF @.isnullable = 1
SET @.sql = @.sql + 'NULL '
ELSE
SET @.sql = @.sql + 'NOT NULL '
PRINT 'RAISERROR (''Executing ALTER cmd ' + CONVERT (varchar (20),
@.currentitem)
+ ' of ' + CONVERT (varchar (20), @.itemcount) + ': ' + @.sql
+ ''', 0, 1) WITH NOWAIT'
PRINT @.sql
PRINT 'GO'
SET @.currentitem = @.currentitem + 1
FETCH NEXT FROM col INTO @.objname, @.columnname, @.type, @.size,
@.isnullable
END
CLOSE col
DEALLOCATE col
PRINT 'PRINT ''
PRINT 'PRINT ''Base table columns that have not been changed to the new
collation:'''
PRINT 'PRINT ''
PRINT 'SELECT '
PRINT ' RIGHT (c.TABLE_SCHEMA + ''.'' + c.TABLE_NAME + ''.'' + COLUMN_NAME,
60) AS COLUMN_NAME, '
PRINT ' LEFT (COLLATION_NAME, 40) AS COLLATION_NAME, '
PRINT ' LEFT (DATA_TYPE, 9) AS DATA_TYPE '
PRINT 'FROM INFORMATION_SCHEMA.COLUMNS c '
PRINT 'INNER JOIN INFORMATION_SCHEMA.TABLES t '
PRINT ' ON c.TABLE_CATALOG = t.TABLE_CATALOG AND c.TABLE_SCHEMA =
t.TABLE_SCHEMA '
PRINT ' AND c.TABLE_NAME = t.TABLE_NAME AND t.TABLE_TYPE = ''BASE TABLE''
'
PRINT 'WHERE DATA_TYPE IN
(''char'',''nchar'',''varchar'_',''nvarc
har'',''text'',''ntex_t'') '
PRINT ' AND OBJECTPROPERTY (OBJECT_ID (t.TABLE_SCHEMA + ''.'' +
t.TABLE_NAME), ''IsMSShipped'')=0 '
PRINT ' AND COLLATION_NAME != ''' + @.newcollationname + ''''
PRINT 'ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME '
PRINT 'GO'
GO
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23lL9n6aRGHA.1576@.tk2msftngp13.phx.gbl...
> Hello there
> Is there a way to change database collation?
>

Sunday, March 11, 2012

Change collation with osql

Hi,
I have an MS SQL Server 2000 database with collation
'SQL_Hungarian_CP1250_CI_AS'. I wanted to run a script file to insert
values into one of my table by osql.exe the following way:
OSQL.EXE -U myUserID -P mypassword -d myDB -i "C:\myscript.sql" -n
The script was run successfully but the inserted character fields
looked as if it were in different collation.
For example the script contains the following insert command:
INSERT INTO admFunction
([FunctionID],[FunctionName],[FunctionMo
des],[ParentFunctionID],[OrderNo],[Modul
ID],[Extra1ID],[Enabled])
VALUES (1,'Vevői ajnlat',8,85,1,1,100,1)
but the inserted 'FunctionName' field contained this after running the
script:
'Vevi ajnlat'
I suppose it is a collation problem but i don't know how to solve it.
Any suggestion?
Thanks in advance
szoli
---
szoli's Profile: http://www.msmcse.ms/member.php?userid=1477
View this thread: http://www.msmcse.ms/t-1870445621If FunctionName is of type nvarchar, try the following:
INSERT INTO admFunction
([FunctionID],[FunctionName],[FunctionMo
des],[ParentFunctionID],[OrderNo],[Modul
ID],[Extra1ID],[Enabled]) VALUES (1,N'Vevői ajnlat',8,85,1,1,100,1)Joo Ara
jo"szoli" <szoli.1pc1ga@.no-mx.msmcse.ms> wrote in messagenews:szoli.1pc1g
a@.no-mx.msusenet
.com...>> Hi,>> I have an MS SQL Server 2000 database with collation> 'SQL_H
ungarian_CP1250_CI_AS'. I wanted to run a script file to insert> values into
one of my table by osql.exe the following way:>> OSQL.EXE -U myUserID -P my
password -d myDB -i "C:\mys
cript.sql" -n>> The script was run successfully but the inserted character fields> looked as if it wer
e in different collation.> For example the script contains the following insert command:>> INSERT INTO
admFunction>([FunctionID],[FunctionName],[Function
Modes],[ParentFunctionID],[OrderNo],[Mod
ulID],[Extra1ID],[Enabled])> VALUES (1,'Vevői ajnlat',8,85,1,1,100,1)>> but
the inserted 'FunctionName' field contained this after running the> script:
> 'Vevi ajnlat'>> I suppose it is a collation problem bu
t i don't know how to solve it.> Any suggestion?>> Thanks in advance>>> --> szoli> --
----> szoli's Profile: http://www.msusen
et.com/member.php?userid=1477> View this thread: http://www.msus
enet.com/t-1870445621>|||If FunctionName is of type nvarchar, try the following:
INSERT INTO admFunction
([FunctionID],[FunctionName],[FunctionMo
des],[ParentFunctionID],[OrderNo],[Modul
ID],[Extra1ID],[Enabled])VALUES (1,N'Vevői ajnlat',8,85,1,1,100,1)Joo Araj
o"szoli" <szoli.1pc1ga@.no-mx.msmcse.ms> wrote in messagenews:szoli.1pc1ga
@.no-mx.msusenet.
com...>> Hi,>> I have an MS SQL Server 2000 database with collation> 'SQL_Hu
ngarian_CP1250_CI_AS'. I wanted to run a script file to insert> values into
one of my table by osql.exe the following way:>> OSQL.EXE -U myUserID -P myp
assword -d myDB -i "C:\mysc
ript.sql" -n>> The script was run successfully but the inserted character fields> looked as if it were
in different collation.> For example the script contains the following insert command:>> INSERT INTO
admFunction>([FunctionID],[FunctionName],[FunctionM
odes],[ParentFunctionID],[OrderNo],[Modu
lID],[Extra1ID],[Enabled])> VALUES (1,'Vevői ajnlat',8,85,1,1,100,1)>> but
the inserted 'FunctionName' field contained this after running the> script:>
'Vevi ajnlat'>> I suppose it is a collation problem but
i don't know how to solve it.> Any suggestion?>> Thanks in advance>>> --> szoli> --
----> szoli's Profile: http://www.msusenet
.com/member.php?userid=1477> View this thread: http://www.msuse
net.com/t-1870445621>|||Finallly i found the solution.
I had to save my sql script in Query Analyzer in Unicode format and
then it worked well.
szoli
---
szoli's Profile: http://www.msmcse.ms/member.php?userid=1477
View this thread: http://www.msmcse.ms/t-1870445621

Change collation on entire database

Hello there
I need to change on the entire database all the collations to one collation.
The only way I now it is possible is by:
ALTER TABLE TableName
ALTER COLUMN ColumnName
ColType(ColSize)
and other properties
EXEPT COLLATE newCollateName
That way is very dangures because i must know all the rest properties of the
fields.
Is there a way to change only the collation?To change this, I will use a query that I Adapted from Justin Mallier''s
blog:
(http://mallier.blogspot.com/2005/11.../drsql/blog/cns!80677FB08B3162E4!842.entry
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23tbCR3tOGHA.3732@.TK2MSFTNGP10.phx.gbl...
> Hello there
> I need to change on the entire database all the collations to one
> collation.
> The only way I now it is possible is by:
> ALTER TABLE TableName
> ALTER COLUMN ColumnName
> ColType(ColSize)
> and other properties
> EXEPT COLLATE newCollateName
> That way is very dangures because i must know all the rest properties of
> the fields.
> Is there a way to change only the collation?
>

Change collation of column with User-Defined Datatype

I need to change the collation of all columns in a table by using ALTER
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?Hi
"DBA72" wrote:

> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change t
he
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John

Change collation of column with User-Defined Datatype

I need to change the collation of all columns in a table by using ALTER
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?
Hi
"DBA72" wrote:

> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change the
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John

Change collation of column with User-Defined Datatype

I need to change the collation of all columns in a table by using ALTER
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?Hi
"DBA72" wrote:
> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change the
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John

change collation of a type of text column

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 :
ImreCheck 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|||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@.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 hav
e
> to remove it before changing the collation.
>
> AMB
> "Imre Ament" wrote:
>|||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@.discussions.microsoft.com> wrote in messag
e
> news:F8284F2D-0D4B-4910-8481-E594FEDB3F40@.microsoft.com...
>

change collation name after creating database ?

can the collation name of a database be changed after it has been created ?
Yes, you can change the collation afterward but this will not change the
collation of columns in existing tables. See ALTER DATABASE in the Books
Online for other information
Hope this helps.
Dan Guzman
SQL Server MVP
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:uFBjV$a2EHA.2608@.TK2MSFTNGP10.phx.gbl...
> can the collation name of a database be changed after it has been created
> ?
>

change collation name after creating database ?

can the collation name of a database be changed after it has been created ?Yes, you can change the collation afterward but this will not change the
collation of columns in existing tables. See ALTER DATABASE in the Books
Online for other information
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:uFBjV$a2EHA.2608@.TK2MSFTNGP10.phx.gbl...
> can the collation name of a database be changed after it has been created
> ?
>

Change Collation- Migration of Data Required

Hi,

If I have a database with collation Sequence X and I change the
collation sequence of database to Collation Sequence Y , do I have to
migrate the data of tables with collation Sequence X to collation
Sequence Y or SQl server takes care of migrating the data internally.

Thanks in advance.

-Kalyankalyan (m_kalyanc@.email.com) writes:
> If I have a database with collation Sequence X and I change the
> collation sequence of database to Collation Sequence Y , do I have to
> migrate the data of tables with collation Sequence X to collation
> Sequence Y or SQl server takes care of migrating the data internally.

You will have to migrate the data.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Change collation in tempdb

Hi,
I'm using MSSQL2000. Is there any way to change the collation of
tempdb database? I just realized that my devel and production tempdb
have different collation and would like to get them in synch,
Thanks,
RolandoRolando
Try something like this
alter database tempdb COLLATION
SQL_Latin1_General_CP1_CI_AS
Hope this helps
John|||John,
You cant run ALTER DATABASE against system databases.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:095801c35ce5$ae8035d0$a601280a@.phx.gbl...
> Rolando
> Try something like this
> alter database tempdb COLLATION
> SQL_Latin1_General_CP1_CI_AS
> Hope this helps
> John|||Rolando,
The collation that you selected on setup becomes the default collation for
tempdb as well as other system databases.To change that, you need to proceed
with the complex process of rebuilding master.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Rolando" <mrrd@.bigfoot.com> wrote in message
news:abcf80d8.0308070451.38eea467@.posting.google.com...
> Hi,
> I'm using MSSQL2000. Is there any way to change the collation of
> tempdb database? I just realized that my devel and production tempdb
> have different collation and would like to get them in synch,
> Thanks,
> Rolando|||OK, my mistake

Change collation in SS7

How can I make just one particular column on a table in SS7 be case-sensitive? I know it can be done in SQL 2000.Afraid you can't. This is a new feature in v2000.

You can code in case sensitivity by

where fld = @.fld
and convert(varbinary,fld) = convert(varbinary,@.fld)

the 'where fld = @.fld' is just so it can use any indexes|||Thanks for the reply.

Change Collation in database/server

Hello group,
Hi everyone I localize aplication to polish language and I want change
collation in database.
How change Collation in data base?
My database name MyDB
I want collation : 'Polish_CI_AS'
And my second question:
How set default collation for server (for new databases) if the collation is
different as operation system (ex. polish collation in databese, on windows
server english version)?
Thanks
PawelRHave you read these:
You can start here:
http://msdn.microsoft.com/library/d...br />
1pwz.asp
Default collation:
http://msdn.microsoft.com/library/d...br />
0f3n.asp
Changing collations:
http://msdn.microsoft.com/library/d...br />
819v.asp
ML|||ALTER DATABASE MyDB COLLATE <collationName>
"PawelR" wrote:

> Hello group,
> Hi everyone I localize aplication to polish language and I want change
> collation in database.
> How change Collation in data base?
> My database name MyDB
> I want collation : 'Polish_CI_AS'
> And my second question:
> How set default collation for server (for new databases) if the collation
is
> different as operation system (ex. polish collation in databese, on window
s
> server english version)?
>
> Thanks
> PawelR
>
>

Change collation for an existing database

Hi !
I would like to know how to change the collation of an imported database. I
tried something with DTS, but I'm quiet a beginner with this tool. So if
anyone could help me on this one, this would be great ! ;-)
Thanks in advance !
I just forgot to say that I use SQL 2000 SP4.

"Vincent D." wrote:

> Hi !
> I would like to know how to change the collation of an imported database. I
> tried something with DTS, but I'm quiet a beginner with this tool. So if
> anyone could help me on this one, this would be great ! ;-)
> Thanks in advance !
|||Seems that similar issue has been handled recently. Pls have a look at the 2
posts of 28/11/05.
http://www.microsoft.com/technet/com...8-B1DE-B6EB49F
31B7E&dglist=&ptlist=&exp=&sloc=en-us
"Vincent D." wrote:
[vbcol=seagreen]
> I just forgot to say that I use SQL 2000 SP4.

> "Vincent D." wrote:

Thursday, March 22, 2012

Change default server collation

Change default server collation

Change default server collation

change default collation for user database

Tuesday, March 20, 2012

Change database collation - quickly

change database collation

Sunday, March 11, 2012

Change collation with osql

Change collation on entire database

Change collation of column with User-Defined Datatype

Change collation of column with User-Defined Datatype

Change collation of column with User-Defined Datatype

change collation of a type of text column

change collation name after creating database ?

change collation name after creating database ?

Change Collation- Migration of Data Required

Change collation in tempdb

Change collation in SS7

Change Collation in database/server

Change collation for an existing database

Sql Case sensitivity

Blog Archive

About Me