Below you will find a few generic SQL queries for generating reports on DNN Users.

Last Activity:
SELECT     Users.Username, Users.FirstName, Users.LastName, aspnet_Users.LastActivityDate
FROM         aspnet_Users INNER JOIN
                      Users ON aspnet_Users.UserName = Users.Username
order by aspnet_Users.LastActivityDate DESC

Last Login:
SELECT   Users.Username, Users.FirstName, Users.LastName, aspnet_Membership.LastLoginDate
FROM         Users INNER JOIN
                      aspnet_Users ON Users.Username = aspnet_Users.UserName INNER JOIN
                      aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId
ORDER BY aspnet_Membership.LastLoginDate DESC

New Users:
SELECT   Users.Username, Users.FirstName, Users.LastName, aspnet_Membership.CreateDate
FROM         Users INNER JOIN
                      aspnet_Users ON Users.Username = aspnet_Users.UserName INNER JOIN
                      aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId
ORDER BY aspnet_Membership.CreateDate DESC

Unverified Users:
select USers.FirstName, Users.LastName, aspnet_Membership.Email, aspnet_Users.UserName from aspnet_Membership, aspnet_Users, Users where aspnet_Membership.IsApproved = 0 AND aspnet_Membership.UserID = aspnet_Users.UserId AND aspnet_Users.UserName = Users.UserName

Users in Role:
SELECT Roles.RoleName AS [Role Name], COUNT(UserRoles.RoleID) AS [Number in Role] FROM Roles INNER JOIN UserRoles ON UserRoles.RoleID = Roles.RoleID GROUP BY UserRoles.RoleID, Roles.RoleName ORDER BY RoleName

We needed a Proper Case Function that would change the first letter of each word to an uppercase letter.

Example:

some text = Some Text

Here is the function we added:

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

CREATE FUNCTION dbo.Proper (@tcString VARCHAR(100))

RETURNS VARCHAR(100) AS

BEGIN

-- Scratch variables used for processing

DECLARE @outputString VARCHAR(100)

DECLARE @stringLength INT

DECLARE @loopCounter INT

DECLARE @charAtPos VARCHAR(1)

DECLARE @wordStart INT

-- If the incoming string is NULL, return an error

IF (@tcString IS NULL)

RETURN ('(no string passed)')

-- Initialize the scratch variables

SET @outputString = ''

SET @stringLength = LEN (@tcString)

SET @loopCounter = 1

SET @wordStart = 1

-- Loop over the string

WHILE (@loopCounter <= @stringLength)

BEGIN

-- Get the single character off the string

SET @charAtPos = SUBSTRING (@tcString, @loopCounter, 1)

-- If we are the start of a word, uppercase the character

-- and reset the work indicator

IF (@wordStart = 1)

BEGIN

SET @charAtPos = UPPER (@charAtPos)

SET @wordStart = 0

END

-- If we encounter a white space, indicate that we

-- are about to start a word

IF (@charAtPos = ' ')

SET @wordStart = 1

-- Form the output string

SET @outputString = @outputString + @charAtPos

SET @loopCounter = @loopCounter + 1

END

-- Return the final output

RETURN (@outputString)

END

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

I am not sure where I found this function to give it the proper credit, but it's been around for a while. I had used it on a SQL Server 2000 database to change all uppercase US State names to lowercase, then running them through the Proper function. Recently I had to search several databases to find the function so I could use it on a SQL Server 2005 database. So, in case I need it again, I decided to save it here....

We are working on a project that required unique email addresses during DotNetNuke registration. In order to satisfy this requirements, the web.config file can be updated to enforce unique emal addresses by changing the default of false to true for the requiresUniqueEmail attribute.

<membership

defaultProvider="DNNSQLMembershipProvider"

userIsOnlineTimeWindow="15">

<providers>

<clear/>

<add

name="DNNSQLMembershipProvider"

type="DotNetNuke.Security.Membership.DNNSQLMembershipProvider, DotNetNuke.Provider.SQLMembershipProvider"

connectionStringName="SiteSqlServer"

enablePasswordRetrieval="true"

enablePasswordReset="true"

requiresQuestionAndAnswer="false"

minRequiredPasswordLength="4"

minRequiredNonalphanumericCharacters="0"

requiresUniqueEmail="true"

passwordFormat="Encrypted"

applicationName="/"

description="Stores and retrieves membership data from the local Microsoft SQL Server database"/>

</providers>

</membership>

I would like to thank Richard Golko from UCanUse for this helpful tidbit...and yes, it does work with their User Attributes module!

