Identifying empty XML Values

Something I was tasked with recently was identifying rows in a table which contained empty xml strings.  Because the column is of XML data types I couldn’t simple check WHERE column = NULL or ‘’ so instead I used the length function to determine the length of data in my XML attribute, an example of which can be found below.


   1: SELECT   LEN(HistoricalParameterValues.value('(/ReportParameters)[1]','varchar(max)')) as L

   2:          ,HistoricalParameterValues 




As you can see by using the the xml.value method against my report parameter attribute and wrapping this in the LEN() function allows me to then filter my records to those where the length equals zero.

Job history emails

One of my crime fighting cohorts had a request to provide some additional details, than that you can normally get via SSMS when a job fails. So the below script is what we ended up throwing together.  It makes use of the msdb database, but allows us to pick and choose which columns in the sysjobs and sysjobhistory tables had value to us.


   1: DECLARE    @BodyHtml NVARCHAR(MAX);

   2: SET        @BodyHtml =    N'Hello, <br /><br />

   3:                           An error has occured attempting to run the Emails orders job<br />'+

   4:                           N'For the last 5 failed executions of this SQL Agent job, please see the table below:<br />'+

   5:                           N'<table border="1">' +

   6:                                           N'<tr><th>Step ID</th><th>Step Name</th><th>Message</th><th>DateRun</th></tr>' +

   7:                                           CAST ( (  SELECT TOP 5     td = t1.step_id,         '',

   8:                                                                      td = t1.step_name,       '',

   9:                                                                      td = t1.[message],       '',

  10:                                                                      td = t1.run_date

  11:                                                     FROM             msdb.dbo.sysjobhistory t1

  12:                                                     LEFT JOIN        msdb.dbo.sysjobs t2

  13:                                                                      on t1.job_id = t2.job_id

  14:                                                     WHERE  'Email orders'                                

  15:                                                     AND              t1.run_status ='0'

  16:                                                     AND              t1.step_id <> '0'

  17:                                                     ORDER BY         t1.run_date ,t1.run_time desc

  18:                                                     FOR XML PATH('tr'), TYPE 

  19:                                                     ) AS NVARCHAR(MAX) ) +

  20:                           N'</table><br /><br />'+

  21:                           N'<br />Thank you,<br />

  22:                           Adventure Works IT' ;      


  24: EXEC msdb.dbo.sp_send_dbmail 

  25: @profile_name = 'AW_Admin',

  26: @recipients = '',

  27: @subject = 'Emai orders job failed - live',

  28: @body_format = 'HTML',

  29: @Body = @BodyHtml,

  30: @importance = 'high'


Granting permissions to execute stored procedures

Something I came across recently, was a client who was granting db_owner permissions to a SQL login which was used as part of a web application. The reasoning for this was that they did not want to manually assign execute permissions to each and every stored procedure they create, and so db_owner gave them what they want with the least amount of effort.

My preferred solution is to create a role myself and assign the execute permission, then assign the new role to my user. This saves me from having to assign execute permissions on a stored proc by stored proc basis, and also means my user doesn’t have additional privileges which they do not require.


   1: --Create a new role for executing stored procedures

   2: CREATE ROLE db_executor


   4: --Grant stored procedure execute rights to the role

   5: GRANT EXECUTE TO db_executor


   7: --Assign role to our new login

   8: EXEC sp_addrolemember 'db_executor','SubjectAccessLog_App'    --allows execution of stored procedures

   9: EXEC sp_addrolemember 'db_datareader','SubjectAccessLog_App'  --allows reading of the data in the security DB

  10: EXEC sp_addrolemember 'db_datawriter','SubjectAccessLog_App'  --allows insert of data into tables in the security DB


More on best practices:

T-SQL date time formatting in English (UK)

We’ve all had fun with dates in SQL server, especially if you’ve worked on a brownfield project where someone else has done the installation for SQL server and hasn’t realised that when selecting “English” for the Language option, this actually means “American English” as opposed to “English English”.

