Cannot resolve collation conflict for equal to operation

Topics: Database Publishing Wizard
May 14, 2007 at 8:25 AM
Hi

Can anyone help me with this?

I’m using RunSQL.aspx to load the ASPNETDB.MDF to my hosting provider. I have generated the T-SQL file using the Database Publishing Wizard and modified the RunSQL.aspx file.

When I load the RunSQL.aspx page I get this error.

Thanks for the help.

Opening file D:\websites\localuser\myDomain\www\App_Data\ASPNETDB.SQL
Connecting to SQL Server database...
An error occured: System.Data.SqlClient.SqlException: Cannot resolve collation conflict for equal to operation. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.runsqlaspx._Render__control1(HtmlTextWriter __w, Control parameterContainer) in d:\websites\localuser\myDomain\www\RunSQL.aspx:line 85
May 14, 2007 at 9:09 PM
Edited May 14, 2007 at 9:09 PM
Brad -

Take a look at this thread and see if it helps you out: http://www.codeplex.com/sqlhost/Thread/View.aspx?ThreadId=8274

Thanks,
alex


BradC wrote:
Hi

Can anyone help me with this?

I’m using RunSQL.aspx to load the ASPNETDB.MDF to my hosting provider. I have generated the T-SQL file using the Database Publishing Wizard and modified the RunSQL.aspx file.

When I load the RunSQL.aspx page I get this error.

Thanks for the help.

Opening file D:\websites\localuser\myDomain\www\App_Data\ASPNETDB.SQL
Connecting to SQL Server database...
An error occured: System.Data.SqlClient.SqlException: Cannot resolve collation conflict for equal to operation. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.runsqlaspx._Render__control1(HtmlTextWriter __w, Control parameterContainer) in d:\websites\localuser\myDomain\www\RunSQL.aspx:line 85

May 15, 2007 at 12:36 AM
Edited May 15, 2007 at 12:37 AM
Hi Alexd

My hosting provider which provides SQL 2000 have told me that the collation is SQL_Latin1_General_CP1_C1_AS

So I opened SQL Server Management Studio Express, attached ASPNETDB.MDF to the Databases then right clicked ASPNETDB.MDF went to properties > options and changed the collation to the above and the compatibility level to SQL Server 2000 (80). I then recreated the T-SQL using SQL Server 2000 for the Script for target database field. Uploaded the new file, pointed my browser to RunSQL.aspx and got the same error:

This is all above my head. I am just learning ASP.NET and SQL. Hope you can help


alexd wrote:
Brad -

Take a look at this thread and see if it helps you out: http://www.codeplex.com/sqlhost/Thread/View.aspx?ThreadId=8274

Thanks,
alex

May 15, 2007 at 5:07 AM
Would you mind sending a copy of the T-SQL script (zipped up if you could) to alexd@microsoft.com and I'll take a look?

Also, could you run the command SELECT DATABASEPROPERTYEX('database_name', 'Collation'); for your target (hosted) database (substitute database_name for your database name) and see what the result is?

Thanks,
alex


BradC wrote:
Hi Alexd

My hosting provider which provides SQL 2000 have told me that the collation is SQL_Latin1_General_CP1_C1_AS

So I opened SQL Server Management Studio Express, attached ASPNETDB.MDF to the Databases then right clicked ASPNETDB.MDF went to properties > options and changed the collation to the above and the compatibility level to SQL Server 2000 (80). I then recreated the T-SQL using SQL Server 2000 for the Script for target database field. Uploaded the new file, pointed my browser to RunSQL.aspx and got the same error:

This is all above my head. I am just learning ASP.NET and SQL. Hope you can help


alexd wrote:
Brad -

Take a look at this thread and see if it helps you out: http://www.codeplex.com/sqlhost/Thread/View.aspx?ThreadId=8274

Thanks,
alex


May 15, 2007 at 5:35 AM
Hi Alex

This is what I have uncovered.

I created a new basic database with 1 table. I was successfully able to use RunSQL.aspx on this.

I then went back to my ASPNETDB.MDF and removed all 55 Stored Procedures and created a new T-SQL file, this time it was successful.

So without removing each Stored Procedure one at a time, is there an easy way to find out which one(s) are causing the problem.
May 16, 2007 at 8:52 AM
Edited May 16, 2007 at 8:53 AM
Hi Alex

I have an update.

The problem is in two Stored Procedures:
aspnet_UsersInRoles_AddUsersToRoles
aspnet_UsersInRoles_RemoveUsersFromRoles

How do I fix the problem?

Also have no idea how you run the command SELECT DATABASEPROPERTYEX('database_name', 'Collation');
Is it the same as RunSQL.aspx page? If it is, you would need to give me all the code as I haven’t got a clue.