September 26, 2008 at 8:46 am
Hi,
I tried to change the Server Collation of a Windows SQL Server 2005.
I googled this command:
"start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SQLCOLLATION= Latin1_General_CI_AS_KS_WS"
and executed it from the command prompt at “C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap”
before executing the command I also stoppt the services "MSSQLServer" and "MSSQLagent" (this was a hint from a colleague, but I also tried it without stopping those services)
when I hit Enter, everything seems normal (the Microsoft SQL Server Setup pops up, a dialog box stating that I have chosen to re-install and if i wish to proceed, I click "yes", the process runs through, the SQL Server Setup closes without any error message)
I reboot the Server (just to be sure^^)
And when I check the Server Collation, it is still the default one.
I realy hope one of you guys can help me with this, because I have no idea what the problem could be and why i not even get an error message. I am a real SQL noob, so please consider even the stupidest mistakes that i could have made...
thx in advance
September 26, 2008 at 9:29 am
1. I do not understand why do you need to change a collation level on server, since it is not changing collation level on existing databases anyway.
2. You do not need to stop services.
3. You copied a command blindly. I doubt that the instance name which you are running called MSSQLSERVER. If you have a default instance, just omit it in the command. Also, don't forget that SAPWD is not test, it is actual SA password for your database.
4. Read the Note after article.
September 26, 2008 at 9:50 am
like Glen said, changing the Server collation does not affect any databases, nor the individual columns that exist int he database...you need to handle those seperately.
Server collation...
each database collation...
Each column of type char/nchar,varchar/nvarch/text/ntext
..all have to be changed to get the desired affect.
I've got this snippet of code I've used lots of times..note how it's doping a TOP5, because the actual list in your database could be thousands of rows.
declare @collname varchar(128)
set @collname='SQL_Latin1_General_CP1_CI_AS'
SELECT 'ALTER DATABASE ' + db_name() + ' COLLATE ' + @collname
SELECT TOP 5
'ALTER TABLE ' + SYSOBJECTS.NAME + ' ALTER COLUMN ' + SYSCOLUMNS.NAME + ' '
+ TYPE_NAME(SYSCOLUMNS.XTYPE) + '(' + CONVERT(VARCHAR,SYSCOLUMNS.LENGTH) + ') '
+ ' COLLATE ' + @collname
+ CASE ISNULLABLE WHEN 0 THEN ' NOT NULL' WHEN 1 THEN ' NULL' END AS ALTERSTMT,
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE,
SYSCOLUMNS.LENGTH as length
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('char','varchar', 'nchar','nvarchar')
ORDER BY TBLNAME,COLNAME
SELECT TOP 5
'ALTER TABLE ' + SYSOBJECTS.NAME + ' ALTER COLUMN ' + SYSCOLUMNS.NAME + ' '
+ TYPE_NAME(SYSCOLUMNS.XTYPE)
+ ' COLLATE ' + @collname
+ CASE ISNULLABLE WHEN 0 THEN ' NOT NULL' WHEN 1 THEN ' NULL' END AS ALTERSTMT,
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE,
SYSCOLUMNS.LENGTH as length
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('text','ntext')
ORDER BY TBLNAME,COLNAME
Lowell
September 29, 2008 at 2:17 am
Hi Glen and Lowell,
first of all thanks for your replies!
why the Server collation?:
I intend to establish a MS SharePoint Server farm. The SQL is a fresh install and the SharePoint Database will be created after I changed the Server Collation. (so in this case changing the Server Collation would be sufficient, right?)
I recently joined this team, and we are "trial- an erroring" our way to an Installation-routine for this kind of Server farm (for later use in several locations of the company).
One of my colleagues found somewhere in some blog, that changing the Collation would be mandatory. Which is why I try it.
Note at the end of the "http://msdn.microsoft.com/en-us/library/ms179254.aspx" article:
If this would work, I think it could be even a better solution to my problems. I will google a way to try it.
Instance name:
As to prove my inexperience.. what exactly is the Instance name? Is it the name of the SQL server itself?
Stopping Services and rebooting:
Good to know that stopping the Services is not necessary. How about the reboot? Is that required?
SAPWD:
You are right! I just copied and pasted this command and at first we also had "SAPWD=Password" in it as well.
Meanwhile we succeeded in changing the Server Collation just by leaving the SAPWD part out.
But when we rebuilt the SQL server once again, it did not work.
Another member of our Team tried it several times as well and one of those attempts it worked.
But exactly this is my problem. We tried the same procedure over and over again. Sometimes it works but most of the times it doesn't (in either case without any "feedback" from the server).
Lowell, I'm sorry, but I don't quite understand the content of your script. (As i mentioned before I have almost no experience with SQL)
Do you still think it can solve my problems after what I have written?
thanks again
Kevin
October 1, 2008 at 1:35 am
can anyone comment on what I wrote?
Or did I write it to confusing? (sorry I am not a native speaker)
October 1, 2008 at 6:23 am
Kevin,
1. Instance name. Beginning from SQL server 2000, you can run multiple sets of services identifying Microsoft SQL server on the same machine. Microsoft calls such a set an instance. If you have only 1 SQL server instance running on your server it called default and does not have any name. Only one default instance allowed per server. The rest of instances have their names.
2. Server collation does not define anything except a default collation on the database which you will be creating afterwords. However, MS recommends using collation order in database creating T-SQL scripts (as well you can use this type of script in table scripts) anyway.
3. No reboots, no restarts.
October 1, 2008 at 7:33 am
Silly question:
If you don't have anything installed on the database server yet, then why not just wipe it and reinstall SQL Server from scratch and choose the collation setting you want then?
(Maybe I just missed something and you can't do that for a reason you already stated?)
October 2, 2008 at 1:31 am
Thanks again Glen! Not having to reboot saves me quite some time.
Maxer: No, You didn't miss anything. But the installation of the SQL server is performed by another department which is using an AEGIS role to do it. Our work right now shall provide the information to create yet another AEGIS role where everything is configured to run a SharePoint farm. So for now I am stuck with having to change the Collation.
I will try out the different approaches you guys mentioned.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply