Self Service BI within Manufacturing #SQLSaturdayEdinburgh #SQLPASS Presentation (#SQLSat)

SQL Saturday

Edinburgh First Conference CentreDuring mid April I was approached by Microsoft (UK) and asked if I would do a presentation at the Microsoft “Accelerate Your Insights” one day conference on the 1st of May 2014. Though hesitant and somewhat nervous about the prospect, as I had never spoken in public… I agreed and prepared a presentation.

The presentation was related to the recent Case Study I had the pleasure of being involved in through my employer, Jabil (@JabilCircuitInc). It would focus on how, at Jabil, we have progressed though the various backend SQL Server infrastructures offered by Microsoft over recent years and how we are using new technologies and features to enable BI delivery to our employees via production systems.

As a direct result of the presentation at Microsoft’s UK headquarters (Reading, UK) I was also invited to speak at a SQL Saturday (SQL PASS community event) being held in Edinburgh on 14th June 2014 at Edinburgh University Conference Centre.

Though hitting a bit of a technical snag with my work laptop, with less than 2 minutes to my presentation: –

Image

I quickly switched to my personal Surface Pro, which by pure chance I had decided to grab as I was leaving in the morning… had only took it so I had something light to play with between session. Just as well I did – quick switch, download of presentation from cloud storage and was good to go minus my demos.

Overall was able to buffer out the presentation, taking about several other aspects and areas we are working with SQL 2014 and BI… had several questions so am taking from that that audience engagement was good. Hopefully all that attended my presentation took away something that they did not know or at least found it useful.

Presentation can be downloaded both the SQL Saturday website and from here: –

Image

Thanks to Jen Stirrup (@JenStirrup) for the invitation to speak and arranging the great free training event; hope to be invited back in the future.

#SQLPASS #SQLSat #SQLSatEdinburgh #SQLSat281

Full case study @ http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=710000004223

Global Firm Takes an Evolutionary Leap in Data Management with Self-Service BI (Case Study)

A case study I was involved in just got published on Microsoft.com.

Over the past few years a lot of the work I have been involved in has been subject to NDAs, including this work with Microsoft (via my employer). Hence been unable to blog about my work or any of the great features of SQL Server 2014 or the Power BI suite of products.

Over the past year as part of the case study we were given advance access to SQL Server 2014 builds, Power BI and enhanced features of SharePoint. We also had assistance and regular contact with the SQL development team and Power BI guys.

As direct result of my participation I was lucky enough to enjoy a few trips to the USA, including to Seattle, Charlotte (for SQL PASS 2013 conference) and Tampa; making 2013 a very enjoyable and educational year for me 🙂 

Business intelligence (BI) information is only valuable when the right users can discover, analyze, use and share it with others—and all in a timely manner. Current technologies produce data at overwhelming rates, often faster than business users can analyze it, and the bottleneck is frequently the time that it takes to generate useful and impactful reports. At US-based supply chain management giant Jabil, as in many enterprises, data analysis has long been a time-consuming and intensive collaboration between the business groups and IT, creating customized reports whose information, by the time it’s used, is already growing stale. With its new solution built on Microsoft SQL Server 2014 and SQL Server 2014 Power View, Jabil users can create their own reports in minutes from business critical data sources using Microsoft Excel, with IT providing training and guidance—freeing up time to work on strategic projects.

Full case study @ http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=710000004223

Download PDF of Case Study

Some free Microsoft Press eBooks…

Currently available books include – Introducing SQL Server 2012, Introducing Windows Server 2012, Programming Windows 8 Apps, Introducing Windows 8 for IT Professionals, Understanding Virtualization Solutions and more… Available in PDF, Mobi (Kindle) and epub, you can check them out at: –

http://blogs.msdn.com/b/microsoft_press/archive/2012/05/04/free-ebooks-great-content-from-microsoft-press-that-won-t-cost-you-a-penny.aspx

Introducing SQL Server 2012   Programming Windows 8 Apps   Introducing Windows Server 2012

Microsoft Office – Exploring the New JavaScript API for Office

Interesting stuff…

“This article is the first in a series of in-depth looks at the JavaScript API for Office, newly introduced in Microsoft Office 2013. It presupposes that you’re familiar with apps for Office. If not…”

