Video Screencast Help

fix collation sql 2008

Created: 09 Nov 2012 • Updated: 09 Nov 2012 | 9 comments
GertjanA's picture
This issue has been solved. See solution.

Has anyone ever created a script to change collation? I have been trying to follow the advice to use the MS script, in conjunction with the Symantec Article, but that fails every time.

 

Thanks.

Comments 9 CommentsJump to latest comment

ZeRoC00L's picture

Are you trying to change the collation set of the SQL server itself or on existing EV databases ?

If this response answers your concern, please mark it as a "solution"

GertjanA's picture

Hi ZeroCool.

Collation on the EV databases.

I am following (or trying to follow) the TechNote (using this MSDN script etc), but that fails.

I am no SQL expert, and there is no DBA available.

when running the 'changecollation' msdn script on the directory database, i get an error:

ALTER TABLE [dbo].[utvf_intParamParser] DROP CONSTRAINT [PK__utvf_int__3214EC0706F8B8D7]

Msg 4909, Level 16, State 1, Line 1

Cannot alter 'dbo.utvf_intParamParser' because it is not a table.

--DROP PK FAILED. SEE ERROR LOG FOR DETAILS.

Msg 50000, Level 16, State 1, Line 1378

DROP PK FAILED. SEE ERROR LOG FOR DETAILS.

I am  sure the 1st step (executing the DirectoryDB_10_Programmability.sql) , up to the first CREATE statement has completed succesfully.

Thx.

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS
Company: www.t2.nl

www.quadrotech-it.com

www.symantec.com/vision

Chau Tran's picture

I think your best bet is to reinstall SQL server with the correct Collation that match the EV databases. Assuming that ALL the EV databases have the same collations?

Depeding on which version SQL server etc. you can install the default setting and then run the SQL setup with some switches to change the SQL server collations. Take a look at this forum:

http://www.sqlservercentral.com/Forums/Topic269173-146-1.aspx

I had to do this not so long ago.

This in particular:

start /wait <DVD Drive>\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> SQLCOLLATION=<NewSystemCollation>

Detach all your databases and run the above to set it your EV SQL collations it should do it. It worked for me. oh and backup everything first :-)

Hope that helps.

Regards,

Chau Tran

ASC, STS, SCS and MCITP

ZeRoC00L's picture

Not sure if it's even supported to change the collation of an existing EV database. Why don't you change the collation of the SQL server ?

 

BTW:  Have found an article how to change the collaction of an existing database:

http://www.codeproject.com/Articles/302405/The-Easy-way-of-changing-Collation-of-all-Database

If this response answers your concern, please mark it as a "solution"

Jeff Shotton's picture

Gertjan,

The first part of the procedure clears out old stored procedures and other programmability objects. What you have there is a table valued function, and it is a new one for Enterprise Vault 10.

However, I found this in the DirectoryDB_10_Programmability.sql script for EV10 sp1, so maybe you haven't executed this properly. I haven't got EV10 base to check. Maybe you could post the script here...

 

If you cant get rid of this using the standard script, what you will need to do is create your own 'drop and create' script, which you can do from the SQL server management studio.

open SQL Management Studio > EnterprisevaultDirectory database > Programmability > Functions > Table Valued Functions > right click on dbo.utvf_intParamParser

Select 'script function as' > Drop and Create to > 'new query editor window'

Run the delete part, then try the conversion, then run the recreate part'

NB you may need to do this iteratively if there are several objects which have not been dropped properly.

 

You should not need to do this though. And if you do (legitimately) then please raise a support case to get the technote changed for the rest of us :)

Regards,

Jeff

 

Jeff Shotton

Principal Consultant

Adept-tec Ltd

Website: here

GertjanA's picture

Hi Jeff,

Mark Barefoot (if still at symantec) was working on an update. If you check, you will see the exact same question from me june 2011.... Here, Mark said they woudl update the document. We also discussed having a better document for users/dba's.

Darn, stuck again...

Changing collation of the SQL server is not an option, as it is already used to host other databases.

 

 

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS
Company: www.t2.nl

www.quadrotech-it.com

www.symantec.com/vision

Jeff Shotton's picture

Hi Gertjan,

No, Im afraid he isn't. He left around the same time I did...

Regards,

Jeff

Jeff Shotton

Principal Consultant

Adept-tec Ltd

Website: here

GertjanA's picture

Google is your friend. I found the below tool. It works 'almost' completely. I only had to drop 2 indexes, and recreate them.

I'm using this tool:

http://www.codeproject.com/Articles/12753/SQL-Server-2000-Collation-Changer

And yes, I am aware it states it is for SQL2000/2005, but I arrived at this tool from some forum where someone said it also worked on SQL2008.

Thank you, Gertjan, MCSE, MCITP,MCTS, SCS, STS
Company: www.t2.nl

www.quadrotech-it.com

www.symantec.com/vision

SOLUTION
ZeRoC00L's picture

Please not that the existing columns will not be changed and retain its current collation type, only the newly created objects will use this new collation type ! See the link i provided before.

If this response answers your concern, please mark it as a "solution"