October 2008 - Posts

I wanted to compare my SQL Server 2008 development database with another on a third party server to make sure they are in sync, unfortunetly even on Visual Studio 2008 SP1 the schema compare does not work - "Schema Compare does not support SQL Server 2008".

Today a RC1 of the Visual Studio Team System 2008 Database Edition GDR was released; this fixes the schema compare amongst other things.

Link is here: http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en




Ok, I wrote a big stored procedure last time my entry on "send table or view as embedded html"; I've found a much simpler method in pure SQL....


declare @body varchar(max)


--    Create the body

set @body = cast( (

select td = dbtable + '</td><td>' + cast( entities as varchar(30) ) + '</td><td>' + cast( rows as varchar(30) )

from (

      select dbtable  = object_name( object_id ),

               entities = count( distinct name ),

               rows           = count( * )

      from sys.columns

      group by object_name( object_id )

      ) as d

for xml path( 'tr' ), type ) as varchar(max) )


set @body = '<table cellpadding="2" cellspacing="2" border="1">'

              + '<tr><th>Database Table</th><th>Entity Count</th><th>Total Rows</th></tr>'

              + replace( replace( @body, '&lt;', '<' ), '&gt;', '>' )

              + '<table>'



print @body


Then just use whatever email mechanism you want, for instance my CLR proc: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/12/31/clr-stored-procedure-to-utilise-more-of-the-mailmessage-net-class-rather-than-db-mail.aspx


The UK SQL Server User Group is proud to present Kalen Delaney this evening 7.30pm - 8pm on Live Meeting for a session on Query Tuning. 

More information including link is here: http://sqlserverfaq.com/events/143/Kalen-Delaney-Query-Tuning-Live-Meeting.aspx.

We welcome Kalen Delaney to kick off the Autumn season of Live Meetings, Kalen is well known for her Microsoft Press Inside SQL Server books for SQL Server 2000 and SQL Server 2005 as well as being a long standing author in SQL Server Magazine, a veteran SQL Server expert of over 20 years.

In this 30 minute Live Meeting Kalen will talk about Query Tuning and give us some tips and techniques as well as introduce some of the new Engine features of SQL Server 2008.

This is a live meeting presentation (online), we suggest you visit the meeting URL 10 minutes before the event starts (the event starts at 7.30pm) in order to make sure you can download and install the Live Meeting applet in time.


Come and join us for this great evening of developer focused content. These meetings are great opportunities to meet other SQL Server professionals, get your burning issues solved.

This event brings a new slot to our agenda - the open mike slot where you can bring your 5 minute canned demo or hints and tips and present it to the group with support from the other presenters for the evening.

6:00 - 6:30 Meet & Greet
6:30 – 6:45 Intro, whats in the news, Q&A – James Rowland-Jones, Christian Bolton & Tony Rogerson.
6:45 – 6:50 – Dev Nugget – James Rowland-Jones
6:50 – 6:55 – Admin Nugget – Christian Bolton
6:55 – 7:00 – Open Mike Slot – Tony Rogerson
7:00 - 7:45 Using Table valued parameters - James Boother.
7:45 - 8:10 Networking with Beer and Pizza
8:10 - 9:00 Brokering Innovation in SQL Server – Simon Munro


About Simon Munro Simon is a solution architect working at Conchango, focusing on architecture within the Microsoft Technology stack. He has been working with SQL databases for nearly fifteen years on Oracle, Interbase and SQL Server, which he first used as version 4.21 on Windows NT. He has been architecting solutions mainly in financial services and retail – based on large databases with complex user interfaces and rigorous transactional requirements. While working on the development and .net side of applications his strong background in formal data modelling methodologies and great successes with well built SQL databases means that he sees SQL Server as more than just a persistence store for objects. Simon champions the use of database capabilities as a core part of a well implemented solution architecture and will speak his mind at every opportunity on the perceived irreconcilable differences and impedence mismatches between application developers and database professionals.

Brokering Innovation in SQL Server

It is not often that we see advances in SQL databases that push the technology into areas that they are well suited and when we do, they are often passed over by developers and DBAs in favour of sticking to the database status quo. SQL Service Broker is one of those technologies that is misunderstood and is, contrary to what we have come to expect from Microsoft, under hyped. It is a technology that helps solve real problems that database professionals are dealing with – including the ability to scale out databases and accompanying applications to cope with the business need of having reliable transactioning across a distributed platform. So, years after its introduction in SQL 2005, SQL Service Broker is coming of age has proven itself as a stable, useful and important part of an organizations’ database platform - the database professionals out there just need to understand what that part is. This session discusses the fit of SQL Service Broker within an overall solution architecture and contains enough practical code and demonstrations to allow attendees to leave with enough to start using it themselves. It also demonstrates the use of SQL Service Broker and the (new to SQL 2008) Filestream datatype to provide a performant, secure and reliable platform for distributing large binary objects amongst occasionally connected databases. If you have heard about SQL Service Broker but haven’t really had a close look at it, you need to attend this session. If you have been wondering about what SQL Service Broker can be used for, you need to attend this session. And if you looking for solutions to keeping different, distributed SQL Server instances in sync in the enterprise, then you need to attend this session.



I'm forever forgetting how to do this and am currently on a project where I get a ton of CSV files daily and need to load them. The trick with getting rid of the " is to know how to escape the " in a format file.

The data looks like this...

"M1","Title for M1"
"M2","Title for M2"
"M3","Title for M3"
"M4","Title for M4"

How can we use BULK INSERT to load this data without the quotes? There probably is a better way to do this without a) using SSIS and b) relying on openrowset to mangle what the type of data is.

create table csv_matrix (
    f1 char(1) not null,
    menu_item_code varchar(5) not null primary key clustered,
    menu_description varchar(250) not null unique

And the format file is:

1       SQLCHAR             0       1       ""         1     f1                                       SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       5       "\",\""    2     menu_item_code                           SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       250     "\"\r\n"   3     menu_item_description                    SQL_Latin1_General_CP1_CI_AS

And the BULK INSERT statement is:

bulk insert csv_matrix from 'e:\dataimport\matrix.csv'
              with ( formatfile = 'e:\dataimport\matrix.fmt'

The key here is the use of the back slash (\) which escapes the character. I cannot rid myself of the first quote though without a filler column to hold and discard it.

Just received my email...

Dear Tony Rogerson,

Congratulations! We are pleased to present you with the 2009 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others.


That's 10 years as a SQL Server MVP, I first received the award in 1998 when there where only about 2 dozen SQL Server MVP's in the world with most of them in the picture below; has it really been that long? Check out Gianluca's site -> http://www.ghotz.com/mvppht-it.shtml, I had more hair then :)