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

The server returned a non-specific error when trying to get data from the data source. (SharePoint Designer 2007 error)

If you see this error in SharePoint Designer (click on Data Source and Show Data) then check the following:
1) If any calculated columns are used on corresponding list then make sure that any conditional statements using IF does not use "" in non-string data type fields. For example using IF([IsActive] = 'Yes', '', 'Test') - use of '' should be replaced with empty string if type is number etc
2) Check the SelectCommand within the Data View as well as DataFields and ensure that there are no fields/columns used which does not exist on the corresponding list

EDMX Issue - The version of SQL Server in use does not support datatype ‘datetime2′

Here is how you resolve - Open your EDMX in Notepad or Visual Studio. At the top you will find the storage model and it has an attribute ProviderManifestToken. This has should have the value 2008. and change that to 2005.

Rrecompile and everything should work.