July 2010 - Posts

Don’t forget today (16th July 2010) is the last day to submit sessions for SQLbits 7 in York.

Thanks all those that have submitted so far the sessions look great.

Posted by simonsabin | 2 comment(s)

You’ve got an error and it gives you a line number

Msg 50000, Level 16, State 1, Procedure TestToSeeWhatTheErrorLineNumberRefersTo, Line 14

Some Error

What line does that actually refer to?

Well its the line in the original batch that compiled the procedure and so in the case below it includes the comments and the white space before it.

Try it for yourself



Some random comments about how great sqlbits is just to fill some space


Make sure you come along to York at the end of September






create procedure  TestToSeeWhatTheErrorLineNumberRefersTo


raiserror ('Some Error',16,1)



exec TestToSeeWhatTheErrorLineNumberRefersTo

It gets interesting when you use dynamic SQL. Change the procedure as follows




Some random comments about how great sqlbits is just to fill some space


Make sure you come along to York at the end of September






alter procedure  TestToSeeWhatTheErrorLineNumberRefersTo


execute ('raiserror (''Some Error'',16,1)')



You should find that you now get the following error.

Msg 50000, Level 16, State 1, Line 1

Some Error

Note two things the line number has changed AND it no longer refers to the stored procedure. Thats because you no longer are in the scope of the stored procedure but a lower scope.

This is why when ever I use dynamic SQL I always add a debug statement and PRINT the SQL that is going to be executed.

alter procedure  TestToSeeWhatTheErrorLineNumberRefersTo

@debug int =0


declare @sql nvarchar(1000)

set @sql = '/* some comment or other code

more comments


raiserror (''Some Error'',16,1)'


if @debug > 0

  print @sql


exec sp_executesql @sql

This allows you to get the SQL statement that was executed and figure out which line it refers to. This is especially useful when you are adding where clauses and joins to your SQL and so trying to figure out what line the error refers to is important.

Did you know that if you double click on an error in management studio it tries to take you to the line that failed. This only works well if you are running a script in management studio, it doesn’t take you to the correct line if you are executing stored procedures.

Posted by simonsabin | 2 comment(s)
Filed under: ,

Having kids is an awesome thing that enlightens you in more ways than you can think. One area I’m going to blog about at some time is the impact of reading to my kids on my presenting. This post is about my 2yrs olds current passion for the word why?

Everything, statement or question is followed by a response of why? He started off by asking “What’s that doing ?” which when asked about a poster on a wall or a cow in a field is a little difficult to answer. My Dad thinks he was asking “Whys that there ?”. Anyway now its just “why?”.

This is interesting because i think most of the time as you grow up you don’t ask as many questions. I think you would be very unpopular at work if you did what my son does but there is a middle ground. Is the reason we don’t ask questions because we think we know everything or because we don’t want to know any more. I think its a combination of both, and partly comes down to the fact that often “We don’t know what we don’t know”. However unlike us children just want to know everything which I think is great. There is a downside, they are a sponge and will believe you and remember what you said. Like when we were on holiday in France and the owner of the Chambre D’hote said there was a duck looking after its eggs by a barn and so I told by 2 year old, when he asked “Whats that doing ?”, when pointing at the barn, “Thats were the monster lives” just a quip in passing. However for 3 days that was that, thats where the monster lived even though we told him it wasn’t.

Does this apply to SQL. You betya. How much should you know about SQL ?

My answer is enough that you can make correct use of. Having a little information is dangerous, a bit like a grenade without the pin. The one thing I’ve learnt since becoming an MVP and being party to some of the MVP discussions, is that SQL is very very deep and even those that are the top experts don’t know everything and it also changes.

Given the breadth and depth of SQL I would suggest that you decide what you want to be the best in and then focus on that. But importantly always ask questions.If you get an answer and realise it’s not really in your area thats great, it helps you define the edges of your knowledge.

Assumptions are dangerous, if you can get answers to questions you will be much better off. We are currently planning an extension, and we are amazed at the difference in attitude of some of the builders, some ask really indepth questions others don’t. Whilst the ones that ask questions will have asked some that don’t really apply, I know that their quote is going to be accurate whereas the others we are going to be hit by all sorts of unkown/unplanned costs later on in the project because they didn’t ask enough questions

