SearchItemWordPosition grows very fast because duplicates

One of my clients got this problem. His DB went from the 80 Mb to 1 Gb in just one week. After deep research i found table SearchItemWordPosition contains 33 millions records and takes a lot of space in the DB. On deeper search i found it contains a lot of duplicates and this problem is very common in the DNN Community. So first of all i have made a script to remove duplicates:

To find pages with installed module you can run this SQL code (just replace HTML with Friendly Name of required module):

Backup MSSQL Database To External Path

It is very popular question from my clients. They have web access only to MS SQL database, but have to backup it to the external path. Ok, it can be done easy:

1. At the external server/computer make a folder, for example "C:\External_Backup".
2. Then go to the properties of this folder, tab "Sharing". Make public share with name "External_Backup" and add permissions for "Everyone" and "ANONYMOUS LOGON". Do not worry - its for few mins only, then we disable it.
3. Temporary disable Firewall.
4. Try to connect to this share from the Explore in this way "\\your_ip\External_Backup". You can see files and peform any actions in this folder.
5. In the web client of MSSQL database run query like this: BACKUP DATABASE your_db_name TO DISK = '\\your_ip\External_Backup\your_db_name.bak';
6. Enable Firewall and remove share from the public access.

DotNetNuke Not Uniq Emails

Sometimes clients ask to make registration at the site with uniq emails only. It somehow prevents to appear duplicates of the users. But sometimes site is live already and have a lot of registered users, then it needs to find all users with not uniq emails in DB and to fix. It is possible with query like this:

SELECT UserID, UserName, FirstName, LastName, Email, DisplayName

FROM {databaseOwner}{objectQualifier}Users

WHERE Email IN

(

SELECT Email

FROM {databaseOwner}{objectQualifier}Users

GROUP BY Email

HAVING COUNT(Email)>1

);

Hope this helps!

Sometimes DotNetNuke DataBase Grows Very Fast

This is very popular problem of DotNetNuke. DB grows very fast, ever you do not add any new modules and content is the same. The most popular problem is quick growing of the log tables SiteLog and EventLog.

One of my clients had this problem, because have been attacked by hackers and log grows very fast with errors. Another is because installed bad written module and this module throws a lot of exceptions.

You can check how many  records in each table with query like this:

SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}SiteLog;

and

SELECT COUNT(*) FROM {databaseOwner}{objectQualifier}EventLog;

You can compare number of records for small period of time (for example 1 hour). If it grows fast, then here is a query to fix problem:

TRUNCATE TABLE {databaseOwner}{objectQualifier}SiteLog;
TRUNCATE TABLE {databaseOwner}{objectQualifier}EventLog;

This query removes all records from these tables. It can be runned from Host/SQL menu.

Hope this helps!

How to upgrade DotNetNuke module or SQL scripts for new versions

DotNetNuke (DNN) is very good CMS. It allows easy and fast build new modules without any problems. Furthermore it allows to do an upgrade of old modules with beautiful and perfect process. For example, we have Module with version 01.00.00. During installation all SQL scripts locate in the 01.00.00.SqlDataProvider. This file contains all required SQL for correct Module functionality.

In case you need to upgrade your module, then all what you do is add to your module new file, like 01.00.01.SqlDataProvider. During installation DNN framework checks current version and runs all scripts above current version. It allows to do cumulative upgrade without any problems.

But some clients do an upgrade manually and it can be a lot of headache. So here is a way how to protect your SQL script:

IF(

NOT EXISTS(

SELECT sc.name 

FROM syscolumns sc

WHERE (sc.id IN 

(SELECT so.id

FROM sysobjects so

WHERE so.name='{objectQualifier}Survey'))

AND

(sc.name='CategoryID')

)

)

BEGIN

ALTER TABLE {databaseOwner}{objectQualifier}Survey ADD CategoryID int NOT NULL DEFAULT(0)

END

As you see, this script checks for field before creation. So, do not need to worry customer runs it twice or more times.

PS: My old teacher said: Good code allows to remove half of lines and continues to work :)

How to rotate SQL Table

There was very good customer from Australia. He always had interesting projects. One of this project was to show report on products sales. At start it looks like simple project, but there were two issues:
1) Customer had very big amounts of data in MS SQL.
2) He would like to show data grouped in columns, not rows.

First version of project does grouping in the columns at the C# code, but with such big amount of data it was crazy slow and takes a lot of memory. This is why grouping has been moved to the MS SQL side. Here is a beautiful script (sorry, but can't remember original source):