ApexSQL Diff review

My place of work recently rolled out ApexSQL to its development team. Having not used it before, but being familiar with other schema comparison programs I must say I am quite the fan.

Based on the screenshot below which compares a DEV and a PROD version of the same database, you can see it is very easy to see which objects have changed and which objects are new/unique in both databases. I particularly like the way it divides out all of this different objects, which you could choose to exclude from the comparison if you wished.

Another feature I like, which is very common amongst schema comparison tools is the script window at the bottom of the program, which highlights any changes and scrolls both versions of the object in synchronicity making it nice an easy to review all the changes to an object.

One concern I do have, is when scripting changes to be executed at a later date, ApexSQL seems quite indiscriminate in choosing to alter an object or dropping and re-creating the object, which could obviously cause some problems down the line with potential data loss.

But overall, for a company still in the infancy of source/version control this program is a welcome relief when it comes to deploying (only) changes to an existing database, and no ApexSQL have not paid me for any of this review.

For more information please visit: https://www.apexsql.com/

Advertisements

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.

Code:

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

   2:          ,HistoricalParameterValues 

   3: FROM     SUBSCRIPTIONS

Screenshot:

image

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.

Script:

   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            t2.name='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' ;      

  23:  

  24: EXEC msdb.dbo.sp_send_dbmail 

  25: @profile_name = 'AW_Admin',

  26: @recipients = 'SQLServerNotification@AdventureWorks.co.uk',

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

  28: @body_format = 'HTML',

  29: @Body = @BodyHtml,

  30: @importance = 'high'

Result:

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.

Example:

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

   2: CREATE ROLE db_executor

   3:  

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

   5: GRANT EXECUTE TO db_executor

   6:  

   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

Result:

More on best practices:

https://msdn.microsoft.com/en-us/library/bb669058(v=vs.110).aspx

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.

image

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

image

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

image

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

image

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.

image

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.

image

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”

image

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.

sql-experts

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.