T-SQL Tidy Blog

SQL Relay National and Local Prizes

2011-09-21T21:23:00.001+01:00

Today I found out what a great prizes are being offered at SQL Relay..

If you come along to one of the local events (running between 3rd to 5th October: In Birmingham, Manchester, Farnham, Kent, Bristol, London, Hatfield, Leeds, Southampton, Edinburgh, Cardiff, Exeter, Cambridge, Essex and Maidenhead)

Not only will you have the chance to go home with one of the following prizes.

Red Gate shirts
Wrox book / stickers
SQLBits 10 Friday ticket
1 PASS 2010 DVD

But you will also be entered in a draw for the following prizes;

Red Gate SQL Developer bundle
A collection of Wrox books
SQLBits 10 Conference Pass
1 PASS 2010 DVD

Its an amazing list of prizes!

The SQL Relay ‘Finishing line’ event on the 6th of October where Itzik Ben-Gan will be speaking is very close to being fully booked!

So register now before its too late!

A big thank-you goes out to all the companies that have sponsored the SQL Relay.

SQL Relay Swag from Red Gate

2011-09-16T20:51:00.000+01:00

Another day, another parcel...
This time an intriguing small box from Red Gate.

I could tell it was from Red Gate.. the red box, the Red Gate logo.. all clues..

Inside...
Two Red Gate t-shirts and two Red Gate pens.

So two lucky people are going to get Red Gate t-shirts at the next Southampton SQL Server User Group.

The SQL Community in the UK will be hosting its first SQL Relay, a series of events culminating in Itzik Ben-Gan (SQL Server Author and MVP, Co-Founder of SolidQ) presenting at Microsoft’s London HQ, Cardinal Place on the 6th October.
Southampton's SQL Relay event is on the 5th of October, Justin Langford (Coeo) will be talking about SQL Server Denali and Nicholas Colebourn will be discussing designing tables for scale.

SQL Relay Swag from Wrox

2011-09-13T21:16:00.001+01:00

When I got in from work today there was something waiting for me…
The swag from Wrox for the SQL Relay…

Coasters, key rings, pens, stickers and a copy of Professional SQL Server 2008 Internals and Troubleshooting. Justin Langford one of the authors will speaking at Southampton SQL Server User Group’s SQL Relay event on Wednesday 5th of October. [I will see if he will sign it! The book is staying in its plastic covering until the SQL Relay event]
SQL Relay is a series of SQL User Groups happening up and down the country between the 3rd and 6th of October,
Events are happening in: Birmingham, Manchester, Farnham, Kent, Bristol, London, Hatfield, Leeds, Southampton, Edinburgh, Cardiff, Exeter, Cambridge, Essex and Maidenhead..
See you there!!

SQL Relay–Filling up fast.

2011-09-10T00:07:00.001+01:00

The SQL Relay events are booking up fast; over 239 people have registered so far.

So register as soon as possible to avoid disappointment!!

If you know you can’t make it, unregister – it will mean someone else can go in your place.

The London (finish line) Event is 70% booked already!

SQL Relay is part of the National SQL Server Week, which starts with SQLBits on the 29th of September and finishing with Itzik Ben-Gan presenting at the finishing line event.

For details of the SQL Relay visit http://www.sqlserverfaq.com

SQLBits + SQLRelay = National SQL Server Week

National SQL Server Week

2011-09-08T17:25:00.000+01:00

From the 29th of September to the 6th of October, it’s the first National SQL Server Week in the UK.

SQLBits + SQL Relay = National SQL Server Week

29th September - Day 1 - Thursday - SQLBits Training Day

30th September - Day 2 –Friday - SQLBits Conference Day

1st October - Day 3 –Saturday - SQLBits Conference Day (Free)

2nd October - Day 4 - Sunday – Rest, Ahhh....

3rd October - Day 5 – Monday - SQL Relay Day 1 - Birmingham  ~   Farnham, Surrey  ~   Kent  ~   Manchester (Free)

4th October - Day 6 – Tuesday - SQL Relay Day 2 - Bristol  ~   Hatfield  ~   Leeds  ~   London  ~   Maidenhead (Free)

5th October - Day 7 – Wednesday - SQL Relay Day 3 - Cambridge  ~   Cardiff  ~   Edinburgh  ~   Essex  ~   Exeter  ~   Southampton (Free)

6th October - Day 8 – Thursday - SQL Relay Finish Line - London - Cardinal Place (Free)

Where else can you get 7 days of SQL?

Essex, Cambridge and Maidenhead…

2011-09-06T22:18:00.001+01:00

What do Essex, Cambridge and Maidenhead all have in common?

Yes – you guessed it!

They all have new SQL User Groups (UG) and they are all joining in the SQL Relay! A Four day SQL-a-thon, a series of events culminating in Itzik Ben-Gan (SQL Server Author and MVP, Co-Founder of SolidQ) presenting at Microsoft’s London HQ, Cardinal Place on the 6th October.

Check out the SQLServerFAQ site for details of your nearest community event.

How to write a SQL Server Management Studio Add-in for Denali (CTP3) (Part 2)

2011-08-31T23:52:00.001+01:00

In my previous post on writing a SSMS Add-in for Denali I detailed the steps that you would need to take to get the Add-in loaded and running but it didn’t do a great deal.

Now we take it a step further. But not too far(!)

I’ve done a number of things;

Hooked up to the events class in DTE, so we can get a view on what’s happening inside the Add-in.
Added some T-SQL code formatting functionality, to show how to access the contents of a query window.
Added a context menu to the Object Explorer
Put all the source on Codeplex under a new project called ‘’SQL Server Management Studio Add-in for Denali (SSMS Addin)” Url: http://ssmsaddindenali.codeplex.com/ Feel free to download or/and join the project and contribute.

1) As we all know, SSMS Add-in’s only work because of a deep dark magic, but there are a few ways we can peek inside to see what’s happening.

