You are here:   Home
  |  Login
DotNetNuke Core Team
Microsoft MVP
 Bookmark this article!
delicious.com delicious.com
digg digg
technorati technorati
reddit reddit
stumbleupon stumbleupon
facebook facebook
google bookmarks google bookmarks
yahoo bookmarks yahoo bookmarks
slashdot slashdot
live live
twitter twitter
DotNetKicks DotNetKicks

Tags

  1. 1 items are tagged with Admin settings
  2. 1 items are tagged with AJAX Extensions
  3. 1 items are tagged with ASP.NET
  4. 2 items are tagged with BBeyond
  5. 1 items are tagged with Caching
  6. 1 items are tagged with Cambrian
  7. 1 items are tagged with CBO
  8. 2 items are tagged with Codeplex
  9. 1 items are tagged with debugging
  10. 1 items are tagged with DotNetNuke
  11. 2 items are tagged with DotNetNuke 5
  12. 1 items are tagged with Email
  13. 1 items are tagged with email validation
  14. 1 items are tagged with GMail
  15. 1 items are tagged with Google
  16. 1 items are tagged with Google Apps
  17. 1 items are tagged with IBaseEntity
  18. 2 items are tagged with IdentitySwitcher
  19. 1 items are tagged with IHydratable
  20. 1 items are tagged with Inline Skin Object
  21. 2 items are tagged with Installation
  22. 1 items are tagged with Installer
  23. 1 items are tagged with intellisense
  24. 1 items are tagged with IPropertyAccess
  25. 1 items are tagged with Language
  26. 1 items are tagged with Manifest
  27. 1 items are tagged with moving
  28. 1 items are tagged with OpenForce
  29. 1 items are tagged with Permissions
  30. 1 items are tagged with Profile Properties
  31. 1 items are tagged with PropertyEditorControl
  32. 1 items are tagged with recent projects
  33. 1 items are tagged with regex
  34. 1 items are tagged with Sample Module
  35. 1 items are tagged with Search Results
  36. 2 items are tagged with Skin Object
  37. 1 items are tagged with Skinning
  38. 6 items are tagged with SQL
  39. 1 items are tagged with SQL Server
  40. 1 items are tagged with SQL Server 2008
  41. 1 items are tagged with Table Functions
  42. 2 items are tagged with TellCo
  43. 1 items are tagged with TokenReplace
  44. 2 items are tagged with Too Good to be True
  45. 1 items are tagged with Twitter
  46. 2 items are tagged with UPC
  47. 2 items are tagged with Upgrading
  48. 1 items are tagged with Users
  49. 2 items are tagged with visual studio
  50. 1 items are tagged with Visual Studio 2008

My Twitter Feed...

Thu, 29 Jul 2010 16:45:45 +0200

erikvb: Gettin unhealthy food. Yummy!

Thu, 29 Jul 2010 07:06:10 +0200

erikvb: RT @mashable: Google Makes Custom Web Typography Ridiculously Easy - http://ow.ly/2i8DG

Wed, 28 Jul 2010 05:05:48 +0200

erikvb: @Alyssa_Milano cool, you have a clone AND you are followed by @wilw . I can only dream of achieving the same...

Tue, 27 Jul 2010 22:20:49 +0200

erikvb: http://bit.ly/bwCZdB (Popularity is Everything: A new approach to protecting passwords from statistical-guessing attacks)

Tue, 27 Jul 2010 16:51:58 +0200

erikvb: RT @Telerik: Win an iPad or Telerik Ultimate licenses. Contest details at http://bit.ly/99ep9O [please RT]

Tue, 27 Jul 2010 10:06:20 +0200

erikvb: RT @TimoBreumelhof: Small bugfix for Style Helper #DNN #skin object posted: http://dnnskinextensions.codeplex.com/releases/view/47234

Mon, 26 Jul 2010 18:39:00 +0200

erikvb: @jbrinkman have fun playing with it :)

Mon, 26 Jul 2010 18:34:22 +0200

erikvb: @schotman als het maar droog blijft

Mon, 26 Jul 2010 17:51:38 +0200

erikvb: RT @christoc: RT @DNNCorp Get new insights from #DotNetNuke CEO Navin Nagiah at CMS Critic: http://bit.ly/aqkKwm

Sun, 25 Jul 2010 20:35:09 +0200

erikvb: RT @Heuserkampf: This will be interesting: Facebook lets publishers contact “likers”: http://bit.ly/9orOQN

Listing DotNetNuke Database versions in all your databases

Last Updated 01/21/2009
By: Erik van Ballegoij

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:

databaseName DNNVersion
DotNetNuke_Cambrian 500

 

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:

DatabaseName DNNVersion
dnn403 403
dnn437 437
dnn441 441
dnn455 455
dnn462 462
dnn470 470
dnn484 484
dnn490 490
dnn490oq 490
DNN500 500
dnn500RC1 500
dnn500RC2 500
DotNetNuke_Cambrian 501

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:

Rate this:
Recent Comments
ALTER PROCEDURE [dbo].[sp_GetAllDNNVersions] I think, should read: CREATE PROCEDURE [dbo].[sp_GetAllDNNVersions]
Posted By: Declan Ward on 01/21/2009
Thanks Declan, fixed that!
Posted By: Erik van Ballegoij on 01/21/2009

Powered By

DotNetNuke Powered! This site is proudly powered by DotNetNuke.

 

(Advertisements)

My other blogs

Thu, 21 May 2009 18:41:00 +0200

Fri, 01 May 2009 14:57:00 +0200

Wed, 29 Apr 2009 16:11:00 +0200

Mon, 27 Apr 2009 20:33:05 +0200

Wed, 21 Jan 2009 20:13:00 +0100

Mon, 05 Jan 2009 23:00:00 +0100

Fri, 05 Dec 2008 16:45:08 +0100

Sun, 05 Oct 2008 22:23:00 +0200

Wed, 17 Sep 2008 14:07:00 +0200

Sun, 24 Aug 2008 21:23:00 +0200