EV9 SQL2005 changing collation

[GertjanA]

Hello all,

Customer is trying to change the collation, using the scipt provided by MS. (link follows shortly)

Script fails somewhere with below error:

ALTER TABLE [dbo].[ReportSettings] DROP CONSTRAINT [PK_ ReportSettings]
ALTER TABLE [dbo].[RetentionCategoryEntry] DROP CONSTRAINT [PK___12__10]
ALTER TABLE [dbo].[RetentionPolicy] DROP CONSTRAINT [PK_RetentionPolicy]
ALTER TABLE [dbo].[RetentionPolicyRuleMapping] DROP CONSTRAINT [PK_RetentionPolicyRuleMapping]
ALTER TABLE [dbo].[udf_TypeBitmaskConverter] DROP CONSTRAINT [PK__udf_TypeBitmaskC__550B8C31]
Msg 4909, Level 16, State 1, Line 1
Cannot alter 'dbo.udf_TypeBitmaskConverter' 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.

Does anyone know how to fix?

This is on the Directory database. EV9.0SP2

[GertjanA]

To SQL file: http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-components-postattachments/00-01-19-58-59/...

To Article: https://www-secure.symantec.com/connect/forums/change-collation-sql2005-ev9

[JesusWept3]

How the heck is it picking up a udf as a table?!?! I'll take a look at the script, might take some time cos I'm on the iPhone though :\

[MarkBarefoot]

udf_TypeBitmaskConverter is not a table, it's a function - not sure yet as to why we are trying this. The collation script that is referenced in http://www.symantec.com/docs/TECH55063 is the same script as referenced in your like above.
 

With regard to the article mentioned, the reason why this TN is wordy is there is a chunk of additional info in our document, like the dropping of temp tables that doesn't happen, and a few more bits. To be honest I would hope that EV customers use Symantec Technotes as opposed to anything else. If you feel that the TN is wrong or needs improvement then please let us know.

Could you dump the whole content of the SQL console output (if you still have it) into a .txt file? I am not sure as to why we are trying to perform a table operation on a function. Unfortunately I will be on vacation from today for 2 weeks, but hopefully a Symc bod (or other eagle eye) will see this and help you out.

[GertjanA]

All I have at the moment from customer is the following:

output.txt

[GertjanA]

Hello Mark,

Yes, the article is wordy indeed. Most EV-admins have knowledge of SQL, but I am not sure to what level. I for one am not an expert at all. I know what to look for, but this specific thing is way above my head.

I do appreciate that some 'additional' steps need to be taken, but have you had a not so experienced SQL person try it? That will no doubt show it is very difficult to perform the steps. I for one found the editing of the EV-sql to drop the table really scary, and hard to understand. It is a trial and error. Someone in the forum indicated to just run the script, without editing as Symantec indicates, which for him worked fine.

I find it not soo customer friendly if you (Symantec I mean) signal an issue with the database, point to a script from someone else, and then have the customer manually edit the script (keep fingers crossed), and than run it.

this specific customer fortunately has little users, and is able to perform these actions easily in office hours, but as he runs into issues, it is very hard to find expert help. I did advise him to contact either MS or Symantec Support, but as the scripts seem te be working ok for some customers, he also would like to be able to fix it himself. He does have a SQL person with more knowledge available after the weekend.

It would be nice if Symantec could provide a script (for SQL2005/2008) that would do exactly what is needed, without the customer needing to edit and run some 'custom' queries himself, without understanding what he is doing. Obviously, that might fall out of scope, but it would help in raising customer satisfaction ;) I also do understand that as this is caused by the underlying software used, it is easier to point to that vendor (MS in this case), but I have found Symantec to be reasonable and willing to help in most cases.

If you can start an internal discussion on this, I would be gratefull. I will also sent a mail to the PM for this (EMEA), so we might get some further information/knowledge/hints and tips.

My personal experience with the collation-issue deems from a few years ago. Luckily, I than had a very good DBA available, who was able to determine following the script what needed to be done, and made it work flawlessly. Alsoluckily, this most of the time is a one-time operation...

Thanks for the help! and Advise! enjoy your holidays!

[MarkBarefoot]

Hi Gertjan :)

This whole collation issue has been a real pain for Support and EV customers. Unfortunately there isn't an "easy" way of doing this without some manual intervention - due to the way SQL scripts are, and in this case it is a very important activity to get right. Thankfully this is normally a "when you upgrade" issue, and only affects those people who have different collations, so not everyone thankfully!!

I think I worked with you actually, a year or so ago as I remember your name and Dutch being part of the customer (say no more..). The big problem is the lack of integrity or rollback features with this script. It goes in, clears everything out, makes the changes and then put things back in place - if for any reason something goes wrong it can appear to work as I think it says "completed" in the last bit of the output. I did speak a while back about the requirement to get this improved, the script that is.

I think, at the current time, the focus should be on that TN as the script "is the script" and does the deed. I will ping out the question this end - can you email me here with the PM name, you can then let them know that you have spoken with me on here too (I guess it will be Glenn or Alex?)

[GertjanA]

I believe indeed you did, and it probably is more something like 2 years ago ;)

The PM is Glenn.

[GertjanA]

Not at customers site, but still trying to help him change the collation.

Customer has logged a case (414-943-247) to get assistance from support. Support told him he has SQL problems, and needs to talk to support.

The technote used (over and over again) states that 'support has scripts'

http://www.symantec.com/docs/TECH55063

"A uniform value for the collation is needed for all the Enterprise Vault databases and this is a permanent solution. The collation can be changed via scripts and Enterprise Vault Technical Support now have two different scripts available to make this update, depending on the version of SQL server (2000 or 2005) in use. The SQL Server 2000 script may be run on any version of Enterprise Vault up to and including 2007 sp3. The SQL Server 2005 script can be run on any version of Enterprise Vault databases. Both scripts can only be run on one database at a time"