The DTE object we get during the OnConnection method has a class full of events – so lets hook them up and see what happens.

  EnvDTE.TextDocument doc = (EnvDTE.TextDocument)ServiceCache.ExtensibilityModel.Application.ActiveDocument.Object(null);

Returns a TextDocument object from which you can get the contents of the window (SQL).

3) Just like previous versions add-ins, you use the same code to pick up Object Explorer events.

   1:                 objExplorerService = (ObjectExplorerService)ServiceCache.ServiceProvider.GetService(typeof(IObjectExplorerService));

   2:                  cs = (ContextService)objExplorerService.Container.Components[0];

   3:                  cs.ObjectExplorerContext.CurrentContextChanged += new NodesChangedEventHandler(ObjectExplorerContext_CurrentContextChanged);

Download the source from codeplex, check it out, play with it, check it in!

http://ssmsaddindenali.codeplex.com/

SQL Relay - 3 Countries, 4 Days, 16 Events.

2011-08-27T00:19:00.001+01:00

You wait ages for a User Group, then a whole load turn up all at once!!

Between the 3rd and 6th of October, up and down the UK the SQL community is coming together; this phenomena is known as “SQL Relay”.

Events are happening in (deep breath): Birmingham, Manchester, Farnham, Kent, Bristol, London, Hatfield, Leeds, Southampton, Edinburgh, Cardiff, Exeter, Cambridge,Essex and Maidenhead.. {and a partridge in a pear tree}.

3 Countries, 4 Days, 16 Events.

So if you have never been to a User Group or go all the time, have only just heard about SQL or are waiting for Denali to hit the streets – get along to one of the SQL Relay events.

[Updated 7/9/2010] Itzik Ben-Gan (SQL Server Author and MVP, Co-Founder of SolidQ) will be presenting at Microsoft’s London HQ, Cardinal Place on the 6th October.

And all this right after SQLBits 9 in Liverpool - 3 days of top SQL Server content given by SQL Server Experts from around the world, including Microsoft Certified Architects (MCA), Microsoft Certified Masters (MCM) and Microsoft Most Valued Professionals (MVP). If you have not already registered, then you better hurry!

Southampton SQL Server User Group is now a PASS chapter

2011-08-25T20:09:00.001+01:00

The Southampton SQL Server User Group (SSSUG) has now been accepted as a PASS chapter (The Professional Association of SQL Server).

We are the 5th UG in the UK to become a chapter.

It's really Jonathan Allen’s fault. I saw his User Group up on the PASS site and thought we need to be part of this!

So what is PASS? Well, its mission statement says it all; ‘Empower the global Microsoft SQL Server and Business Intelligence community to connect, share, learn and be inspired through networking, knowledge sharing, peer-based learning, and the ability to influence the direction of Microsoft SQL Server data platform.’

It’s certainly a lot to live up to, but I’m sure we are up to it!!

How to draw a Polygon in SQL Server

2011-08-20T23:13:00.001+01:00

In my previous post 'Drawing a circle in SQL Server (almost)' I showed how to plot 360 points to draw a circle. {Well} It looked like a circle, but to SQL Server, it was just a series of points.

We can use a LineString to draw a shape, but the most appropriate type is the Polygon.

The polygon is a closed [at least 3 sided] shape, i.e. the first point is the same as the same point. If it’s not, SQL will throw an exception. SQL Server will also throw an exception if you attempt to create a polygon with just two sides..

   1:  -- Drawing a polygon

   2:  declare @Sides float = 3

   3:  declare @count int = 0

   4:  declare @size int = 10

   5:  declare @firstpoint varchar(400)

6:

   7:  declare @sql varchar(8000);

8:

9:

  10:  set @sql =  'POLYGON(('

11:

  12:  while(@count < @Sides)

  13:  begin

14:

  15:  set @sql = @sql + cast(SIN(RADIANS((CAST(@count AS FLOAT)

  16:              /CAST(@Sides AS FLOAT))

  17:              * 360)) * @size as varchar)

  18:             + ' ' +

  19:              cast(COS(RADIANS((CAST(@count AS FLOAT)

  20:              /CAST(@Sides AS FLOAT))

  21:              * 360)) * @size as varchar) + ','

22:

  23:  if @count = 0

  24:  set @firstpoint  = cast(SIN(RADIANS((CAST(@count AS FLOAT)

  25:              /CAST(@Sides AS FLOAT))

  26:              * 360)) * @size as varchar)

  27:             + ' ' +

  28:              cast(COS(RADIANS((CAST(@count AS FLOAT)

  29:              /CAST(@Sides AS FLOAT))

  30:              * 360)) * @size as varchar)

31:

  32:  set @count = @count + 1

33:

  34:  end

35:

  36:  set @sql = @sql + @firstpoint + '))'

37:

  38:  print @sql

  39:  DECLARE @g geometry;

  40:  SET @g = geometry::STGeomFromText(@sql, 0);

  41:  select @g

42:

Simply change

   1:  declare @Sides float = 3

to be how ever many sides you need. Simple.

Here are a few examples.

All scripts can be found on my SkyDrive using the link below.

SQL Server in the Evening (3rd Event)

2011-08-20T00:30:00.001+01:00

Last night I did my first slot as a presenter. I’d like to thank Gavin Payne, the event organiser for giving me the opportunity to speak.

So how I did go? Okay, I think. It is very difficult to know how you go down until you get feedback (so if you attended, please feel free you email me your comments).

The initial feedback was positive and incredibly useful (Thank-you Christian), its given me ideas of how to structure my next presentation…

But back to the evening…

My presentation was on Geospatial data types in SQL Server 2008 (r2 Express) – to try and make my presentation more memorable I used props, balls for Points, string for LineString and Polygons –> I hoped that it would be different , not in code or PowerPoint would help the concepts stick..

If you look really closely you can see the string.. (Thanks again Christian – I took no pictures all evening).

After me, Vilosh (Smart Reporting Solutions) did a demo of his BI product.

Then the buffet, although I feel the term buffet doesn’t do justice to food. I was bowled over by the quality and quantity. The Ramada did a great job.

