Earlier today Chris Webb posted a blog entry entitled OData and Microsoft BI where he remarked upon the increasing importance of Microsoft’s burgeoning OData protocol for accessing data over HTTP. Chris also linked to Douglas Purdy’s blog post OData: The Movie which listed the following Microsoft and non-Microsoft technologies that support (or soon will support) OData either as a data producer or a data consumer:

• Excel
• .Net client
• AJAX Client
• PHP client
• Java client
• Visual Studio
• Sharepoint
• SQL Server (via Reporting Services)
• Codename "Dallas"
• Websphere

To that list I would add Windows Azure which exposes data from its storage engine using OData and also the forthcoming Live Framework technology that is expected to surface user-centric data from Microsoft’s various Live Services properties (e.g. SkyDrive, Messenger, Calendar).

Chris suggested that a pervasive format for data on the web would be a big step forward because any consumer that understood that format could talk to any producer that chose to expose it; win-win-win indeed. Unfortunately life is of course never that simple and as is so often the case there are many competing technologies in this space including (but, I suspect, not limited to) Microsoft’s OData, Google’s GData and the World Wide Web consortium’s Resource Description Framework (RDF).

This is nothing new of course; we can draw parallels with Atom and RSS which were recent competing technologies used to expose blog syndication feeds. Interestingly both of these technologies generally sit side-by-side quite happily these days (read: Will the Online Identity War turn out like the XML Syndication War?) and it will be interesting to see whether the same will happen in the OData/GData/RDF space; will organisations opt to expose multiple service heads for their various databases? I await the answer to that one with interest.

RDF is of particular interest chiefly because one of its main proponents is Sir Tim Berners-Lee, the inventor of the world wide web. RDF has been knocking around for about 10 years now and Berners-Lee sees it as the technology that will underpin the growth of the fabled Semantic Web; indeed he has been heavily involved with the recently announced service http://data.gov.uk which exposes data held by the UK government and it is no surprise that that service exposes its data using RDF.

I don’t know too much about GData but I do have a working knowledge of both OData & RDF so perhaps an overview and comparison of the two might be useful. To this observer it appears as though OData is closely tied to the tables-rows-and-columns paradigm that anybody familiar with traditional relational databases will understand. An OData service exposes multiple entities (roughly analogous to tables) and the relationships between them.

RDF however is built around the concept of triples which I assert is more analogous to a conceptual Entity-Attribute-Value model (that assertion is something I hope to explore in a future blog post). The key difference between RDF and OData though is that an RDF document can contain links to RDF documents that are hosted on other services and attributes of entities are defined in terms of those other services. Put more simply, RDF data is one massively distributed, non-centralised, interlinked web of data and if that sounds an awful lot like the mass of HTML documents which form the world wide web then you shouldn’t be too surprised given that Berners-Lee is so heavily involved. The notion of linking data together in this way has also given rise to the very descriptive moniker linked data which you may well hear mentioned in the same sentence as “semantic web” from time to time.

This has been a rather rambling blog post and really its just me dumping some thoughts out onto your computer screen as you can tell from the rather unimaginative title. Nonetheless there is some interesting stuff going on here that I hope to explore in the future so if you're interested please return some time. If you have any thoughts I'd love to read them in the comments section below.

@Jamiet

[This is just a quickie, I’m putting it up here for anyone that received the messages above and searches for it online!]

Earlier today my team experienced some issues with a Reporting Services report that when attempting to export to Excel would cause Excel to throw the error “Excel found unreadable content” (I really should have got a screenshot but I didn’t think to do that. Sorry.)

Of course we went down the usual routes. Searching online threw up a Microsoft KB article Error message when you try to open a workbook in Excel 2007: "Excel found unreadable content in Book_Name" which describes a situation related to Analysis Services.

Turns out it was nothing at all to do with Analysis Services. The solution was actually a lot simpler – our Reporting Services server had run out of disk space and clearing down some space on that disk immediately solved the problem.

So, if you encounter the above error – check your disk usage!

Hope that helps!

@Jamiet

