|
Hi,
I'm still waiting for the finish of the executing of the query, so I can't say if it will work for me. I hope...
Just a suggestion, (could have done it myself, but didn't think of it at that moment  ...): perhaps putting some print messages in between, eg after all the triggers are finished, after all the drops are finished, after every 10 alter tables, after the last alter table, and so one.
Than you have an idea where in the query you are ?
|
|
|
|
|
22.5 hours later, I'm still waiting... Hope it will end anytime soon ?
|
|
|
|
|
I used the fork:
[^]
I suggest merging that all into a single project on github
|
|
|
|
|
I have added the source code for this project to GitHub and hope to integrate some some community contributions soon
Alex Baker
|
|
|
|
|
Script failed for schema bound views. Dropping and re-creating views fixed the issue. Worked without errors for SQL Server 2014.
Great tool.
Thanks
modified 31-Mar-16 2:04am.
|
|
|
|
|
Hi
First GREAT tool - saved me hours of work!
One strange thing is that in just 3 out of about 1000 tables, it decided to double the size of a few varchar columns.
Other than that - worked like a dream.
|
|
|
|
|
I haven't had a chance to look at this project for many years as I have been unable to sign in to the site. Somehow the magic of LastPass somehow found the information I needed burried somewhere.
I put this project together as a result of some work frustrations, I never expected it to still have relevance 10 years later. I will take a look at the various comments people have added and see what I can do about putting together an updated version.
Alex
|
|
|
|
|
Hello Alex,
your project is still used. Right now I am struggling with collation problem and finding your tool is just amazing and a real timesaver.
I still am working on it because of some small errors (Typographical error on the declaration of the cursor (c instead of C) in the "202 Create Table" script,
and other stuff  .
Anyway thank you for being the first to have shared it.
Best Regards
|
|
|
|
|
I used this tool to set our MSSQL 2012 confluence database to SQL_Latin1_General_CP1_CS_AS.
It works like a charm, but keep in mind that you do not attempt to use this tool against a production site MSSQL server, it will leave it in single user mode after the change.
I'd like to express my gratitude to Alex, and anyone who has contributed to the cause.
Thank you guys!
|
|
|
|
|
We have a production SQL Server 2005 DB from Confluence 3.5 and earlier (now at 5.1.3) that has char, varchar and text instead of their "n" counterparts.
That is sufficient for accented characters and we decided we keep it that way. (Asian scripts go somewhere else.)
But we need to change collation from the default CI to CS, because it is enforced from Confluence 5.5.3 on and we want to upgrade to 5.5.6.
I tried the suggested
ALTER DATABASE community COLLATE SQL_Latin1_General_CP1_CS_AS
and SQL Server 2012 / Management Studio executed it without complaining.
I had thought it would complain that the character-based indices need treatment.
Do you know if there is such a fundamental difference between varchar (our DB) and nvarchar (normal 5.x DB) indices that the one goes without a hitch and the other needs your script?
Thanks
G.
PS: There was a computed field CONTENT.TITLE_LOWER including index that did get in the way, but as I only found it in our DB stemming from 3.5 I simply got rid of it.
|
|
|
|
|
Just realized that I could do a simple test (DB) not to mess with a Confluence schema.
Turns out SS 2012 MS doesn´t complain when you change collation from CI default to CS when there are indices on nchar and nvarchar.
|
|
|
|
|
I needed to convert a Confluence database, worked like a charm. Thanks for sharing this tool!
|
|
|
|
|
Hi Alex, thanks for excelent code! Its amazing !
When I execute the script in my database, those messages appears in result:
Warning! The maximum key length is 900 bytes. The index 'GXGCACHECHAVE' has maximum length of 3000 bytes. For some combination of large values, the insert/update operation will fail.
Msg 1505, Level 16, State 1, Line 2
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.GCONSSQL' and the index name 'PKGCONSSQL'. The duplicate key value is (1, F, PREVISÃOPAG).
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Those errors appeas cause I change collation from AS (Accent sensitive) to AI (Accent Insensitive).
Then PREVISAOPAG and PREVISÃOPAG (with Ã) returns the same information; in a UNIQUE KEY COLUMN, is duplicate key.
In my case, occurs only 2 little situations. I be afraid in a database with many FKs in many tables.
After I solve de duplicated keys, I run the rest of the script.
But another problem appears: All my Views stays with the old collation. The way to solve this is recompile them.
Maybe a ALTER VIEW SCRIPT for all of them would be nice. I try to generate an CREATE VIEW script for All VIEWS from MMSM tools and substitue CREATE by ALTER, but doesn´t works.
Thanks a lot!
modified 8-Aug-14 9:52am.
|
|
|
|
|
I ran this on an 80g database set to a funky collation. Took 9hr 15min with a 100% success rate. Thank you so much for sharing your knowledge!
|
|
|
|
|
Hi Alex,
Thanks for a very useful product.
I had to add support for schema bound views so am contributing the sql code template I created below for you.
Note that rather than drop the views and encounter dependency issues, I simply remove the schema binding
from views by generating ALTER statements. The segment has to be executed after indexes are dropped in case the view has indices. I've named both scripts so that they sort into the correct position although it does mean the numbering is at odds with your original. To make any further additions easier perhaps you could consider 4 digit numbers for any update you create.
Regards
Alan Nicholas
0111 Disable Schema binding.2005.sql
<br />
declare @name sysname,<br />
@id int,<br />
@id_last int,<br />
@last sysname,<br />
@owner sysname,<br />
@test_text nvarchar(4000),<br />
@pos_create_fn int,<br />
@pos_comment_start int,<br />
@pos_comment_end int,<br />
@offset int,<br />
@pos_function_name int,<br />
@pos_object_name int,<br />
@c cursor,<br />
@sql_segment nvarchar(4000),<br />
@text_ptr binary(16),<br />
@ansi_nulls nvarchar(100)<br />
<br />
set @id_last =0<br />
<br />
set @C = cursor for<br />
select o.name as functionName, <br />
o.id,<br />
sc.text,<br />
u.name,<br />
'SET ANSI_NULLS ' + case when OBJECTPROPERTY(o.id,'IsAnsiNullsOn') =1 then 'ON' else 'OFF' end as [AnsiNulls]<br />
from sysobjects o<br />
join syscomments sc<br />
on o.id = sc.id<br />
join sysusers u<br />
on u.uid = o.uid<br />
where objectproperty(o.id,'IsView') =1<br />
and objectproperty(o.id,'IsSchemaBound')=1 <br />
and objectproperty(o.id,'IsMSShipped')=0 <br />
order by o.id, <br />
sc.colid <br />
<br />
<br />
<br />
open @C<br />
fetch next from @C into @name, @id, @sql_segment, @owner, @ansi_nulls<br />
while @@Fetch_Status=0<br />
begin<br />
if @id<>@id_last<br />
begin<br />
--add the ansi nulls setting<br />
insert into #sql (sql) values (@ansi_nulls)<br />
<br />
--add a row for our data<br />
insert into #sql (sql) values ('')<br />
<br />
--get a text pointer<br />
SELECT @text_ptr = TEXTPTR(sql) FROM #sql where id = (select max(id) from #sql)<br />
<br />
set @id_last =@id<br />
<br />
set @pos_create_fn = patindex('%with%schemabinding%', @sql_segment collate latin1_general_ci_ai)<br />
while @pos_create_fn>0<br />
begin<br />
set @pos_comment_end = charindex('schemabinding',@sql_segment,@pos_create_fn) + len('schemabinding')<br />
set @sql_segment = stuff(@sql_segment,@pos_create_fn,@pos_comment_end-@pos_create_fn,' ')<br />
set @pos_create_fn = patindex('%with%schemabinding%', @sql_segment collate latin1_general_ci_ai)<br />
end<br />
<br />
set @pos_create_fn = patindex('%create%view%', @sql_segment collate latin1_general_ci_ai)<br />
while @pos_create_fn>0<br />
begin<br />
set @pos_comment_end = charindex('view',@sql_segment,@pos_create_fn) + len('view')<br />
set @sql_segment = stuff(@sql_segment,@pos_create_fn,@pos_comment_end-@pos_create_fn,'alter view')<br />
set @pos_create_fn = patindex('%create%view%', @sql_segment collate latin1_general_ci_ai)<br />
end<br />
<br />
end<br />
<br />
UPDATETEXT #sql.sql @text_ptr NULL 0 @sql_segment<br />
<br />
fetch next from @C into @name, @id, @sql_segment, @owner, @ansi_nulls<br />
end<br />
Close @C<br />
deallocate @C<br />
2011 Enable Schema binding.2005.sql
<br />
declare @name sysname,<br />
@id int,<br />
@id_last int,<br />
@last sysname,<br />
@owner sysname,<br />
@test_text nvarchar(4000),<br />
@pos_create_fn int,<br />
@pos_comment_start int,<br />
@pos_comment_end int,<br />
@offset int,<br />
@pos_function_name int,<br />
@pos_object_name int,<br />
@c cursor,<br />
@sql_segment nvarchar(4000),<br />
@text_ptr binary(16),<br />
@ansi_nulls nvarchar(100)<br />
<br />
set @id_last =0<br />
<br />
set @C = cursor for<br />
select o.name as functionName, <br />
o.id,<br />
sc.text,<br />
u.name,<br />
'SET ANSI_NULLS ' + case when OBJECTPROPERTY(o.id,'IsAnsiNullsOn') =1 then 'ON' else 'OFF' end as [AnsiNulls]<br />
from sysobjects o<br />
join syscomments sc<br />
on o.id = sc.id<br />
join sysusers u<br />
on u.uid = o.uid<br />
where objectproperty(o.id,'IsView') =1<br />
and objectproperty(o.id,'IsSchemaBound')=1 <br />
and objectproperty(o.id,'IsMSShipped')=0 <br />
order by o.id, <br />
sc.colid <br />
<br />
<br />
open @C<br />
fetch next from @C into @name, @id, @sql_segment, @owner, @ansi_nulls<br />
while @@Fetch_Status=0<br />
begin<br />
if @id<>@id_last<br />
begin<br />
--add the ansi nulls setting<br />
insert into #sql (sql) values (@ansi_nulls)<br />
<br />
--add a row for our data<br />
set @sql_segment = replace(@sql_segment,'CREATE ' COLLATE Latin1_General_CS_AS ,'ALTER ')<br />
insert into #sql (sql) values (@sql_segment)<br />
end<br />
<br />
fetch next from @C into @name, @id, @sql_segment, @owner, @ansi_nulls<br />
end<br />
Close @C<br />
deallocate @C<br />
|
|
|
|
|
 Brilliant piece of work! I have 7 databases to add to an enormous instance. Some of the new DBs contain hundreds of GBs and hundreds of tables.
Q: (How) Can I run several instances of the tool on one instance simultaneously?
Q: How does it deal with views? It doesn't seem to change view COLLATEs. This may be an issue with the next phase of my re-collation project.
modified 18-Jul-13 3:52am.
|
|
|
|
|
Awsome, saved me hours of pain. Cheers.
|
|
|
|
|
Seriously excellent program, worked (almost!) perfectly for me. I had to make one ammendment to the SQL (I was running this on SQL Server 2008). In the Create Indexes sql file for 2005 (which works for 2008 too), i added the following to the filters:
and i.is_hypothetical = 0
I had an issue where a lot of hypothetical indexes (created by the DTA - Tuning Advisor) were then being created by the application, when hypotheticals are not actually created indexes on the tables. To be honest though, i should just remove them from the database, so i am not marking the application down for it! Just a piece of advice for anyone who might encounter similar problems.
Thanks for the awesome work.
modified 27-Jun-13 10:37am.
|
|
|
|
|
Works perfectly even on 2008 R2!
|
|
|
|
|
It very useful for me thanks alex for U're help
|
|
|
|
|
The schema in the disable trigger and drop function statements is wrong.
Here is the corrected version
insert into #sql (sql)
select 'alter table ['+SCHEMA_NAME(u2.uid)+'].[' + o2.name + '] disable trigger [' +o1.name+']'
from sysobjects o1
join sysobjects o2
on o1.parent_obj = o2.id
join sysusers u2
on o2.uid = u2.uid
where o1.type = 'TR'
and OBJECTPROPERTY(o1.id,'ExecIsTriggerDisabled')=0
and OBJECTPROPERTY(o2.id, 'IsTable')=1
<pre lang="sql">
insert into #sql (sql)
select 'DROP FUNCTION ['+ SCHEMA_NAME(u.uid)+ '].['+o.name+']'
from sysobjects o
join sysusers u
on o.uid = u.uid
where objectproperty(id,'IsMSShipped')=0
and objectproperty(id,'IsTableFunction')=1
|
|
|
|
|
Thank you very much for this excellent script Alex! It saved me many hours.
|
|
|
|
|
Thanks!!!! Works fine in SQL2008R2
|
|
|
|
|
Thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Works fine in SQL2008R2
|
|
|
|
|
If you have a filtered index, Collatioin Changer doesn't script the Where clause of the filtered index. It tries to create the index without the filter.
In my case it was an Unique index, which was only valid with the filter. It showed the error. It was no fatal error and didnot stop the script.
Afterwards I could recreate the filtered index from the original database script.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
