Tony Rogerson's ramblings on SQL Server

Common Table Expressions (CTE's) - How it works; How Recursion Works; Using with Adjacency List

2008-05-11T18:47:00Z

Recursion in Common Table Expressions (CTE’s), how does it work? How can I use it with the adjacency list model (parent / child columns)? In this blog entry I show how recursion works, how to use it with the adjacency list model and talk about other aspects and uses for CTE’s; I also demonstrate some of the areas where CTE’s aren’t that good and how to help remedy that performance penalty suffered. This weekend I’ve been at the Scottish Developers Conference in Glasgow – a great day, I gave my making...(read more)

Using the OUTPUT clause on INSERT when loading data using OPENROWSET and BULK operator

2008-05-05T08:52:00Z

Say you have an external file you want to load into your SQL Server database; you need to load – verify – cleanse and insert and at the same time keep a cross reference between the original source file records and the inserted database rows. The OUTPUT clause is an ideal candidate to help you with this cause; most people use the IDENTITY property for surrogate keys; the OUTPUT clause allows you to get at the value returned by the IDENTITY for each row you’ve just inserted; unlike SCOPE_IDENTITY(...(read more)

UK MS Premier Customer? - Premier Field Engineering are hosting an Operations Day for SQL 2008

2008-04-27T19:06:00Z

5th June, Thames Valley Park, Reading sees an Operations Day for SQL Server 2008 for Premier customers only. More information: http://sqlblogcasts.com/blogs/thepremiers/archive/2008/04/27/operations-day-for-sql-server-2008-open-for-premier-customers-only.aspx Don't worry - we've plenty of stuff in the pipe line if you aren't lucky enough (or are for that matter). SQLBits is scheduled for the 12th and 13th Sept in Hatfield but more information on that in about a months time....(read more)

MVP Summit 2008 - Seattle; Euan Garden BBQ - 5 SQL Server MVP's as "the band"

2008-04-15T00:33:00Z

This week I'm over in Seattle for the MVP Summit, a number of us UK SQL Server MVP's are out here - myself, Simon Sabin, David Portas, Jamie Thomson, Rob Farley and Satya Jantry.

We popped over to see Euan yesterday for a BBQ - I knew things started to go down hill when we started to play on his xbox and the Rock Band game.

Want to see? http://sqlblogcasts.com/r.ashx?F

Shaping up for a good week - met up with some folks I've not seen in a long while - Russell Fields, Roy Harvey - great to see you guys back in the program.

MSTSC - No more /Console

2008-04-09T12:30:00Z

If like me you spend the majority of your life remote desktoping to various servers you should know that as of Vista SP1 the /console switch no longer gets you to the Console session on the server - you need to use the /admin switch instead; apparently the behaviour is the same on Windows 2008. It's the client side that's the problem. Anyway- this blog entry explains it all -> http://blog.shijaz.com/2008/01/no-more-mstscexe-console.html Hope that saves you a few minutes headache. Tony...(read more)

London Event 13-16 May: Insight from Kimball University: Microsoft Data Warehouse in Depth

2008-04-07T11:01:00Z

Microsoft Data Warehouse in Depth Joy Mundy & Warren Thornthwaite London, 13 – 16 May 2008 Attend Microsoft Data Warehouse in Depth at Crown Plaza London, May 13-16 to discover how to build a successful business intelligence system using Microsoft SQL Server 2005. Covering the complete suite of data warehousing tools that accompanies SQL Server 2005, Warren Thornthwaite and Joy Mundy , authors with Ralph Kimball of the Microsoft Data Warehouse Toolkit, focus on the full project lifecycle, including...(read more)

Rude easter egg in SQL 2005 - Uniqueidentifier gives the F0FF

2008-04-01T04:41:00Z

I found this while researching a blog article; very interesting - I always thought code was put in place to prevent this sort of thing from happening. Only seems to work on SQL Server 2005 SP1. set language british set ansi_defaults off set ansi_nulls on declare @uid uniqueidentifier set @uid = newid () declare @tb table ( blah char ( 36 ) not null ) insert @tb values ( cast ( @uid as char ( 36 ) ) ) select cast ( blah as uniqueidentifier ), * from @tb Gives the result set -> F0FFB2EE - F0FFEEEEE...(read more)

Send Table or View as embedded HTML in an email – stored procedure
2008-03-28T20:19:00Z
Ever wanted to take a table, permanent or temporary or a view and just email it straight from SQL where the query result sits in the email as a nicely formatted HTML table rather than that ugly text file attachment you get when using sp_send_dbmail? Ever hit that infinite deadlock I blogged about the other day if you use the sp_send_dbmail attachment feature? Well, this stored procedure is for you! This is an expansion of an example in the sp_send_dbmail section in books online, all I’ve done is...(read more)

Taking stock on the success of SQLBlogCasts.com

2008-03-20T15:03:00Z
Spent a while today upgrading SQLBlogCasts.com from CS2.0 to CS2007.1 - very simple migration, a few things I'd forgotten to do but up and running now. It's resident on one of my own servers down in Docklands running on SQL 2005 Express. While tidying up the database bits I thought to myself, I wonder how popular the site is because I've been noticing more and more posts on the RSS feed from the site. Total blog posts: 1,692 from 72 blogs since 5th February 2006 which is when SQLBlogCasts...(read more)

Database taste challenge - SQL Server 2005 v Oracle 11g.

2008-03-18T05:32:00Z
"What tastes better, Microsoft® SQL Server® 2005 or Oracle 11g? What do people think? We're taking the taste challenge to the street. Find out what people are saying about their favorite database. Tast-ay!" Very good, very funny. http://www.databasetastechallenge.com/ When you are waiting for that 50GB database to restore on your slow server instead of reading FHM you can play a game :) Enjoy. Becareful though - some subliminal marketing going on there, think it's about with Oracle you have to buy...(read more)

Rampant Frogs Run Riot in Tony's pond Froggy Style

2008-03-14T11:08:00Z
Spring is upon us here in Harpenden, UK. Just took a couple of shots from my pond whilst having a break. Enjoy.... (I'll give a book to the best captions (postage to UK Mainland only)) If you want a bigger side image then -> http://sqlblogcasts.com/files/folders/9368/download.aspx Tony....(read more)

sp_send_dbmail in a transaction with @query causes unresolvable deadlock

2008-03-11T13:01:00Z
So you are using the @query parameter of sp_send_dbmail and all of a sudden you start getting blocks and your connection running sp_send_mail is the cause, your connection running sp_send_mail just sits there in a runnable state with the lastwaittype of MSQL_XP. What is happening? create table ##ed_volval ( acol int not null ) begin tran insert ##ed_volval values ( 1 ) EXEC msdb . dbo . sp_send_dbmail @recipients = 'tonyrogerson@torver.net' , @subject = N 'Bad Idea' , @body = N 'Bad idea' , @query...(read more)

SQLBits - My Session - Making the Leap into Advanced T-SQL

2008-03-07T22:13:00Z
Got round to sorting the bits out, the session I did at SQLBits Birmingham on 1st March I looked at some of the table expressions available in SQL Server 2005 - Derived Tables, Temporary Tables and Common Table Expressions. I look at the basic framework and then go on into more advanced uses like running totals and cumulative totals. Presentation: http://sqlserverfaq.com/controls/kbase/store/downloads/SQLBits20080301MakingtheLeapintoAdvancedTSQL.ppt Recording: http://sqlserverfaq.com/controls/kbase...(read more)

SQLBits II - Some thoughts from last Sat

2008-03-07T19:06:00Z
As I draw breath from another busy week I take stock on what a great event last Saturday was - 350 people on the day, tons of positive feedback, throughout the day the whole atmosphere was great - can't wait until SQLBits III. The day did start off in a panic, mine and Charles laptops only have DVI output and the projectors only took VGA so I had to run into town and pick up a convertor - thank god for Maplins! Not sure I could squeeze 120 people around my laptop screen. The Saturday night after...(read more)

Rafal Lukawiecki - Data Mining and Business Intelligence - London, 3rd April - all day event

2008-02-28T11:05:00Z
I was talking with David Hobbs-Mallyon (SQL Server Product Manager here in the UK - a good guy to know for swag and believe me Simon Sabin and I whipped a load away from yesterday's launch event for give aways at SQLBits (thanks Ellie too!), anyway I digress), I was chatting with David about how BI and Data Mining is a growing area of the product, seems apt and goes with one of the messages from the launch presentation I'd heard before talking to David; anyway - this event is nearly full so I though...(read more)