If the script is referred to as like being from support, why is it not being supported? I do realize the script for SQL2005 is from an external source, but that should not stop support from supporting this. There must be an internal kb that outlines EXACTLY what needs to be done, even in 'non-dba' terms. That is the issue here. The document is all fine and dandy for an experienced dba, but how many small organizations have a dba?

Anyway. If anyone could either point me to a document that is more detailed (ie a non dba can use it), or anyone Symantec internal can look again at the mentioned case and inform me, or the customer, I would be gratefull. It should not be that hard should it?

Thanks,

[MarkBarefoot]

Hi Gertjan

I've emailed Glenn and a few more about this particular matter. I want to be honest here and just say what I feel about your comment fon non-dba stuff. As you are fully aware, SQL is at the heart of EV and I, personally, wouldn't feel comfortable having someone with no SQL experience performing operations on a SQL server, especially something like this.

With that said however, I do feel there can be improvements made in certain areas. I will PM you with my email address (though you probably already know it)

[GertjanA]

Hello Mark,

Yes yes, I know. However, especially in smaller environments, it most of the times is the System Administrator who has some Exchange knowledge that gets to manage EV and thus SQL.

The script that Symantec points to is a script written by someone at MS. Symantec's KB advises a customer to edit the EV specific SQL bits and bytes to drop tables etc.. For an admin that can keep ev running unless there are real problems, this is too technical.

It would be benifical that Symantec provides a more detailed document (ie instead of "copy and past everything up to the first select", show the part that needs to be copied, or add a 'example.txt' file of some sort.

Thanks again for your effort.

[MarkBarefoot]

Just a little update, nothing to get excited about. I've ran parts of the script (the gathering of what has a PK set) and we do set one on that UDF. I've asked the question if we are doing this, how does this affect this script....

It's the first time I can recall of seeing this. I checked the script against my EV10 lab, and there was only that one UDF which came back......will keep you posted...

I guess there is a workaround here, but I'm not overly keen on those!!

[MarkBarefoot]

Hey Gertjan

So, I think I have the answer here, and I am also going to see about getting this TN reviewed so it's easier to use....

3. Identify the ViewsStoredProcs programmability script for the database in question in the EV Install_Path 
 

In here it points out some .sql scripts to be run (some parts to be run at least) and then goes to step 4...

4. Execute the first part of the appropriate ViewsStoredProcs programmability script (Ex. DirectoryDB_7_ViewsStoredProcs.sql) , up to the first CREATE statement

This drops relevant parts within the DB, and in this instance the udf is one of them.....

It then goes through the steps of editing the script to change the collations etc, and then shows

12. Execute the Programmability (Ex. DirectoryDB_8_Programmability.sql) script/s from Step 3 in its/their entirety to restore the Programmability objects

this puts back the stuff dropped in step (4) .....

any chance you could revisit this and let us know how it goes? I reckon if you follow those steps it will work ok, as it won't choke on this udf_ - also, these scripts do change with each release (both major and service pack) and it would be inefficient to produce a TN for each release, hence this one should be ok to use - once I've looked at improving it where I can to make it easier to use!!!!!!

[GertjanA]

I hope he has a chance to do it before tomorrow evening. I'll be on holiday as of wednesday with limited to no access to I-net/mail/phone.

GJ

[GertjanA]

the DirectoryDB_7_ViewsStoredProcs.sql, but I believe he needs to use the - EV 8.0 and higher:  DirectoryDB_X_Programmability.sql

I believe my customer is confused, due to the mentioning of xx_7_xx names etc.

Perhaps, (hint hint) the document can be adjusted to list which SQL files need to be edited for which version (ie for EV8 use DirectoryDB_8_Programmability.sql, for EV9 user DirectoryDB_9_Programmability.sql etc)

Pointed customer to this. Hope he has time today to fix.

[MarkBarefoot]

The filenames are just an example, and I am already editing the document etc, but we need to make it as "less changeable" as possible, so I personally would prefer to keep the X in there, but point out that this means whatever version you are running...

I think the new TN will be easier to read as I am looking to split it into separate documents etc. I will update this blog when I have finished.

[GertjanA]

Looking forward to the check

[GertjanA]

Hello Mark, and others.

It appeared to be the wording of the document causing the error. I asked customer what he did, and we found out together that he tried to change collation on the Directory database using the VaultStoreDB_8_Programmability instead of the DirectoryDB_8_Programmability script. Then we found that he did not change the MS-script (step 10) which caused it to fail when doing something during the collation change.

He will now continue doing the other db's, and then move them to the new SQLserver....

As far as I am concerned, this threas is now closed, but if it needs to remain open for the rewording of the document, than that is fine by me....

[MarkBarefoot · Accepted solution]

Hi Gertjan

Good news. It's an easy mistake to make. I'm thinking of doing this in the new version:

Database	Version	Filename
Vaultstore	EV2007 and lower	VaultStoreDB_X_ViewsStoredProcs.sql
Directory	EV2007 and lower	DirectoryDB_X_ViewsStoredProcs.sql
Vaultstore	EV8 and higher	VaultStoreDB_X_Programmability.sql
Directory	EV8 and higher	DirectoryDB_X_Programmability.sql
Monitoring	ALL	EVOM_X_ViewsStoredProcs.sql
FSAReporting	ALL	FSAReporting_X_ViewsStoredProcs.sql

which should make it easier.......

Keep this open and check it on your return - hopefully the docs should be released by then....

[GertjanA]

More clear yes. Perhaps an indication of what the X is representing?

'Where X equals the highest number you have in the Enterprise Vault installation folder' ?