Then Jen Stirrup presented iPad and PowerPivot- Mobile Business Intelligence in Action. A really thought provoke presentation on the future of mobile BI and BI in general. The iPad is a game changer and a great business tool. Check her previous and next SQLBits sessions out.

Next up was Keith Burns presenting Denali CTP3 - An overview and update. I was excited about CTP1 of Denali, but it was a bit of a non-starter, yes, it was a new engine and new SSMS, but the cool sexy features, Crescent, Column Store indexes, file table, Juneau etc etc were all missing, but now in CTP3 – they are all there!!!!!!

I feel like it’s time to get enthusiastic about Denali again. Keith briefly mentioned Feature pack for Denali…Check out the goodies!!

One of the great things about going to User Groups is seeing wonderful presenters talk about subjects they love, but its a fantastic place to meet up with the twitterati – people you only know by cryptic code names are real flesh and blood people.

I’d like to thank Gavin again for putting on a great evening and I’m really looking forward to the next one.

'Denali' Community Technology Preview 3 (CTP 3) Feature Pack

2011-08-19T17:19:00.001+01:00

I was at Keith Burn’s presentation of ‘What’s new in Denali CTP3’ at SQL Server in the Evening (Gavin Payne’s User Group) last night and I heard about Denali Feature pack.

A small about of googling with Bing later, this popped up - ‘Feature Pack pour SQL Server "Denali" CTP3’

I had a Homer Simpson moment ”Hmmm Feature Pack...”

How to write a SQL Server Management Studio Add-in for Denali (CTP3) (Part 1)

2011-08-17T08:00:00.000+01:00

Writing a SSMS Add-in is not for the faint hearted or those with weak constitutions, but for those who persevere the rewards are small and fleeting, yet worth while.

This post is for people who know roughly what they are doing, so without any more non-sense, here we go.

You will need;
                       Visual Studio 2010 (I have Premium - I've not tried other editions)
                       SQL Server Denali CPT3 (Should work with CTP1 and CTP2 - but I've not tried)

1) Start up VS2010

2) Create a new project, a Visual Studio Add-in, you should find this under 'Other Project Types \ Extensibility' (You may not have this project type, do not panic! I will make this source available)

3) Give the project a sensible name - like MyAddin1 :-)

4) The Add-in Wizard should start, 'Click Next'

5) Pick you language of choice, I picked 'Create an Add-in using C#' (C++/CLR and C++/ ATL are both greyed out for me) - Click Next.

6) Un-Select 'Microsoft Visual Studio 2010 Marcos' - Click Next.

7) Give your Add-in a sensible name and description. - Click Next.

8) Tick 'Yes, create a Tools menu Item...' and 'I would like my Add-in to load...' - Click Next.

9) Its up to you if you want a 'Help About' box. - Click Next.

10) Click Finish.

You are almost ready, we just need to sort out the Add-in registration.

Your new Add-in will be already setup to be a Visual Studio 2010 Add-in.
We just need to customise it a bit, so that SSMS recognises it.

11) VS2010 has created you two .Addin files, delete the 'MyAddin1 - For Testing.AddIn'

12) Locate the 2nd .Addin file, it will be somewhere like; c:\users\XXXX\documents\visual studio 2010\Projects\addin\MyAddin1\MyAddin1\MyAddin1.AddIn
Move it to C:\Users\Public\Documents\microsoft\MSEnvShared\AddIns
This is the folder that SSMS is looking for Add-in files.
[Update 19/08/2011: You may need to create the MSEnvShared\AddIns path. Thanks to Raphael for spotting this]

13) You can now delete the MyAddin1.Addin from your project.

14) Edit the C:\Users\Public\Documents\microsoft\MSEnvShared\AddIns\MyAddin1.Addin' file.
Change the top section from :

<HostApplication>
<Name>Microsoft Visual Studio</Name>
<Version>10.0</Version>

</HostApplication>

to be:

<HostApplication>
<Name>Microsoft SQL Server Management Studio</Name>
<Version>*</Version>

</HostApplication>

and

Update the Assembly to be the full path to your DLL.

<Assembly>c:\users\XXXXX\documents\visual studio 2010\Projects\addin\MyAddin1\MyAddin1\bin\Debug\MyAddin1.dll </Assembly>

14) Update the Project Properties, in the Debug section, update Start external program to be C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe (Of course, update this if you have installed Denali somewhere else)

15) Turn off P-Invoke warnings. Press CRLT + ALT + E - In Managed Debugging Assistants, find PInvokeStackImbalance, untick it.

16) Add a break point to the Connect() line, Hit F5 and run for the hills - because you should have a SQL Server Management Studio Add-in working.

The Add-in doesn't do very much yet, I will save that for another post.

But you have an Add-in running, which was easier to write than Add-ins for SQL 2005 and 2008.

The only tricky, bit was the location and contents of the .Addin file.

Have fun writing your own Add-ins!!

[Updated 19/08/2011: I’ve got to say thanks for Mladen Prajdić for the telling me what the version needs to be in the XML]

Can not connect to SQL Server remotely

2011-08-07T21:48:00.000+01:00

Having trouble connecting to a new instance of SQL on a new Windows 2008 install?

Took me longer to work out than I care to admit.

I checked all the basics, allow remote connections, browser service running etc.etc..

Answer: Turn off Windows Firewall!! DOH! (Or at least add the SQL ports to the allowed list!)

SQL Server User Group in Southampton 10th August

2011-08-07T21:36:00.000+01:00

The next Southampton SQL Server User Group is on the 10th of August.

Neil Hambly (@Neil_Hambly | web ) and John Martin (@SQLServerMoney) are both presenting - for more details check out the SQLServerFAQ.com website.

See you there!!

Drawing a circle in SQL Server (almost)

2011-07-28T18:28:00.002+01:00

I've just started playing with the Geo spatial data types in SQL Server 2008 R2 Express.
The first thing I wanted to do was draw a circle. How difficult could it be?

I could not find an example on the interweb - so I put one together.

   1:  DECLARE @Points AS FLOAT = 360;