Its healthy to ask questions.If you don’t you won’t moved forward, your knowledge will stagnate. We are a luck generation that were brought up not fearing technology and what it can do. Have a go, ask a question, what can you loose, you will probably be better off for it in the end.

Be like a 2 year old and next time ask “Why?”

Posted by simonsabin | 6 comment(s)

Interesting to read this blog by Mark Blakey about database mirroring network performance http://sqlblogcasts.com/blogs/blakmk/archive/2010/04/30/optimising-database-mirroring-over-wan.aspx

He found that he could reduce network traffic by 75% by turning off encryption. This is understandable because you can’t compress encrypted data, there aren’t any patterns of data to encrypt. Its why if you want encrypted compressed backups you still have to go to 3rd parties like Redgate and Quest. Thats because to do it in SQL you have to encrypt the data first which means the built in backup compression in SQL Server can’t get any compression.

Posted by simonsabin | 2 comment(s)

One thing that is often required of a database developer is to zip up some extract files and send them to someone, or unzip some files that have been sent to them. Currently you can only zip and unzip a single file, which is good but has huge limitations.

Well the bcl (base class libarary) team have blogged about support zip archives.


This is awesome news and is one I am sure I’ll be putting into a script task in SSIS for processing trace files, perfmon data etc.

I hope this gets into the bcl in time for SQL 11.

Posted by simonsabin | 2 comment(s)

What are peoples views on intellectual property and being an independent worker?

I don’t think I’ve yet had two contracts with the same IP clause, whats frustrating is having to read them to make sure you aren’t giving away rights to all the content you own.

What it comes down to as someone doing work like troubleshooting. If I develop a query against a DMV or develop a process for processing xEvents then in many contracts I have the client owns that meaning I can’t use it again elsewhere. I understand when what you develop is core to their business i.e. a trading application, but I wonder if one can enforce IP rules on utility type code that is core for your business and not theirs.

Posted by simonsabin | 2 comment(s)

I’m replacing my DVD drive with a second harddrive bay and the only reason I currently need the DVD is in case I need to do a system repair, to start the windows repair utility.

Annoyingly the System repair utility doesn’t allow you to specify a USB drive as the destination for your repair disc.

To find out what I needed I created a system disc properly and then copied the files to the USB drive and heh presto it worked.

I didn’t want to waste DVD but couldn’t find any other way to find the files I needed.

The files you need can be found on the setup disk.

You need,


The files (and importantly the wim I think) are the full setup files and not just the repair ones, so you get the setup dialogue, thats fine it just means that you have to click the repair option when the setup dialogue fires.

I’ve created a useful batch file to copy the files you want.

@echo off
echo ---------------------------------------------------------
echo .
echo . Windows 7 System Repair disk creation.
echo .
echo . This allows you to copy the required files for a system
echo . repair disk to any device.
echo .
echo . Simon Sabin 2010
echo ---------------------------------------------------------
echo .

