OT: Airlines and Podcasts

Those that know me know that I spend an inordinate amount of time on airlines. I also love podcasts, as you can tell from my www.sqldownunder.com site and show. So anything that combines the two is just awesome.

Fly With Joe fits that perfectly. Joe D'Eon provides great insights in his show. I was sad last year that he hadn't posted many shows. I've also been quiet for a couple of months (but that's about to change with a bunch of SQL Server 2008 R2 shows). But I've been so pleased that Joe's got back into the cockpit on his show lately. And also providing some live streaming shows. Recommended!

On a similar vein, flight attendant Betty has been a long time favourite of mine (and Mai's) with her Betty in the Sky with a Suitcase show. Great to see her posting new shows lately. Recommended also!

Betty also has an upcoming book: http://www.amazon.com/Betty-Sky-Suitcase-Hilarious-Attendant/dp/1606390112 Ordered!

SQL Server 2008 R2 – Application and Multiserver Management Learning Materials

My colleagues and I have been working with Microsoft to produce the Metro training materials for SQL Server 2008 R2. We've using those materials to train other trainers around the world. (If anyone will be in Reading in the UK next week, ping me and say "hi". Same for London the following week).

Roger Doherty's group have been hard at work turning these materials into consumable bite-sized pieces of training. This involves videos, demos and hands-on-labs.

The Application and Multiserver Management learning materials I worked on (often called the DACPAC materials and originally codename Synthesis) are now released (for free) as part of the update to the SQL Server 2008 R2 Training Kit. You'll find details here: http://blogs.msdn.com/rdoherty/archive/2010/03/02/sql-server-2008-r2-update-for-developers-training-kit-march-2010-update.aspx

Enjoy!

SQL Server Reporting Services: Should support include files

It's common to want to embed custom code within reports in Reporting Services. One thing I don't like is the inclusion of anything that looks like business logic directly in the reports. However, formatting functions, etc. seem totally appropriate.

 

If I want to embed custom code within Reporting Services though, I currently have two options. One is to embed the code in the report, the other is to reference an assembly. Each of these has drawbacks.

 

If I embed the code in a report (say just some formatting functions), I have to edit every report if I ever change that code.

 

 The answer has been to use an assembly instead. The downside of placing code in an assembly is that I now have a deployment issue. It's very easy to deploy an RDL report file but quite another thing to deploy a .NET assembly that is referenced by it.

 

What I’d really like to see is an in-between option. I think a good solution would be to allow me to have another node in Solution Explorer, called something like “Shared Code” or some similar name. I could then have sets of code that I might want embedded in various reports. Then in the properties of a report, I could just specify that which of these pieces of code I'd like to have included (and embedded) in the report.

 

This would give me the benefit of only a single place to need to write/update the code but no downside on deployment as the code would simply be embedded in the RDL files.

 

I'd love to hear your thoughts on this. If you like it (or even if you don't), the Connect item is here: https://connect.microsoft.com/SQLServer/feedback/details/534679/reporting-services-should-support-include-files-via-code-inclusions

 

 

DevWeek in London – coming up in March – early bird ends soon

DevWeek is on again this year http://www.devweek.com

Should be good to catch up with many of my European colleagues again. DevWeek is on March 15 – 19 at the Barbican Centre in London. The early bird pricing runs till 19th February.

A number of my colleagues will be speaking as well: Itzik Ben-Gan, Javier Loria and Davide Mauro.

I'm looking forward to seeing them and all the SQL crowd that will make it to London for the event.

On the Monday, I'm presenting a precon entitled "A Day on SQL Server 2008 R2". Should be fun. Then a number of breakout sessions during the week: "Understanding SQL Server Indexing", "SQL Server Management Studio Tips and Tricks", "Working with addresses and locations in SQL Server". I'm hoping to also get to see some of Itzik's, Javier's and Davide's sessions.

If you're heading to DevWeek, please stop by and say hello.

New entry in the unbelievably-misleading error message category: Windows 7 x64 RDP Client

I spent quite a while earlier trying to make an RDP connection to another system on my network. The error message from the RDP client was:

Your computer could not connect to another console session on the remote computer because you already have another console connection in progress.

You can imagine the range of things I tried to resolve the issue.

The actual issue? The machine had a new IP address and I was trying to connect to its old IP address. Great error message 🙁

<sigh>

Stored Procedure Contracts – Return Values

Yesterday's blog post on the need for contracts for stored procedures caused a lot of comments and email. One of the most interesting comments came from Jamie Thomson regarding return values. Jamie's totally correct on this. Return values should be part of any contract.