2:

   3:  DECLARE @count AS INT = 0;

4:

   5:  DECLARE @size AS INT = 1;

6:

   7:  DECLARE @tmpTable TABLE (

   8:      geo geometry);

9:

  10:  WHILE (@count < @Points)

  11:      BEGIN

  12:          INSERT INTO @tmpTable

  13:          SELECT geometry::STGeomFromText('POINT(' + CAST (SIN(RADIANS((CAST (@count AS FLOAT) / CAST (@Points AS FLOAT)) * @Points)) * @size AS VARCHAR) + ' ' + CAST (COS(RADIANS((CAST (@count AS FLOAT) / CAST (@Points AS FLOAT)) * @Points)) * @size AS VARCHAR) + ')', 4326) AS Posn;

  14:          SET @count = @count + 1;

  15:      END

16:

  17:  SELECT geo

  18:  FROM   @tmpTable;

Now choose the 'Spatial Results' tab - you might have never seen this before. It only appears if the results set has a geospatial data type in it.

The observant of you might notice, it's not actually a circle. Just 360 different point (there is a different!) But I'll cover that in my next blog post.

[UPDATED: Added link to scripts]
All scripts can be found on my SkyDrive using the link below.

Southampton SQL User Group Next Events

2011-07-18T17:29:00.003+01:00

[Updated: For latest news and events at Southampton SQL User Group follow this link]

I've been busy organising the next events for the Southampton SQL User Group and here they are.

10th August, John Q Martin and Neil Hambly	Details
7th September, Jessica Meats and Simon Sabin (MCM/MVP)	Details
5th October, Justin Langford (MVP) and TBC	Details
One year Anniversary 9th November, Allan Mitchell and James Rowland-Jones	Details

For details of your local SQL Server User Group visit http://www.sqlserverfaq.com

SQLBits 9 - Session submission

2011-07-17T23:13:00.001+01:00

I’ve finally done it, I’ve submitted a session for SQLBits 9!!

God news knows if it will be picked, but it will only get picked if people vote for it!!!

So get on and vote!! If not for me, then someone else! http://sqlbits.com/information/publicsessions.aspx

SQL Server User Groups in the UK!!

2011-04-16T21:52:00.003+01:00

I tried to get this list together before SQLBits 8, but failed. It contains SQL Server User Groups and the postal code of where it was held. I've included SQLBits (for fun!). Its not every single one thats ever happened, buts ones that have happened over the last 7 or 8 months. I apologise if I've missed you off!!

UG Name,City,Postcode
---------------------
Scottish Area User Group,Edinburgh,EH1 3EG
SQL Herts,Hatfield,AL9 5HZ
Leeds User Group,Leeds, LS3 1EP
Manchester User Group, Manchester, M1 3HU
SQLSoton,Southampton,SO17 1XQ
Cardiff User Group,Cardiff,CF10 5BT
Bristol User Group, Bristol,BS1 4QD
Kent SQL Server and .NET User Group,Maidstone,ME14 1ED
Birmingham User Group,Birmingham,B2 5NY
Surrey User Group,Camberley,GU16 7ER
Bracknell User Group,Bracknell, RG12 1AQ
Dundee User Group,Dundee, DD1 4HN
Reading UG,Reading, RG6 1WG
London UG,London, EC4M 7AN
Cambridge UG,Cambridge,CB4 0WZ
BCS,Telford, TF2 9FT
SQL SouthWest,Exetor,EX1 1JJ
SQLBits 8,Brighton, BN1 2FW
SQLBits 7,York,YO10 5DD
SQLBits 6,London,SW1P 3NZ
SQLBits 5,Newport,NP18 1HQ
SQLBits 4,Manchester,M15 6BH
SQLBits 3,Hatfield,AL10 9AB
SQLBits 2,Birmingham,B4 7ET
SQLBits 1,Reading, RG6 1WG

Link to these points on a bing map

*SQL SouthWest postcode is a guess.

The SQLBits 8 Community Corner Map!

2011-04-14T21:03:00.000+01:00

After 2 days of pinning, this is the result....The SQLBits 8 Community Corner Map!

I would like to thank all the people that came to Community Corner and stuck pins in the map and all the very kind non-UK people that took the time to draw their own countries (including Italy, Germany, Holland, Sweden, Norway, Denmark, Austria and Australia ) and pin them on the map.

Here is all your work.......

I have to thank all the people who stayed in Community Corner and represented the SQL Communities in the UK! - You know who you are!!! Finally we have to thank the SQLBits committee who gave us the opportunity to have community corner.

See you at the next SQLBits!!

Red pins = User Groups , Blue Pins = People..

T-SQL Tidy SSMS 2008 R2 released...

2011-03-04T00:30:00.000Z

Tonight I've finally done it, posted up the R2 version of the SQL Server Management Studio Add-in.

You can download it from here; http://www.tsqltidy.com/downloads/addinR2.zip

It's got some new features!!!

Local formatting (no longer uses the web service - so no internet connection required)
Lots and lots of formatting options.
Format SQL to HTML!

So on give it a go, what have you got to lose?

Comments now supported on web site and in SSMS add-in!

2011-02-17T18:33:00.003Z

After a very very long long long long time, I've finally got support for single and multi-line comments.

Many many thanks to Matt (@atlantis_uk ) at http://www.atlantis-interactive.co.uk/ without who the site would be comment less.

So enjoy! Its not 100% perfect, there are some HTML formatting issues, but I felt I needed to get this release out there.

If you are using the SQL Server Management Studio Add-in, comments are now automatically supported.

So enjoy well formatted SQL and HTML!!

SWAG from Redgate arrived!

2011-02-04T10:11:00.004Z

The SWAG for the SQLSoton (SQL Southampton) User Group has arrived, Woo Hoo!

Red-Gate has sent;

USB Keys preloaded with some Ebooks.

Red-Gate stickers.

Pens

Lanyards

and what I think are luggage ID tags (made from real cow).