http://msdn.microsoft.com/en-us/magazine/jj891051.aspx

The SQL & MS Access LIKE statement

Anyone who deals with SQL and Access queries is probably familiar with the LIKE statement and the use of the standard wildcards (% or *).

Less documented however is the additional wild cards that you should be aware of if you are building queries that will be run against your database when constructing more complicated LIKE statements.

The following describes the wildcard’s that are available in Microsoft SQL.

Microsoft T-SQL LIKE statement wildcards

Wildcard character

Description

Example

%

Any string of zero or more characters.

SELECT * FROM [myTable] WHERE [title] LIKE ‘%computer%’This will find all the entries the column ‘title’ from ‘myTable’ contains the word ‘computer’ anywhere in the field.

_ (underscore)

Any single character.

SELECT

* FROM [myTable] WHERE [surname] LIKE ‘sm_th’

This will find all five-letter last names that start with ‘sm’ and end with ‘th’ (for example Smith, Smeth, etc).

[…]

Any single character within the specified range ([a-f]) or set ([abcdef]) or a numeric range [0-9].

SELECT

* FROM [myTable] WHERE [surname] LIKE ‘[C-P]arsen’

This will find all the last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, Paresen, etc.

[^]

Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

SELECT

* FROM [myTable] WHERE [surname] LIKE ‘de[^l]%’

This will get all the last names starting with de and where the following letter is not l, for example it will return Desmond, but not Delimetry.

In addition after an MS SQL LIKE statement you can include an additional definition of an escape character, which can be placed in front of a wildcard character so that it is treated as a regular character or you could the square brackets as escape characters for your query, for example: –

SELECT * FROM [discounts] WHERE [discount] LIKE ’30[%]’ 
                    or
SELECT * FROM [discounts] WHERE [discount]
   
LIKE ’30!%’ ESCAPE ‘!’

This will select any discounts that are 30%.

Reference: – http://msdn2.microsoft.com/en-us/library/ms179859.aspx


In Microsoft Access the wildcards and syntax is slightly different but similar

The following describes the wildcard’s that are available in Microsoft Access.

Microsoft Access Query LIKE statement wildcards

Wildcard character

Description

Example

*

Any string of zero or more characters.

SELECT

* FROM [myTable] WHERE [title] LIKE ‘*computer*’

This will find all the entries the column ‘title’ from ‘myTable’ contains the word ‘computer’ anywhere in the field.

?

Any single character.

SELECT * FROM [myTable] WHERE [surname] LIKE ‘sm?th’

This will find all five-letter last names that start with ‘sm’ and end with ‘th’ (for example Smith, Smeth, etc).

[…]

Any single character within the specified range ([a-f]) or set ([abcdef]).

SELECT

* FROM [myTable] WHERE [surname] LIKE ‘[C-P]arsen’ 

This will find all the last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, Paresen, etc.

[!…]

Any single character not within the specified range ([!a-f]) or set ([!abcdef]).

SELECT

* FROM [myTable] WHERE [surname] LIKE ‘de[!l]*’

This will get all the last names starting with de and where the following letter is not l, for example it will return Desmond, but not Delimetry.

#

Any single numeric character.

SELECT

* FROM [myTable] WHERE [postcode] LIKE ‘eh## #??’

This will get all the postcodes starting with EH followed by two numeric characters a space another numeric character and a further two characters, for example it would return EH54 6LY, EH55 8TZ, etc but not EH4 1NJ.

In Microsoft Access there is no ESCAPE definition so you must use square brackets, for example: –

SELECT

* FROM [discounts] WHERE [discount] LIKE ’30[%]’

This will select any discounts that are 30%.


Query Syntax in Microsoft Access on Linked SQL tables 

Now as anyone who has worked with Microsoft Access linked SQL tables will likely know, performing like queries can be complicated… this is because it can sometimes be difficult to determine where a SQL statement/query might be executed (on the SQL Server or my the local Microsoft Access engine).

As a general rule of thumb, if you are running the statement (Microsoft Access query or Access DAO Recordset) directly against the linked table, it will use the SQL Server syntax. For example: –

