Login

Username:
Password:

Code: 1415 Publish date: 2016/2/28
WebsitePanel AuditLog Error SQL Collation

Fix Web Site panel 2.1.0  AuditLog  error for SQL collation



WebsitePanel 2.1.0 AuditLog Error Collation on GetPackageExceedingQuotas

You will see error below on your WSP AuditLog and disk quota or bandwidth calculator will not work.

Page URL:     http://x.x.x.x/Default.aspx?pid=AuditLog&UserID=1

Logged User:     serveradmin

Work on Behalf:     serveradmin

Hosting Space:     0

Stack Trace:     System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)

at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters)

at WebsitePanel.EnterpriseServer.DataProvider.GetAuditLogRecordsPaged(Int32 actorId, Int32 userId, Int32 packageId, Int32 itemId, String itemName, DateTime startDate, DateTime endDate, Int32 severityId, String sourceName, String taskName, String sortColumn, Int32 startRow, Int32 maximumRows)

at WebsitePanel.EnterpriseServer.AuditLog.GetAuditLogRecordsPaged(Int32 userId, Int32 packageId, Int32 itemId, String itemName, DateTime startDate, DateTime endDate, Int32 severityId, String sourceName, String taskName, String sortColumn, Int32 startRow, Int32 maximumRows)

at WebsitePanel.EnterpriseServer.esAuditLog.GetAuditLogRecordsPaged(Int32 userId, Int32 packageId, Int32 itemId, String itemName, DateTime startDate, DateTime endDate, Int32 severityId, String sourceName, String taskName, String sortColumn, Int32 startRow, Int32 maximumRows)

--- End of inner exception stack trace ---

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at WebsitePanel.EnterpriseServer.esAuditLog.GetAuditLogRecordsPaged(Int32 userId, Int32 packageId, Int32 itemId, String itemName, DateTime startDate, DateTime endDate, Int32 severityId, String sourceName, String taskName, String sortColumn, Int32 startRow, Int32 maximumRows)

at WebsitePanel.Portal.AuditLogHelper.GetAuditLogRecordsPaged(Int32 maximumRows, Int32 startRowIndex, String sortColumn, String sStartDate, String sEndDate, Int32 packageId, Int32 itemId, String itemName, Int32 severityId, String sourceName, String taskName)

--- End of inner exception stack trace ---

at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)

at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)

at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

at System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance)


This is a bug or problem on Website Panel.

To solve this problem open your MS SQL and browse your WebsitePanel database to found GetPackageExceedingQuotas

1-  Databases > WebsitePanel (Or any name you add for WSP db) > Programmability > Functions> Table-valued Function > GetPackageExceedingQuotas
Right Click on function and delete it.





 Databases > WebsitePanel
(Or any name you add for WSP db) > Programmability > Functions> Table-valued Function > GetPackageExceedingQuotas
Right Click on function and delete it.

2- Run this query on SQL sever:

ALTER DATABASE websitepanel SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

ALTER DATABASE websitepanel COLLATE Latin1_General_CI_AS;

GO

ALTER DATABASE websitepanel SET MULTI_USER;

GO





3- Run This query, your WebsitePanel database name may be different:

USE [WebsitePanel]

GO

/****** Object:  UserDefinedFunction [dbo].[GetPackageExceedingQuotas]    Script Date: 02/28/2016 11:30:34 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[GetPackageExceedingQuotas]

 @PackageID int

)

RETURNS @quotas TABLE (QuotaID int, QuotaName nvarchar(50), QuotaValue int)

AS

BEGIN

DECLARE @ParentPackageID int

DECLARE @PlanID int

DECLARE @OverrideQuotas bit

SELECT

 @ParentPackageID = ParentPackageID,

 @PlanID = PlanID,

 @OverrideQuotas = OverrideQuotas

FROM Packages WHERE PackageID = @PackageID

IF @ParentPackageID IS NOT NULL -- not root package

BEGIN

 IF @OverrideQuotas = 0 -- hosting plan quotas

  BEGIN

   INSERT INTO @quotas (QuotaID, QuotaName, QuotaValue)

   SELECT

    Q.QuotaID,

    Q.QuotaName,

    dbo.CheckExceedingQuota(@PackageID, Q.QuotaID, Q.QuotaTypeID) AS QuotaValue

   FROM HostingPlanQuotas AS HPQ

   INNER JOIN Quotas AS Q ON HPQ.QuotaID = Q.QuotaID

   WHERE HPQ.PlanID = @PlanID AND Q.QuotaTypeID <> 3

  END

 ELSE -- overriden quotas

  BEGIN

   INSERT INTO @quotas (QuotaID, QuotaName, QuotaValue)

   SELECT

    Q.QuotaID,

    Q.QuotaName,

    dbo.CheckExceedingQuota(@PackageID, Q.QuotaID, Q.QuotaTypeID) AS QuotaValue

   FROM PackageQuotas AS PQ

   INNER JOIN Quotas AS Q ON PQ.QuotaID = Q.QuotaID

   WHERE PQ.PackageID = @PackageID AND Q.QuotaTypeID <> 3

  END

END -- if 'root' package

RETURN

END

GO

4- Finished, Just refresh Table-valued Function to make sure function recreated.



Resource: Day Telecom
Resource URL: https://day.ir