Determining Database & Table Sizes in SQL Azure

11 Aug

As the popularity of Microsoft's Azure platform continues to rise, I find myself constantly adding new scripts to my library.  As technology continues to evolve we need to find different solutions to meet technology needs.  SQL Azure has introduced a number of changes to processes and protocols as it relates to development & support.  One of the most viewed topics on this blog is my Determining SQL Server Table Size, a quick post from 2007 after helping a co-worker solve a problem.  Fast forward 8 years and this post still sees more than 1,000 views in a month.  However, try to run that on SQL Azure and you will have an issue.  SP_SpaceUsed is not available to us on that platform!  In this post I will provide a few helpful queries to get around this limitation.

tags: SQL, Quick Tips

Simple Zip Code to Zip Code Distance Calculations with SQL Server

23 Jan

Over the years I have implemented a number of various "near me" style searches using third-party zip code databases and a long, complicated formula to find the distance between two different latitudes and longitudes.  Well, starting with SQL Server 2008, it is possible to get the same result, with a lot less effort, following two simple steps.  In this post I will walk through things step by step.

tags: SQL, Quick Tips

SQL Server Forward_Only Cursor for Performance

23 Sep

For those of you that follow me on Twitter you might have heard my recent comments around a DotNetNuke SQL Script that I had to run that would have an impact of between 6 and 9 million SQL statements being executed on a database.  As part of this project I was focusing a lot on SQL Server performance, and getting the best overall bang for the buck with the query. Sadly, given what I was working with a Cursor was needed to ensure that I had the best, most stable solution.  In this post I'll share a little about the process and the lesson learned with cursors.

tags: SQL, DNN Development

Selecting the Right Source Control Provider

08 Apr

One of the most common questions that I have been getting recently has been "what source control provider do you use and why?".  This article is the first in a series of two articles about source control.  This article takes a quick look at criteria that I believe is important to consider when it comes to evaluating source control systems for use.  The next article in the series will be a review of the source control system that I use, in relation to the evaluation criteria that I list in this article.

tags: SQL, ASP.NET, .NET 1.1, .NET 2.0, General Tech, .NET 3.5, C#, VB, DNN Development

Creating Comma Separated List in SQL

30 Jan

It seems that every client project I have started in recent months has involved a new form of complexity in SQL Server scripts.  The most recent task that I had to tackle was with generating a comma separated list based on entries in a table.  Now this was something that I needed for each and every row of a result set, so I didn't want to do the actual list creation in .NET.  So, reaching back to some content and examples that I built for my "Creating Dynamic SQL Pivots" article which will be published in an upcoming edition of SQL Server I found the following helpful snippet of code.

tags: Tutorials, SQL

Creating DotNetNuke SqlDataProvider Files Using SSMS

26 Jan

One of the biggest failure points I see with third-party DotNetNuke modules, including some of my first released modules were failures due to the lack of support for ObjectQualifier or DatabaseOwner values within the SqlDataProvider files.  Although the process to add support for these two tokens is easy to add, it is very hard for those of us that like to test our database structures outside of DotNetNuke before we actually commit to building our modules.  Thankfully, with a bit of research, I have found a way to build SQL Scripts for DotNetNuke using SQL Server Management Studio (SSMS) in a normal fashion and using a simple find/replace operation to bring everything into compliance with DotNetNuke integration.  This post will explore the method that I use to accomplish this.

tags: DNN, Tutorials, SQL, DNN Development

Creating Dynamic Where Clauses with SQL

09 Jan

One very common scenario in when working with User Interface database queries is the concept of a "Dynamic Search Query". A select statement that can have optional items in the where clause. There are a number of methods to implement this, however, they have varying levels of difficulty and, overall each have their own tradeoffs, in implementation, readability, performance, and accuracy. This article explores a few of the most common methods of doing this.

tags: Tutorials, SQL

Why Dynamically Attached Databases Cause Problems

24 Nov

One item that many people find unusual about my DotNetNuke installation guides is my insistence on NOT using the Database.mdf, dynamically attached database file for the creation of a DotNetNuke database.  In my tutorials I have quickly dismissed the topic by noting that "issues" come up when trying to use a dynamically attached database and I leave it at this.  Well after seeing more questions on this topic recently in the DotNetNuke.com forums I wanted to take a moment to put my $0.02 out there as to WHY I don't like it.  I will do this by discussing quickly what the differences are in the two approaches, then I'll discuss the "downfalls" of each.