SELECT

* FROM tblContacts
   
WHERE Firstname LIKE ‘Pa%’ –[WILL WORK]

If however your WHERE clause is in a join query or your query runs some kind of function on some of the data. For example : –

[a bad example at that, yes I would never recommend a statement like this]
SELECT Nz(contactid, 1), * FROM tblContacts
   
WHERE Firstname LIKE ‘Pa%’ –[WILL NOT WORK]

This statement would not return the correct results and would have to be modified to the following: –

SELECT

Nz(contactid, 1), * FROM tblContacts
   
WHERE Firstname LIKE ‘Pa*’ –[WILL WORK]

A more likely scenario would be where you are joining two tables within your Microsoft Access query and you needed to perform a like statement against it. For example: –

SELECT

* FROM tblContacts
   
LEFT JOIN tblCompanies ON
    tblContacts
.CompanyID = tblCompanies.CompanyID
   
WHERE Firstname LIKE ‘Pa%’ –[WILL NOT WORK]

In MS Access this statement would not return the correct results, as the join is performed by MS Access and would have to be modified to the following: –

SELECT

* FROM tblContacts
   
LEFT JOIN tblCompanies ON
        tblContacts
.CompanyID = tblCompanies.CompanyID
   
WHERE Firstname LIKE ‘Pa*’ –[WILL WORK]

This also applies to any MS Access Queries based other queries, even if the other Query is a direct Query against a linked SQL table.


Something to remember when generating queries from code

Now you are most likely writing code that will generate your LIKE statements based on a user’s selections. This applies whether you are coding from MS Access or through ASP, ASP.NET, VB, etc… but not if you are running through parameterised objects, for example a .NET 2.0 dataset query.

If you have to code in your SQL you need to remember to change key characters, this means these reserved wildcards as well as the standard single quotes. For example (in VBA): –

SET

myRS = CurrentDB.OpenRecordset( _
   
"SELECT * FROM tblContacts WHERE FirstName LIKE ‘" & _
    Replace(someVariable,
"’","”",1,-1,1) & "%’", _
    dbOpenDynaset, dbSeeChanges)
‘[WILL NOT WORK]

Though this will work in most situations, if "someVariable" contained any of the wildcard characters, you will experience unexpected results. So you should really do something more like: –

SET

myRS = CurrentDB.OpenRecordset( _
  
"SELECT * FROM tblContacts WHERE FirstName LIKE ‘" & _
    Replace( _
     Replace( _
      Replace( _
       Replace( Replace( _
        Replace(someVariable,
"’","”",1,-1,1) _
       ,
"%","[%]",1,-1,1), "#","[#]",1,-1,1) _
      ,
"[","[[]",1,-1,1) _
     ,
"]","[]]",1,-1,1) _
    ,
"_","[_]",1,-1,1) & "%’", _
    dbOpenDynaset, dbSeeChanges)
‘[WILL WORK IF RAN AGAINST SQL LINKED TABLE]

Or if ran against MS Access table or situation described above: –

SET

myRS = CurrentDB.OpenRecordset( _
    "SELECT * FROM tblContacts WHERE FirstName LIKE ‘" & _
     Replace( _
      Replace( _
       Replace( _
        Replace( Replace( _
         Replace(someVariable,
"’","”",1,-1,1) _
        , "*","[*]",1,-1,1), "#","[#]",1,-1,1)
_
       ,
"[","[[]",1,-1,1) _
      ,
"]","[]]",1,-1,1) _
     ,
"?","[?]",1,-1,1) & "*’", _
    dbOpenDynaset, dbSeeChanges) 
‘[WILL WORK IF RAN AGAINST ACCESS TABLE/QUERY]

As with any form of coding it is usually a good idea to extract out common functions into a small library that can be used throughout your applications.

As such if you do build queries based on user input I’d recommend using a standard function to tidy up any users data and prevent possible conflicts or security flaws. The function could look something like (VBA): –

Function

fnTidyLikeQuery(ByVal myString As Object) As String

        On Error Goto ExitHere
   
‘as it does no harm to escape all characters 
    ‘(SQL or Access specific) this function can 
    ‘take care if both).

    If isNull(myString) Then
       