if "%1"=="" (
    echo You need to specify a drive and optionally a folder to get the source files from
    echo There should be a folder called boot and sources in that location
    goto error
if "%2"=="" (
    echo You need to specify a drive to use as the repair drive
    goto error

set source=%1%
set target=%2%
set err=0

if exist "%target%\*.*" (
echo Files already exist on the target, if you continue they will be overwritten
choice /C YN /M "Do you want to continue?"
if errorlevel 2 ( goto exit)

if not exist "%source%\boot\bcd" (
    echo The bcd file in the boot folder is missing
    set err=1
if not exist "%source%\boot\bootfix.bin" (
    echo The bootfix.bin file in the boot folder is missing
    set err=1
if not exist "%source%\boot\boot.sdi" (
    echo The boot.sdi file in the boot folder is missing
    set err=1
if not exist "%source%\sources\boot.wim" (
    echo The boot.wim file in the sources folder is missing
    set err=1
if not exist "%source%\bootmgr" (
    echo The bootmgr file in the root folder is missing
    set err=1

if "%err%" == "1" (
  goto error

if not exist %target%\boot mkdir %target%\boot
if not exist %target%\sources mkdir %target%\sources
xcopy %source%\boot\bcd %target%\boot /Y
xcopy %source%\boot\boot.sdi %target%\boot /Y
xcopy %source%\boot\bootfix.bin %target%\boot /Y
xcopy %source%\sources\boot.wim %target%\sources /Y
xcopy %source%\bootmgr %target% /Y

got :exit
echo An error occurred
echo .
echo Example
echo .        MakeSystem f: g:
echo .
echo . This copies the system repair files from the media on f: to the g: drive
echo .
echo This program copies the following files on to the target
echo \boot\bcd
echo \boot\boot.sdi
echo \boot\bootfix.bin
echo \sources\boot.wim
echo \bootmgr


Posted by simonsabin | 2 comment(s)

I was lucky enough to host a usergroup with some of the guys from the StreamInsight team last month. A number of questions arose regarding licensing and processing limitations.


Here are some answers that I have put together, they aren’t legally binding so don’t hold me to any of them. This is a new technology with a different behaviour to other SQL Server features. As with all licensing questions you have to verify with your licensing provider as it may vary.


1.       Is the processing number a governed limit?

No the limit is restricted by the amount of resources the difference editions will use.

DC edition will use all processors in the box whilst all other editions will not.

The built in server will follow these rules to. 

2.       So could I end up processing messages faster than the limits defined for each edition?

Yes if you processing is very efficient, have high performance hardware, etc 

3.       What resources are limited in each edition

With Enterprise edition and lower the StreamInsight engine won’t use all the processing power of the server, potentially only one core. With Data Center StreamInsight will use all cores on the server. 

4.       Is the limit per stream or per server?

As mentioned above the limit isn’t a limit, it is merely a result of the resources that each edition will use.

The limit is per engine, so if you have 4 applications hosting the engine they each will use the resources allowed for the edition that is installed.

5.       Could you have multiple applications using streaminsight to process data?

Yes you can host the StreamInsight engine in multiple applications on the server. 

6.       So could I run lots of instances of your application and use all the resources on your server?
Yes you could, that would be a cunning way to get round the processing limitations. You would have to have a way of partitioning your streams so multiple engines could process the data. Interestingly StreamInsight is also good at doing that.


For more information on StreamInsight go to http://www.microsoft.com/sqlserver/2008/en/us/R2-complex-event.aspx or their blog http://blogs.msdn.com/b/streaminsight

Thanks to Torsten Grabs, Azam Husain, Sethu Raman and Roman Schindlauer for helping me put this together.

Posted by simonsabin | 3 comment(s)

I was in discussion with the StreamInsight team about the licensing of the StreamInsight engine and was getting my knickers in a twist about the fact that you could host multiple SI engines with multiple apps with only 1 SQL license and thus work around the processing limitation on Enterprise edition and lower. My issue was that I thought that this was dodgy with respect to the licensing because you effectively had multiple instances and Standard edition and lower you had to license each instance.

So having wiped the egg off my face I can now point out to anyone that thought the same as me, you’re wrong.

In SQL 2000 up until April 2003 you had to license each instance, but since then you don’t. How on earth did I miss that for 7 years.

That means from a consolidation perspective you could consolidate lots of instances onto a dualx8 core server with 256GB Ram, pay for 1x2 processor standard edition license and have 4 instances running each with 64GB ram. How cool.

If you still don’t believe me read this from the SQL 2008 licensing overview http://download.microsoft.com/download/1/e/6/1e68f92c-f334-4517-b610-e4dee946ef91/2008%20SQL%20Licensing%20Overview%20final.docx 

When licensed Server / CAL

Workgroup and Standard editions now allow you to run any number of instances of the server software in one physical or virtual operating system environment on the licensed server at a time. Previously, only the Enterprise edition of the Server license allowed multi-instancing. This is a great incentive for customers to adopt the Server/CAL model.

For Enterprise edition, for each server to which you assign a software license, you may run, at any one time, any number of instances of the server software in one physical and any number of virtual operating system environments on that server.

When licensed Per Processor

With Workgroup, Web, and Standard editions, for each server to which you have assigned the required number of per processor licenses, you may run, at any one time, any number of instances of the server software in physical and virtual operating system environments on the licensed server. However, the total number of physical and virtual processors used by those operating system environments cannot exceed the number of software licenses assigned to that server

For enterprise edition there is an added option: if all physical processors in a machine have been licensed, then you may run unlimited instances of SQL server 2008 in one physical and an unlimited number of virtual operating environments on that same machine”

You need to be aware that with SQL 2008 R2 the situation changes for EE edition in that you are limited to 4 virtual servers.


Posted by simonsabin | 4 comment(s)
More Posts « Previous page