A huge thanks to the peeps at Red-Gate for sending over this SWAG - there will be many happy faces at the next UG.

So if you want your share, come to the User Group on the 9th Feb... details here.

SQLSoton (SQL Southampton) User Group dates..

2011-01-25T21:15:00.003Z

Here are the User Group dates for the next few months:

Feb 9th
March 9th
April 13th
May 11th
June 8th

Go to SQLServerFAQ.com to register.

See you there!!

Your User Group needs you.[Big pointy finger]

2011-01-24T23:06:00.005Z

The SQL Server User Group in Southampton (@SQLSoton ) needs you!!

If you are an experienced speaker or just want to try presenting for the first time then get in contact with us! We are looking for presentation between 15 and 60 minutes.

Dont just sit there and think about it, send us an email and you could be presenting next month!

Cultural Learnings of User Group (dis)Organiser for Make Benefit Glorious Nation of SQL Server

2011-01-20T23:45:00.001Z

WOW!!!

I think that about sums it up; WOW!!!

Before I get all gushy, I just want a quick Oscar moment - I would firstly like to thank every person that came, without your support there would be no User Group.

Secondly, I’d like to thank @AdaTheDev (Adrian) and @atlantis_uk (Matt) for organising and sorting out the Feng shui.

And of course @ChristianBolton, for being our crowd pulling speaker. I’m sure the turnout would not have been so high without him.

Come to User Group, It's Nice!

Be careful what you wish for as it just might happen. I was expecting a few more people to turn up this time, but based on previous experience I was expecting 9 or 10. I was not expecting to more than double the turnout; 15! Every chair in the room (which was bigger than last time) was taken…

So my learnings are – You have no idea how many people will turn up until 7.10pm.

I ordered the food based on the number of people I was expecting. Since all the food was gone, even the biscuits,… I don’t think there was enough! I’ve been tweeting @GavinPayneUK about the pizza to persons ratio.

So my learnings are – order more!

We were trying out a new room, so Matt, Adrian and I spent a while working out the best room configuration before the geeks the punters arrived, which worked well. Once Christian had finished his talk and I’d got back with my inadequate supply of pizza, people eat, chatted. But as Matt pointed out we should have put the chairs away to free up space in the room.

So my learnings are – work out the best use of space in advance.

For the first UG, I created some feedback forms which never got used as there were so few of us. Okay, I’ll put my hands up and say I didn’t create the feedback form; @ctesta_oneill did. I just changed the name of the UG at the top! But I did click print… (Don’t think I even convinced myself). We really could have done with them. Even if it was just a way of keeping track of who turned up.

So my learnings are – Don’t throw away feedback forms.

Now I’m eagerly checking twitterverse and blogosphere to see what people thought…. If you were there, you too can improve the cultural learnings of User Group (dis)organiser for make benefit glorious nation of SQL Server [Please tweet or blog your thoughts… I promise ~~not~~ to get offended!!]

See you all in Feb!!!

Confessions of the User Group (dis)Organiser

2010-12-09T18:28:00.004Z

Yesterday was the first Southampton SQL Server User Group and I just wanted to share some of my thoughts on how to arrange a successful one...

1) DON'T RUSH - Whatever you do, don't sit at work one day and think; I know, I'll start a UG, then pick the phone up and book a venue. Take your time, have a couple of months lead-up. This gives you time to organise and think of EVERYTHING.

2) DON'T LOOK AT A PICTURE OF THE VENUE AND THINK; THAT ROOM LOOKS OKAY - Visit the room. The size and shape of a small room can dictate the kind of UG that you can have. Is there going to be enough room to get a decent projection onto a wall or white-board? Can you move the chairs around and not affect the function of the room (blocking-off an area of the room)?

3) DON'T PUT DIRECTIONS TO VENUE UP THE DAY BEFORE THE EVENT - Put up directions or special instructions on the internet well in advance.

4) BRING A 4-WAY - Who knows where the power points are? There is a good chance there will not be power where you need power.

5) BELIEVE IN MURPHYS' LAW - If you can bring spare kit, just in case something goes wrong, then do it. What's the worst that can happen? You don't use it.

6) ORGANISE THE FOOD IN ADVANCE - As Murphy's Law states; what can go wrong will go wrong... Including my phone stopping working and managing to block my debit card when ordering food in a rush.

7) DON'T EXPECT PEOPLE THAT SAID THEY WOULD DEFINITELY TURN UP TO TURN UP - Random stuff happens; people say they can come, but life gets in the way. Always expect less people to turn up. Perhaps even 1/3 to 1/2 less.

And even if you don't manage to follow all these steps, it is still possible to have a really successful UG!

For the next UG, I have promised myself that I won't make the same mistakes again. I'll make a whole load of new ones!

Parking to Southampton SQL Server User Group [Updated]

2010-12-07T19:11:00.004Z

For those coming tomorrow, I'd thought I would just post up some details on where to park.

If you are driving towards Southampton down the Avenue, turn left onto Alma Road, then the next left. It should take you directly under my arrow.

SQL Southampton - SQL Server User Group in Southampton 8th December [Updated 24/11/2010]

2010-11-19T22:28:00.004Z

This is the second draft of the agenda - Keep your eyes peeled on http://www.sqlserverfaq.com and http://www.facebook.com/event.php?eid=108621982541648 for updates.

This is the first SQL Southampton User Group (Hoping it’s not the last) – A great opportunity to get together with your peers and discuss all things SQL.

I’ve stolen the nugget idea from another user group!

8th December at Avenue St Andrew's Church Southampton (In the Lounge)

6.30pm – 7:00pm Registration and Networking.

Meet and greet.

7:00pm – 7:15pm Welcome and Introduction - Mark Pryce-Maher Blog: http://tsqltidy.blogspot.com/ Twit: http://twitter.com/tsqltidy/

Brief introduction to the evening.

7.15pm – 8:00pm SQL CLR Demystified – Matt Whitfield Blog: http://www.atlantis-interactive.co.uk/blog/ Twit: http://twitter.com/atlantis_uk

