Hi

Just running some tests and a few things I have noticed wth installation errors with Dot Net Nuke and the infamous 4.4.0 errors that seem to crop up a lot

I was trying to find out why some installations it fails with

[Installing Database - Version 4.4.0...Upgrading Database to Version ERROR: See c:\.....\SqlDataProvider\04.04.00.log for more information...FAILURE 400 - Conversion from string "ERROR: See c:inetpubwwwrootdnn2P" to type 'Integer' is not valid]

And sometimes it installs fine.

(Testing with 4.7)

Firstly it installs fine when I set the db to be Latin1_General_CI_AS [Server collation also Latin1_General_CI_AS]

It fails however when the collation is Sql_Latin1_General_CI_AS [Server collation Latin1_General_CI_AS]

Later then I tried to copy the installation that did work out to a development server using the enterprise manager export tools and I started to notice a few strange things.

The server instance to which I was copying was Sql_Latin1_General_CI_AS

About 70 of the stored procs would not copy across.

When I looked closer I noticed there are loads of errors in the Latin1_General_CI_AS version installation (the one that did install), loads of small things mainly characters in stored procs in the incorrect case.

Take for example the stored procedure dbo.[UpdateUser]

UPDATE dbo.Users
SET
    FirstName = @FirstName,
    LastName = @LastName,
    Email = @Email,
    DisplayName = @DisplayName,
    UpdatePassword = @UpdatePassword
WHERE  UserId = @UserId
AND UserID = 1

Notice UserId and UserID - the column name is UserID

When you try to copy this database the second server, the creation of this stored proc fails (like many others) with the error [UserId does not exist] (which is correct, it does not, UserID does though)

It seems Latin1_General_CI_AS is case insensitive when it comes to creating stored procs (the tables are correct, for example its UserID in the tables).

Sql_Latin1_General_CI_AS on the other hand is case sensitive

I also tried creating a Latin1_General_CI_AS db on the development server but it still fails. It appears the entire sql server instance needs to be Latin1_General_CI_AS, its not good enough that just the one db is set this way.

If the server is Sql_Latin1_General_CI_AS (I've tried several) I can not get it to install.

SqlDataProvider Files

Unfortunately these errors are in the sqldataprovider files themselves.

For example 04.00.04

Line 1096: WHERE  UserId = @UserId

There are countless examples where the case used in the sql code - stored proc, view, function etc is different from the case used in the db table.

And on databases where this is a problem, the installation fails.

At least thats what I've been finding.

I hope this information is useful because these problems are causing deployment issues for myself and many others it seems.

The case in the stored procs should match those of the tables. If the underlying sql scripts on DNN are not being written strictly they should be cleaned up as its causing issues.

Regards

J

This is a known issue and does not require a new Gemini entry.  DotNetNuke does not support case sensitive collations.

there are already floating issues about this around in gemini. None of which can be solved.

To elaborate on Joe's statement: We've investigated the issue and we found that changing the sql is not good enough. We would also need to fix all info object classes to reflect proper casing. In itself this could be done, however, this would create an incompatibility with c# modules (c# is case sensitive)

DNN Blog | Twitter: @erikvb | LinkedIn: