Archive for February, 2010

SQL Server Print Logging in TSQL / Stored Procedures

11 February, 2010 Leave a comment

In tsql when running stored procedures you may wish to output debug statements.

The problem is that in a long running tsql the “Print” statements will not output until the entire proc has completed.

The solution is to use the RAISERROR statement.

You dont want to raise an error so you call it with a 0 error code, but it has the ability to specify “WITH NOWAIT” which outputs the message straight away.

Combine this with my previous post and you can do this;

set @msg = dbo.udfGetDurationMsg('Task 1', @jobStart, getDate())

which would produce output like:

Task 1 – 0mins 43sec


Categories: SQL Server

SQL Server Print logging message function

11 February, 2010 Leave a comment

I use this to log duration information when I am testing stuff with Sql

create function [dbo].[udfGetDurationMsg]
(@msg varchar(max), @start datetime, @end datetime) RETURNS VARCHAR(100)
declare @diff bigint
set @diff = DateDiff(ss, @start, @end)
return @msg + ' - ' + Convert(varchar, @diff / 60) + 'mins ' + Convert(varchar, @diff % 60) + 'sec'

I find this useful when testing how loing things are taking in big procedures

You call it like this

print dbo.udfGetDurationMsg('Create Index xyz', @start, @end)

Here is a second version this puts tabs in and formats as time so you can copy and paste into excel 🙂
CREATE function [dbo].[udfGetDurationMsg](@msg varchar(max), @start datetime, @end datetime) RETURNS VARCHAR(100)
declare @diff bigint
declare @hour int
declare @min int
declare @sec int
declare @hourDisplay varchar(2)
declare @minDisplay varchar(2)
declare @secDisplay varchar(2)
set @diff = DateDiff(ss, @start, @end)
set @hour = @diff / 3600
set @min = @diff / 60
set @min = @min % 60
set @sec = @diff % 60
set @hourDisplay = Convert(varchar, @hour)
if(@hour < 10)
set @hourDisplay = '0' + @hourDisplay
set @minDisplay = Convert(varchar, @min)
if(@min < 10)
set @minDisplay = '0' + @minDisplay
set @secDisplay = Convert(varchar, @sec)
if(@sec < 10)
set @secDisplay = '0' + @secDisplay
return @msg + char(9) + @hourDisplay + ':' + @minDisplay + ':' + @secDisplay

Categories: SQL Server

SQL Server Performance Testing

9 February, 2010 Leave a comment

When attempting to assess the performance of a given script on SQL Server it is quite difficult.

The caching of execution plans etc etc, lead to the script taking 20 seconds on the first pass, 10 on the next and settles in to only taking 2 seconds.

This makes it hard to compare apples with apples.

I have found that running the following improves the consistency of the reporting timings, according to books online

“Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.”


It runs each script with a “Cold buffer cache” and gives you a consistent starting point for comparing queries and gives you a more realistic time frame for your SQL execution.

Also helpful when attempting to time your query execution time:

set statistics time ON

And to further analyse poorly running queries

set statistics IO ON

With IO statistics on you can see the results of physical disk activity. For example previously I used this to see that on a particular query when I changed one small part of the query it caused the execution plan to change drastically and the disk IO went from doing a few thousand reads on a very large table to a couple of million reads, which blew out the performance of the query.

Hope this is useful

Categories: SQL Server

Reporting Service 2008 Install – “Input String was not in a correct format.”

9 February, 2010 1 comment

I got this error the other day whilst trying to install Reporting services.

After much digging I found this blog entry that fixed it:

In short the performance counters on the machine were corrupted, so the following command rebuilt them and fixed the RS Install error.

c:\windows\system32\lodctr /R

RS was left in a half installed state:

  • it was unable to be installed because it thought it was already installed
  • it was unable to repair cause it said it was not installed
  • Solution was to run the command above, uninstall, (I removed all SQL Server 2008 components) and re-install from scratch.

    Categories: Reporting Services

    Deploy reporting services web parts (RSWebParts) to SharePoint 2010

    9 February, 2010 Leave a comment

    I had to do this the other day and here are the short and curlies of doing it.

    If you have SQL 2008 installed the file can be found @
    C:\Program Files\Microsoft SQL Server\100\Tools\Reporting Services\SharePoint

    The stsadm.exe can be found here on a 2010 SharePoint install
    C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN

    Cd to this directory and run this command

    STSADM.EXE -o addwppack -filename “C:\Program Files\Microsoft SQL Server\100\Tools\Reporting Services\SharePoint\” -globalinstall

    The webparts are now available in SharePoint.
    They show up under miscellaneous, when you try to add a web part

    Categories: Uncategorized