An introductory session on CLR integration within SQL Server, looking at what is required for implementation of CLR types, aggregates, triggers, stored procedures and functions.

A few practical examples will also be covered, including some performance-based CLR implementations, as well as maintenance based ones.

8:00pm – 8:15pm BREAK: Light refreshments.

More time to network and ask questions...

8:15pm – 8:30pm Quick Look at Denali - Mark Pryce-Maher

As many demos as I can manage to do in 15 minutes...

8:30pm – 9.00pm - Round Table discussion and Nuggets (Self-help) - ALL

Take stock and get the latest news in the SQL Server field. This is also a great opportunity to ask any burning questions you have.

9.00pm... The End - Network or just slope off home.

There may also be prizes, courtesy of Atlantis Interactive... and a chance to play with Denali!!

How to start a regional group and be part of the UK SQL Server User Community

2010-10-24T22:01:00.002+01:00

Tony Rogerson posted this really guide on 'How to start a regional group and be part of the UK SQL Server User Community'

It's a "must read" for any one wanting to start a regional group.

SQLBits 8 Venues...

2010-10-08T20:58:00.002+01:00

Zara has been busy today....

Some options of where to host SQLBITS.

Cambridge...

http://www.chilfordhall.co.uk/cgi-bin/ch/info.html?domain=info&name=Facilities&section=Conference%20and%20Events

Durham...

http://www.ramsidehallhotel.co.uk/location/default.aspx

Edinburgh...
http://www.ece.uk.com/

http://www.assemblyroomsedinburgh.co.uk/index.phtml

Southampton

http://www.devere.co.uk/our-locations/grand-harbour/business/layouts-and-dimensions.html

Are you trying to tell me something...

2010-10-07T19:54:00.002+01:00

This was on the fridge when I got home...

I think my wife was trying to tell me something, but I've not worked it out yet.

Whoop!! Whoop!! Site Down!!!

2010-10-07T11:21:00.004+01:00

Just checked tsqltidy.com and it's down!
Grrrrr....

*Amendment:

DOH! It was my fault, some dodgy coding around a twitter feed.

Oh well. Wont do that again (for a few months!).

Finding Non-SARGable queries

2010-10-06T16:28:00.003+01:00

I was in Rob Farley's sessions at SQLBits when he was talking about: Understanding SARGability (to make your queries run faster)

It got me thinking....

Is there a way I can find out which SQL queries are doing table or index scans?

So I put some proof of concept code (POC) together.

SELECT top 10 text ,query_plan ,*

FROM sys.dm_exec_query_stats

CROSS APPLY sys.dm_exec_query_plan(sys.dm_exec_query_stats.plan_handle)

CROSS APPLY sys.dm_exec_sql_text (sql_handle)

WHERE

CONVERT(varchar(max), query_plan) like '<tablescan%'

CONVERT(varchar(max), query_plan) like '<IndexScan%'

ORDER BY total_elapsed_time desc;

Its not a very sophisticated query (it only took a few minutes to write and yes, I agree, I should shred the xml instead of using a convert and like) but it shows the concept of querying the execution plans to get details of queries that has run (rather than the best way of getting the information).

It takes a while to run so don't go running it in LIVE, and might bring back some false positives - so please be careful with the results.

References:

http://msmvps.com/blogs/robfarley/archive/2010/01/22/sargable-functions-in-sql-server.aspx

http://sqlbits.com/Agenda/event7/Understanding_SARGability__to_make_your_queries_run_faster_/default.aspx

Gadget Show Top 5 Budget Mobile Phones

2010-10-05T19:41:00.001+01:00

I've been trying to find this list all day and its nowhere on the Gadget Show website.

It's...

1) Samsung Genio Qwerty

2) LG KS365

3) Huawei U7510

4) INQ Mini 3g

5) Alcatel OT808

How to setup a MSSQL Server Management Studio (SSMS) 2008 New Query Template

2010-10-05T09:41:00.005+01:00

It took me a small amount of Googling with Bing to find this post on Stack Overflow.

But it works for SQL 2008 r2 - apart from changing the paths slightly.

%Install Dir%:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\SQLFile.sql

I cant work out why is there nothing on MSDN about this cool feature?
Or why is not part of the SQL templates.

References:
http://sqlsoundings.blogspot.com/2010/03/using-ssms-templates-for-fun-and-profit.html
http://msdn.microsoft.com/en-us/library/ms174169.aspx
http://stackoverflow.com/questions/593047/mssql-server-management-studio-ssms-2005-new-query-template

Microsoft Certified IT Professional: Database Administrator 2008

2010-08-15T21:03:00.001+01:00

I’ve now passed both Microsoft certification exams for the MCITP: Database Administrator 2008 :

Exam 70-451: PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008
Exam 70-432: TS: Microsoft SQL Server 2008, Installation and Maintenance

As preparation for the 70-432 exam I got the following books:

I read all the MCTS Self-Paced Training Kit and actually enjoyed it (as much as you can enjoy reading a technical book – but it wasn’t as painful as Gallbladder surgery!) – I have to agree with some of the comments on Amazon that there are a few mistakes, but who hasn’t released code/documentation that didn’t need a few tweaks after it was released?

I have to make a special mention of SQL Server 2008 Administration: Real-World Skills for MCITP Certification and Beyond (Exams 70-432 and 70-450)- it’s the only book I found that covers the 70-450 exam!!! In the appendix, there is a mapping of chapters to exam objectives so you only need to read the appropriate chapters.

After leaving the test centre having passing 70-432 with 960, I thought this exam felt much easier than the 70-433 exam.

I was (quickly) convinced (by my wife – Hello!) that I should just try and take the next exam straight away. The odd thing about the Microsoft exams is you only need to get 700 – it doesn’t matter how well you do, you still get the qualification in this case ‘just enough is good enough’ .

So, just a few days after passing 70-432, I took 70-451 and passed with 929.