Upgrading DotNetNuke

Current Assembly Version: 04.00.03

Current Database Version: 04.00.02

Upgrade Status Report

00:00:00.078 - Upgrading to Version: 4.0.3
00:00:11.640 - Performing General Upgrades
00:00:12.156 - Installing Module File D:\Dev\DNN\Website\Install\Module\Announcements_3.1_Install.zip:
00:00:12.843 - Installing Module File D:\Dev\DNN\Website\Install\Module\Contacts_3.1_Install.zip:
00:00:13.109 - Installing Module File D:\Dev\DNN\Website\Install\Module\Discussions_3.1_Install.zip:
00:00:13.250 - Installing Module File D:\Dev\DNN\Website\Install\Module\Documents_3.1_Install.zip:
00:00:13.421 - Installing Module File D:\Dev\DNN\Website\Install\Module\Events_3.1_Install.zip:
00:00:13.625 - Installing Module File D:\Dev\DNN\Website\Install\Module\FAQs_3.1_Install.zip:
00:00:13.796 - Installing Module File D:\Dev\DNN\Website\Install\Module\Feedback_3.1_Install.zip:
00:00:13.906 - Installing Module File D:\Dev\DNN\Website\Install\Module\HTML_3.1_Install.zip:
00:00:14.140 - Installing Module File D:\Dev\DNN\Website\Install\Module\IFrame_3.1_Install.zip:
00:00:14.250 - Installing Module File D:\Dev\DNN\Website\Install\Module\Image_3.1_Install.zip:
00:00:14.343 - Installing Module File D:\Dev\DNN\Website\Install\Module\Links_3.1_Install.zip:
00:00:14.515 - Installing Module File D:\Dev\DNN\Website\Install\Module\NewsFeeds_3.1_Install.zip:
00:00:14.640 - Installing Module File D:\Dev\DNN\Website\Install\Module\Survey_3.1_Install.zip:
00:00:14.843 - Installing Module File D:\Dev\DNN\Website\Install\Module\UserDefinedTable_3.1_Install.zip:
00:00:15.109 - Installing Module File D:\Dev\DNN\Website\Install\Module\UsersOnline_3.1_Install.zip:
00:00:15.375 - Installing Module File D:\Dev\DNN\Website\Install\Module\XML_3.1_Install.zip:

Upgrade Complete

Click Here To Access Your Portal

Yay! No errors. I did a little testing, and after checking to make sure nothing was really broken, I went ahead an made copies of all DNN 4.0.2 sites we have running and backed up their databases. So far I have installed DNN 4.0.3 on 5 different DNN instances, and on 3 different servers.  All well so far. I will post any problems I find here...

Update:
I noticed there is not an annoucement on the DotNetNuke website...yet. But, the Install, Source, and Templates are available for download at the SourceForge link above.

2 issues have been fixed in the latest release of the Venexus SignIn module, an alternative DotNetNuke Login module that enforces Terms of Use agreement.

Password Reminder and Register buttons are both working now.

 

The "Remember Me" functionality during DotNetNuke 4 login works for only 1 hour by default. Previously, this had been set to 50 years. In order to change persistent login to an extended time, a change to the web.config file is necessary. In the web.config you should see this:

<authentication mode="Forms">
      <forms name=".DOTNETNUKE" protection="All" timeout="60"/>
</authentication>

To increase the period of persistent login to 7 days would be this:

<authentication mode="Forms">
      <forms name=".DOTNETNUKE" protection="All" timeout="10080"/>
</authentication>

To increase the period of persistent login to 1 Year would be this:

<authentication mode="Forms">
      <forms name=".DOTNETNUKE" protection="All" timeout="525600"/>
</authentication>

Hope that helps!

Nothing To Install At This Time

Current Assembly Version: 04.00.02

Current Database Version: 04.00.02

 

If you get the "Nothing to Install At This Time" message when accessing your DotNetNuke 4 portal, you can add the following line to the appSettings in the web.config file:

<add key="InstallationDate" value="3/9/2005" />

We are looking for beta testers for a DotNetNuke module that allows users to set/update their Active Directory password. The module has been tested in DNN 3.1.13 and DNN 4.0.2.

Module Settings:

 

Module View:

If anyone has a DotNetNuke development environment, with Active Directory in use, and is interested in testing this module, please let us know by sending an email to support@venexus.com

For a project we are working on, we have a backend process that needed to get the PostalCode from ASPNET_Profile for a UserName. Thanks to David Silverlights post on DotNetSlackers.com we added the following functions to our database:

 --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

