Using a Sequence in the Insert Action of a Merge Statement

If you create a MERGE which tries to use a sequence to get a new key value for a record, you can experience the error

NEXT VALUE FOR function can only be used with MERGE if it is defined within a default constraint on the target table for insert actions.

To demonstrate, try the following code.

create table MergeTarget ( TableKey int not null ,MyColumn varchar(12) not null ); create table MergeSource ( MyColumn varchar(12) ); insert into MergeSource ( MyColumn ) values ( 'New row' ); GO create sequence NewKey as int start with 1 increment by 1; GO merge MergeTarget as t using MergeSource as s on t.MyColumn = s.MyColumn when not matched by target then insert ( TableKey ,MyColumn ) values ( next value for NewKey ,s.MyColumn );

You will get the error when trying to run the MERGE.

To get around, you can create a default constraint on the column in the target table which is based on the sequence, then specify “default” as the inserted value:

alter table MergeTarget add default next value for NewKey for TableKey; GO

Then change the MERGE to remove the reference to the sequence and replace it with the keyword ‘default’:

merge MergeTarget as t using MergeSource as s on t.MyColumn = s.MyColumn when not matched by target then insert ( TableKey ,MyColumn ) values ( default ,s.MyColumn );

Creating a SQL Server LineString in SSIS

According to Books Online, “a LineString is a one-dimensional object representing a sequence of points and the line segments connecting them.” That is, a single row which can describe a complete path rather than needing many rows each containing just one point.

My earlier post deals with a way to get GPX files (the format used by most handheld GPSr units and by most smartphone position tracking apps) loaded into a SQL server table, but all I have ended up with is a list of points and the time I was at them. There’s no concept of travel, and I can’t use any simple methods to calculate the distance travelled or what other tracks I might have intersected with. Also, I have 2652 rows for a single day of walking, which I could condense to 1 row if I used LineStrings instead of points. I’m up for that and most client programs are going to prefer it too.


Creating a LineString is trivial using SQL Server’s spatial methods:

declare @line geometry;
set @line = geometry::STGeomFromText('LINESTRING(-0.01278 51.68764, -0.01400 51.74256)',0);
select @line;

The LineString is created from the list of coordinates in the format longitude[space]latitude in a comma separated list. You can include as many longitude/latitude pairs as you like in the list. So the challenge is to take the table of coordinates and pivot it into a text column which contains all of them in a single string. SQL Server’s PIVOT methods don’t provide a way of doing this and nor does the SSIS pivot transform. You could do it with a cursor in T-SQL but that’s not going to scale particularly well.

Much better, then, to do this at load time in SSIS using a script which can group the track points by their identifier and create the string of points that can be used for the LineString. Scripts generally scale really well and you’ve effectively got all of .NET at your disposal to accomplish the task.

The first step is to write a query that provides the data. The only vital thing is that you need to sort the results by your grouping key and in time order. Based on the simple schema I have loaded the points into I need to write code like this:

select as TrackName ,p.time as PointTime ,p.ele as PointElevation ,convert(varchar(11), p.lon) + ' ' + convert(varchar(11), as Coordinate from Tracks t inner join Segments s on t.trk_Id = s.trk_Id inner join Points p on s.trkseg_Id = p.trkseg_Id order by t.trk_Id ,p.time

The script is going to work by adding the points to a string one by one, so they need to be in time order. It checks for changes in the track identifier (in this case it’s TrackName) to create each line. I’ve also stitched the longitude and latitude together into a single column with a space between them.

Because the script is going to take multiple incoming records and group them together to create a single output record, it needs to be an asynchronous script. To accomplish this, add a script transform to the data flow. On the Inputs and Outputs page, delete the Output 0 output and add a new output. Check that its SynchronousInputID property is set to None to make it an asynchronous script. Add the columns which you want to output to the script; at a minimum you’re going to need a column for your track identifier and a column for the LineString but I’m also adding a start and end time and a number of points. The LineString column should probably be created as a DT_TEXT type, because the DT_STRING type can only hold 4000 characters. DT_TEXT has to be worked with using binary methods in the script but at least it can hold as many characters as you like.


Each row flowing through the script could match one of three conditions:

  1. It could be the first row of the entire data set.
  2. It could be a row which belongs to the same track as the previous track.
  3. It could be a row which is part of a different track.

On cases 1 and 3, a new line needs to be started. On case 2, the points for the incoming row need to be added to the LineString currently being built. On case 3, the current LineString needs to be closed and a row written to the output, and a new LineString started. Finally, on the very last row of the data, the last LineString needs to be finished and a row written to the output.

Here’s the script that does the work. Notice that I am also keeping a record of the start time of the track, the end time, the name of the tracks, and the number of points that make up the track.

#region Namespaces using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; using System.Text; using Microsoft.SqlServer.Dts.Pipeline; #endregion [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { public override void PreExecute() { base.PreExecute(); /* * Add your code here */ } public override void PostExecute() { base.PostExecute(); /* * Add your code here */ } bool FirstRow = true; string Track = null; DateTime Date = default(DateTime); DateTime StartDate = default(DateTime); string Coordinate = null; string StartPoint = null; int Points = 0; StringBuilder Linestring = new StringBuilder("LINESTRING("); public override void Input0_ProcessInput(Input0Buffer Buffer) { while (Buffer.NextRow()) { Input0_ProcessInputRow(Buffer); // After the first row has been processed, turn off the first row flag FirstRow = false; } if (Buffer.EndOfRowset()) { // Last row so finish off final callsign record AggregatedTrackLinesBuffer.TrackName = Track; AggregatedTrackLinesBuffer.LineStartTime = StartDate; AggregatedTrackLinesBuffer.LineEndTime = Date; AggregatedTrackLinesBuffer.NumberOfPoints = Points; Linestring.Remove(Linestring.Length - 2, 2); Linestring.Append(")"); AggregatedTrackLinesBuffer.TrackLine.AddBlobData(System.Text.Encoding.ASCII.GetBytes(Linestring.ToString())); } } public override void Input0_ProcessInputRow(Input0Buffer Row) { // Rows are in track and date/time order. Need to add each pair of points // to a string, separated by commas. Start a new string if the callsign changes. if (FirstRow) // First row in data set { AggregatedTrackLinesBuffer.AddRow(); Points = 1; Track = Row.TrackName; Date = Row.PointTime; StartDate = Row.PointTime; Linestring = Linestring.Append(Row.Coordinate); Linestring = Linestring.Append(", "); Coordinate = Row.Coordinate; StartPoint = Row.Coordinate; } else if ((!FirstRow) & (Track != Row.TrackName)) //New track { // Write the current variables out to the row AggregatedTrackLinesBuffer.TrackName = Track; AggregatedTrackLinesBuffer.LineStartTime = StartDate; AggregatedTrackLinesBuffer.LineEndTime = Date; AggregatedTrackLinesBuffer.NumberOfPoints = Points; Linestring.Remove(Linestring.Length - 2, 2); Linestring.Append(")"); AggregatedTrackLinesBuffer.TrackLine.AddBlobData(System.Text.Encoding.ASCII.GetBytes(Linestring.ToString())); // Begin a new line AggregatedTrackLinesBuffer.AddRow(); Points = 1; Track = Row.TrackName; Date = Row.PointTime; StartDate = Row.PointTime; Coordinate = Row.Coordinate; StartPoint = Row.Coordinate; Linestring.Clear(); Linestring.Append("LINESTRING("); Linestring = Linestring.Append(Row.Coordinate); Linestring = Linestring.Append(", "); } else if ((!FirstRow) & (Track == Row.TrackName)) // New point for same track { Points += 1; Date = Row.PointTime; Coordinate = Row.Coordinate; Linestring = Linestring.Append(Row.Coordinate); Linestring = Linestring.Append(", "); } } }

Running the package with this script condenses the inbound rows to one row per track name:


The TrackLine column is DT_TEXT so the data viewer can’t show it, but it can be inserted into a varchar(max) column in SQL Server with no problem:


To render the LineString column as a geography or geometry spatial column you would use the appropriate syntax:


I have saved the database I used, including the track points, and the SSDT project with the SSIS and a couple of reports to show points and LineStrings in action here.

Loading GPX Track Files with SSIS

Just a quick post because this isn’t really that hard to do, but it might help someone sometime.  As part of another project I needed some dummy spatial data so I went out for a quick cycle ride and used my GPS unit to save a track file which I then copied onto my PC to see if I could make use of it.

A GPX file is just an XML file.  I expect the exact format used by each device is slightly different, but the file generated by my rather ancient Garmin CSx is very simple:


After the headers, there are three nested elements: a <trk> (track) which has one or more <trkseg> (track segments) which have multiple <trkpt> (track points).  The number of track points is probably configurable on your device.  A track point has four attributes: its latitude and longitude, its elevation and a timestamp.

To load the file you will need an appropriate schema file.  The full schema for GPX is at so you can save the whole file if you like.  However, SSIS will still complain a bit because published XSD files don’t often contain length and other descriptive attributes of files.  To make things simple, I created my own XSD file which you can download from here.  This does include the length attributes of the text fields so SSIS won’t throw warnings about using default string column lengths.  However it only describes the elements which my GPX file contains so you might need to edit it if your file includes more attributes.

With the GPX file and the XSD file ready, all we need to do is create an SSIS package containing a data flow. Add an XML data source and configure the file and XSD location.

The columns should then be configured automatically.  You can rename the output columns as you wish.


One thing to notice is that SSIS creates three outputs, one each for the track, track segment and track point levels of the GPX file.  In this example I am only using the track point output because I only have one track but if you were building a proper tracks database you’d certainly want to deal with the other two outputs.

All that remains is to send the track data to a destination table.


With the data loaded you can do clever things, such as use the Office 2013 version of Power View in Excel to plot where you were:

track analysis

You can also take the latitude and longitude and use them to create spatial data fields in SQL Server, which I’ll cover next time.

Working With Hierarchies and Drill Down in Power View for Excel 2013

A whole slew of improvements to Power View have arrived today with the preview release of Office 2013. It’s not just that Power View is now component of Excel on the desktop but that Power View itself can do many more things than in its original release as part of Reporting Services 2012. Here’s just one example: drill down.

Drill down (and drill up) is the ability to negotiate a vertical hierarchy. For example, it’s very easy to understand a calendar as a hierarchy – a year inherits four quarters, each of which inherit three months, each of which inherit around thirty days. Generally a dashboard tool allows a user to visualise data grouped by the top of the hierarchy and then use some simple mechanism like a click to drill down through any path. Power View now offers this facility – one step on the road to an analytic/dashboarding tool maybe?

Here’s how to achieve it.

For a start we need to create something that actually is a hierarchy*.  This can be done in PowerPivot’s Data Model designer.  It’s easy to do:

  1. After you’ve imported a table that contains hierarchical data, switch to Diagram View.
  2. Click the Create Hierarchy button on the table’s title.
    Create a hierarchy in a PowerPivot table
  3. Drag each attribute from the table to the right place in the hierarchy.
  4. Give the hierarchy and its attributes a useful, meaningful name.
    A PowerPivot hierarchy

Power View (and any other client tool you use this model with) will now be able to use this hierarchy.

Back in Excel, select Power View from the Insert ribbon.

This causes a new tab to appear in the workbook, with the Power View interface. For this example I have just added some of the tables from the Adventure Works DW sample database and created a SUM measure on the [InternetSales].[SalesAmount] field.  With this measure checked, the visualisation changed to a column chart, and the Calendar hierarchy ticked, Power View builds this view for me:
Power View chart with Drill Down

To drill down through the data you just double-click on whichever column you want to drill into:
Power View drill down in a chart

Power View automatically (and instantly because this is PowerPivot data) redraws the chart at the next level down. You can keep going to until you reach the bottom level of the hierarchy.

To drill up, hover your mouse pointer over the chart and three tiny icons appear in the top left corner.  The first of these is the drill up command. It only needs a single click.

Drill down also works with other types of object.  With a matrix, a double click on a row or column header goes down one level. A drill up is done by single-clicking the up arrow icon on the left hand side of the first header cell.
Power View drill down in a matrix

All this functionality works with Power View in SharePoint 2013 as well as in Excel on the desktop.

* OK, you don’t really need to define a hierarchy in your data model because Power View will treat any attributes placed on the axis as a hierarchy.  But it’s good practice to create hierarchies in your data source for consistency.

SharePoint Authentication Basics–What Authentication Mode is the Site Using?

Or, more accurately, “What authentication mode is the web application using?”.  However, most site collections only have one web application so for most of the time the two questions are interchangeable.

In the last few days I have had to ask several people on the forums to check their authentication mode and then had to give instructions. This is a quick post to explain a bit better than one can manage in a forum reply.

IIS supports two authentication providers when you are using integrated Windows authentication: NTLM and Negotiate (Kerberos).  There are other options that allow forms-based, anonymous and other types of sign on but for business intelligence purposes it’s likely that most of the time integrated Windows authentication is what you will want.

When a web application is created through Central Administration, the authentication settings are defined as part of the setup.  You can later find them by the following procedure.

  1. From Central Admin, select the Security link, then under the General Security heading click Specify authentication providers.
  2. On the list of defined zones, make sure the correct web application is selected in the top-right corner then click on the name of the zone you need to check.
    Most of the time there will only be one zone and it will be called Default.
  3. Scroll down to the IIS Authentication Settings section to see what mode is enabled.

If you make a change to this setting, SharePoint should update the IIS website to ensure that the selected provider is enabled there.  You can check it (and you should) by using the following steps:

  1. Launch IIS Manager
  2. Select the web site in the left hand pane then double-click the Authentication icon
  3. Check that Windows Authentication is enabled then click the Providers link on the right hand side.
  4. Check that the required method is in the list

PowerPivot Configuration Tool Requires SharePoint Service Pack 1

How’s this for an error message?

System Validation error running PowerPivot 2012 configuration tool

I got this on a client’s site this week when running the PowerPivot configuration tool after installing SQL Server 2012 into an existing SharePoint farm.  The same error message appears in the installer log:

High: SharePoint 2010 SP1 is not installed on this computer. You must apply SP1 before using a SQL Server 2012 instance of the Database Engine as the farm’s database server.

Of course, I was aware that PowerPivot 2012 requires service pack 1 to be installed in SharePoint.  I was so aware of it that I had just finished installing SP1 and the April 2012 CU before even trying to install PowerPivot 2012.  So I knew the right patch was in place.  But still I couldn’t get past this error message, even after trying the usual suspects of IISRESET and even a complete server reboot.

Then it occurred to me that perhaps the PowerPivot configuration tool was checking that the server was at exactly service pack 1, with no other updates applied.  A quick Google provided one existing report of this error message, at  The resolution there was to uninstall SharePoint completely, reinstall it, apply SP1, then install and configure PowerPivot.  That might be OK in a brand new build or in a little PoC, but it certainly isn’t an option in any environment that has been in use and has content.

So what can you do?  You can’t uninstall a SharePoint cumulative update, there is simply no mechanism for doing it.  Well, there are two approaches.  Firstly you could use PowerShell to configure PowerPivot rather than the configuration GUI tool.  Alternatively you can cheat, as long as you don’t mind a little registry fooling.

The configuration tool is checking the value of the registry key HKLM\Software\Microsoft\Office Server\14.0\BuildVersion and it needs to find the value of 14.0.6029.1000.  Strangely, the April 2012 cumulative update has a lower number than this rather than a higher one.  Anyway, my resolution for the issue was to temporarily change the value of the registry key to 14.0.6029.1000.  The configuration wizard then ran perfectly happily.  After it was complete I changed the value back to its original value.

I have raised a Connect bug for this problem.  Please vote for it if you have experienced the issue.

Update 30th June 2012: As per Lee’s comment and a message I had from Microsoft, this issue only occurs if your most recent SharePoint patch is the April 2012 cumulative update, because this (erroneously) has a lower build number than service pack 1 does. So this should be a problem only until the next cumulative update.  Thanks for the clarification.

Using Images in Power View, PowerPivot and Tabular Models

A colleague demonstrated what using images can do to make Power View really stand out yesterday.  I was aware of this functionality but hadn’t seen it in use before, and we had a bit of a hunt around together in order to get it to work from a PowerPivot model so I thought I’d put together this guide.

There are two ways in which Power View can retrieve and display an image: from a URL reference and from a BLOB object in a database.  I’ll cover both of them, starting with using a URL to an existing image.

The first thing to do is get all your images together.  It doesn’t really matter where they are or if they’re all in one place but I’d say a good idea is to gather them all into a media asset library in SharePoint, like this:

images hosted in SharePoint library

That means each one will have its own URL but they’ll be similar enough to make adding them to a database table easy.  Of course the pictures might be anything – products would be obvious – but I have gone with people in this case.

The next step is to associate the images with the database record they represent.  Add a column to the database table that contains your people (or products or whatever):

ImageURL column added to dimension table

Populate the column with the URLs:

Adding URLs to a column

That’s all we need at the database end.  To use those images in PowerPivot, we can start a new model and import the tables and columns we need.  Make sure to include the URL column we just populated.  If you’re working through this example, here are the AdventureWorksDW2012 tables that I imported:

Records imported to PowerPivot

But as far as PowerPivot is concerned, that column we added is just a string.  The fact it contains a URL pointing at an image is not known at this point.  If I upload the model to SharePoint and create a Power View report from it, I get the txt not the image:

URLs displayed as text instead of retrieving images in Power View

At this point in testing, my colleague and I went back to PowerPivot and tried to find out how we could let the model know this was a URL.  It’s not on the Home ribbon:

Power View Home toolbar ribbon

And it’s not on the Design ribbon:

Power View Design toolbar ribbon

But, after much random poking about at various bits in PowerPivot, I discovered the previously-unknown “Advanced Mode”:

Enable Advanced Mode in PowerPivot

When I say “previously-unknown”, I mean unknown by me.  Perhaps everyone else already knew about it but it was new to me and my colleague who’s pretty much the best front-end developer I know.  Anyway, when you switch it on it provides another ribbon toolbar with some goodies on it, one of which is a simple checkbox to indicate that a column contains an image URL:

Mark a column as containing an ImageURL in PowerPivot

With this ticked and the model re-deployed to SharePoint, Power View displays those images when they’re used on the canvas.  And if you use them as a tile or slicer they become clickable and live so you can do things like this:

Images from URLs used as clickable tiles in Power View

This is pretty cool.  Remember you can export a Power View to PowerPoint and keep all this lovely interactivity for your next sales presentation.

So, what about the other way of using images?  Depending on your point of view it’s either easier or harder.  What you do is import the images into a BLOB column in your database.  AdventureWorksDW2012 already has this in the DimProduct dimension in a column called LargePhoto, which you can import as part of the PowerPivot model.

BLOB column imported to a PowerPivot model

However, the column doesn’t seem to appear in the list of available data choices in Power View:

BLOB binary columns do not show in Power View

Again you fix this with a trip to the Advanced toolbar in PowerPivot.  On this toolbar is a button labelled Table Behaviour which opens a dialog box from which you can select the table’s UID column, then pick any BLOB column as the default product photo:

Setting the default image BLOB column for a data row in PowerPivot

With everything saved and republished to SharePoint, that column now appears in Power View and it can be used in exactly the same way as an ImageURL column can.

Images from binary data BLOB columns used as clickable tiles in Power View

What about an Analysis Services tabular model?  With the luxury of a full IDE, the options aren’t so hidden away.  Both the properties are simply accessed from the Properties box.  Select the column you want to make an Image URL and set the Image URL property to True.

Setting the Image URL and Default Image properties for a column in a tabular model.

The Default Image property is in exactly the same place.  It’s enable if you’ve selected a BLOB column or a text column for which you’re already set the Image URL property.

Is this a bug in PowerPivot?

Jamie Thomson made AdventureWorks2012 available on Azure as a public service.  I’ve never used Azure because so far none of my clients or projects have been able to come up with a use case for it, but I thought it would be a cool thing to demo with PowerPivot – import data from Azure into a PowerPivot model.  So I used the provided connection information to try to import the data.  Here are the steps I followed.

  1. Open Excel and launch the PowerPivot Window.
  2. Select “From Other Sources” and choose “Microsoft SQL Azure”.
  3. Enter the connection information as follows:
    (Visit Jamie’s page for the details.)
    As you can see, the connection succeeds when tested.
  4. Choose to select from a list of tables and views:
  5. It correctly lists all the tables.  Now, check one of the standard AdventureWorks tables – such as SalesOrderHeader.  Click “Select Related Tables” and it correctly follows all the FK relationships in the database and checks them:
  6. Select any one of the tables and click “Preview & Filter”.  ERROR MESSAGE!!!
    For the benefit of any search engines watching, it reads “Failed to retrieve data from SalesOrderHeader. Reason: Invalid object name ‘SalesOrderHeader’.”
  7. If you ignore this and click Finish, you will just get errors when it tries to import the data.

At this point (which I have reproduced several times), it’s time for some troubleshooting.  First of all, there’s no problem at all using SSMS to connect to the database, viewing all the tables and selecting data.  So the actual Azure database is working and the permissions are OK.

Secondly, if you write a query to get data into PowerPivot, it works fine.  So my connection settings are working:

Thirdly, PowerPivot does work if you select one of the tables in the database that isn’t part of the standard AdventureWorks database:

What’s the difference between the [SqlFamily] table and the [SalesOrderHeader] table?  The only one I can see is that the first is in the default (dbo) schema whilst the second is in a different (Sales) schema.  My conclusion is that the PowerPivot GUI doesn’t add the schema name when it tries to retrieve data.

Can anyone else reproduce this?  I can’t find a Connect item or any solution on the web.

SQL Relay Session

I would imagine if you are in the UK and reading my blog, you know what SQL Relay is. A spin-off from SQLBits, it’s part Microsoft SQL Server roadshow, part MVP teaching session, and part community knowledge sharing event.

I’ll be attending and speaking at the Manchester event on May 22nd. I picked Manchester because Jamie Thomson (b | t) and Chris Testa-O’Neill (b | t) are two of the featured MVP speakers so it gives me a chance to pick up some of their knowledge around my core skills (BI) as well as getting a broader understanding of the rest of SQL Server 2012 with Morris Novello and Andrew Fryer (t) from Microsoft, then Tony Rogerson’s (b | t) session on availability.

My session is a circular trip through business intelligence with SharePoint and SQL Server 2012. Why circular? Because it starts in Excel and ends in Excel but it covers an awful lot of ground on the way. It’s fast paced, it’s fun and it has absolutely no slides at all™. It pushes the limits of what it’s sensible to demo in an hour, so if nothing else it gives you the chance to see me sailing close to the edge. Be great to see you there.

Fixing HTTP 401: Unauthorized With the Reporting Services Web Service

An enjoyable day spent finding this one.

A project I’m part of makes extensive use of the SSRS web service.  Not SSRS itself but the reportservice2010.asmx and related web services.  This tip would also apply to previous versions where the application is reportservice2005.asmx or reportservice2006.asmx and their related web services.

The issue was that although SSRS itself worked fine – you could go to a library of reports and click on one and it rendered fine – any managed code that used reportservice2010.asmx always failed, catching an HTTP error of 401: Unauthorized.  Troubleshooting was a painful exercise, here are the steps I went through to get to the root of the problem:

  1. Calling the web service directly in a web browser.  You do this simply by putting the address of the .asmx into the browser.  It will be something like http://server/_vti_bin/ReportServer/reportservice2010.asmx.  If the service is physically working you will get back the wsdl definition, looking something like this:
    As this worked fine, I was onto a good bet that the web service was alive and could be called by my user account.
  2. Logged onto a desktop as the service account which should be calling the web service in the application.  Again, this worked fine.

This pretty much said to me that the service was working and the correct account could access it.  The most logical deduction I could make was that for some reason the application wasn’t using that account to connect to the SSRS web service for some reason.  To get to the bottom of that:

  1. I checked the ASP.NET Impersonation setting of the calling web site.  This has to be set to Disabled in order for the web site to use its own service account when connecting to external services rather than trying to use the credentials of the current user.
  2. I checked the Anonymous Authentication property of the ReportServer web.  This must be set to Disabled – because Reporting Services does not support anonymous authentication (well, not without the world’s most hair-raising hack).

    And in this case, that was the problem.  Somehow the Anonymous Authentication property had been set to Enabled.  Switching it back to Disabled and running a quick IISRESET fixed the problem.