I just helped a user who was using SQL Server as the data store and was getting the following error when using the search function:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the like operation
The call stack looked like this:
Code:[SqlException (0x80131904): Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the like operation.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +212
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +245
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, TdsParserStateObject stateObj) +2733
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +78
System.Data.SqlClient.SqlDataReader.get_MetaData() +112
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +2518996
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +2517793
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +424
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +28
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +211
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +87
GalleryServerPro.Data.SqlServer.Application.SearchGallery(String[] searchTerms, List`1& matchingAlbumIds, List`1& matchingMediaObjectIds) in C:\_hubert\_rc1\website\GSPLibSrc.2.1.3162\TIS.GSP.Data.SqlServer\Application.cs:66
GalleryServerPro.Data.SqlServer.SqlDataProvider.SearchGallery(String[] searchTerms, List`1& matchingAlbumIds, List`1& matchingMediaObjectIds) in C:\_hubert\_rc1\website\GSPLibSrc.2.1.3162\TIS.GSP.Data.SqlServer\SqlDataProvider.cs:343
GalleryServerPro.Business.HelperFunctions.SearchGallery(String searchText, IGalleryServerRoleCollection roles, Boolean userIsAuthenticated) in C:\_hubert\_rc1\website\GSPLibSrc.2.1.3162\TIS.GSP.Business\HelperFunctions.cs:957
GalleryServerPro.Web.search.SearchGallery() in C:\_hubert\_rc1\website\GSPLibSrc.2.1.3162\gsweb\search.aspx.cs:43
GalleryServerPro.Web.search.Page_Load(Object sender, EventArgs e) in C:\_hubert\_rc1\website\GSPLibSrc.2.1.3162\gsweb\search.aspx.cs:16
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +25
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +42
System.Web.UI.Control.OnLoad(EventArgs e) +132
System.Web.UI.Control.LoadRecursive() +66
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2428
The Gallery Server database had a default collation of Latin1_General_CI_AS. The Gallery Server scripts specify SQL_Latin1_General_CP1_CI_AS for all nvarchar fields. As far as I can tell, this shouldn't be a problem. But at least for this user, it was.
The solution was to change the collation of several columns in the GSP tables and the stored procedure gs_SearchGallery to the database's default collation, in this case Latin1_General_CI_AS. Here is how:
1. Close all open connections to your GSP database. You may need to restart the IIS app pool to accomplish this (or run iisreset at a command prompt).
2. Open SQL Management Studio and navigate to your GSP database. Right-click the database name and choose Properties. Click the Options tab and note the collation in the pane on the right.
3. Using SQL Management Studio, right-click the table gs_Album and choose Design. Click each of the columns that are nvarchar and update the collation to your database's collation in the dropdown box in the bottom pane.
4. Repeat this step for the gs_MediaObject and gs_MediaObjectMetadata tables.
5. Open the stored procedure gs_SearchGallery. Replace all instances of SQL_Latin1_General_CP1_CI_AS with your collation. Save the changes.
If you are affected by this issue, reply to this post and let me know. As best I can tell, this issue shouldn't even be happening in the first place, so you if any insight I would love to hear from you.
Roger Martin
Lead Developer for Gallery Server Pro