There is probably nothing wrong with the collation in justnorth's database. The problem is likely to be with the temp database (and only the host can change that with a reinstall of SQL or (potentially) lots of (down)time and effort). If the collation is different (the host may have elected to chose a different collation when installing SQL - maybe due to country and expected client base) then any stored procedure which creates temporary tables will be affected.
You can get around this hosting provider limitation if you are prepared to modify any stored procedures which use temporary tables by adding a specific collation statement to each (n)varchar and (n)char and (n)text fields in the create table part of the stored procedure. See this example which has been modified.
CREATE PROCEDURE dbo.GetEventLog
@PortalID int,
@LogTypeKey nvarchar(35),
@PageSize int,
@PageIndex int
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
CREATE TABLE #PageIndex
(
IndexID int IDENTITY (1, 1) NOT NULL,
LogGUID varchar(36) COLLATE Latin1_General_CI_AS
)
INSERT INTO #PageIndex (LogGUID)
SELECT EventLog.LogGUID
FROM EventLog
INNER JOIN EventLogConfig
ON EventLog.LogConfigID = EventLogConfig.ID
WHERE (LogPortalID = @PortalID or @PortalID IS NULL)
AND (EventLog.LogTypeKey = @LogTypeKey or @LogTypeKey IS NULL)
ORDER BY LogCreateDate DESC
SELECT EventLog.*
FROM EventLog
INNER JOIN EventLogConfig
ON EventLog.LogConfigID = EventLogConfig.ID
INNER JOIN #PageIndex PageIndex
ON EventLog.LogGUID = PageIndex.LogGUID
WHERE PageIndex.IndexID > @PageLowerBound
AND PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
SELECT COUNT(*) as TotalRecords
FROM #PageIndex
GO
If you generate a SQL script for all the stored procedures and search for CREATE TABLE you should be able to identify and fix the problem(s). From my recollection, the above stored procedure is the only one in a default DNN installation.
Alternatively, look for an alternative hosting provider which provides full support for DNN.
|