tags: DNN, SQL, .NET 2.0, General Tech, .NET 3.5

SQL Server RANK, ROW_NUMBER, and Partition

01 Nov

Finally getting the opportunity to get back to SQL Server 2005 development, and coming to the conclusion that NOT all of my projects have to support SQL Server 2000 I started looking all of those "fun" new items that we all really wanted. This article is going to go through a scenario that demonstrates how to use Common Table Expressions, Rank() and Partition to get results for a very common data scenario. We will present this information with an introduction to the problem, scripts to setup the problem in a test environment, and lastly the implementation, with a review/summary at the end.

tags: Tutorials, SQL

Limit SQL Server Database List

15 Oct

Recently when working with a client I was asked how in a SQL Server database environment with multiple databases how we can prevent users from seeing all other databases on the server. Permissions are configured to restrict who can actually query the database, but in the end, individuals still can see the names of the other databases on the server, getting potentially an idea of who else you are hosting, or information that you just don't want to share. I have found many blog articles that talk about how to get around this, but all of them were a bit choppy, so I thought I would take a moment to walk through a demo of the process. This article walks through the entire scenario.

tags: Tutorials, SQL

Creating Random SQL Server Test Data

12 Sep

Recently I was tasked with the creation of a large database system that consisted of a database table with 5 date columns, 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.

tags: Tutorials, SQL

Using the IN Clause With Stored Procedures

08 Aug

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.

tags: Tutorials, SQL, .NET 1.1, .NET 2.0, .NET 3.5

SQL Server Table Size Calculation

18 Jul

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 for the method.

tags: Tutorials, SQL

The How, What, and Why of Injection Vulnerabilities

20 May

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. 

tags: DNN, SQL, ASP.NET, .NET 1.1, .NET 2.0, General Tech, .NET 3.5

What Modules Do I Have and How Many of Them?

25 Mar

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.

tags: DNN, Tutorials, SQL

DNN 4.6.0 Upgrade and Whitespace Filter Errors

19 Sep

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.

tags: DNN, Tutorials, SQL

Fixing Lockout Due to Profile Property Requirement

01 Aug

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.

tags: DNN, Tutorials, SQL

Determing SQL Server Table Size

27 Jul

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.

tags: Tutorials, SQL

Fixing DNN Errors that are Related to Search Scheduler

18 Jul

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.

tags: DNN, Tutorials, SQL

Creating a Standard DotNetNuke User Via SQL

14 May

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.

tags: DNN, Tutorials, SQL

View Statistics of your News Article Module Blog

03 May

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


tags: DNN, Tutorials, SQL

Changing Skins via Database (Emergency Procedures)

02 Apr

At one point or another it seems that almost everyone accidentally makes a change to a DNN installation that ends up breaking things. One of these situations that I never considered in the past was the accidental application of a skin with errors. I was contacted by someone today that accidentally applied a skin that caused a null reference exception, this caused the entire site to be unavailable. They couldn't access any pages and couldn't get back to the skin admin pages. Well, this lead me to find a method of resolving the issue via the database. In this entry I will describe the steps needed to change the skin to a default skin via the database.

tags: DNN, Tutorials, SQL

Promoting DNN User to Super user

24 Feb

This follow-up post discusses the process to change a user account to a "host" account via SQL. This is a follow-up from my previous blog regarding creating a DNN Host account via SQL.

tags: DNN, Tutorials, SQL

Creating DotNetNuke Host Account Via SQL

23 Feb

This posting provides a guide on how to create a new host account within DotNetNuke from your SQL Database. This is very helpful if you ever get locked out of your host account for any reason or for all of the new DotNetNuke users that have been having installation problems with the host and admin accounts. NOTE: this is provided for your use at your own risk!

tags: DNN, Tutorials, SQL

Content provided in this blog is provided "AS-IS" and the information should be used at your own discretion.  The thoughts and opinions expressed are the personal thoughts of Mitchel Sellers and do not reflect the opinions of his employer.

Connect with Mitchel

I hope the information here has been helpful. To stay connected you can also subscribe to blog updates via email, contact Mitchel about consulting services, or reach out for assistance via CodeMendor

Content Copyright

Content in this blog is copyright protected.  Re-publishing on other websites is allowed as long as proper credit and backlink to the article is provided.  Any other re-publishing or distribution of this content is prohibited without written permission from Mitchel Sellers.