Now of course America is pretty much the only country in the world which uses the MM-DD-YYYY format, while we Brits much prefer DD-MM-YYYY (small, bigger, biggest). So when it comes to universal date formats, one thing I didn’t realise is that our chums in America thought that we would still want the day to appear before the month.

As you’ll see in my screenshot below, if you set your language to British over America, and then cast a YYYY-MM-DD as a datetime, it’ll get converted to YYYY-DD-MM.

My screenshot kind of gives away the solution though, as you can see this doesn’t affect datetime2 or date types.

If anyone from America can enlighten me on why datetime handles dates in such a crazy format i’d love to know, as I’ve never seen it used anywhere in Europe.

Power BI Maps Handling Duplicate City Names

The Bing map engine behind the map visualisation in Power BI is very intuitive allowing users to provide textual data such as City or Country or Postcode to map metrics, instead of just latitude and longitude as most other applications do. However one thing which is not immediately obvious is how to get around the issue of duplicate City/Town names.

In this blog I will explain how to map your metrics when your data source contains duplicate cities/towns.

To start with we have a simple data set with quarterly sales for 6 different cities based in 5 different states which is being loaded from a CSV into Power BI. Straight away you can see that we only have 2 distinct city names.

image  image

As soon as we try to map the sales data by city, we get an obvious problem all of the Bristol sales are being assigned to Bristol, England, while the Georgetown sales are appearing in Guyana.


Adding state to the Location field does nothing to help the problem as Power BI only reads a single input in the Location field.


So the solution is to create a new column containing both City and State data. To do this you need to complete the following steps:

1. Click “Edit Queries”
2. Select the data source in question.
3. Select the two or more columns which contain the data we want to merge eg: City and State 
    -If additional geographical data is available such as Country then this can be included in the merged column.
4. Navigate to the “Add Columns” menu and select “Merge Columns”
5. Choose the separator value and name the new column


For simplicity I have just called this “Merged” and separated the values using only a space.


Once the new column has been created it can be dropped into the Location field of the map visualization.

As you can see from the screenshot below I now have 6 data points, showing all three variations of Bristol, and all three variations of Georgetown.


One final tip, is to ensure you have set the Data Category value for the column in question.  In this case I have set the Data Category to City to help Bing identify the type of data I believe I am providing it.


The only problem with this, is if you set the Data Category value incorrectly no data will be displayed as shown in this final screenshot where I have changed the Data Category to “Continent”


SQL Funny

Below is something which really made me chuckle when browsing a Business Intelligence providers website.

Absolutely no malice is meant here, hence me not naming the website in question, but it just made me chuckle to see them offering SQL expertise while showing a screenshot of a VB or C# (not my area of expertise) script executing a SELECT * FROM SQL statement.

Now we all know that rarely, if ever should a SQL expert be writing SELECT * queries, especially if that query is being executed as part of a parent application which will surely be expecting a consistent result set from SQL.


So remember folks, when querying SQL server, it’s best practice to provide actual column names, even if you really do need every column, if only to prevent your application / reports from falling over the moment a new column is added, or existing columns are re-ordered.

Concatenating T-SQL data using XML

Recently I was tasked with concatenating data from multiple rows in a table, to form one single row containing comma separated values for each different ID in a dataset.

Below is a screenshot of the original data I had to work with, along with an example of the code I used, followed by a screenshot of the result set.

As you can see,  my plan involved using a CTE to get a distinct list of ID’s and Values and then for each ID, I used the FOR XML Path Clause to return one record containing all Values in the CTE prefixed with a comma and a space. I then used the stuff function to replace first character in the results with an empty character therefore removing the prefixed comma at the start of the string.



Source code:

   1: WITH CTE AS (

   2:             SELECT     ID, Value

   3:             FROM       #TMP

   4:             )



   7:             ID

   8:             ,STUFF(

   9:                     (

  10:                       SELECT      ', ' + Value

  11:                       FROM        CTE t1

  12:                       WHERE =

  13:                       FOR XML PATH('')

  14:                     ) ,1,1,''

  15:                    ) AS Txt   

  16: FROM        CTE t2

  17: GROUP BY    ID