I was surprised by how easy the MCITP: Database Administrator 2008 was compared with the MCITP: Database Developer 2008. There were (in total) 84 questions for the Database Administrator and 128 for the Database Developer – a big difference.

It’s not the number of questions, although the more questions, the more sweating you do. It’s the breadth of the content. There was so much to learn and remember. I think Brent Ozar conveyed it really well in his blog post: MCM Prep Week: Microsoft Exams 70-433 and 70-451

There is only the MCITP: Business Intelligence Developer 2008 to go, then I’ll have the whole set! :-)

MCITP: Database Developer 2008

2010-07-09T18:23:00.003+01:00

In May I passed 70-433 and in June I passed 70-451 . Which means I can call myself a Microsoft Certified IT Professional (MCITP)!!!

I started in Feb/March working through two books;

MCTS Self-Paced Training Kit (Exam 70-433): Microsoft SQL Server 2008 - Database Development Book/DVD Package and The Real MCTS SQL Server 2008 Exam 70-433 Prep Kit: Database Design

The books cover a huge range of subjects; the MS press book covered more areas and was in some ways better but was also tedious at times - often sending me off for a good nights' kip! The Prep Kit was written in (my opinion) a nicer style for the user, but didn't cover nearly enough of the exam.

I read the Prep-Kit book first, took a practice exam and scored very low! I bought the MS Press book and found this filled-in the gaps and reinforced what I learnt from the first book. I found the different writing styles very useful as it didnt feel like I was reading the same book twice.

I took 70-433 and scored 1000.

There is no official exam prep for the 70-451 exam, so I took it 2 weeks after passing the first exam. The exam was tough, very tough! I thought I'd failed. When I clicked 'finish' I closed my eyes as I really thought I would have to tell everyone at work I'd failed.

But to my amazement, I passed with 865! I spent the next 4 hours in shock. I had a hospital appointment later that day and my resting heart rate was over 100 bpm!

Now for a little rest before I start on the MCITP: Database Administrator 2008!

Website updates...

2010-07-08T13:01:00.007+01:00

I'm now working on a few updates for the T-SQL Tidy website while I take a break between Microsoft Certification exams.

There aren't any (huge) increases in functionality. I'll just fixed problems that annoy me. :-)

Fixes;
New menu - I did the old one in a rush so it didn't really work very well.
Updated blog feed - I sorted out the problems with the formatting.
Twitter account - I created a twitter account (Apparently social networking is the next big thing!)

SQLBits (episode) VI - Westminster Strikes Back...

2010-02-12T17:13:00.004Z

After the hugely successful SQLBits V (in Newport), SQLBits VI has been announced today. It's going to be held at the Church House Conference Centre, London on the 16th April 2010.

Check out the SQLBits.com website for more details.

Format T-SQL as HTML and more...

2010-01-13T18:55:00.004Z

I've just added some new features to the T-SQL Tidy website.

Format the T-SQL into HTML (for blogs and web sites).

More formatting options than you can shake a stick at.

New Web Service call "ParseSQLwithOptions". http://www.tsqltidy.com/SQLTidy.asmx?op=ParseSQLwithOptions

Special thanks to Jean-Claude Manoli for the HTML formatting code website:http://www.manoli.net/csharpformat/

SQL Source Control

2009-11-30T19:50:00.004Z

Just released a beta of SQL Version Tracker.

It's a version control utility that is integrated into SQL Server Management Studio 2008 and SSMS 2008 Express.

Simply install the Add-in to Management Studio and enable it on your database.

It will track all the changes on the schema of the SQL Server databases.

Feel free to download and try it!! But most importantly give me feedback!!!

SQL Server Version List update (part 2)

2009-11-30T18:26:00.003Z

I've now added the option to download the SQL Server Version List.

You can download it in XML or CSV formats.

SQL Server Version List update.

2009-11-05T18:14:00.002Z

As well as adding a couple of new versions to SQL Server version list to the list, I've added the the ability for people to added any missing versions.

Simply go to the SQL Server version list, scroll to the bottom of the list and enter the missing version information

New Release of T-SQL Tidy Add-in for SQL Server Management Studio 2008

2009-10-21T20:04:00.004+01:00

Beta 2 of the T-SQL Tidy Add-in for SQL Server Management Studio 2008 has just been released.

New features include;

Support for SQL Server Management Studio 2008 Express Edition
Feedback when T-SQL does not format correctly.
Script data to: Update and Upsert now supported.
Version checker now added - so you will be alerted when a new version is available.
Can now script tables that are called database keywords.

Still on the to do list;

Add support for SQL Server 2005 Management Studio.

T-SQL Tidy Add-in for SQL Server Management Studio 2008

2009-09-14T18:58:00.002+01:00

I have just released the first (early) ‘beta’ of the T-SQL Tidy Add-in for SQL Server.

The add-in is a collection of 3 free tools;

T-SQL Tidy – Formatter of SQL statements.

SQL Version Checker – Reports the service packs and patches of the SQL Server.

Script Data to.. – Scripts the insert statements for a table to a file or new query window.

Over time I hope to add more tools to the collection.

Feel free to leave any feedback or suggestions for new features.

P.S. Did I mention this is free? Download it now!!

What version of SQL Server are you running?

2009-08-20T08:17:00.002+01:00

I've added a new facility to identify how patched your instance of SQL Server is;

1) Find out your SQL Server version - if you don't know, here is a link to the Microsoft site that will tell you how to.

2) Go to the following "SQL Server Version List" web page.

3) Enter your version number in to the box.

Easy as that!!

Useful SQL:

SELECT @@Version

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

New Features

2009-07-26T15:42:00.001+01:00

I’ve added a new page to the web site called ‘Experiments’. This is were I will put new things before they live.

The first Experiment is a ‘T-SQL formatting as you type’ applet.

Just start typing in the text area and it will create a formatted SQL statement in the area below. I’m surprised no one has done this before!!

Enjoy!

Moved hosting (at short notice)….

2009-07-26T15:35:00.001+01:00

