Monday, January 30, 2012

Annoying DOTNETNUKE 5.x or 6.x version ISSUE - Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I was running a DNN app on my local machine. I have now moved it to my server and am trying to get it working.

Note that I am using Godaddy hosting

ERROR IS AS FOLLOWS:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
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: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1953274
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4849707
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) +74
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) +93
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, String spName, Object[] parameterValues) +87
DotNetNuke.Data.SqlDataProvider.UpdateServerActivity(String ServerName, String IISAppName, DateTime CreatedDate, DateTime LastActivityDate) +192
DotNetNuke.Entities.Host.ServerController.UpdateServerActivity(ServerInfo server) +76
DotNetNuke.Common.Initialize.InitializeApp(HttpApplication app) +474
DotNetNuke.Common.Initialize.Init(HttpApplication app) +145
DotNetNuke.HttpModules.RequestFilter.RequestFilterModule.FilterRequest(Object sender, EventArgs e) +187
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +68
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +75

ISSUE FIX IS AS FOLLOWS:
Goto SQL Server, Find the Stored Procedure called "UpdateServerActivity" and alter it as follows (change highlighted in RED):


ALTER PROCEDURE dbo.UpdateServerActivity
    @ServerName   nvarchar(50),
    @IISAppName   nvarchar(200),
    @CreatedDate  datetime,
    @LastActivityDate datetime
AS
 DECLARE @ServerID int
 -- SET @ServerID = (SELECT ServerID FROM dbo.WebServers WHERE ServerName = @ServerName AND IISAppName = @IISAppName)
 SET @ServerID = (SELECT Top 1 ServerID FROM dbo.WebServers WHERE ServerName = @ServerName AND IISAppName = @IISAppName order by LastActivityDate DESC)

 IF @ServerID IS NULL
  BEGIN
   -- Insert
   INSERT INTO dbo.WebServers (
    ServerName,
    IISAppName,
    CreatedDate,
    LastActivityDate,
    [Enabled]
   )
   VALUES (
    @ServerName,
    @IISAppName,
    @CreatedDate,
    @LastActivityDate,
    0
   )
  END
 ELSE
  BEGIN
   -- Update
   UPDATE dbo.WebServers
    SET
     LastActivityDate = @LastActivityDate
    WHERE  ServerName = @ServerName AND IISAppName = @IISAppName
  END

GO

1 comment:

Ransems said...

Solved my issue, thanks. Upgrading from 6.2.x to 7.0.0