I've been thinking further about how return values should be incorporated into a contract and initially thought it should be something like this:

CREATE OR ALTER PROCEDURE SomeSchema.SomeSproc (someparameters)

WITH CONTRACT SalesOrderHeaderAndDetails ENFORCED

     (ROWS OrderHeaders(SomeColumn INT, SomeOtherColumn NVARCHAR(50) NULL),

           OrderDetails(AnotherColumn INT, YetAnotherColumn INT,

                        EvenYetAnotherColumn GEOGRAPHY),

      RETURNS INT,

      EXCEPTIONS NoSuchCustomer(50020,’No such Customer’),

                 DuplicateOrder(50022,’That order already exists’)),

     EXECUTE AS (execution options here if needed)

I thought the values could be RETURNS INT or RETURNS NULL, but on reflection (no pun intended), I realized that in many cases it is necessary to resort to documentation to know what a stored procedure return value is. That would be eased if the return value also had a name as part of its metadata. So perhaps a more complete contract would look like:

CREATE OR ALTER PROCEDURE SomeSchema.SomeSproc (someparameters)

WITH CONTRACT SalesOrderHeaderAndDetails ENFORCED

     (ROWS OrderHeaders(SomeColumn INT, SomeOtherColumn NVARCHAR(50) NULL),

           OrderDetails(AnotherColumn INT, YetAnotherColumn INT,

                        EvenYetAnotherColumn GEOGRAPHY),

      RETURNS OrderCount(INT),

      EXCEPTIONS NoSuchCustomer(50020,’No such Customer’),

                 DuplicateOrder(50022,’That order already exists’)),

     EXECUTE AS (execution options here if needed)

The idea is that you could have a value like RETURNS SomeName(INT) or RETURNS NULL where there is no return value.

The Connect item is here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=525653 

SQL Server 2008 R2: StreamInsight Development Models

One thing that seems confusing to people when they first look at StreamInsight is that there are several development models:

1. Implicit Server

2. Explicit Server

3. IObservable/IObserver 

Implicit server is fairly straightforward. You define your input stream and create a query to consume your output stream. StreamInsight does all of the heavy lifting associated with creating the server, binding your adapters, etc.

Explicit server is the most flexible but the most coding work. You specifically spin up (create and instantiate) a server object, from it spin up an application, register your input/output adapters and create them via a factory and spin up a query binding object. You use the query binding object to bind together your standing query (via a query template) and your input and output adapters. You use this query binding object to then create a query object (an instance of a query) which you then start/stop as required. With the explicit server model, you can get reuse of query templates, adapters, etc. and you can reuse an existing event stream for multiple queries. This model is also how you'd work with a server that's not hosted in-process if that's required.

IObservable/IObserver makes the creation of an input stream quite easy. It is based on the RX extensions (Reactive Extensions) to the .NET Framework. You take an enumerable class (ie: one that implements IEnumerable) and you create an Observable class from it. You then hook up subscribers to the observable class.

Picking the right model is something you'll need to do fairly early on in your StreamInsight project.

Stored Procedures – Time for a real contract?

Increasingly, developers are using tools that try to automate code generation when dealing with databases. Stored procedures have been a thorn in the side of this. Mostly that's because it's difficult to obtain the metadata that is really needed.

RowSets

Most automated tools try to work out what rowsets can come back from the sprocs. The Entity Framework, LINQ to SQL and many others use SET FMTONLY ON to try to determine what might come back from the sproc. This is a flawed mechanism as it returns one set of metadata for every potential code path through the sproc. It really only works for the most trivial sprocs ie: single statements that perform basic CRUD operations.

The first thing that is needed is a way to express the sets of rows that might be returned. This should be part of the definition of the sproc. It should include the ability to express multiple sets of rows. Some tools only work with the first rowset but that isn't sufficient because it has become quite common for people to build sprocs that return multiple rowsets to avoid network round trips. I should be able to say something like:

[sql]
CREATE OR ALTER PROCEDURE SomeSchema.SomeSproc (someparameters)
WITH ROWS OrderHeaders(SomeColumn INT, SomeOtherColumn NVARCHAR(50) NULL),
OrderDetails(AnotherColumn INT, YetAnotherColumn INT,
EvenYetAnotherColumn GEOGRAPHY),
EXECUTE AS (execution options here if needed)
[/sql]

Note that I think the rowsets should be able to be named and I also threw in the need for CREATE OR ALTER. Please, please, please can we have this !!!

Exceptions

For a client to work effectively with a server, it should have knowledge of the potential exceptions that might be thrown by that code. This should also be available in the metadata of the sproc. I think it could be included like this:

[sql]
CREATE OR ALTER PROCEDURE SomeSchema.SomeSproc (someparameters)
WITH ROWS OrderHeaders(SomeColumn INT, SomeOtherColumn NVARCHAR(50) NULL),
OrderDetails(AnotherColumn INT, YetAnotherColumn INT,
EvenYetAnotherColumn GEOGRAPHY),
EXCEPTIONS NoSuchCustomer(50020,’No such Customer’),
DuplicateOrder(50022,’That order already exists’),
EXECUTE AS (execution options here if needed)
[/sql]

Clearly, other exceptions could occur in an unplanned way but client code should be able to be configured automatically to deal with potentially expected errors. For example, a code generation tool could automatically build a skeleton error-handling routine for errors that it already knows could exist.

Contract

Clearly, this is all then heading towards having a contract for a sproc. When you have multiple people (or worse multiple teams of people) building parts of an application, it is really important to have a contract at the interface point. Perhaps the contract itself should have a name ie: something like:

[sql]
CREATE OR ALTER PROCEDURE SomeSchema.SomeSproc (someparameters)
WITH CONTRACT SalesOrderHeaderAndDetails
(ROWS OrderHeaders(SomeColumn INT, SomeOtherColumn NVARCHAR(50) NULL),
OrderDetails(AnotherColumn INT, YetAnotherColumn INT,
EvenYetAnotherColumn GEOGRAPHY),
EXCEPTIONS NoSuchCustomer(50020,’No such Customer’),
DuplicateOrder(50022,’That order already exists’)),
EXECUTE AS (execution options here if needed)
[/sql]

Enforcement in TSQL

I’d love to have a situation where some of this is enforced as much as possible within T-SQL. For example, given the declarations above, I’d love to see a situation where a SELECT statement that doesn’t match one of the rowsets in the metadata or a RAISERROR with an error number not in the list of declared exceptions was enough to make the creation or alteration of the sproc fail. Perhaps any sproc with a declared CONTRACT could have these sorts of restrictions or we could have a word like ENFORCED:

[sql]
CREATE OR ALTER PROCEDURE SomeSchema.SomeSproc (someparameters)
WITH CONTRACT SalesOrderHeaderAndDetails ENFORCED
(ROWS OrderHeaders(SomeColumn INT, SomeOtherColumn NVARCHAR(50) NULL),
OrderDetails(AnotherColumn INT, YetAnotherColumn INT,
EvenYetAnotherColumn GEOGRAPHY),
EXCEPTIONS NoSuchCustomer(50020,’No such Customer’),
DuplicateOrder(50022,’That order already exists’)),
EXECUTE AS (execution options here if needed)
[/sql]


In the feedback, I was asked for a Connect item. Here it is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=525653

SQL Server 2008 R2 – StreamInsight – Event Payloads

One of the key decisions you'll make when working with StreamInsight is the payload that will be carried by each event. Events contain and EventKind (which is related to whether they're inserting a new event or modifying an existing one), some temporal information (depending upon the EventShape -> Point, Interval or Edge) and a payload that is user-defined.

A payload is defined via a .NET class or struct. In general, a class will be a better option as it ensures field ordering which is likely to be important for generic (vs typed) adapters. StreamInsight ignores anything except public fields and properties and there are limitations on the data types. For example, basic .NET types are permitted but not any of the nested types.

A payload will typically look like:

public class TollPayload

{

    public int TollBoothID { get; set; }

    public int LaneID { get; set; }

    public int VehicleType { get; set; }

    public String TagID { get; set; }

}

In addition to the fields and properties, I'd suggest a few other items are helpful.

First, make sure you override the ToString() method. It's helpful to be able to just show the contents of an event as a string without needing to couple the consumer's code to the payload fields. This is particularly useful with logging.

Second, I find it useful to provide public static methods that convert types (or enums) to much more meaningful names. In particular, this can be useful while projecting the event fields in the LINQ standing queries.

SQL Server 2008 R2: Reporting Services – Pagination

Another item I had on the Connect site was about pagination. I wanted to be able to restart page numbering within a report. In my case, this was because I wanted to output a set of invoices in a single report but I wanted each invoice to have its own set of page numbers.

This was another item on Connect that came back saying "Done!"

And again, the team went further.

You can disable pagination if it doesn't make sense for your rendering target. You can restart page numbers at the group level based on an expression. You can also set the page name that will be used as a tab name (worksheet name) in Excel rendering.

These sorts of items might seem small additions to the product but they make life so much simpler for those of us designing reports.

Again, thank you Reporting Services team!