The T-SQL Tidy website has disappeared from its original web host. I was in the process of updating the site and putting it onto its new home….but it looks like I’ve had to accelerate my plans somewhat.

I’ve decided to go with the new design, although its still ‘fluid’.

Which is were I ask for feedback!

I’m going to start work on the next feature-set for T-SQL Tidy, so I would like a bit of feedback about the site. What features would you like on it? What does work (very well) and what doesn’t work?

All feedback will be award a prize of my respect and adoration for a minimum of 30 working days. No refunds will be given!

Visits from Redmond…

2009-07-08T21:44:00.001+01:00

I was obsessively checking my Google Analytics over the weekend when I noticed a hit from Redmond.

I did some digging around and found out it was from Microsoft Corp – In fact it was 4 visits over a couple of months (see pic). It made me feel smug…. for at least 15 minutes.

Of course, it’s probably some automated bot. But for a couple of moments I thought it could actually be a human, beavering away in a cubicle in Redmond – what a dreamer I am.

New Web Service, Stats and tracking

2009-06-04T09:03:00.007+01:00

Been meaning to do an update for a while. Finally got around to it this morning.

I've created a new Web Service for non-Microsoft (all) clients. It's a simplified version of the existing web service. The test page contains all the source code (Javascript) required for using the Web Service.

Although this site is new (just a few months old), I thought it would be interesting to display the usage statitics. The stats show how many Tidies have been done (for Today, Last 7 days, Last Month and Year).

I've also added a 'Share' button, so people can now add TSqlTidy.com to their favourite social networking site. Please, please, please use it! :-)

Free hosting coming to an end...

2009-06-03T07:52:00.002+01:00

I was just looking at my hosting provider (http://aspspider.com) and there was an odd message, "Your website is already expired and all files will be deleted soon. Please back up all your files." (in red!).

When I checked the message boards, I found they have changed their free hosting from only having a limit on disk space to now being time limited; 90 days.

Of course the gravy train has to stop somewhere and I can see why being the only company that offer free asp.net hosting and be a bit of an overhead.

The long and short of it means that http://www.tsqltidy.com has to move - and I'm going to have to pay!

Hopefully I will have sorted a new home out before they remove the site completely.

Format your SQL with T-SQL Tidy

2009-05-08T13:42:00.004+01:00

Sorry for this shameless self-promotion, but in a vain attempt to attract more visitors to the T-SQL Tidy web site - I'm writting a small piece to advertise my site.

If you want to format / parse or tidy your T-SQL then visiting TSQL Tidy.com. Enter your y unformatted SQL into the box on screen then click 'Tidy', your SQL will then be transformed and beautified into well formatted SQL.

Update to SQL Formatting engine..

2009-05-07T13:13:00.003+01:00

I think I finally got the sql formatting and parsing engine complete (or as good as its going to get). I've rewritten the way INSERTs are formatted, I was never happy with the way it was done orginally, so now I'm happy with it.

I've also released a tweek for the T-SQL Tidy Utility ( the quick tidy button was not working correctly).

I've done a couple of minor tweeks to the web-site, so hopefully they will get indexed better by search engines. A couple of days ago I noticed I was no longer in the first 10 search results from google. Which is really bizar - one day in the top 10 the next somewhere in the 50's. Not sure what is going on with google at the moment.

Minor Update and Feedback

2009-04-24T19:16:00.004+01:00

New formatting option added and updates to the Web Services. I'm also opening up the flood gates to getting feedback. Email or post up options or functionality you would like added. I will publish a wish list, then work on adding all the easy bits of functionality. :-)

Formatting SQL is harder than you think..

2009-04-24T18:12:00.003+01:00

I've had a couple of pieces of feedback about the T-SQL Tidy site. One is that the hosting is a bit slow and that the format of the SQL produced is not as good as the SQL generated from SQL Server.

The T-SQL generated by SQL Server when you right click on a table and choose 'Script table as' is much better than the format of the T-SQL after it has been parsed by the formatting engine.

This is true, I don't think it would be possible to format the T-SQL any better than the way SQL Server is doing it. This is because SQL Server understands what it is scripting, the definition of each object (table's and column's) are stored inside SQL Server. T-SQL Tidy has no knowledge of this, it reformats the SQL outside the context of the SQL Server.

I was trying to explain why the formatting engine behind this site is not as good as formatting by hand to a friend of mine. He asked why don't you just insert line breaks after commas? I explained that although an early version of the engine did just that, but its not that simple. Commas are used for separating parameters in internal functions and stored procedures, how do you know when a comma is separating fields or separating parameters in a function?

The answer is you cant. (Okay – you could, but not practical)

So I have added a new option to the site, “Insert New Lines after Commas” - so if you are formatting very simple T-SQL and you want new lines after each field, just enable this tick box.

The hosting is provided by ASPSpider.com, which is a free hosting site, as it is free I cant complain. It the speed becomes a big issue or it the site becomes too popular, it may need a new home but until then I am happy with ASPSpider.com.

Format SQL on your desktop.

2009-04-18T10:34:00.006+01:00

Inbetween all the normal household chores I've writen the first of (hopefully) many utilites using the T-SQL Tidy web service. The binary and source code are aviable from here. I'm currently working on another utility, which will tidy all the stored procesdures in a database. It should be released in the next few weeks.

I am also working on producing an offline version, but there is no planned release date yet.

I thought I would just note that the domain name http://www.tsqltidy.info/ also links to http://aspspider.info/tsqlparser - It was only 99p!! I just cant resist a bargin. :-)

Minor Updates.

2009-04-07T11:24:00.004+01:00

I've added cross browser support for cutting and pasting - but there are still some issues with Chrome (details here).

I've updated the feed from blogger to show the first 200 characters of each post. (Source code coming soon)

Updated descriptions on web service methods.

Version 0.1 Released

2009-04-04T17:31:00.007+01:00

I've finally put the T-SQL Tidy site live today. It feels like a very long time, but its only been a few weeks from conception to implementation.

As it's only on version 0.1, please feel free to leave feedback or suggestions for improvements.