|
Hi,
Congratulation for this tool.
I use it to change the collation for a database from a mssql 2000. and after all done I run the following script on database:
SELECT collation, collationid, count(*) AS count
FROM syscolumns
GROUP BY collation, collationid
and the result was:
collation collationid count
NULL NULL 1865
SQL_Latin1_General_CP1_CI_AI 906031112 1521
is something wrong with this tool or NULL collation is the same as database default?
|
|
|
|
|
i think null collation is the same as database_default in sql2000
Alex
|
|
|
|
|
I have the same feeling, that null collation is the same as database_default in mssql2000, but in this case can you change all collation to null? isn't better?
By the way, I have no errors in database after I run the tool even there is null in collation ...thanks for you work is really useful!
|
|
|
|
|
Hello,
This program is a godsend. Thank you very much. I built it from the source and ran it against a SQL 2000 database and received some errors for 3 variables that were not declared. Turned out it was just that they had caps when declared but not when they were used. I think they were @IX_objid,
@IX_objname, and a couple more. Plus the reference to USE [MASTER] generated an nonfatal error that MASTER could not be found. I made the vars consistently cased, changed the MASTER to lowercase, and it ran like a dream.
Thanks again for a wonderful contribution. I'll be trying it on SQL 2005 soon.
Scott
|
|
|
|
|
Scott,
Thanks for the feedback - i think someone else encountered this issue a few months back. I am currently in the process of integrating all the feedback in to a new version which should be ready in the next couple of weeks. Along with many bug fixes i will add functionality to rebuild full text indexes...
Alex
|
|
|
|
|
Nice work! I wish you prosperity, wealth and all the women you need, you deserve it!
A few things that I think is SQL2005-related:
Error 1:
Alter table [tablename[ alter column [columnname[ [my datatype] callate database default not null
Collate clause cannot be used on user-defined data types.
Seems to be a problem with the script and user defined data types.
Error 1:
alter table [table] disable trigger [table_insert_inseadOf]
Seems like the eminent script doesn't support InsteadOf-triggers on views. (since the parent is a view in this case ist hard to alter the table)
Error 3:
the object 'My own defult' is dependent on column 'a column'
Alter table drop column 'a column' failed because one or more objects access this column
Seemes like I have to sp_unbindefault the column before I can drop the column in 2005.
Hope you understand my explanation of the problems, if you can propose a fix to theese I will raise you above my newly becomed hero up to the stage of a semi-god...
Thanks!
Ola Carlander
|
|
|
|
|
I have been working on a new version of the collation changer in which i hope to have resolved the issues you mentioned above along with a number of others. Do you have some spare time where you could try it out? If so would it be possible to e-mail you the new version?
Do you have Visual Studio 2005 or .NET 2 installed?
Thanks
Alex Baker
|
|
|
|
|
Hi,
Sure I'd be happy to try it out. Just send it over (I was not able to email you my address but I think you can email me directly from this page?).
I have .net 2.0 installed on my machine and also on the server that I ran the script, not visual studio 2005 though. We have some developers here that do if you need me to test something on one of their computers?
BR
Ola
|
|
|
|
|
here is the convert project to C#
http://www.mediafire.com/?5zgdgg20xxg[^]
it is the exact manual conversion of the code.
compiled without error, but execution against my ms sql 2005 database an error occurred.
if anyone could solve it, it'd be welcomed.
Thanks
|
|
|
|
|
Sounds like you are republishing my work! You'd better fix it yourself.
Alex Baker
|
|
|
|
|
Yeah, i'm trying to fix it myself but was unable to do so.
Here's the msg:
Failed
Cannot insert the value NULL into column 'groupname', table 'tempdb.dbo.#spindtab_____________________________________0000000000003'; column does not allow nulls. INSERT fails.
happens to both the vb.net version as well as the c# version.
well, i still hope this does not offend you in any way.
i'm still acknowledging this program is written by you and you should get full credits for it, i'm just trying to contribute back to the community which i've taken something from
and hoping the c# version would help anyone out there who needs it as well.
this is a very useful tool and i did not create any other thread for it. i linked it back to this same thread where i thought it should be.
in any case if it really annoy you in anyway, give me a note and i'll take it down.
Thanks.
|
|
|
|
|
on SQL SERVER 2005 while mirroring, setting database in a single user mode doesn't work.
how ever I was brave enough to continue the script execution and everything went fine.
INCLUDING the mirror database. I am impressed.
thx and best regards...
Darko
|
|
|
|
|
I have used this utility to change the collation of 5 databases. It was successfull but the database size has increased to double. Can you please tell me what exactly went wrong or why the size has been doubled.
Chao,
Thanks for this utility.
Hamidch
|
|
|
|
|
You will probably find that the database size increased as temporary tables were created and indexes were reorganised. the following sql will shrink your database again. NOTE only run the first line of code if you understand its impact on your ability to do a point in time restore on your data.
backup log MyDatabase with truncate_only
dbcc shrinkdatabase (MyDatabase)
s
|
|
|
|
|
Worked beautifully for almost all of the database with a few minor errors as follows
Error Type 1:
Occured a few times throughout the script run. All these errors occured when creating functions.
CREATE function [dbo].fGetParameterValues] (@ParameterName as VarChar(255))
returns @Results table (ParameterValues varchar(255))
as
begin
insert @Results (ParameterValues)
select ParameterValue
from dbo.rb_Parameters
where spid = @@spid and ParameterName = @ParameterName
return
end
GO
102 - Incorrect syntax near ']'.
1087 - Must declare the table variable "@Results".
137 - Must declare the scalar variable "@ParameterName".
Notes:
The [ is missing from the [dbo].fGetParameterValues] section. Once added and script section rerun works fine.
Error Type 2:
Occured a few times during the script run. Unknown reason except that the occurances of this error followed the initial error above.
CREATE FUNCTION [dbo].[fCGTCapitalGains] ()
(Removed for brevity)
return
END
GO
924 - Database 'vfpsd_Network' is already open and can only have one user at a time.
Notes:
This error occured a few times after the initial error above. Just reran the script section and it worked fine.
|
|
|
|
|
As a side note that i forgot to put in my original post. It would be very handy if the sql that generated errors could be posted to a new text area to make it easier to review and correct and not miss anything.
|
|
|
|
|
Nice work you did with this app!
Found a small bug though
If a ntext or text column is NOT NULL it will be NULL after the running the tool.
This is because the @CC_NullText variable isn't used.
Change codeline 385 of Script.SQL to this to fix it:
if @CC_NullText = 'NOT NULL'
I tested it on SQL Server 2005.
|
|
|
|
|
Very useful tool...
Congratulations :->
Segundo Serrano P.
|
|
|
|
|
Well done for thinking of this... changing collation is never a nice task to do manually!
I have an error that popped up a few times:
Cannot alter or drop column 'DisplayName' because it is enabled for Full-Text Search.
Server: Msg 7614, Level 16, State 1, Line 2
So I guess it would be good if the script removed the full-text indexes and then re-created them.
Also, a minor GUI improvement would be if the server box was a dropdown that contained all detected SQL instances detected on the local network.
But great tool!
Cheers,
Lance
|
|
|
|
|
Thank you for this utility. It is very well done, and is exactly what I was looking for.
I had to make a couple minor changes in order for this to work in my environment. Although I plan to run this on a server whose default collation is CI_AS, I was testing on a server whose collation is CS_AS. Thus I needed to make four changes to the "script.sql" file, all related to running this on a case-sensitive system:
1. The @IX_ObjID variable is referenced using different mixes of case. I changed all references to be consistent.
2. Ditto for the @IX_ObjName variable.
3. Ditto for fn_idlast
4. Line 252, I downshifted the reference to the [MASTER] database, since in a CS environment the DB is named [master].
With those changes, the program worked perfectly.
Thanks so much!
----------------
BitBanger
|
|
|
|
|
Thanks for the feedback - i will include them in the next update which i will hopefully get round to soon.
Alex Baker
|
|
|
|
|
Alex,
One more thought.
Unfortunately, the application I am supporting depends on columnar position within a table (it does lots of "SELECT *"s). So the fact that text columns are moved to the end of the table is problematic for this application.
I found that, in SQL 2005, the ALTER TABLE statement works on text columns, so I changed your script a bit. It now looks to see if it's running on SQL Server 2005. Down below where it checks for a text column, it only executes that logic if it's a text (or ntext) column AND it's not running on SQL 2005.
This worked swimmingly.
Thanks again for such an outstanding piece of code.
----------------
BitBanger
|
|
|
|
|
Hi,
its a fantastic utility thanks very much.
I have a few questions becuase I want to change the collation for the entire server:
Is it possible to change the SQL2000 server default collation for any new databases ?
Is it possible to change the SQL2005 server default collation for any new databases ?
Should I alter the collation on the existing system databases or just leave them alone ?
I was thinking about master model msdb and temp dbs
thanks very much for a very useful utility
Mike
|
|
|
|
|
Changing the default collection for the server (and new databases) is pretty difficult - it can also cause issues. The tempdb has the server collation order so when you do some SFE code like:
create table #temp (something nvarchar(20))
the column in the temp table has a collection order of the temporary database NOT the collation order of the database you executed the create table statment in.
I am not sure if this applies to SQL2005 but it definatly does on SQL2000.
If you try to join using the something column you will get a mismatching collation error - you can put collate statments in the join but it gets messy - you can explicitly specify collection order in the create table statment by specifying database_default as the collection order.
Clearly your issue is what happens to all the old code you have? Will changing the collection order break a lot of stuff - i know i have experience this problem backing up a development database and sending it to a customer who has a different collection order on their server.
As for actually changing the collection order of the server - i am not sure about SQL 2005 - you may now be able to do it? SQL 2000 it is a reinstall job as you can not change the collection order of the system databases - i have seen some articles explaining how to execute some scripts to rebuild system databases but i beleive it will be easier to reinstall the server then reattach the user databases.
Alex Baker
|
|
|
|
|
Just a minor bug... I have some triggers that are attached to Views (INSTEAD OF INSERT, etc..). The code generates the following, which fails due to the fact that the view is not a table:
Alter table [myview_vw] disable trigger [insert_myview]
GO
For SQL 2000 you probably have to script a DROP/CREATE pair for these triggers as I'm not sure if you can enable/disable them. However, for SQL 2005 there is a new syntax feature which would solve this issue and greatly reduce the size of the script. Check out the DISABLE TRIGGER and ENABLE TRIGGER code as these are essentially shortcuts to disable/enable all triggers in the DB. They are used as follows:
DISABLE TRIGGER ALL ON dbname
ENABLE TRIGGER ALL ON dbname
Thanks for the code!
-Ian
|
|
|
|