The new T-SQL MERGE statement in SQL Server 2008 seems to be finding many uses over and above its de facto UPSERT usage scenario, probably the most popular of which is its ability to update one table from another in an ANSI-compliant manner as detailed expertly by Hugo Kornelius in his blog post Let's deprecate UPDATE FROM!

Today I stumbled upon a different use for it, returning values using an OUTPUT clause from a table used as the source of data for an insertion. In other words, if I’m inserting from [tableA] into [tableB] then I may want some values from [tableA] after the fact, particularly if [tableB] has an identity. Observe my first attempt using a straight insertion where I am trying to get a field from @source.[id] that is not used in the insertion:

DECLARE @source TABLE (    [id] INT PRIMARY KEY ,  [name] VARCHAR(10) ); INSERT @source VALUES(1000,'Harold'),(2000,'Madge'); DECLARE @destination TABLE (    [id] INT PRIMARY KEY IDENTITY(1,1) ,  NAME VARCHAR(10) ); INSERT @destination ([name]) OUTPUT INSERTED.[id] AS NEWID,[source].[id] AS OldId SELECT [name] FROM   @source;

This failed with the error:

The reason this fails is that the INSERT statement has no FROM clause thus the OUTPUT clause can never know about where the data is sourced from; the FROM clause in the SELECT that is used to source the data for the insertion is not actually part of the INSERT statement. Let’s try this with a MERGE instead:

DECLARE @source TABLE (    [id] INT PRIMARY KEY ,  [name] VARCHAR(10) ); INSERT @source VALUES(1000,'Harold'),(2000,'Madge'); DECLARE @destination TABLE (    [id] INT PRIMARY KEY IDENTITY(1,1) ,  NAME VARCHAR(10) ); MERGE  @destination USING  (SELECT [id], [name] FROM @source) AS [source] ON     (1=0) --arbitrary join condition WHEN   NOT MATCHED THEN        INSERT (name)        VALUES  (source.Name)        OUTPUT  INSERTED.id AS NEWID,[source].[id] AS OldId,INSERTED.name;

This worked!

Notice how we are able to reference the source of our data in the OUTPUT clause of of our MERGE statement which we couldn’t do when using INSERT. Pretty cool huh!

I remembered while writing this that Adam Machanic has already documented this capability in his blog post Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE and he has covered it in a lot more detail than I have here but still, I see no harm in reiterating and the code above is an easily executable demo of this cool little feature.

@Jamiet 

N.B. Thanks to Aaron Bertrand for his instructions here on producing well-formatted T-SQL code for blogs! I’m putting this comment here so I know where to find it next time!

One requirement in any database implementation that I have ever worked on is that the notion of a point-in-time has to be represented in some way. The tool that I use on a regular basis, SQL Server, provides numerous datatypes that aid in the representation of a point-in-time and I’m sure that most people reading this will be au fait with them. They are:

• time
• date
• smalldatetime
• datetime
• datetime2
• datetimeoffset

Read more at Date and Time Data Types and Functions (Transact-SQL).

I suspect (though stand to be corrected) that the accuracy with which people wish to record a point-in-time is usually to the nearest day or the nearest second however until very recently neither of these accuracies was specifically catered for with a dedicated datatype. Up until the introduction of the date datatype in SQL Server 2008 datetime was the datatype that most would use to record a point-in-time however the accuracy of datetime is neither day nor second, it is 0.00333 seconds; smalldatetime could be used but that also does not have accuracy to the day or second, it is to the minute.

The point I’m trying to make is not that people are using the wrong datatypes to store their data, nor that everyone should just use the most accurate point-in-time datatype (that being datetime2 which is accurate to 100 nanoseconds). No, my point is that there are many different situations which have a need to store a point-in-time and SQL Server does not provide datatypes to cater for all of them. That is what prompted me to raise a Connect submission entitled [T-SQL] Month & Quarter datatype where I asked for datatypes that represent a Month or a Quarter. Simon Sabin commented that he also wanted datatypes to represent a time interval (and Aaron Bertrand pointed out that a Connect submission already exists for that) whilst Rob Farley ran with my idea and suggested that we should have week, hour, semester datatypes as well. There are some good justifications on the submission for why such datatypes are required so if I have failed to convince you here feel free to click through and take a read.

