change MSDB owner: collation

Showing posts with label collation. Show all posts

Tuesday, March 27, 2012

Changing column collation

Hi all,
I have an interesting doubt:
In our SQL environments we have some problems with different collations.
Unfortunatelly when this servers and db's were created people didn't care
about collations.
Now would like normalize these environments and db's.
I have a DB with SQL collation SQL_Latin1_General_CP1_CI_AS and all tables
with string fields Latin1_General_CI_AS.
I did an scripts to first drop index and constraints of these string fields,
after I created a script with ALTER TABLE ALTER COLUMN to change the field
collation.
I would expected that this command would do the conversion of the data, but
it was so fast that I'm almost sure that it change only the collation in the
system table...
If i try to change the collation in Enterprise Manager and ask for save the
script SQL always create a new table and transfer the data doing so the
conversion.
My doubt is: If the command ALTER COLUMN doesn't convert the data, the SQL
collation SQL_Latin1_General_CP1_CI_AS is compatible with windows collation
Latin1_General_CI_AS in the sense that also there's no conversion I will not
have any join (between char fieds) problems?
Regards and thanks a lot
Alexandre Calderaro
MCDBA Avanade ItalyHi Alex
You didn't post the script you ran to change the tables. If your data in the
columns are mainly in the normal range (of alphanumerics) there should not be
a need to change the data. The following post gives a way of creating a
script to change the collations http://tinyurl.com/qwulo this assumes the
database has alredy been chagned to the correct collation with an ALTER
DATABASE statement.
John
"Alex" wrote:
> Hi all,
> I have an interesting doubt:
> In our SQL environments we have some problems with different collations.
> Unfortunatelly when this servers and db's were created people didn't care
> about collations.
> Now would like normalize these environments and db's.
> I have a DB with SQL collation SQL_Latin1_General_CP1_CI_AS and all tables
> with string fields Latin1_General_CI_AS.
> I did an scripts to first drop index and constraints of these string fields,
> after I created a script with ALTER TABLE ALTER COLUMN to change the field
> collation.
> I would expected that this command would do the conversion of the data, but
> it was so fast that I'm almost sure that it change only the collation in the
> system table...
> If i try to change the collation in Enterprise Manager and ask for save the
> script SQL always create a new table and transfer the data doing so the
> conversion.
> My doubt is: If the command ALTER COLUMN doesn't convert the data, the SQL
> collation SQL_Latin1_General_CP1_CI_AS is compatible with windows collation
> Latin1_General_CI_AS in the sense that also there's no conversion I will not
> have any join (between char fieds) problems?
> Regards and thanks a lot
> Alexandre Calderaro
> MCDBA Avanade Italy|||Thanks John,
I have a similar script to change the collation of all tables.
For each column I have a similar script:
ALTER TABLE tab ALTER COLUMN col nvarchar(255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
Could using a Unicode datatype change the behavior of this command?
My doubt is: what's the difference between using ALTER COLUMN (very fast)
and create a new table with the new collation and transfer data using INSERT
SELECT (obviously much more resource consuming and it's that SQL does if you
change the collation using Enterprise Manager)?
Regards
Alexandre
"John Bell" wrote:
> Hi Alex
> You didn't post the script you ran to change the tables. If your data in the
> columns are mainly in the normal range (of alphanumerics) there should not be
> a need to change the data. The following post gives a way of creating a
> script to change the collations http://tinyurl.com/qwulo this assumes the
> database has alredy been chagned to the correct collation with an ALTER
> DATABASE statement.
> John
>
> "Alex" wrote:
> > Hi all,
> > I have an interesting doubt:
> > In our SQL environments we have some problems with different collations.
> > Unfortunatelly when this servers and db's were created people didn't care
> > about collations.
> > Now would like normalize these environments and db's.
> > I have a DB with SQL collation SQL_Latin1_General_CP1_CI_AS and all tables
> > with string fields Latin1_General_CI_AS.
> > I did an scripts to first drop index and constraints of these string fields,
> > after I created a script with ALTER TABLE ALTER COLUMN to change the field
> > collation.
> > I would expected that this command would do the conversion of the data, but
> > it was so fast that I'm almost sure that it change only the collation in the
> > system table...
> > If i try to change the collation in Enterprise Manager and ask for save the
> > script SQL always create a new table and transfer the data doing so the
> > conversion.
> > My doubt is: If the command ALTER COLUMN doesn't convert the data, the SQL
> > collation SQL_Latin1_General_CP1_CI_AS is compatible with windows collation
> > Latin1_General_CI_AS in the sense that also there's no conversion I will not
> > have any join (between char fieds) problems?
> > Regards and thanks a lot
> >
> > Alexandre Calderaro
> > MCDBA Avanade Italy|||Hi
Enterprise Manager tends to use a belt and braces approach to the SQL
generated and not always the most efficient. The alter table command does not
have to move the data around. Your alter table command should be fine, but it
is always better to thoroughly test in a non-production first!
John
"Alex" wrote:
> Thanks John,
> I have a similar script to change the collation of all tables.
> For each column I have a similar script:
> ALTER TABLE tab ALTER COLUMN col nvarchar(255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> Could using a Unicode datatype change the behavior of this command?
> My doubt is: what's the difference between using ALTER COLUMN (very fast)
> and create a new table with the new collation and transfer data using INSERT
> SELECT (obviously much more resource consuming and it's that SQL does if you
> change the collation using Enterprise Manager)?
> Regards
> Alexandre
> "John Bell" wrote:
> > Hi Alex
> >
> > You didn't post the script you ran to change the tables. If your data in the
> > columns are mainly in the normal range (of alphanumerics) there should not be
> > a need to change the data. The following post gives a way of creating a
> > script to change the collations http://tinyurl.com/qwulo this assumes the
> > database has alredy been chagned to the correct collation with an ALTER
> > DATABASE statement.
> >
> > John
> >
> >
> >
> > "Alex" wrote:
> >
> > > Hi all,
> > > I have an interesting doubt:
> > > In our SQL environments we have some problems with different collations.
> > > Unfortunatelly when this servers and db's were created people didn't care
> > > about collations.
> > > Now would like normalize these environments and db's.
> > > I have a DB with SQL collation SQL_Latin1_General_CP1_CI_AS and all tables
> > > with string fields Latin1_General_CI_AS.
> > > I did an scripts to first drop index and constraints of these string fields,
> > > after I created a script with ALTER TABLE ALTER COLUMN to change the field
> > > collation.
> > > I would expected that this command would do the conversion of the data, but
> > > it was so fast that I'm almost sure that it change only the collation in the
> > > system table...
> > > If i try to change the collation in Enterprise Manager and ask for save the
> > > script SQL always create a new table and transfer the data doing so the
> > > conversion.
> > > My doubt is: If the command ALTER COLUMN doesn't convert the data, the SQL
> > > collation SQL_Latin1_General_CP1_CI_AS is compatible with windows collation
> > > Latin1_General_CI_AS in the sense that also there's no conversion I will not
> > > have any join (between char fieds) problems?
> > > Regards and thanks a lot
> > >
> > > Alexandre Calderaro
> > > MCDBA Avanade Italysql

Changing column collation

Changing collation when installing SQL2000

Hi,
I'm installing SQL2000 on a new cluster server.
I selected a 'custom installation' and was presented with a
'Collation settings' screen.
I need it to be SQL_Latin1_General_CP1_CI_AS collation
as I will be restoring databases with this collation.
What should I choose from the 'Collation designator' and 'SQL
Collations' box.
Is it 'Dictionary order, case-insensitive, for use with 1252
characters'?

What will be the default? Will it be the same default as for the
'typical' installation?
Thanks(polinaskulski@.aol.com) writes:
> I'm installing SQL2000 on a new cluster server.
> I selected a 'custom installation' and was presented with a
> 'Collation settings' screen.
> I need it to be SQL_Latin1_General_CP1_CI_AS collation
> as I will be restoring databases with this collation.
> What should I choose from the 'Collation designator' and 'SQL
> Collations' box.
> Is it 'Dictionary order, case-insensitive, for use with 1252
> characters'?

I would guess that is be the one, although the description also fits into
SQL_Latin1_General_CP1_CI_AI.

When you install Window collations, there are check-boxes for the
sensitivity stuff, but there might not be for SQL collations. (I
never install these.)

> What will be the default? Will it be the same default as for the
> 'typical' installation?

I believe that SQL_Latin1_General_CP1_CI_AS is the default is your
regional settings is English (USA).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Changing Collation SQL 2000 sp4

Hello
We just upgraded to SQL server 2000 sp3a from sp4 . Could the
collation
change upgrading to SP4 (from SQL_Latin1_General_CP1_CS_AS to
SQL_Latin1_General_CP1_CI_AS. Or can that only be changed on the
initial installation.
ThanksHi
"jsully79" wrote:

> Hello
> We just upgraded to SQL server 2000 sp3a from sp4 . Could the
> collation
> change upgrading to SP4 (from SQL_Latin1_General_CP1_CS_AS to
> SQL_Latin1_General_CP1_CI_AS. Or can that only be changed on the
> initial installation.
> Thanks
I have not know the collation change when applying the service pack. To do
for the whole system would have required alot of work. Are you asking about
a
specific database/column/setting?
John

Changing Collation SQL 2000 sp4

Hello
We just upgraded to SQL server 2000 sp3a from sp4 . Could the
collation
change upgrading to SP4 (from SQL_Latin1_General_CP1_CS_AS to
SQL_Latin1_General_CP1_CI_AS. Or can that only be changed on the
initial installation.
ThanksHi
"jsully79" wrote:
> Hello
> We just upgraded to SQL server 2000 sp3a from sp4 . Could the
> collation
> change upgrading to SP4 (from SQL_Latin1_General_CP1_CS_AS to
> SQL_Latin1_General_CP1_CI_AS. Or can that only be changed on the
> initial installation.
> Thanks
I have not know the collation change when applying the service pack. To do
for the whole system would have required alot of work. Are you asking about a
specific database/column/setting?
John

Changing Collation SQL 2000 sp4

Hello
We just upgraded to SQL server 2000 sp3a from sp4 . Could the
collation
change upgrading to SP4 (from SQL_Latin1_General_CP1_CS_AS to
SQL_Latin1_General_CP1_CI_AS. Or can that only be changed on the
initial installation.
Thanks
Hi
"jsully79" wrote:

> Hello
> We just upgraded to SQL server 2000 sp3a from sp4 . Could the
> collation
> change upgrading to SP4 (from SQL_Latin1_General_CP1_CS_AS to
> SQL_Latin1_General_CP1_CI_AS. Or can that only be changed on the
> initial installation.
> Thanks
I have not know the collation change when applying the service pack. To do
for the whole system would have required alot of work. Are you asking about a
specific database/column/setting?
John

Changing collation on server

Hello there
On one of my servers diffrent collation is being declered.
I need to change it to SQL_LATIN1_GENERAL_SP1_CI_AS
I used rebuildm program for that. and it failed
what i need to do on the regional settings for allowing this type of
collation?Roy,
what do you mean with rebuildm.exe failed. Did you get any errors or do
you just mean that the collation of your server hasn't changed? By the
way be aware of the fact that rebuildm.exe only changes the collation
of the master, model, msdb and tempdb. Your user databases you have to
convert "manually".
Markus|||How do you change the user databases collation manualy? I have changed the
master db by using rebuildm.exe.
George
"MarkusB" wrote:
> Roy,
> what do you mean with rebuildm.exe failed. Did you get any errors or do
> you just mean that the collation of your server hasn't changed? By the
> way be aware of the fact that rebuildm.exe only changes the collation
> of the master, model, msdb and tempdb. Your user databases you have to
> convert "manually".
> Markus
>sql

Changing collation on server

Hello there
On one of my servers diffrent collation is being declered.
I need to change it to SQL_LATIN1_GENERAL_SP1_CI_AS
I used rebuildm program for that. and it failed
what i need to do on the regional settings for allowing this type of
collation?Roy,
what do you mean with rebuildm.exe failed. Did you get any errors or do
you just mean that the collation of your server hasn't changed? By the
way be aware of the fact that rebuildm.exe only changes the collation
of the master, model, msdb and tempdb. Your user databases you have to
convert "manually".
Markus|||How do you change the user databases collation manualy? I have changed the
master db by using rebuildm.exe.
George
"MarkusB" wrote:

> Roy,
> what do you mean with rebuildm.exe failed. Did you get any errors or do
> you just mean that the collation of your server hasn't changed? By the
> way be aware of the fact that rebuildm.exe only changes the collation
> of the master, model, msdb and tempdb. Your user databases you have to
> convert "manually".
> Markus
>

Changing collation on server

Sunday, March 25, 2012

Changing COLLATION on a table

Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Oh1bpGnxEHA.1396@.tk2msftngp13.phx.gbl...
> By the way, you can quasi-automate this, e.g.
>
>
> DECLARE @.tablename VARCHAR(64)
> SET @.tablename = 'something'
> SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
> + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
> + ' COLLATE <new collation>'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME=@.tablename
> AND RIGHT(DATA_TYPE,4) IN ('char', 'text')
>
> You will still have to deal with constraints, computed columns etc.
> yourself...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
|||Thanks guys...I'll give it a try.
I was aware that the collation was specific for the columns, but I thought
there was an "out of the box" funktion that could change it for all columns
in a table.
Regards
Steen
Adam Machanic wrote:[vbcol=seagreen]
> Also, the COLLATE and NULL clauses were in the wrong order...
> Here's a fixed version:
>
> DECLARE @.tablename VARCHAR(64)
> SET @.tablename = 'something'
> SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
> + ' COLLATE <new collation> '
> + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME=@.tablename
> AND RIGHT(DATA_TYPE,4) = 'char'
>
> ... and now to really automate it, I leave it up to you to plug it
> into sp_execresultset

> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:Oh1bpGnxEHA.1396@.tk2msftngp13.phx.gbl...

Changing COLLATION on a table

Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Oh1bpGnxEHA.1396@.tk2msftngp13.phx.gbl...
> By the way, you can quasi-automate this, e.g.
>
>
> DECLARE @.tablename VARCHAR(64)
> SET @.tablename = 'something'
> SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)+') '
> + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
> + ' COLLATE <new collation>'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME=@.tablename
> AND RIGHT(DATA_TYPE,4) IN ('char', 'text')
>
> You will still have to deal with constraints, computed columns etc.
> yourself...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Thanks guys...I'll give it a try.
I was aware that the collation was specific for the columns, but I thought
there was an "out of the box" funktion that could change it for all columns
in a table.
Regards
Steen
Adam Machanic wrote:[vbcol=seagreen]
> Also, the COLLATE and NULL clauses were in the wrong order...
> Here's a fixed version:
>
> DECLARE @.tablename VARCHAR(64)
> SET @.tablename = 'something'
> SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)+') '
> + ' COLLATE <new collation> '
> + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME=@.tablename
> AND RIGHT(DATA_TYPE,4) = 'char'
>
> ... and now to really automate it, I leave it up to you to plug it
> into sp_execresultset

Changing COLLATION on a table

Hi
Can any of you help with the syntax for changing the COLLATION on all
columns in a table. I have no problem in changing the collation for the
database itself, but that doesn't affect existing columns in the database.
I can change it for one column at the time, but that's a bit cumbersome.
I've tried different variations of ALTER TABLE but it gives me a syntax
error unless I include the column name I want to change.
Regards
SteenYou need to do the columns individually. A table on its own does not have a
collation.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uYsbmAnxEHA.3844@.TK2MSFTNGP12.phx.gbl...
> Hi
> Can any of you help with the syntax for changing the COLLATION on all
> columns in a table. I have no problem in changing the collation for the
> database itself, but that doesn't affect existing columns in the database.
> I can change it for one column at the time, but that's a bit cumbersome.
> I've tried different variations of ALTER TABLE but it gives me a syntax
> error unless I include the column name I want to change.
> Regards
> Steen
>|||COLLATION is defined on columns, not tables, so you will have to change one
column at a time.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uYsbmAnxEHA.3844@.TK2MSFTNGP12.phx.gbl...
> Hi
> Can any of you help with the syntax for changing the COLLATION on all
> columns in a table. I have no problem in changing the collation for the
> database itself, but that doesn't affect existing columns in the database.
> I can change it for one column at the time, but that's a bit cumbersome.
> I've tried different variations of ALTER TABLE but it gives me a syntax
> error unless I include the column name I want to change.
> Regards
> Steen
>|||By the way, you can quasi-automate this, e.g.
DECLARE @.tablename VARCHAR(64)
SET @.tablename = 'something'
SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
+DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
+ CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
+ ' COLLATE <new collation>'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@.tablename
AND RIGHT(DATA_TYPE,4) IN ('char', 'text')
You will still have to deal with constraints, computed columns etc.
yourself...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uYsbmAnxEHA.3844@.TK2MSFTNGP12.phx.gbl...
> Hi
> Can any of you help with the syntax for changing the COLLATION on all
> columns in a table. I have no problem in changing the collation for the
> database itself, but that doesn't affect existing columns in the database.
> I can change it for one column at the time, but that's a bit cumbersome.
> I've tried different variations of ALTER TABLE but it gives me a syntax
> error unless I include the column name I want to change.
> Regards
> Steen
>|||Aaron,
I think you may have some problems with TEXT/NTEXT columns, as you cannot
use ALTER TABLE on them.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Oh1bpGnxEHA.1396@.tk2msftngp13.phx.gbl...
> By the way, you can quasi-automate this, e.g.
>
>
> DECLARE @.tablename VARCHAR(64)
> SET @.tablename = 'something'
> SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
> + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
> + ' COLLATE <new collation>'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME=@.tablename
> AND RIGHT(DATA_TYPE,4) IN ('char', 'text')
>
> You will still have to deal with constraints, computed columns etc.
> yourself...|||> I think you may have some problems with TEXT/NTEXT columns, as you cannot
> use ALTER TABLE on them.
Good catch... can you tell I reused an automation script from a completely
different task? ;-)|||Also, the COLLATE and NULL clauses were in the wrong order...
Here's a fixed version:
DECLARE @.tablename VARCHAR(64)
SET @.tablename = 'something'
SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
+DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
+ ' COLLATE <new collation> '
+ CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@.tablename
AND RIGHT(DATA_TYPE,4) = 'char'
... and now to really automate it, I leave it up to you to plug it into
sp_execresultset :)
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Oh1bpGnxEHA.1396@.tk2msftngp13.phx.gbl...
> By the way, you can quasi-automate this, e.g.
>
>
> DECLARE @.tablename VARCHAR(64)
> SET @.tablename = 'something'
> SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
> + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
> + ' COLLATE <new collation>'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME=@.tablename
> AND RIGHT(DATA_TYPE,4) IN ('char', 'text')
>
> You will still have to deal with constraints, computed columns etc.
> yourself...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Thanks guys...I'll give it a try.
I was aware that the collation was specific for the columns, but I thought
there was an "out of the box" funktion that could change it for all columns
in a table.
Regards
Steen
Adam Machanic wrote:
> Also, the COLLATE and NULL clauses were in the wrong order...
> Here's a fixed version:
>
> DECLARE @.tablename VARCHAR(64)
> SET @.tablename = 'something'
> SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
> + ' COLLATE <new collation> '
> + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME=@.tablename
> AND RIGHT(DATA_TYPE,4) = 'char'
>
> ... and now to really automate it, I leave it up to you to plug it
> into sp_execresultset :)
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:Oh1bpGnxEHA.1396@.tk2msftngp13.phx.gbl...
>> By the way, you can quasi-automate this, e.g.
>>
>>
>> DECLARE @.tablename VARCHAR(64)
>> SET @.tablename = 'something'
>> SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
>> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
>> + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
>> + ' COLLATE <new collation>'
>> FROM INFORMATION_SCHEMA.COLUMNS
>> WHERE TABLE_NAME=@.tablename
>> AND RIGHT(DATA_TYPE,4) IN ('char', 'text')
>>
>> You will still have to deal with constraints, computed columns etc.
>> yourself...
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)sql

Changing Collation on a Cluster

Hi,
I am sure that someone has managed to do this. We have a 64_bit Clustered
SQL2005 SP2 build 3175 server that we created as Latin1_General_Bin. We want
to change it to the North American default but keep getting errors. The
latest was: - SQL Server Setup is unable to run on this node as it is
unavailable due to a SCC failure or other reason
SQL Server Setup is unable to run. Either the owning node or all nodes that
are currently part of the virtual server are not available. Review any SCC
failures and validate that all nodes are online.
Can someone help before we uninstall? Will we have problems if we uninstall?
Thanks
Chris
Ureka. We did it. We found that the registry key value as to where the
install came from was different on the two nodes and we were using the sa
password for the adminpassword instead of the Windows account we installed
with.
The build still shows 3200, where we had got to, even though BOL seems to
think that this may not be right. It mentioned about having to re-apply the
service packs and hotfix builds.
Chris
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:uWLbw$EOIHA.2376@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am sure that someone has managed to do this. We have a 64_bit Clustered
> SQL2005 SP2 build 3175 server that we created as Latin1_General_Bin. We
> want to change it to the North American default but keep getting errors.
> The latest was: - SQL Server Setup is unable to run on this node as it is
> unavailable due to a SCC failure or other reason
> SQL Server Setup is unable to run. Either the owning node or all nodes
> that are currently part of the virtual server are not available. Review
> any SCC failures and validate that all nodes are online.
> Can someone help before we uninstall? Will we have problems if we
> uninstall?
> Thanks
> Chris
>
|||BOL is correct.
The binaries on all the nodes are build 3200, but likely the procedures on
Master (and possibly the resource database) are still at RTM. Reapply all
service packs and hotfix/cumulative updates to guarantee binaries and data
elements are all in sync.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:u0QWCrFOIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Ureka. We did it. We found that the registry key value as to where the
> install came from was different on the two nodes and we were using the sa
> password for the adminpassword instead of the Windows account we installed
> with.
> The build still shows 3200, where we had got to, even though BOL seems to
> think that this may not be right. It mentioned about having to re-apply
> the service packs and hotfix builds.
> Chris
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:uWLbw$EOIHA.2376@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks Geoff.
I checked the summary.txt says 1399.06. That's what made me suspicious.
Looks like MS need to make a change somewhere otherwise people will not
re-run the updates.
Chris
PS. Hope this is handled better in SQL2008.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:e96bTBOOIHA.2208@.TK2MSFTNGP06.phx.gbl...
> BOL is correct.
> The binaries on all the nodes are build 3200, but likely the procedures on
> Master (and possibly the resource database) are still at RTM. Reapply all
> service packs and hotfix/cumulative updates to guarantee binaries and data
> elements are all in sync.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:u0QWCrFOIHA.4272@.TK2MSFTNGP06.phx.gbl...
>

Changing Collation in SQL Server 2000

Hi.

I don't know how to change the collation in SQL Server 2000, my collation is Modern_Spanish_CI_AS, if somedoby knows the procedure to change the collation I would be thankful.

Thank you again and best regards.

Christian

Hi,

For database, ALTER DATABASE databasename COLLATE collation_name.

Change the order rule of tables, ALTER TABLE TabelName ALTER COLUMN ColumnName varchar(10)COLLATE ordername NOT NULL

Some good links for you to refer:

http://support.microsoft.com/kb/325335
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=60020&SiteID=1

Thanks.

Changing collation in SQL 2000

I have a database in SQL 2000 on which I am trying to convert the collation to case insensitive from case sensitive. I am trying to use DTS to do this. The master database is already case insensitive. I have created a new database on the same server with the proper collation. I am then trying to use the DTS to import the data by replacing all objects. I am essentially trying to copy all the database objects changing only the collation. Everytime I try the process, I get errors and the process stops. It does not tell me what the errors are.

Could someone please give me some guidance on what may be causing this problem, and where I can find what kind of errors are occurring?

Thank you
GarryHi Garry, I am sorry but this is the wrong forum to ask this question. I hope you can or did ask in http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=80 (the SSIS/DTS forum).|||You can change the database collation using the statement ALTER DATABASE ... COLLATE ... - see Books Online for more information.

Make sure tempdb has a compatible collation. Tempdb is used for several operations (e.g. sorting intermediate results) and a discrepency will result in incorrect or failed execution. In that case, you may want to change the server's collation. This is done by rebuilding the master database specifying the new collation in the SQLCOLLATION property of the setup command. Books online contains more information.

Thank you.|||

Thanks Shankar,

I was able to change the collation of the database using the ALTER DATABASE, however, in the the accounting system, for which SQL 2000 serves as the backend, we are getting an error on a report query we are trying to run. The error states: "Collation cannot be resolved". I checked the KB and there is an article stating that it could be a conflict with the collation in the Tempdb and the accounting database, and that is what the software support for the accounting system is telling me. When I check the properties of the accounting database and the tempdb, however, they are the same. That was the purpose of running the ALTER DATABASE command in the first place.

Do you know of any remaining conflicts in the database that I could resolve to eliminate this problem?

Thank you,
Garry

|||How are you ensuring that the two database collations are the same? You could try to copy some of the data into a temp table and see whether you get the behavior of the old collation. This would be a verification.

Changing the server's collation would be the more reliable way if that is a choice.

Thank you,

Shankar
Program Manager, Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights|||

Hi Shankar,

Can we change collation per query, while keeping a standard collation for the table. I just asking whether is it possible to specify the collation when running a SQL query, without altering collation on table or column.

Thanks,

Roshan.

Changing collation in SQL 2000

Could someone please give me some guidance on what may be causing this problem, and where I can find what kind of errors are occurring?

Thank you.|||

Thanks Shankar,

Do you know of any remaining conflicts in the database that I could resolve to eliminate this problem?

Thank you,
Garry

Changing the server's collation would be the more reliable way if that is a choice.

Thank you,

Shankar
Program Manager, Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights|||

Hi Shankar,

Thanks,

Roshan.

Changing collation for all columns

I need to change the collation for my database from
SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
Is the statement "ALTER DATABASE MyDatabase COLLATE
SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
fields (how to do it)?
Any ideas? Any precautions to take?
Thanks!
Changing the collation on the database level affects everything beneath it -
fields included
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>I need to change the collation for my database from
>SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
> Is the statement "ALTER DATABASE MyDatabase COLLATE
> SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
> fields (how to do it)?
> Any ideas? Any precautions to take?
> Thanks!
|||> Changing the collation on the database level affects everything beneath it - fields included
That is *not* correct, I'm afraid. Here's a simple repro which demonstrates that changing db
collation *does not* modify your existing data:
USE master
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
GO
CREATE DATABASE x_coll_test COLLATE Albanian_CI_AS
GO
USE x_coll_test
GO
CREATE TABLE t(c1 varchar(5))
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
USE master
GO
ALTER DATABASE x_coll_test COLLATE Croatian_CI_AS
USE x_coll_test
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
GO
USE master
GO
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:O6ZvAL$0HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Changing the collation on the database level affects everything beneath it - fields included
> "Gaspar" <gaspar@.no-reply.com> wrote in message news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>
sql

Changing collation for all columns

I need to change the collation for my database from
SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
Is the statement "ALTER DATABASE MyDatabase COLLATE
SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
fields (how to do it)?
Any ideas? Any precautions to take?
Thanks!Changing the collation on the database level affects everything beneath it -
fields included
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>I need to change the collation for my database from
>SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
> Is the statement "ALTER DATABASE MyDatabase COLLATE
> SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
> fields (how to do it)?
> Any ideas? Any precautions to take?
> Thanks!|||> Changing the collation on the database level affects everything beneath it
- fields included
That is *not* correct, I'm afraid. Here's a simple repro which demonstrates
that changing db
collation *does not* modify your existing data:
USE master
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
GO
CREATE DATABASE x_coll_test COLLATE Albanian_CI_AS
GO
USE x_coll_test
GO
CREATE TABLE t(c1 varchar(5))
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
USE master
GO
ALTER DATABASE x_coll_test COLLATE Croatian_CI_AS
USE x_coll_test
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
GO
USE master
GO
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:O6ZvAL$0HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Changing the collation on the database level affects everything beneath it
- fields included
> "Gaspar" <gaspar@.no-reply.com> wrote in message news:OJRh5B40HHA.6072@.TK2M
SFTNGP03.phx.gbl...
>

Changing collation for all columns

I need to change the collation for my database from
SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
Is the statement "ALTER DATABASE MyDatabase COLLATE
SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
fields (how to do it)?
Any ideas? Any precautions to take?
Thanks!Changing the collation on the database level affects everything beneath it -
fields included
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>I need to change the collation for my database from
>SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
> Is the statement "ALTER DATABASE MyDatabase COLLATE
> SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
> fields (how to do it)?
> Any ideas? Any precautions to take?
> Thanks!|||> Changing the collation on the database level affects everything beneath it - fields included
That is *not* correct, I'm afraid. Here's a simple repro which demonstrates that changing db
collation *does not* modify your existing data:
USE master
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
GO
CREATE DATABASE x_coll_test COLLATE Albanian_CI_AS
GO
USE x_coll_test
GO
CREATE TABLE t(c1 varchar(5))
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
USE master
GO
ALTER DATABASE x_coll_test COLLATE Croatian_CI_AS
USE x_coll_test
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
GO
USE master
GO
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:O6ZvAL$0HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Changing the collation on the database level affects everything beneath it - fields included
> "Gaspar" <gaspar@.no-reply.com> wrote in message news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>>I need to change the collation for my database from SQL_Latin1_General_CP1_CI_ASto
>>SQL_Latin1_General_CP1_CI_AI
>> Is the statement "ALTER DATABASE MyDatabase COLLATE SQL_Latin1_General_CP1_CI_AI" enough or
>> should I change every existing fields (how to do it)?
>> Any ideas? Any precautions to take?
>> Thanks!
>

Changing collation -- URGENT

Hi,
I need an urgent help.
I have a database backup file with 30,000 tables, which has a particular collation SQL_Latin1_General_CP1_CI_AS.
Now I need to restore this database on a different server (srv2), but with different collation Latin1_General_BIN.
So I created a database on srv2 with Latin1_General_BIN collation. But When I tried to restore it from the backup file, am getting all the tables with SQL_Latin1_General_CP1_CI_AS collation. I tried to change the database collation on srv2 to Latin1_General_BIN , but still the collation is the old one.
Can anybody provide me the solution to this
Thank
GYKGyk
BOL says
You can also use the COLLATE clause on an ALTER DATABASE to change the
default collation of the database:
ALTER DATABASE MyDatabase COLLATE French_CI_AS
Altering the default collation of a database does not change the collations
of the columns in any existing user-defined tables. These can be changed
with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement changes:
CREATE TABLE MyTable
(PrimaryKey int PRIMARY KEY,
CharCol varchar(10) COLLATE French_CI_AS NOT NULL
)
GO
ALTER TABLE MyTable ALTER COLUMN CharCol
varchar(10)COLLATE Latin1_General_CI_AS NOT NULL
GO
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:DCC92136-EE54-41F7-83ED-A07AC7BADDCC@.microsoft.com...
> Hi,
> I need an urgent help.
> I have a database backup file with 30,000 tables, which has a particular
collation SQL_Latin1_General_CP1_CI_AS.
> Now I need to restore this database on a different server (srv2), but with
different collation Latin1_General_BIN.
> So I created a database on srv2 with Latin1_General_BIN collation. But
When I tried to restore it from the backup file, am getting all the tables
with SQL_Latin1_General_CP1_CI_AS collation. I tried to change the database
collation on srv2 to Latin1_General_BIN , but still the collation is the old
one.
> Can anybody provide me the solution to this?
> Thanks
> GYK|||You can use the following script to change the collations of your columns,
but note that it is not really production quality code and that there are a
few issues with it. One of them is that you will see quite a lot of
duplicate primary key constraints create statements, which you will have to
remove manually.
--
Jacco Schalkwijk
SQL Server MVP
/*
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_problems
- makes an assessment of how difficult it would be to change
collation in-place
2. sp_generate_index_constraint_script_for_collation_change
- outputs a CREATE INDEX/ALTER TABLE script to recreate indexes &
constraints
3. sp_generate_drop_cmds_for_collation_change
- outputs ALTER TABLE DROP CONSTRAINT and DROP INDEX commands
4. sp_generate_alter_cmds_for_collation_change
- 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_problems
sp_generate_index_constraint_script_for_collation_change
sp_generate_drop_cmds_for_collation_change
sp_generate_alter_cmds_for_collation_change
2. Change to the database with the collation that is to be modified, and run
the proc
sp_check_for_common_collation_change_problems. 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_problems
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_problems,
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_script_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_collation_change
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_collation_change
@.newcollationname = 'SQL_Latin1_General_CP437_CI_AS'
7. Address any problems identified by
sp_check_for_common_collation_change_problems.
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_collation_change_problems') IS NOT NULL
DROP PROC sp_check_for_common_collation_change_problems
GO
CREATE PROCEDURE sp_check_for_common_collation_change_problems 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,'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'
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_script_for_collation_change'
PRINT ' sp_generate_drop_cmds_for_collation_change '
PRINT ''
PRINT ''
END
GO
IF OBJECT_ID ('sp_generate_index_constraint_script_for_collation_change') IS
NOT NULL
DROP PROC sp_generate_index_constraint_script_for_collation_change
GO
CREATE PROC sp_generate_index_constraint_script_for_collation_change
-- Output file to create (default:
'C:\<DBNAME>_IDX_CONSTRAINT_TRANSFER_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_ObjectPermissions int
DECLARE @.SQLDMOScript_PrimaryObject int
DECLARE @.SQLDMOScript_ClusteredIndexes int
DECLARE @.SQLDMOScript_Triggers int
DECLARE @.SQLDMOScript_DatabasePermissions 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_IncludeIfNotExists int
DECLARE @.SQLDMOScript_NonClusteredIndexes 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_TimestampToBinary 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_AllConstraints int
DECLARE @.SQLDMOScript_DRI_All int
DECLARE @.SQLDMOScript_DRIWithNoCheck int
DECLARE @.SQLDMOScript_NoIdentity int
DECLARE @.SQLDMOScript_UseQuotedIdentifiers 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_OnlyUserTriggers int
DECLARE @.SQLDMOScript2_EncryptPWD int
DECLARE @.SQLDMOScript2_SeparateXPs int
-- SQLDMO_SCRIPT_TYPE values
SET @.SQLDMOScript_Default = 4
SET @.SQLDMOScript_Drops = 1
SET @.SQLDMOScript_ObjectPermissions = 2
SET @.SQLDMOScript_PrimaryObject = 4
SET @.SQLDMOScript_ClusteredIndexes = 8
SET @.SQLDMOScript_Triggers = 16
SET @.SQLDMOScript_DatabasePermissions = 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_IncludeIfNotExists = 4096
SET @.SQLDMOScript_NonClusteredIndexes = 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_TimestampToBinary = 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_AllConstraints = 520093696
SET @.SQLDMOScript_DRI_All = 532676608
SET @.SQLDMOScript_DRIWithNoCheck = 536870912
SET @.SQLDMOScript_NoIdentity = 1073741824
SET @.SQLDMOScript_UseQuotedIdentifiers = -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_OnlyUserTriggers = 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','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
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_script_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_script_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_OwnerQualify | @.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_collation_change') IS NOT NULL
DROP PROC sp_generate_drop_cmds_for_collation_change
GO
CREATE PROCEDURE sp_generate_drop_cmds_for_collation_change 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_script_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_collation_change') IS NOT NULL
DROP PROC sp_generate_alter_cmds_for_collation_change
GO
CREATE PROCEDURE sp_generate_alter_cmds_for_collation_change
@.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'',''nvarchar'',''text'',''ntext'') '
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
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:DCC92136-EE54-41F7-83ED-A07AC7BADDCC@.microsoft.com...
> Hi,
> I need an urgent help.
> I have a database backup file with 30,000 tables, which has a particular
collation SQL_Latin1_General_CP1_CI_AS.
> Now I need to restore this database on a different server (srv2), but with
different collation Latin1_General_BIN.
> So I created a database on srv2 with Latin1_General_BIN collation. But
When I tried to restore it from the backup file, am getting all the tables
with SQL_Latin1_General_CP1_CI_AS collation. I tried to change the database
collation on srv2 to Latin1_General_BIN , but still the collation is the old
one.
> Can anybody provide me the solution to this?
> Thanks
> GYK|||Thanks Uri and Jacco...
Jacco, I tried your script... but it doesn't work when indexes are created on the columns... it gives error of identity... So I was thinking of generating the database script from the old database, into a text file. then find and replace the collation and then run this new script to create the new database with new collation. Am trying that out, lets see if this works.
thank
GYK|||Can you post the exact error message you get. As I stated in my previous
post there can be quite a few issues with this script, although most of them
can be resolved reasonably easily.
--
Jacco Schalkwijk
SQL Server MVP
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:0D16CC56-47B9-4BF0-9FA8-C4FD99F40B29@.microsoft.com...
> Thanks Uri and Jacco....
> Jacco, I tried your script... but it doesn't work when indexes are
created on the columns... it gives error of identity... So I was thinking
of generating the database script from the old database, into a text file.
then find and replace the collation and then run this new script to create
the new database with new collation. Am trying that out, lets see if this
works..
> thanks
> GYK

Tuesday, March 27, 2012

Changing column collation

Changing column collation

Changing collation when installing SQL2000

Changing Collation SQL 2000 sp4

Changing Collation SQL 2000 sp4

Changing Collation SQL 2000 sp4

Changing collation on server

Changing collation on server

Changing collation on server

Sunday, March 25, 2012

Changing COLLATION on a table

Changing COLLATION on a table

Changing COLLATION on a table

Changing Collation on a Cluster

Changing Collation in SQL Server 2000

Changing collation in SQL 2000

Changing collation in SQL 2000

Changing collation for all columns

Changing collation for all columns

Changing collation for all columns

Changing collation -- URGENT

change MSDB owner

Blog Archive

About Me