Goto ExitHere
   
Else
       
fnTidyLikeQuery = _
            Replace( Replace( _
             Replace( Replace( _
               Replace( Replace( _
                Replace( Replace( _
                 myString _
                ,
"’","”",1,-1,1), "#","[#]",1,-1,1) _
               ,
"*","[*]",1,-1,1), "[","[[]",1,-1,1) _
              ,
"%","[%]",1,-1,1), "_","[_]",1,-1,1) _
            
, "]","[]]",1,-1,1), "?","[?]",1,-1,1) 
   
End If

    Exit Funtion
ExitHere:
    fnTidyLikeQuery =
""
End Function

Function

fnTidyQueryString(byVal myString as Variant) as String

    On Error Goto ExitHere

    If isNull(myString) Then
       
Goto ExitHere
   
Else
        fnTidyQueryString= Replace(myString, "’","”",1,-1,1) 
   
End If

    Exit Funtion
ExitHere:
    fnTidyQueryString =
""
End Function

A sample query based on the above functions would look something like: –

SET

myRS = CurrentDB.OpenRecordset( _
    "SELECT * FROM tblContacts WHERE FirstName LIKE ‘*" & _
    fnTidyLikeQuery(someVariable) &
"*’ AND Surname = ‘" & _
    fnTidyQueryString(someOtherVariable) &
"’", _
    dbOpenDynaset, dbSeeChanges) 
‘[WILL WORK IF RAN AGAINST ACCESS TABLE/QUERY (uses * as wildcard)]


Some more samples of LIKE statements

SQL Clause

Access Equivalent

Result

Like ‘%’

Like ‘*’

All except blanks

Like ‘A%’

Like ‘A*’

Starting with ‘A’

Like ‘%A%’

Like ‘*A*’

‘A’ somewhere in the field

Like ‘[A,B,D]’
  or
Like ‘[ABD]’

Like ‘[A,B,D]’
  or
Like ‘[ABD]’

One character an ‘A’ or ‘B’ or ‘D’

Like ‘[A-C]%’

Like ‘[A-C]*’

One character ‘A’ through ‘C’ as the first character

Like ‘[A-C][A-H]%’

Like ‘[A-C][A-H]*’

‘A’ through ‘C’ as the 1st character and ‘A’ through ‘H’ as the 2nd character

Like ‘SM_TH’

Like ‘SM?TH’

Starting with ‘Sm’, ending with ‘th’, and anything for the 3rd character

Like ‘[0-9]%’

Like ‘[0-9]*’
  or
Like ‘#*’

Digit for the 1st character

Like ‘[^a-c]’

Like ‘[!a-c]’

Not in a range of letters ‘a’ through to ‘c’

Like ‘[^a-c]%’

Like ‘[!a-c]*’

Not start with a range of letters

Like ‘[^0-9]%’

Like ‘[!0-9]*’

Not start with a number

Working with Microsoft Word files – “-2147319779 Automation error Library not registered” error message when creating object.


A few weeks back I had a problem on a clients site, our application which interacts with Word files, was falling over when trying to create a Word object. This was not a problem with our coding as it has been working fine for many years and this problem was specific to a couple of workstations.

The error given was "-2147319779 Automation error Library not registered".

This error does happen if parts of Office become corrupt and sometimes if the normal.dot template file is corrupted, so tried all the usual fixes to correct these problems. None of these seemed to work and correct the fact that it could not create a Word object.

On further investigation I had found that the client had decided to try out Office 2007 on the two workstations in questions, they later decided they did not like it that much, uninstalled and re-installed Office 2003.

When Office 2007 is uninstalled, by default it leaves behind it’s Interop Assemblies. These must be removed separately, which the users had not done. Uninstalling ”Microsoft Office 2007 Primary Interop Assemblies” though the control panel -> add and remove programs, corrected the problem.

Problem: error code "-2147319779 Automation error Library not registered" when trying to work with Word files or Excel from separate application.
Cause: Installing Office 2007 and then removing it at later date replacing with previous version.
Resolution: Uninstall ”Microsoft Office 2007 Primary Interop Assemblies”.
Cause of Problem: Microsoft Office. 

Paul B
MCP