November 17, 2010 at 9:41 am
Michael Meierruth (11/17/2010)
eduardo.pin (11/17/2010)
Stop the running server.2. Open a Command Prompt and go to the SQL Server directory.
3. Type:
sqlservr -m -T4022 -T3659 -q"new collation"
Example: sqlservr -m -T4022 -T3659 -q"latin1_general_ci_ai"
I don't know How, but it works just fine. Easy, clean, fast.
Anybody knows how this command works?
I think that it is a good case for an article.
I think this only changes the collation of the server instance. And I don't see in your example where you are telling it which instance you want this for. In any case, I tried it on SS2005 and I get a very ugly error message.
To change the collation of a database, I agree with Gail, there is only one way: the hard way. I had to do it once:
1. delete constraints and indexes,
2. issue the magic command 'alter database NAME collate ...'
3. alter the varchar columns of all tables
4. recreate the constraints and indexes
Step 3 can be fairly easily automated by generating a script that reads syscolumns/sysobjects.
For steps 1 and 4 I used Management Studio and manually did a
a. Script Index as/CREATE to/DROP to
b. Script Constraint as/CREATE to/DROP to
for each Index and Constraint after having examined if they involve a varchar column.
Each instance is installed in a different directory. You have to issue the command on the instance dir you want to make the changes.
All databases and all objects collation are changed.
I've used it several times on our databases with no problems.
November 17, 2010 at 11:33 am
Will the following sequence work with your magic command in place of the 'alter database' command?
1. create a new database COLL with collation Latin1_General_BIN
2. create a table and load a row of data:
create table t1(c1 varchar(10))
insert into t1 values('row1')
3. change the collation of the database
alter database COLL collate Latin1_General_CI_AS
4. create another table and load a row of data:
create table t2(c2 varchar(10))
insert into t2 values('row1')
5. the following query will fail:
select t1.c1
from t1
join t2 on t1.c1=t2.c2
the error message being:
Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN" in the equal to operation.
November 17, 2010 at 4:20 pm
Michael Meierruth (11/17/2010)
Will the following sequence work with your magic command in place of the 'alter database' command?
Sure! That what it does. It will change collation of all active databases to the one specified in the command line.
November 17, 2010 at 10:29 pm
Looks like this collation change (db: AdventureWorks) to SQL_Latin1_General_CP1_CI_AS is hell like a task. Dont understand why MSFT can provide simpler solution? appreciate everybody, if there is a simple procedure to do this task. This is taking my mood out of it...
November 17, 2010 at 11:29 pm
du.pereira (11/17/2010)
Michael Meierruth (11/17/2010)
Will the following sequence work with your magic command in place of the 'alter database' command?Sure! That what it does. It will change collation of all active databases to the one specified in the command line.
So you are saying that with the magic command for changing collations of all databases within a sql server instance, the collation of the varchar column in table t1 gets changed automatically?
And for a more complex scenario, if you have indexes and constraints involving varchar columns (as I described earlier), these get changed automatically with the magic command?
November 18, 2010 at 2:30 am
Michael Meierruth (11/17/2010)
du.pereira (11/17/2010)
Michael Meierruth (11/17/2010)
Will the following sequence work with your magic command in place of the 'alter database' command?Sure! That what it does. It will change collation of all active databases to the one specified in the command line.
So you are saying that with the magic command for changing collations of all databases within a sql server instance, the collation of the varchar column in table t1 gets changed automatically?
And for a more complex scenario, if you have indexes and constraints involving varchar columns (as I described earlier), these get changed automatically with the magic command?
Changes everything. =)
November 18, 2010 at 11:08 pm
I chatted with a few friends on this.
The trace flags aren't necessary. 4022 ignores startup procedures. 3659 is used as part of installation.
The -q does change collation of everything. That said...
It is undocumented and unsupported. If you use this and it breaks your server, Microsoft support will offer no assistance.
Undocumented means not fully tested under all situations. I would strongly not recommend you use this on real-life databases unless you are willing to reinstall SQL and recreate (or restore) those databases.
The two people I know who tried using this had it fail. One got the following:
2010-11-18 23:23:40.76 spid7s Error: 3434, Severity: 20, State: 1.
2010-11-18 23:23:40.76 spid7s Cannot change sort order or locale.
An unexpected failure occurred while trying to reindex the server to a new collation. SQL Server is shutting down. Restart SQL Server to continue with the sort order unchanged. Diagnose and correct previous errors and then retry the operation.
The other got a stack dump.
This command should not be used. To quote someone from MS "This particular option is undocumented for a reason."
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 2, 2011 at 8:22 am
Just thought I'd add my resolution when changing server collation in SQL 2005 (thankfully before any user dbs created) so no hassle in rebuilding the system dbs)
I had window auth, so no use for sapwd. This worked for me:
[font="Courier New"]<Drive>:\<SQL Install DirPath>\servers>start /wait setup.exe /qb REINSTALL=SQL_Engine
REBUILDDATABASE=1 SQLSYSADMINACCOUNTS=BUILTIN\ADMINISTRATORS INSTANCENAME=MSSQL
SERVER SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS[/font]
Rgds,
qh
April 12, 2011 at 7:10 am
Guys and girls, this seems like an appropriate thread to tag onto.
My db and relevant SSIS processes have been developed on a local copy of SQL server collation Latin1_General_CI_AS. the database also as Latin1_General_CI_AS
I've copied the database to a test server which has a server collation of SQL_Latin1_General_CP1_CI_AS however the database has the existing collation of Latin1_General_CI_AS.
If however I run a query in the database, I get the following error:
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
....... the query is referencing only tables within the 1 database so I thought collation should not come into it?
Any suggestions on how I work around or resolve this?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
April 12, 2011 at 8:36 am
Please post new questions in a new thread. Thank you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 13, 2011 at 10:48 pm
I got the same problem...
The better way is to reinstall again and change the collation during the installation itself....
sunilreddy
http://msbimentalist.wordpress.com 🙂
August 6, 2011 at 8:34 am
For a single database this codeproject solution (updated for SQL 2005) may still be worth a look:
November 16, 2011 at 5:22 pm
I tried this solution to change my collation from Latin1_General_CI_AS to SQL_Latin1_General_CP1_CI_AS and it did not work. I did not get any errors, but it did not work nonetheless. Please advise.
November 16, 2011 at 5:29 pm
To be precise, the solution of running the start ... settup.exe .. SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS to rebuild the instance.
November 30, 2011 at 2:46 pm
I had a SQL Server 2005 installation where the server was defined with Latin1_General_CI_AI and databases defined with SQL_Latin1_General_CP1_CI_AS (I didn't install it and don't know how it got that way). The client was receiving the "Cannot resolve the collation conflict between" error while accessing their application.
I detached the databases defined with the correct collation, shutdown SQL Server and associated services, ran
sqlservr -m -T4022 -T3659 -q"SQL_Latin1_General_CP1_CI_AS"
restarted the server and associated services, reattached the databases, and everything worked perfectly!
Startup option -m forces single user mode.
Trace flag 3659 allows logging all errors to SQL Server logs.
Trace flag 4022 forces SQL Server to skip startup stored procedures (if you have any).
Startup option -q rebuilds all databases and contained objects into the specified collation, without reinstalling the instance.
Per link: http://spaghettidba.com/tag/trace-flags/
Viewing 15 posts - 61 through 75 (of 76 total)
You must be logged in to reply to this topic. Login to reply