When you need to upgrade multiple DotNetNuke websites it is sometimes hard to figure out what version a specific site is. Of course, you could logon to all your sites, but that takes quite some time.

If you have installed the databases of your sites in one sql server instance, there is another option available. You could open all databases, open the Version table, and check the highest version number in that table. This is still a lot of work though… however, it is something that can be automated with a little effort.

The first part is getting the DNN version from a DNN database. This can be done by this select statement:

SELECT top 1 Major*100+Minor*10+Build AS [Version] 
    FROM [Version]  
    ORDER BY VersionId DESC

This select statement will give you the value 500 for DotNetNuke 5.0.0. Of course, we will run into an issue if the database was installed using an objectQualifier. Since DNN does not store the value of that objectQualifier in the database, we need to be a little more flexible. The following script will first get the internal object id of the version table in the database. It will pick the first available table that has a name that ends in “version” and has a column with the name “major”. Next, it will use that table to get the version in a similar way as above.

DECLARE @TableId int
SELECT @TableId = id 
    FROM dbo.syscolumns 
    WHERE [name]='major' 
          AND id IN 
          (SELECT id 
                FROM dbo.sysobjects 
                WHERE [name] LIKE '%version' 
                       AND  OBJECTPROPERTY(id, N'IsUserTable') = 1)

DECLARE @VersionTable NVARCHAR(100)
SELECT @VersionTable = [name]  FROM dbo.sysobjects WHERE id = @TableId

DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @IntVersion int;
DECLARE @IntVariable int;
SET @SQLString = N'select  top 1 @Version = major*100+minor*10+build  from ' + @VersionTable + '  order by versionid DESC'
SET @ParmDefinition = N'@Version INT OUTPUT';

EXECUTE sp_executesql
    @SQLString
    ,@ParmDefinition
    ,@Version = @IntVersion OUTPUT;

PRINT @IntVersion

If you run this script from SQL Server Management Studio, it should give you the value 500 for a DotNetNuke 5.0 database.The next step is to create a stored procedure that will be stored in the Master database. This stored procedure will return a select that contains both the Database name and the DNN Version:

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_GetDNNVersionOfDB]
    @DNNVersion INT OUTPUT

AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT * 
                    FROM dbo.syscolumns 
                    WHERE [name]='major' 
                          AND id IN 
                          (SELECT id 
                                FROM dbo.sysobjects 
                                WHERE [name] LIKE '%version' 
                                      AND  OBJECTPROPERTY(id, N'IsUserTable') = 1))
    BEGIN
        DECLARE @TableId int
        SELECT @TableId = id 
            FROM dbo.syscolumns 
            WHERE [name]='major' 
                  AND id IN 
                  (SELECT id 
                        FROM dbo.sysobjects 
                        WHERE [name] LIKE '%version' 
                               AND  OBJECTPROPERTY(id, N'IsUserTable') = 1)

        DECLARE @VersionTable NVARCHAR(100)
        SELECT @VersionTable = [name]  FROM dbo.sysobjects WHERE id = @TableId

        DECLARE @SQLString nvarchar(500);
        DECLARE @ParmDefinition nvarchar(500);
        DECLARE @IntVersion int;
        DECLARE @IntVariable int;
        SET @SQLString = N'select  top 1 @Version = major*100+minor*10+build  from ' 
                        + @VersionTable + '  order by versionid DESC'
        SET @ParmDefinition = N'@Version INT OUTPUT';
    
        EXECUTE sp_executesql
            @SQLString
            ,@ParmDefinition
            ,@Version = @IntVersion OUTPUT;
    
        SELECT @DNNVersion = @IntVersion
        SELECT DB_NAME() AS datatabaseName,@IntVersion AS DNNVersion;
    END

END

The above SPROC will actually first test if it can find a DNN version table in the database, and will only return something if this table is found. If you execute this sproc like this:

USE [DotNetNuke_Cambrian] -- change this in the name of your database!
DECLARE @RC int
DECLARE @DNNVersion int
EXECUTE @RC = [sp_GetDNNVersionOfDB] @DNNVersion OUTPUT

it should give you back something like this for a DNN 5.0 database:

The second stored procedure is much easier, as it will simply call the first stored proc for every database in the table. This uses an undocumented SQL Server Stored Procedure “sp_MSforeachdb”. This will allow you to execute commands for every database.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_GetAllDNNVersions] 
AS
BEGIN
SET NOCOUNT ON 
 
 
    CREATE TABLE #foo 
    ( 
        DatabaseName VARCHAR(255), 
        DNNVersion INT 
    ) 
     
    INSERT #foo 
    EXEC sp_MSforeachdb @command1 ="use [?];declare @DNNVersion INT;EXEC sp_GetDNNVersionOfDB @DNNVersion OUTPUT;" 
    
    SELECT DatabaseName, DNNVersion FROM #foo order by databasename
 
    DROP TABLE #foo
    
END

The above sproc will create a temp table #foo, and will insert output of sp_GetDNNVersionOfDB for every database in #foo. Next, the content of table #foo is displayed, and the temp table is deleted. For my local development system it give these results:

Databases dnn4900q and DotNetNuke_Cambrian both have an object qualifier set, as you see this is handled just fine. Also, the server contains more non dnn databases, and these are all excluded, as expected.

For this solution i used information from these sources:

• http://sqlserver2000.databases.aspfaq.com/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts.html
• http://www.sql-server-performance.com/articles/dba/ac_sql_server_2000_undocumented_sp_p1.aspx
• http://www.sqlservercentral.com/articles/Administering/sp_msforeachworker/711/