The point of this blog post though is not to drum up support for my Connect submission (though that would be nice), instead it is to make reference to the reply I received from a gentleman on the SQL Server team going by the name ‘Jim’. Jim pointed me to an article on Wikipedia which talks about the ISO 8601 standard that provides an agreed standard for the representation and exchange of date and time related data.

I previously assumed that the only standard date/time formats were something like YYYYMMDD &  YYYYMMDDTHH24:MI:SS but not so, ISO 8601 provides representations for centuries, years, months, weeks, ordinal dates, hours, minutes, seconds, fractions of time units, timezones and time intervals (but not quarter, unfortunately). I won’t go into much detail about the standard as you can go and read about it for yourself (and I recommend the wikipedia article as essential reading for any data professional, particularly the General Principles) but for the benefit of both myself and others I have provided below a summary of all the point-in-time representations that the wikipedia article mentions; and fascinating it is too (to me anyway). Take the time to have a read, you may be enlightened:

Some things to note:

• Some representations are overloaded (e.g. 21 can mean the 21st century or 21st hour of the day) so context can be a factor.
• The week notation is still prone to ambiguities; when does week 1 start? More pertinently, when does week 2 start? Apparently the ISO defines a standard for defining this but it isn’t adhered to as stringently as ISO 8601.
• The week notation can also be quite confusing, if the ISO directives are followed to the letter then “2009-W53-7” represents the 3rd day of the first month of 2010 (read the wikipedia article to understand why).

The article also provides some standard representations of durations:

and time intervals:

One last interesting point of trivia, the second edition of ISO 8601 (published in 2000) allowed for 2-digit dates whereas the third (and at the time of writing, current) version (published in 2004) does not. Hmmm… did someone say Y2k?

OK, time to wrap up; why am I writing this? Well, unambiguous representations of data are of paramount importance especially in the distributed heterogeneous XML/JSONified world of data exchange that we live in today so a good understanding of the standards that define those representations should be a pre-requisite to working in that arena. I hope this blog post has highlighted the fact that these standards exist and that you feel compelled to adhere to them in your future work. If you want to understand how damaging it can be to veer from the unambiguous formats then take a read of my recent blog post Unambiguous date formats.

Oh, and if you want to vote for a month datatype in SQL Server, be my guest :)

@Jamiet

There was lots of news that came out of Microsoft’s recent Professional Developer Conference (PDC) event however one item that may have slipped under the radar of many is that the data format specification for WCF Data Services (previously known as ADO.Net Data Services aka Astoria) has been proposed as an open standard for data exchange across the web. Its name? The Open Data Protocol, or OData for short. From the OData website:

The Open Data Protocol (OData) is a web protocol for querying and updating data. OData applies web technologies such as HTTP, Atom Publishing Protocol (AtomPub) and JSON to provide access to information from a variety of applications, services, and stores.

You can read more at Open Data Protocol on MSDN.

[It should be noted that WCF Data Services and OData are not the same thing. My interpretation is that WCF Data Services is a technology that allows data to be surfaced as an OData-compliant service.]

Microsoft’s desire to get OData accepted as a universal data access protocol should not come as a surprise, I view OData as the next evolution in a journey that can count ODBC and OLE DB as significant milestones:

1. According to wikipedia Microsoft created ODBC back in 1992 as a standard for accessing relational database management systems.
2. OLE DB was the next evolution which provided a standard for accessing both relational and non-relational data sources in a uniform manner.
3. Now we have OData which is an attempt to provide a uniform representation for any data that can be surfaced over that which is becoming the lowest common denominator for data exchange - HTTP.

I’ve been following the evolution of Astoria/ADO.Net Data Services/WCF Data Services for quite some time now, even back as far as when an influential team within Microsoft were probably going to reject AtomPub (according to Dare Obasanjo) and instead use their own proprietary alternative (according to Yaron Goland). Back in March 2008 I noted with interest an announcement by George Moore on the Live Services development blog that ADO.Net Data Services (as it was then known) would form an integral part of Microsoft’s stack of data access protocols and technologies across all data storage technologies:

The complete storage and developer tools stack revealed at MIX08 looks like this (described top to bottom). You are free to utilize this stack at any level of abstraction – there are no requirements to use all layers, and you are free to substitute your own developer tools against any layer

Area	Product, Library or Protocol
4: Synchronization infrastructure:	"Astoria Offline"
	Microsoft Sync Framework
	Feedsync AtomPub extensions
3: Developer tools:	ADO.NET Data Services
	.NET WCF Syndication libraries
	AtomPub URI namespace conventions
2: Protocols:	AtomPub
	Atom
1: Underlying Products and Services:	On premises: SQL Server
	Structured Cloud Storage: SQL Server Data Services
	Live services: Spaces Photos and Application Data Storage

A Unified Standards-Based Protocols and Tooling Platform for Storage from Microsoft

21 months later and we are really starting to see the fruits of those labours come to bear. Sharepoint 2010 Lists, SQL Server Reporting Services 2008 R2, Excel 2010 (through Powerpivot), Project “Dallas” & Windows Azure are all either an OData producer or consumer and we are promised that there are more to come. Microsoft have received deserved criticism down the years for different teams building competing technologies yet here we witness a quiet unification around a common data access stack and that can only be a good thing. Do I sense the impact of Ray Ozzie here?

This has been a short commentary on the evolution of the OData protocol, any comments would be much appreciated.

@Jamiet

Further reading (some of which are linked to above):

• OData is grease to cut data friction by Jon Udell
• Now you know...it's Windows Azure by Pablo Castro
• Open Data Protocol
• OData.org
• A Unified Standards-Based Protocols and Tooling Platform for Storage from Microsoft
• Every SharePoint 2010 server is a Data Services server by Pablo Castro

One of the most commonly used data types in SQL Server is [datetime] which unfortunately has some vagaries around how values get casted. A typical method for defining a [datetime] literal is to write it as a character string and then cast it appropriately. The cast syntax looks something like this:

DECLARE @dt NVARCHAR(19) = '2009-12-08 18:00:00'; SELECT CAST(@dt AS datetime);

Unfortunately in SQL Server 2005 the result of the cast operation may be dependent on your current language setting. You can discover your current language setting by executing:

SELECT @@LANGUAGE

To demonstrate how your language setting can influence the results of a cast take a look at the following code:

ALTER  DATABASE tempdb SET        COMPATIBILITY_LEVEL = 90 ; --Behave like SQL Server 2005 USE tempdb GO DECLARE @t TABLE (       dateString  NVARCHAR(19) ); INSERT @t (dateString) VALUES ('2009-12-08 18:00:00') --'yyyy-MM-dd hh24:mi:ss' ,      ('2009-12-08T18:00:00') --'yyyy-MM-ddThh24:mi:ss' ,      ('20091208 18:00:00')   --'yyyyMMdd hh24:mi:ss' SET        LANGUAGE french; SELECT 'french' AS lang ,      DATENAME(MONTH,q.[dt]) AS mnth ,      q.[dt] FROM   (        SELECT  CAST(dateString AS DATETIME) AS dt        FROM    @t        )q; SET        LANGUAGE us_english; SELECT 'us_english' AS lang ,      DATENAME(MONTH,q.[dt]) AS mnth ,      q.[dt] FROM   (        SELECT  CAST(dateString AS DATETIME) AS dt        FROM    @t        )q;

We are taking the value which can be described in words as “6pm on 8th December 2009”, defining it in three different ways, then seeing how the @@LANGUAGE setting can affect the results. Here are those results:

Notice how the interpretation of the month can change depending on @@LANGUAGE. If @@LANGUAGE=’french’ then the string '2009-12-08 18:00:00' is interpreted as 12^th August 2009 (‘août’ is French for August for those that don’t know) whereas if @@LANGUAGE=’us_english’ it is interpreted as 8^th December 2009.

Clearly this is a problem because the results of our queries have a dependency on a server-level or connection-level setting and that is NOT a good thing. Hence I recommend that you only define [datetime] literals in one of the two unambiguous date formats:

• yyyy-MM-ddTHH24:mi:ss
• yyyyMMdd HH24:mi:ss