CREATE FUNCTION dbo.fn_GetElement
(
@ord AS INT,
@str AS VARCHAR(8000),
@delim AS VARCHAR(1) )
RETURNS INT
AS
BEGIN
  -- If input is invalid, return null.
  IF  @str IS NULL
      OR LEN(@str) = 0
      OR @ord IS NULL
      OR @ord < 1
      -- @ord > [is the] expression that calculates the number of elements.
      OR @ord > LEN(@str) - LEN(REPLACE(@str, @delim, '')) + 1
    RETURN NULL
  DECLARE @pos AS INT, @curord AS INT
  SELECT @pos = 1, @curord = 1
  -- Find next element's start position and increment index.
  WHILE @curord < @ord
    SELECT
      @pos    = CHARINDEX(@delim, @str, @pos) + 1,
      @curord = @curord + 1
  RETURN    CAST(SUBSTRING(@str, @pos, CHARINDEX(@delim, @str + @delim, @pos) - @pos) AS INT)
END

 --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

CREATE FUNCTION dbo.fn_GetProfileElement
(
@fieldName AS NVARCHAR(100),
@fields AS NVARCHAR(4000),
@values AS NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN

  -- If input is invalid, return null.
  IF  @fieldName IS NULL
      OR LEN(@fieldName) = 0
      OR @fields IS NULL
      OR LEN(@fields) = 0
      OR @values IS NULL
      OR LEN(@values) = 0
    RETURN NULL

-- locate FieldName in Fields
DECLARE @fieldNameToken AS NVARCHAR(20)
DECLARE @fieldNameStart AS INTEGER, @valueStart AS INTEGER, @valueLength AS INTEGER

-- Only handle string type fields (:S:)
SET @fieldNameStart = CHARINDEX(@fieldName + ':S',@Fields,0)

-- If field is not found, return null
IF @fieldNameStart = 0 RETURN NULL
SET @fieldNameStart = @fieldNameStart + LEN(@fieldName) + 3

-- Get the field token which I've defined as the start of the field offset to the end of the length
SET @fieldNameToken = SUBSTRING(@Fields,@fieldNameStart,LEN(@Fields)-@fieldNameStart)

-- Get the values for the offset and length
SET @valueStart = dbo.fn_getelement(1,@fieldNameToken,':')
SET @valueLength = dbo.fn_getelement(2,@fieldNameToken,':')

-- Check for sane values, 0 length means the profile item was stored, just no data
IF @valueLength = 0 RETURN ''

-- Return the string
RETURN SUBSTRING(@values, @valueStart+1, @valueLength)
END

 --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Then a stored procedure was created to retrieve the users PostalCode by passing in the UserName.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetPostalCodeFromUserName
 @UserName nvarchar(50)
AS
BEGIN
 SET NOCOUNT ON;
 SELECT dbo.fn_GetProfileElement(N'PostalCode', dbo.aspnet_Profile.PropertyNames, dbo.aspnet_Profile.PropertyValuesString) AS PostalCode FROM dbo.aspnet_Profile INNER JOIN dbo.aspnet_Users ON dbo.aspnet_Profile.UserId = dbo.aspnet_Users.UserId where dbo.aspnet_Users.UserName = @UserName

END
GO

We are gathering requirements for the initial release of DNN FIND, a DotNetNuke search module. There are 2 pieces to this project, the crawler/spider and the fulltext indexing based search module. Currently the crawler/spider is a standalone multi-threaded VB.Net application that we are considering embedding inside of a DotNetNuke module and scheduled service (Host > Schedule). However for large sites, it may be critical for performance to crawl from a seperate server. Also, full text indexing is not included in SQL Server 2005 Express. To get an idea about the website environment of users in the community, we are running a survey to gather requirements for the initial release.

We are considering the following options:

1. DNN FIND Lite Version: a free DotNetNuke module that utilizes web services to query for results and limited to 250 pages indexed.
2. DNN FIND Standard Version: a DotNetNuke Module that untilizes web services to query for results and limited to 1000 pages indexed.
3. DNN FIND Pro Version: a DotNetNuke Module that utilizes a scheduled crawler/spider embedded in DotNetNuke to use collected data for local full text indexing and search. Limited to 5000 pages.
4. DNN FIND Enterprise Version: A crawler/spider standalone application and a DNN module for querying the fulltext index for the largest of websites. Unlimited pages.

To see an example of our "global search" in development, based on full text indexing, please see the DNN FIND website and/or Find News. Find News now has almost 900,000 articles/pages it its full text index.

So, if you are interested in a DotNetNuke search module for your site, please answer the web environment questions we have in the following: Release Requirements Survey.

If you are interested in beta testing, please let us know by completing the form on the Spider page.

Have you built a DotNetNuke site and now want to move DNN to production? Or, are you simply moving a DotNetNuke website from one server to some other server? Over the last month I have seen several searches in the logs from people trying to find instructions on moving a DotNetNuke website from development to production, or simply from one server to another. After looking around for similar detailed instructions, I really did not see anything out there that really explained how to setup a IIS box for DotNetNuke, optimized and ready for production. So, I decided to provide instructions on how we do it…

The following instructions are provided to minimize any downtime of the production domain/website, assuming there is an existing website running. However, the same instructions can be applied for a brand new domain/website that does not have a critical execution plan. These instructions will have a basic assumption that you understand how to manage IIS, DNS, and DNN. While we do not provide any guarantees to your particular setup, the following has worked well for us, managing the release of many DotNetNuke websites from our development environment to our production environment, while allowing continued development during the process.

DNS and Subdomains

First, we need to create subdomains. For this example, we will use dev.domain.com and prod.domain.com. Assuming the development environment you are currently working on is using just localhost, adding a subdomain for dev.domain.com may not be neccessary, but provides an easy way to work with multiple domains and projects. Point DNS for dev subdomain to the IP address of your development box and point the prod subdomain to your production box IP. Leave, www.domain.com pointing to where ever you have it pointing currently, but turn your TTLs down to 5 seconds. If this is a new website, you may already be pointing www.domain.com to your dev box, you can leave it there for now. For those who do not know what DNS is, see How Stuff Works.

We use the service of DNS Made Easy. Hands down, this is the best business decision we have made concerning management of DNS. While their site is unfortunately not a DNN site, it provides the interface to get the job done quickly and easily. We use their Business Membership plan that allows us to turn down our domain TTLs to 5 seconds. They offer 100% uptime and have servers globally to handle DNS request. They also provide services for MX backup (your domain will still receive emails, even if your email server is down) and site monitoring (pings your site to ensure it is up, sends notification if down). We have never had a problem, they have excellent support, and great prices for their services.

Once you have the new subdomains created, make sure they are working by pinging from the server(s) or use something like DNS Stuff. If you cannot ping, make sure your firewall is setup correctly to accept and route HTTP traffic to the correct server.

If it’s working, then you are ready for the next step…

The Development DNN Installation

Login as Host and go to Host > Portals > Edit Portal > Portal Aliases > Add New HTTP Alias

Add the following aliases, assuming localhost is the only one created:

1.      domain.com

2.      www.domain.com

3.      dev.domain.com

4.      prod.domain.com

Copy the entire DNN folder on development box to the location on your production box where DNN will reside.

SQL Server Database on Development and Production

Go into Enterprise Manager or SQL Server Management Studio, development database server, and find your DotNetNuke database. Right click and go to Tasks > Detach. Clear the connections if you have to. Once the database has been successfully detached, go to the folder where the database files live (.mdf and .ldf files) and make a copy of your DotNetNuke database and transaction log. If you simply made a copy of the file in the same folder, you can now reattach the database (Tasks > Attach) so development can continue if other developers are working on your project, minimizing development downtime, plus have a backup of development database in case of unexpected issues. If you have a very small database, it may not be an issue just to copy and paste the files to the production server, assuming you are on the same network. However, our “development DNN database” is almost 3 Gb and takes a minute to move over to our production box. So, however you decide to do it, copy the .mdf and .ldf file to production database server. Rename them if necessary and Attach the database similar to the way you detached. Refresh database view if necessary and ensure the database is accessible. If you want to create a custom SQL Server login on production for security reasons, now is the time to do it.

IIS on Production Box

Create a new website in IIS. Map the website to the DNN folder you created on Prod. For the initial host header, use prod.domain.com.

**Note** DNN Documentation show creating Virtual Directories. We have had great success with configuring and managing DNN websites as actual websites, instead of virtual directories and would recommend this route for anyone who is planning on running this in a production environment to reap the benefits of IIS, especially IIS 6 and performance as discussed further down…

If this site is NOT replacing an existing and running domain/website, go ahead and add domain.com and www.domain.com host headers from Website Properties > Web Site Tab > Web site identification > Advanced Button. If you use internal IP addresses (Firewall routes the HTTP traffic), make sure you have the right IP addresses and port specified. By default HTTP traffic runs on port 80.

If this is a DNN 4 install, make sure you change ASP.Net version to 2.0 from the ASP.NET tab in Web site propoerties.

For performance enhancements, and as a necessity if you are running both ASP.NET 1.1 AND ASP.NET 2.0 websites, create a new application pool for the website and a web garden as described in the following entry on IIS 6 Horticulture.

Production DNN folder

Within Windows Explorer, go to your production DNN folder, right-click and select Properties > Security and make sure Network Service has the proper permissions. Now, modify the web.config’s database connection string(s) (2 of them for DNN 4) to the production database server and production database. If you created new SQL Server accounts for access, you will want to update the id and pass as well.

Testing it out…

First, open up a command prompt and make sure you can ping prod.domain.com and dev.domain.com. You should get the production IP address for prod.domain.com and the development server IP address for dev.domain.com. Now try to navigate to prod.domain.com in a web browser. If you get a successful page load, you now can do your testing to ensure everything is there and working. Since you created a subdomain for dev.domain.com, you can do your development as needed, test, and when ready, move to production. Periodic replication of the database from Production to Development allows you to keep a development environment that closely resembles your production environment. If you have tested everything and are sure the prod.domain.com is working correctly, you are ready to go live…

Going Live with DotNetNuke 4

Okay, so you have tested and are confident that the site is ready for primetime. First, go ahead and change your DNS settings to point domain.com traffic to the production server IP address. With IIS Manager open, stop the old website if one was running and add www.domain.com and domain.com to the host headers to the new website if necessary. Try www.domain.com and it should work. You now have a live DNN site. Assuming all went well, you can turn your TTLs back up in DNS for better caching.

Updates to this post

I hope you have found this useful. If you have any suggestions, please write a comment and share your experiences. I will try to keep this entry updated if I come across any other tips or updates and eventually may even get around to adding some pictures for futher help. Stay tuned!

We had to create a DotNetNuke module that would enforce that the user agrees to the Terms and Conditions of the website before being able to login, to satisfy a legal and SEC requirement for a financial company's client portoflio access. The module has been in use on the site for over 9 months now and we recently upgraded it to support DotNetNuke 4.x.

The Venexus SignIn module is free for download. As a "filecasting" test, I will be including the DNN 4 version of this module as an enclosure to this entry. It will be interesting to see how RSS/ATOM news readers interpret the enclosure. More on this later... To download the DNN 3 or 4 version, if you are not reading this by a news reader, create an account on the Venexus website and go here. Registered users will be able to download.

To use the module, backup your DNN installation and install as you would any other module. Drop the Venexus SignIn module on a page and go to the Settings. You can specify your custom, or existing Terms of Use page within Edit Terms and Conditions Settings.

Currently the sourcecode is not available until we have time to write some comments. If you have any suggestions for future releases feel free to comment here and we might get around to adding them

I noticed something new that I have not seen in previous versions of DotNetNuke...both, web.config and siteurls.config are backed up to the Config directory. With each change, a folder labeled "Backup_YYYYMMDDTT" is created and a copy of the original files are placed within. I am assuming this is the cached version that gets backed up during the application restart. I have always been careful and made a copy of the web.config file before making the change, but it is nice to know that there is another copy stored in the Config folder just in case. If anyone has any information about how this process works, I would be interested in knowing more details. Please share!

A lot of DotNetNuke developers/webmasters forget the importance of traffic derived from their blogs or RSS feeds. Any seach engine optimization plan should include submitting links to  popular blog/news feed directories.

For those who are unaware, you can enable syndication within several DotNetNuke modules. For example, in the Text/HTML module Settings > Page Settings > Basic Settings is Allow Syndicate checkbox. If this is enabled, a XML icon will appear on the rendered page.

There are also many DotNetNuke modules that provide RSS feeds for syndication. A couple of my favorite modules are Orizonti's NukeNews and Scott Schecter's NukeSyndicate. Both of these modules provide a RSS feed for consumption. NukeSyndicate allow for posting audio and video files, know as podcast and vodcast...more on this later

Recently I spent a few hours submitting this blog to many directories. Most of the links below do not require reciprocal links, but in order to satisfy those who do and to provide a resource for others, here is the list I submitted our RSS feed to:

Blogs for Small Businesses Chordata Complete RSS Easy RSS Every Feed Feed-Directory Feeds Farm Feedster Feedzie Findory

Fyber Search GoBig Network IceRocket Jordo Media News XS Newzfire Nooked Plazoo PubSub Press Radar  Read A Blog Redtram RSS Feeds RSS Network Yahoo! RSS

If you have any other suggestions, please do tell! Check back for updates....