How to Verify Your Stored Procedures

Posted in: Programming by Steve on May 5, 2010

One of the things I’ve been trying to do is to eliminate the unnecessary stored procedures from our project.  Currently, we have about 150 left, but a lot of them are still used.  Some of them are still left over and no longer are called from our application.  As I was building a new SQL install script, I was trying to run a script of all stored procedures in one database and create them all in a new one.  I found that some of them no longer would work because of changes we’ve made to the database that have broken these stored procedures without even knowing. 

I asked on twitter how this could be done and Argenis Fernandez (@afernandez) suggested that I look into a Powershell script to verify stored procedures still work.

After some investigation on how Powershell and SQL work together, I scraped together this script:

$server = "localhost";		# The SQL Server instance name
$database = "MyDatabase";		# The database name

# Load the SMO assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

# Create the SMO objects
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server;
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database");

# Get the database
$db = $srv.Databases[$database];

# For each stored procedure in the database
foreach($proc in $db.StoredProcedures)
{
    # NOTE: my stored procedures are all prefixed with "web_"
    if ($proc.Name.StartsWith("web_"))
	{
        $proc.TextBody = $proc.TextBody;
        $proc.Alter();
    }
}

If any error occurs, it will display which procedure name did not get altered properly.

In my application, all my stored procedures that I use in the websites are prefixed with “web_”.  This made it easy to check if the procedures used still work. 

SQL Query Madness

Posted in: Programming by Steve on April 15, 2010

I would consider myself to be pretty good with database design and queries, but this one has me stumped.

Our application allows administrators to add “User Properties” in order for them to be able to tailor the system to match their own HR systems.  For example, if your company has departments, you can define “Departments” in the Properties table and then add values that correspond to “Departments” such as “Jewelry”, “Electronics” etc…  You are then able to assign a department to users.

Here is the schema:

In this schema, a user can have only one UserPropertyValue per Property, but doesn’t have to have a value for the property.

I am trying to build a query that can use the PropertyValues as the filter for users.  My query looks like this (SQL Server 2005):

SELECT UserLogin, FirstName, LastName
FROM Users U
LEFT OUTER JOIN UserPropertyValues UPV
	ON U.ID = UPV.UserID
WHERE UPV.PropertyValueID IN (1, 5)

When I run this, if the user has ANY of the property values, they are returned.  What I would like to have is where this query will return users that have values BY PROPERTY.

So if PropertyValueID = 1 is of Department (Jewelry), and PropertyValueID = 5 is of EmploymentType (Full Time), I want to return all users that are in Department Jewelry that are EmployeeType of Full Time, can this be done?

I’m not sure how this can be accomplished without building dynamic SQL, so I thought I would throw this out to see what I could find.

UPDATE:

I posted this question on StackOverflow to see if there are any more ideas

http://stackoverflow.com/questions/2647570/where-clause-on-joined-table-used-for-user-defined-key-value-pairs

Combining Date and Time in SQL Server

Posted in: Programming by Steve on March 5, 2009

I have been working with Log Parser to go through IIS data from the last week. In working with it, I found that the queries I wanted to run were a little obscure to me, and not having time to mess around with learning a new query language, I just imported the data into SQL.

The W3C logs store the date and time in separate columns, and for one of my queries, I wanted to combine the date part with the time part to create a single datetime field. 

So here’s my function that I created:

CREATE FUNCTION [dbo].[CombineDateTime](@dtDate DATETIME, @dtTime DATETIME)
RETURNS DATETIME
AS
BEGIN
    RETURN @dtDate + CONVERT(CHAR(8), @dtTime, 108)
END

Hope this helps!

Revisiting the slow SqlDataAdapter.Fill method

Posted in: Programming by Steve on January 30, 2009

In my previous post, Do Not Taunt sp_updatestats, I described a problem where a query was taking a lot longer than expected.  I followed my own advice and ran sp_updatestats on the database and tried to run my query from the website again. 

What happened? Timeout. The same thing was happening where my query was running immediately in Sql Server Management Studio, but not when using ADO.NET.

I used Red Gate ANTS Profiler to see which method was slowing down.  It determined that the calling of the SqlDataAdapter.Fill(DataTable) method was causing the issue.

I then refactored to use a DataReader to rule out any weirdness and the same issue occurred.  I then looked for any issues with long queries in .NET, but should be shorter in SSMS.

I came across a couple of articles mentioning that if you want to replicate this behavior in SSMS, run this statement before you execute your proc:

SET ARITHABORT OFF

This will mimic the behavior of ADO.NET. 

So how do you fix this?  There are 3 things I found that you could do.  First, make sure your database indexes are reindexed in some kind of maintenance plan.  I didn’t have any kind of maintenance on my database.  If you need to reindex immediately, execute this SQL:

-- Execute this to rebuild all for a given database. Replace the <databasename> after EXEC.
EXEC <databasename>..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')', @replacechar='*'

The next solution would be to execute “SET ARITHABORT ON” before running your stored procedure.

Using cn As New SqlConnection(Me.ConnectionString)
    Dim cmd As New SqlCommand("web_LongIntenseQuery", cn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add("@UserID", SqlDbType.BigInt).Value = UserID
    cn.Open()
    'this will allow the sproc to run
    Dim arithabortCmd As New SqlCommand("SET ARITHABORT ON", cn)
    arithabortCmd.ExecuteNonQuery()

    Dim ret As Integer = ExecuteNonQuery(cmd)
    Return (ret = 1)
End Using

Or the last option would be to add the SET ARITHABORT ON in your sproc.

More on stored procedure performance from Steven Smith (ASPAdvice.com, SteveSmithBlog.com)