|
Ian,
Thanks for the feedback - I will include your recommendations in to the next verion of the tool.
Alex Baker
|
|
|
|
|
Couple of issues for SS 2005
1. There's a column definition varchar(MAX) which gets scripted as varchar(-1) which SQL Query does not like.
2. You don't appear to drop any related full text indexes which means the collation changes fail.
Paul
|
|
|
|
|
Paul,
Firtsly thanks for your feedback.
I have noted in my article that i have not written code to handle full text indexes. Since you ask i will take a look and put something in for you although this may take me a few days as SQL server 2005 uses a completly different syntax that SQL server 2000.
The varchar(max) issue is a strange one - i only tested using nvarchar and it worked. Open the file script.sql and look for the comment "--nvarchar max functionality for SQL 2005" (line 413 I think). modify the next line to read
if @CC_Length COLLATE DATABASE_DEFAULT in ('0' , '-1' )
Now recompile the code.
Alex Baker
|
|
|
|
|
Alex, thanks for the response; don't worry about FTI just for me I managed to get around it myself.
BTW Good work on the project
Paul
|
|
|
|
|
Alex,
I tried to change the default collation of AdventureWorks DB on SQL Server Enterprise Edition + SP3 + Cumulative Update 4, and I got a lot of errors, even using "Drop All Keys and Constraints" option.
I skimmed the errors. Most of them happen because of no code for dropping and recreating "CHECK" constraints:
"The object 'CK_ProductVendor_StandardPrice' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation."
This CHECK has the next definition:
"ALTER TABLE [Purchasing].[ProductVendor] WITH CHECK ADD CONSTRAINT [CK_ProductVendor_StandardPrice] CHECK (([StandardPrice]>(0.00)))"
GO
It seems stupid that SQL Server requires dropping this constraint even though it does not reference to a text column.
Also, there were several errors because of XML indexes existing of a teabl like this one:
"Could not drop the primary key constraint 'PK_Contact_ContactID' because the table has an XML index."
I will recreate the DB myself using different collation, but you may be interested in these errors.
Thank you,
Vitali
|
|
|
|
|
Great utility when you need it. Very painful process when you have mismatched server/database/column collation. Is it possible to add the capability to set the collation to blank to it effectively picks up the default database collation -- i.e., don't explicitly set the value.
|
|
|
|
|
I am not sure what you are refering to - once the tool has been run the collation of every column in your database will be the database default regardless of the previous collation.
Perhaps you could describe the steps you have been through and the state the database is in after running the tool. I should then have a better idea of what your requirements are and can develop a solution for you.
Alex Baker
|
|
|
|
|
Thanks for the response. What I'm referring to is If you go into design view of an object in Enterprise Manager or Management studio the collation of a column is set to "database default". You can get a column to say this if you delete the collation text in a particular column and save. When you reopen design view it will show "database default". If it is set this way I can change the database collation and the columns will inherit the new collation automatically.
Your tool is helpful to fix a problem we have when a developer mistakenly scripts a database with collate statements and deployed it to a database/server with different collation. It would be nice to not have to go and run your tool each time the database collation changes to keep the database/columns in sync.
A concrete example of what we do is in development we use binary collation to ensure that our code/scripts/procs/etc. will work in a binary collated database which some enterprise apps such as Siebel require. In our test lab we run both binary and non-binary and in production we run non-binary collation since some applications (other than ours) don't work well with binary databases. As a result we often move promote dev - integration - qa - prod and sometimes a developer will have mistakenly set columns explicitly. If the collation changer application could set collate to the "database default" setting it makes this promotion trivial.
Hope this is clear enough.
Thanks.
-John
|
|
|
|
|
you say:
....When you reopen design view it will show "database default". If it is set this way I can change the database collation and the columns will inherit the new collation automatically.
This did not happen in SQL 2000 which is why i wrote this tool in the first place. This may happen in SQL 2005 - i have not tried it - I can see that the documentation on Alter Database [dbname] collate ... is definatly different to that for SQL 2000.
You have requested that I change the collation order of the database columns to database default - this is in fact what the collation changer does - see line 419 of the file script.sql in the source files provided with this sample.
You should find that if you run the tool against your database and select the "script only" option that the script file it generates does an "alter column <whatever> collate database_default" thus setting each column to have the database default collation. For example a section of a generated script file may look like this:
Alter table [dbo].[MeasureGroup] Alter Column [Format] [nvarchar] (5) COLLATE DATABASE_DEFAULT NULL<br />
<br />
Alter table [dbo].[MeasureGroup] Alter Column [Tag] [nvarchar] (100) COLLATE DATABASE_DEFAULT NULL
Hope this helps explain a little.
Alex Baker
|
|
|
|
|
Hi sir,
I run the app and the produced script to change my database into Latin1_General_BIN, the script was successful for there were no errors reported. however, when i check the database properties at SQL server 2000 Ent Manager it remained unchange (Latin1_General_CP1_CI_AS)
Please help.
Thanks.
Master
|
|
|
|
|
SQL Server enterprise manager does cache a lot of stuff - have you tried closing it down and starting it up again?
Alex
|
|
|
|
|
Yes Sir i tried that. i even shutdown my server.
|
|
|
|
|
And you chose the script and execute option not simply the script option?
If you are still experiencing issues have you tried selecting the script only option and copying the script in to query analyzer - does this work?
Alex Baker
|
|
|
|
|
I only chose the script only option. have not tried script and execute. do you recommend that?
*** i tried script and execute just now but i have encountered the issues on "Must Declare Variables" error. though i encountered the same error on "SCRIPT ONLY" option, i changed all variables affected with lowercase. i ran the script at SQL query analyzer it was successful though and has no errors, but still, the collation remains unchange.
-- modified at 20:44 Sunday 1st April, 2007
|
|
|
|
|
Mr. Baker any comment please...
|
|
|
|
|
My appologies for not getting back to you - the code project's e-mail notifications are somewhat spuradic and sometimes i do not get notified of posts.
Could you send me the script as it is generated by the tool - i can then try to identify the issues you are experiencing.
my e-mail is alexb at csl-uk.com
Alex Baker
|
|
|
|
|
I don't think it was worth the any comment ...
|
|
|
|
|
Hey Everyone!!!
Has anyone used this tool with SQL Server 2005??? If so did you hit any problems? I am Trying but maybe i am not giving the tool enough time to build the script because it doesnt seem to finish within about 45 min. Should i give it more time?
"FIRST TIMER" with this blog stuff!!! Thank you in advance!
Runnin_Blind***
Love this STUFF!
|
|
|
|
|
I have not really tested with SQL 2005 yet but it should work - if there are problems then you should get see errors. You will probably find your problem is because the application can not get an exclusive lock on the database.
Start the collection changer tool and then open query analyzer (on the master database) and use the SP_WHO stored procedure to idendify active connections that are open on the database you are trying to change. Use the "kill" statment to kill off the offending connections.
Alex
|
|
|
|
|
Thanks very much for this tool!
well done on sqlserver2000
|
|
|
|
|
I hada similar issue with SQl 2000. But it appeared that I had another script window open in the Query Analyser and connected to the DB I wanted change collation for. So double check in the Enterprise Manager that you don't have any other connections...
BTW, great tool.
|
|
|
|
|
Runnin_Blind***
Love this STUFF!
|
|
|
|
|
You, sir, are a genius. I was just about to start writing a plugin for a code generator I'm using to do just this when I came across your tool. You've saved me several hours of work, and for that I kiss you (err, a virtual kiss on the cheek, eh?).
______________ Marc Heiligers Not one shred of evidence supports the notion that life is serious
|
|
|
|
|
Thanks - flattery will get you a long way.
I have posted an update to the code project that addresses some of the issues mentioned in the posts below and adds some SQL 2005 support. It should be published in a few days
Alex
|
|
|
|
|
Server: Msg 170, Level 15, State 1, Line 78
Line 78: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 296
Line 296: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 304
Line 304: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 308
Line 308: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 384
Line 384: Incorrect syntax near 'COLLATE'.
Server: Msg 156, Level 15, State 1, Line 480
Incorrect syntax near the keyword 'else'.
Server: Msg 170, Level 15, State 1, Line 483
Line 483: Incorrect syntax near 'COLLATE'.
Server: Msg 170, Level 15, State 1, Line 486
Line 486: Incorrect syntax near 'COLLATE'.
Server: Msg 170, Level 15, State 1, Line 488
Line 488: Incorrect syntax near 'COLLATE'.
Server: Msg 170, Level 15, State 1, Line 545
Line 545: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 653
Line 653: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 655
Line 655: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 657
Line 657: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 658
Line 658: Incorrect syntax near 'collate'.
Server: Msg 170, Level 15, State 1, Line 665
Line 665: Incorrect syntax near 'collate'.
|
|
|
|