SQL error System.Data.SqlClient.SqlException: Cannot resolve collation conflict for column 9 in SELECT statement.
   at System.Data.SqlClient.SqlConnection. (SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection. (SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL)
   at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions)

 

 

CREATE VIEW dbo.vw_Tabs
AS
 SELECT    
  TabID,
  TabOrder,
  PortalID,
  TabName,
  IsVisible,
  ParentId,
  [Level],
        CASE WHEN LEFT(LOWER(T.IconFile), 7) = 'fileid=' THEN
                  (SELECT Folder + FileName
                    FROM  dbo.Files
                    WHERE fileid = CAST((RIGHT(LOWER(T.IconFile), Len(T.IconFile) - 7)) AS int)) ELSE T.IconFile END AS IconFile,
        CASE WHEN LEFT(LOWER(T.IconFileLarge), 7) = 'fileid=' THEN
                  (SELECT Folder + FileName
                    FROM  dbo.Files
                    WHERE fileid = CAST((RIGHT(LOWER(T.IconFileLarge), Len(T.IconFileLarge) - 7)) AS int)) ELSE T.IconFileLarge END AS IconFileLarge,
  DisableLink,
  Title,
  Description,
  KeyWords,
  IsDeleted,
  SkinSrc,
  ContainerSrc,
  TabPath,
  StartDate,
  EndDate,
  Url,
  CASE WHEN EXISTS (SELECT  1 FROM dbo.Tabs T2 WHERE T2.ParentId = T.TabId) THEN 'true' ELSE 'false' END AS HasChildren,
  RefreshInterval,
  PageHeadText,
  IsSecure,
  PermanentRedirect,
  SiteMapPriority,
  CreatedByUserID,
  CreatedOnDate,
  LastModifiedByUserID,
  LastModifiedOnDate
 FROM dbo.Tabs AS T

 

 -----------------------------------------------

 

System.Data.SqlClient.SqlException: Cannot resolve collation conflict for column 2 in SELECT statement.
   at System.Data.SqlClient.SqlConnection. (SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection. (SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL)
   at 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,
  dbo.PortalSettings.CreatedByUserID,
  dbo.PortalSettings.CreatedOnDate,
  dbo.PortalSettings.LastModifiedByUserID,
  dbo.PortalSettings.LastModifiedOnDate
 FROM dbo.PortalSettings
 WHERE  PortalID = @PortalID

------------------------------------------------------------

I used the following script (courtesy of Sebastian Leupold) to verify the collation settingd for the server:

SELECT

DATABASEPROPERTYEX( '<mydb>' , 'Collation' ) AS DotNetNukeDB_Collation,

DATABASEPROPERTYEX( 'tempdb' , 'Collation' ) AS TempDB_Collation,

SERVERPROPERTY( 'Collation' ) AS Server_Default_Collation

and this showed that all three properties were set to Latin1_General_CI_AS - i.e the default database setting.

Predictably, the stored procedure 'GetPortalSettings' and the view 'vw_Tabs' were not created and the site is not working.


The fact that the relevant collations are all set to the same value would suggest that this really isn't a collation issue, but I'm no expert in the area.

Any help would be much appreciated, workarounds, fixes etc.

Cheers

Phil