That was going to be the end of this blog post but then I found out that this behaviour changed slightly in SQL Server 2008. Take the following code (see if you can figure out what the results will be before I tell you):

ALTER  DATABASE tempdb SET        COMPATIBILITY_LEVEL = 100 ; --Behave like SQL Server 2008 GO USE tempdb GO SET        LANGUAGE french; DECLARE    @dt NCHAR(10) = '2009-12-08 18:00:00'; --Ambiguous date format SELECT CAST(@dt AS datetime) AS [ExplicitCast] ,      DATENAME(MONTH,@dt) AS [MonthFromImplicitCast] ,      DATENAME(MONTH,CAST(@dt AS datetime)) AS [MonthFromExplicitCast];

Here we are doing three different things with our nchar literal:

• explicitly cast it as a [datetime]
• extract the month name from the char literal using the DATENAME function (which results in an under-the-covers implicit cast)
• extract the month name from the char literal using the DATENAME function after it has been explicitly casted as a [datetime]

Note that the compatibility level is set to SQL Server 2008 and @@LANGUAGE=’french’. Here are the results:

(Were you correct?)

Let’s take a look at what is happening here. The behaviour when we are explicitly casting as [datetime] hasn’t changed, our nchar literal is still getting interpreted as 12th August rather than 8th December when @@LANGUAGE=’french’. The [MonthFromExplicitCast] field is interesting though, it seems as though the implicit cast has resulted in the desired value of 8th December. Why is that?

To get the answer we can turn to BOL’s description of the DATENAME function syntax:

The implicit cast is not casting to [datetime] at all, it is actually casting to [date] which is a new datatype in SQL Server 2008. The new date-related datatypes in SQL Server 2008 (i.e. [date], [datetime2], [time], [datetimeoffset]) disregard @@LANGUAGE and hence we get behaviour that is more predictable and, frankly, better.

These new behaviours for SQL Server 2008 were unknown to me when I began this blog post so I have learnt something in the course of authoring it, I hope it has helped you too. No doubt someone somewhere is going to get nastily burnt by this at some point, make sure that it isn’t you by always using unambiguous date formats:

• yyyy-MM-ddTHH24:mi:ss
• yyyyMMdd HH24:mi:ss

regardless of which version you are on!

Don’t forget to check out other T-SQL Tuesday blog posts, see Adam Machanic’s blog post Invitation to Participate in T-SQL Tuesday #001: Date/Time Tricks for details!

@Jamiet

Earlier today Brent Ozar blogged about how he had been archiving tweets from various people that he follows into a SQL Server database.

He made a backup of that database available for download on his blog so that others could download it and have a play of their own. I thought that rather than have all those people that wanted to party on the data download and restore the database for themselves it might be fun (and prudent) to stick it somewhere where anyone could get it so (with Brent’s permission) I’ve hosted the database up on SQL Azure.

Here are the credentials that you’ll need if you want to connect:

You can connect using SQL Server Management Studio although if you’re not using the November 2009 community technology preview (CTP) of SQL Server 2008 R2 there are some hoops you need to jump through in order to do that. First thing to know is that you can’t connect the Object Explorer, only a query window. So, after hitting the “New Query” button here’s what you need to do:

Firstly, enter the details as shown in this screenshot:

and then hit “Options”. On the options screen you need to enter the name of the database that you need to connect to which, as I mentioned above, is [brentotweets]:

Then hit “Connect”. You’ll probably get the following error about not being able to get to sys.configurations. Don’t worry about it, you can safely click OK and ignore it:

Once you’re in you can hit the database just like you would any other SQL Server database:

SQL Azure requires that you specify a range of IPs that can connect to the server and I’ve made it as wide as possible so I’m assuming it should be OK:

Have fun! Note that this won’t be available forever because at some point I’m going to have to start paying for it, you’ll be good for the next few weeks though.

@Jamiet

P.S. For those that are interested I pumped all the data up there using SSIS.

It took 41m 41s over my home broadband line from a Virtual PC guest. Bear in mind that I’m in London and the server is I believe somewhere in Texas, US. According to speedtest.net I’m getting 4.67Mbps up and 0.57Mbps down:

(Note to self: Get new ISP. Or move house!]