Recently I was tasked with the creation of a large database system that consisted of a database table with 5 date colums, and a varchar primary key. This table was to hold upwards of 3.5 million records, and I needed to know exactly how much disk space was going to be needed to store not only the database, but also the index required to facilitate the search requirements. After looking for a number of different ways to do this, and many free third party tools I decided that the most simple way to do this with the tools that I had available was to generate my own method to populate a test database.
Read more...
Posted by Mitchel on Friday, September 12, 2008
Recently I have been working on creating a dynamic reporting engine for use within the DotNetNuke system, a method to allow for the execution of a stored procedure and the proper display of the results. This includes creating a list of parameters, including dynamically loaded lookup systems and more. Well the most complex item that I have had to condition for was how to deal with filter options that require the use of the SQL IN clause, there are many ways of doing this, but I have found a way that appears to work perfect for my needs, this will be explained in this posting.
Read more...
Posted by Mitchel on Friday, August 08, 2008
In one of my previous blog entries I provided a helpful script that would allow you to find the space used for all SQL Server tables in a single database. It was a very simple method using the SP_SPACEDUSED stored procedure that is included in SQL Server. In that article I touched a bit on the general usage fo the method.
SP_SPACEUSED TableName
Where TableName was the specific table you are researching. Well there is a secondary option with a second parameter that is a very helpful item as well. I'll provide a quick scenario that sets up my specific example. I was working with a table that has about 80 columns, and somewhere in the neighborhood of 1.8 million records. Trying to condense the size of the database, we removed 20 columns that were no longer necessary. Prior to dropping the table we ran SP_SPACEUSED to get the table size, which provided us the following.
Reserved: 5,474,304 KB Data: 3,451,368 KB Index_Size: 196,704 KB
Overall the table itself was in the nighborhood of 3Gb, we executed a statement that dropped 20 columns from the table, however, running SP_SPACEUSED for the table only provided us the same results. You can pass SP_SPACEUSED a second parameter which expects a value of 'true' or 'false' with a default of false. If it is set to true, it will update usage information before returning the results. We then ran the following statement
SP_SPACEUSED TableName 'true'
Now we got the accurate information that showed our reduction in size.
Reserved: 1,826,320 KB Data: 1,727,624 KB Index_Size: 98,344 KB
I hope that this might help those of you optimizing table sizes and noticing incorrect numbers!
Posted by Mitchel on Friday, July 18, 2008
Recently when browsing the forums on DotNetNuke.com I have noticed more and more
questions regarding Script Injection vulnerability in the core and third-party modules.
I have often found that at times it is hard for non-developers to truly understand
the concept of script injection and what makes a site vulnerable. So in this
article I will take a bit of time to discuss both types of injection that users
of DotNetNuke should be aware of, how to test for them, and also how to prevent
the vulnerability when creating modules.
Read more...
Posted by Mitchel on Tuesday, May 20, 2008
Finding out how many modules you have installed on a site, and additionally how many times those modules are used is something that you would think is very simple within DotNetNuke. However, it is a bit more complex than one would hope. By default DNN does not provide a mechanism for you to perform this kind of research, however, I have a very simple SQL Script that will allow you to see ALL modules and from there the total number of instances (in all portals) and the total number of instances that are deleted (in the recycle bin). From an administrative perspective this is a key piece of information to know.
Read more...
Posted by Mitchel on Tuesday, March 25, 2008
As noted in my previous blog posting I have upgraded this website to be running DotNetNuke 4.6.0. Prior to the upgrade this site was running DNN 4.5.2 and I performed a direct upgrade to 4.6.0. Also as I mentioned in my previous posting the upgrade didn't go all that smooth, with the site being down for almost an hour as I worked out the "bugs" that I identified with the 4.6.0 upgrade. This article will discuss one of the issues that I encountered relating to the Whitespace Filter.
Read more...
Posted by Mitchel on Wednesday, September 19, 2007
One problem I recently encountered myself is one that I see appearing every so
often on the DotNetNuke forums. I was working with profile properties and I set
a property as required but accidentally forgot to set it to visible. I then
proceeded to update the preferences that require a valid profile for login. I
am now effectivly locked out of my site as I did not have that property set for
my host or admin account. In this article I will provide you the information
needed to reset the custom properties and to get you back into your site.
Read more...
Posted by Mitchel on Wednesday, August 01, 2007
I have just released a new version of the SQL Formatter/Colorizer utility. This release is used to resolve issues caused by changes to the parent web service used for formatting! You may download it for free from the "Software" section of this site.
Posted by Mitchel on Tuesday, July 31, 2007
A common problem that I have recently encountered was trying to identify areas of my website database that were
taking up the most physical storage space. At work I maintain a DotNetNuke installation that includes a 4.5 Gb
reporting database and I was trying to pinpoint the exact sizes of ALL tables in my database and was having a few problems.
I started using the "sp_spaceused" stored procedure but I had to run that for every table, I have over 200 tables in this
database and it was just not feasiable for me to do it this way. Therefore I wrote a stored procedure that will perform
all needed data calls and will return a result set with the data on all tables. In this post I will share the script
as well as a few interesting things I learned while writing it.
Read more...
Posted by Mitchel on Friday, July 27, 2007
A little over a month ago I posted a SQL Query that can be used to find the most popular links
on a given portal. In that example if you had links that were being tracked that were files it would only
show the fileid for the file and not the file name. Now since there has been a fair amount of reader demand I have
created a new version of this query to actually show the URL for URL based links and to show the file name
for file based links.
To successfully parse the "fileid=xx" portion of the results when needed I use the substring function to
grab just the id so I can join to the files table to grab the file name. Due to the different methods needed
for files and links I have done this via two queries and then used the "UNION" statement to combine the results for
display. For you to use this on your portal you will need to be sure to modify the portalid value to match that
of your portal.
--Get standard links
SELECT m.ModuleTitle,
t.url,
t.clicks,
t.LastClick
FROM urltracking t
INNER JOIN Modules m
ON (t.ModuleId = m.ModuleId)
WHERE t.PortalId = 0
AND t.trackclicks = 1
AND t.urltype <> 'F'
--Combine with the others
UNION
--Get File links
SELECT m.ModuleTitle,
f.[FileName] as url,
t.clicks,
t.lastclick
FROM urltracking t
INNER JOIN Modules m
ON (t.moduleid = m.moduleid)
INNER JOIN Files f
--Grab the file id from the url field for the join
ON (CAST(SUBSTRING(t.url, 8, LEN(t.url)) as int) = f.fileid)
WHERE t.urltype = 'F'
ORDER BY t.clicks DESC, url
Please post any comments/questions below
Posted by IowaComputerGurus on Thursday, July 19, 2007
I have noticed a number of posts recently on the DotNetNuke.com forums regarding numerous errors being reported in the
event log relating to portal id of -1 and typically with a InnerException of "Value cannot be null. Parameter name: type". This issue
can become very annoying and can have an actual effect on your sites performance as when the size of the event log grows you will start to notice performance slow downs. This article
will discuss the root cause of the issue and a solution.
Read more...
Posted by Mitchel on Wednesday, July 18, 2007
After having my DNN sites up and running for almost a year I was very curious to see exactly how many clicks I had on the various tracked links. I started to look around and was getting tired of clicking "Edit:" on each link to view the information. So I created this simple query to quickly pull the links in the "Most clicked" order.
This is a very primitive query that gets the module name, link, click count and last click time. For documents loaded in the documents module you will only get the file id of the file, but it at least helps to illustrate the activity.
SELECT m.ModuleTitle, t.url, t.Clicks, t.LastClick
FROM URLTracking t
INNER JOIN Modules m
ON (t.ModuleId = m.ModuleID)
WHERE t.PortalId = 0
AND TrackClicks = 1
ORDER BY t.Clicks DESC
You will need to edit the PortalId portion of the where clause to ensure that you reference the correct portal.
Posted by IowaComputerGurus on Wednesday, June 06, 2007
Due to popular request this article discusses the steps necessary to create a new standard DotNetNuke user account based on the password for an existing user. This is a follow-up article from my creating a host account via SQL Server that was published about a month ago.
Read more...
Posted by Mitchel on Monday, May 14, 2007
As I see more and more people using the Ventrian News Articles module for their
blogs I have to imagine that others are curious as I am as to which blog posts
are the most popular, the highest rated, or maybe even the lowest rated.
Yes I understand that I could look through my blog and find this out, but with
as many posts as I have I decided to do some research and have made a few
helpful queries that will provide this type of information. This entry
will walk you through each of the scripts.
Click "Read More..." to view the scripts and the remainder of the article
Read more...
Posted by Mitchel on Thursday, May 03, 2007
As promised this is a detailed migration process article that shows you how to move existing DotNetNuke blog entries over to the Ventrian News Articles module. This is the exact process that I just used to migrate the blog on this site!
Read more...
Posted by Mitchel on Saturday, April 07, 2007
Previous Page
1 of 2
Next Page