Hello,

I have a problem when i try to update Dnn to 5.0.1:

05.00.00.log :

System.Data.SqlClient.SqlException: Impossible de résoudre le conflit de classement de la colonne 2 dans l'instruction SELECT.
   à System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   à System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   à System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   à System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   à System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   à System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   à System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   à DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL)
   à DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions)

CREATE PROCEDURE dbo.[GetPortalSettings]
 @PortalID int

AS
 SELECT
  SettingName,
  CASE WHEN LEFT(LOWER(dbo.PortalSettings.SettingValue), 6) = 'fileid'
   THEN
    (SELECT Folder + FileName 
     FROM dbo.Files
     WHERE 'fileid=' + convert(varchar,dbo.Files.FileID) = dbo.PortalSettings.SettingValue
    )
   ELSE
    dbo.PortalSettings.SettingValue 
   END
  AS SettingValue
 FROM dbo.PortalSettings
 WHERE  PortalID = @PortalID

System.Data.SqlClient.SqlException: La sous-requête a retourné plusieurs valeurs. Cela n'est pas autorisé quand la sous-requête suit =, !=, <, <= , >, >= ou quand elle est utilisée en tant qu'expression.
L'instruction a été arrêtée.
   à System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   à System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   à System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   à System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   à System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   à System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   à System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   à DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL)
   à DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions)

/* Update IconFile References for Admin Tabs */
/*********************************************/

UPDATE dbo.Tabs
 SET IconFile = '~/images/' + IconFile
 WHERE IconFile LIKE 'icon_%'
  AND (TabPath LIKE '//Host%' OR TabPath LIKE '//Admin%')

System.Data.SqlClient.SqlException: La sous-requête a retourné plusieurs valeurs. Cela n'est pas autorisé quand la sous-requête suit =, !=, <, <= , >, >= ou quand elle est utilisée en tant qu'expression.
L'instruction a été arrêtée.
   à System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   à System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   à System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   à System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   à System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   à System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   à System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   à DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL)
   à DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions)

/* Set Admin and Host Pages to be Secure By Default */
/****************************************************/

UPDATE dbo.[Tabs]
    SET IsSecure = 1
WHERE PortalID IS NULL
    OR TabID IN (SELECT AdminTabId FROM dbo.[Portals])
    OR ParentId IN (SELECT AdminTabId FROM dbo.[Portals])

 Anyone can help me ?

Thank you

I got same error when upgrading from 4.8.4 to 5.0.1, then i found my database collation is Latin1_General_CI_AS not same as collation used in [Files] table SQL_Latin1_General_CP1_CI_AS

So I changed sql script in \Providers\DataProviders\SqlDataProvider\05.00.00.SqlDataProvider from

SELECT Folder + FileName

to

SELECT (Folder + FileName) collate SQL_Latin1_General_CP1_CI_AS

problem solved.

if you already installed you could run following script in sql server

GO

/****** Object:  StoredProcedure [dbo].[GetPortalSettings]    Script Date: 04/07/2009 09:30:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




alter PROCEDURE [dbo].[GetPortalSettings]
    @PortalID int

AS
    SELECT
        SettingName,
        CASE WHEN LEFT(LOWER(dbo.PortalSettings.SettingValue), 6) = 'fileid'
            THEN
                (SELECT (Folder + FileName) collate SQL_Latin1_General_CP1_CI_AS
                    FROM dbo.Files
                    WHERE 'fileid=' + convert(nvarchar,dbo.Files.FileID) = dbo.PortalSettings.SettingValue
                )
            ELSE
                dbo.PortalSettings.SettingValue 
            END
        AS SettingValue
    FROM dbo.PortalSettings
    WHERE  PortalID = @PortalID

GO

hope this help

Hi

I've had exactly the same problem upgrading from 4.9.2 to 5.1.1. To get the install to run (almost 100% cleanly) without the collation errors I had to modify  05.00.00.SqlDataProvider, 05.00.01.SqlDataProvider & 05.01.00.SqlDataProvider as per dnnix's solution above.

A bug report on this was closed as it could not be reproduced. However it does seem to be reproducing itself.

Has anybody found a solution to this?

Thanks in advance

Phil