Cannot resolve the collation conflict

 

Server Error in ‘/WebSite1’ Application.


Cannot resolve the collation conflict between “Chinese_PRC_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

Description: An unhandled
exception occurred during the execution of the current web request.
Please review the stack trace for more information about the error and
where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Cannot resolve the
collation conflict between “Chinese_PRC_CI_AS” and
“SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

Source Error:

The source code that generated this unhandled exception can only be shown when compiled in debug mode. To enable this, please follow one of the below steps, then request the URL:1. Add a "Debug=true" directive at the top of the file that generated the error. Example:<%@ Page Language="C#" Debug="true" %>or:2) Add the following section to the configuration file of your application:<configuration><system.web><compilation debug="true"/></system.web></configuration>Note that this second technique will cause all files within a given application to be compiled in debug mode. The first technique will cause only that particular file to be compiled in debug mode.Important: Running applications in debug mode does incur a memory/performance overhead. You should make sure that an application has debugging disabled before deploying into production scenario.

Stack Trace:

[SqlException (0x80131904): Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2030802
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5009584
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +86
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +144
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +319
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +94
   Insus.NET.Base.ExecuteProcedure(String procedureName, DataSet& dataSet, SqlParameter[] prams) +190

[Exception: Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.]
   Insus.NET.Base.ExecuteProcedure(String procedureName, DataSet& dataSet, SqlParameter[] prams) +254
   Insus.NET.BusinessBase.GetDataToDataSet(String procedureName, Parameter[] sqlParameter) +324
   Insus.NET.WipStocks.GetSearchResult(InsusSearch insusSearch, String step) +176
   System_Report_OldEis_Wip_StockReports.Data_Binding() +165
   System_Report_OldEis_Wip_StockReports.Page_Load(Object sender, EventArgs e) +52
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
   System.Web.UI.Control.OnLoad(EventArgs e) +91
   System.Web.UI.Control.LoadRecursive() +74
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207

 


 

Version Information:
Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1

 

用户反映,程序出现上边Error,不可能解析的排序规则争辨。两台服务器,一台(前者)SQL
Server的排序规则为“ Chinese_PRC_CI_AS ”另一台(后者)SQL
Server 服务器排序规则为“SQL_Latin1_General_CP1_CI_AS  ”

来人服务器创立前者为链接服务器。因为不可以对两台服务器排序规则作过多修改。只能去修改存储过程,修改前代码(部分):

图片 1图片 2View Code

IF OBJECT_ID(‘dbo.#TempWipStocks’) IS NOT NULL
        DROP TABLE dbo.#TempWipStocks    
    CREATE TABLE  dbo.#TempWipStocks 
     (      
        [Location] NVARCHAR(30),
        [ItemCode] NVARCHAR(60),
        [Description] NVARCHAR(100),
        [CustomerID] NVARCHAR(30),
        [Quantity] DECIMAL(18,2),
        [Allocated] BIT,
        [Allocatee] NVARCHAR(200)
     ) 
     –从链接服务器询问到数量再插入这多少个临时表中。   
     INSERT INTO dbo.#TempWipStocks SELECT * FROM [dbo].[udf_WipStocks]()  

 

修改后代码(部分):

图片 3图片 4View Code

IF OBJECT_ID(‘dbo.#TempWipStocks’) IS NOT NULL
        DROP TABLE dbo.#TempWipStocks    
    CREATE TABLE  dbo.#TempWipStocks 
     (      
        –Insus.NET作了之类一些改动,只对字符型的字段添加链接服务器的排序规则
        [Location] NVARCHAR(30) COLLATE Chinese_PRC_CI_AS, 
        [ItemCode] NVARCHAR(60) COLLATE Chinese_PRC_CI_AS, 
        [Description] NVARCHAR(100) COLLATE Chinese_PRC_CI_AS, 
        [CustomerID] NVARCHAR(30) COLLATE Chinese_PRC_CI_AS, 
        [Quantity] DECIMAL(18,2),
        [Allocated] BIT,
        [Allocatee] NVARCHAR(200) COLLATE Chinese_PRC_CI_AS 
     ) 
         
     INSERT INTO dbo.#TempWipStocks SELECT * FROM [dbo].[udf_WipStocks]()

 

仓储过程修改更新之后,程序在运作时跑出正规介面了。

相关文章