Missing Stored procedures in database

Vote:
 

I'm having an issue with a Episerver installation, the database are missing some stored procedures.

The database has been around since version 9.x-something and the problem got to my attention when doing an upgrade to episerver and got this errormessage:

[SqlException (0x80131904): Could not find stored procedure 'netBlockTypeGetUsage'.]

After looking in to the issue I noticed that a couple of more stored procedures were also missing... I have no idea why the haven't been installed in the first place. 

I would like to know if there is some script I can run to verify that all exists and if not create them.


#201626
Feb 25, 2019 13:45
Vote:
 

When investigating further I noticed there are three or four more Stored procedures missing. And when trying to create the "nerBlockTypeGetUsage" SP manually i got an error saying:

Msg 468, Level 16, State 9, Procedure netBlockTypeGetUsage, Line 21 [Batch Start Line 6]
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Finnish_Swedish_CI_AS" in the like operation.

It looks like there are some mismatch in the collation in the database, I don't know if this information helps...

#201629
Feb 25, 2019 15:47
Vote:
 

Pjuh, sounds like a hassle,

Start by running all sql scripts to the right version under Episerver.CMS.Core.x.x.x\tools\epiupdates\sql, one at a time.

If no work, i would be bald and delete all SPs, and run the skripts from CMS package...

Check your databaseversion here: https://nuget.episerver.com/compare-database/

Did it work Mattias?

#201645
Edited, Feb 26, 2019 12:30
Vote:
 

Yes it was a real hassle!

It took some time to figure out what was wrong. But eventually we found a solution.

We fixed the issue by writing a SQL-script that first changes the collate in on the columns that were wrong, then creates all missing stored procedures.

Now everything works as it should and hopefully we identified all the missing stored procedures.

#201841
Mar 05, 2019 10:33
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.