Thursday, February 18, 2016

How to use wildcards with the SQL Server LIKE operator

I answered a question today in one of my groups... a whole lot of LIKE operator and wildcard conditions. Great subject matter! This is just a few different tips for using SQL's LIKE operator with wildcards.  Be sure to look at the data you're loading into the variable, and read my comments for each of the sample statements.  

   /*   table variable */
   DECLARE @DBVersions TABLE (
       DatabaseVersion VARCHAR(25) NOT NULL,
       VersionDate DATETIME NOT NULL,
       ModifiedDate DATETIME NOT NULL )

   /*  load some test data  */
   INSERT @DBVersions (DatabaseVersion,VersionDate,ModifiedDate)
   VALUES ('ssAsxb567','2016-01-14','2015-03-14'),

   /* just look at the raw data  */
   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions

     7 different conditions with wildcard
   #1 return anything that contains OR begins with the letter 'A'
   #2 return anything beginning with 'A', but does not contain it elsewhere
   #3 return anything that contains the letter 'A', but does not begin with it
   #4 return anything LIKE the given DatabaseVersion, but is in ALL CAPS
   #5 return anything that LIKE the given DatabaseVersion, regardless of CASE
   #6 return anything that has 3 digits the given DatabaseVersion
   #7/8 return anything that has the % literally within the DatabaseVersion      */

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE '%A%'

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE 'A%'

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE '%A%'
   AND DatabaseVersion NOT LIKE 'A%'

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE 'ABX%' COLLATE Latin1_General_CS_AS; --only UPPERCASE

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE 'ABX%' -- you'll see we get them both back on this one

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE 'ABX___' -- now only one
   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE (DatabaseVersion) LIKE '%' -- why do we get them all?!

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE (DatabaseVersion) LIKE '%[%]%' -- use the brackets as an escape character
                                        -- first and last %s are the WILDCARD
                                        -- the middle one is treated as a literal
                                        -- good stuff  

That's all I've got for now, but I may add more later. Take a look at this for more information, and more examples:

Wednesday, February 17, 2016

"The syspolicy_purge_history SQL Server Agent job may fail in SQL Server 2008"

... and in SQL Server 2012. Doubt I've found a v2012 bug, but I've definitely found the same failure there. My customer's syspolicy_purge_history job failed last night with this error:

A job step received an error at line 1 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\CLUSNOD3\VISION).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Failed to connect to server .
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I'll bet the server wasn't found because 'CLUSNOD3\VISION' does not exist. This occurred on SQLPROD\VISION, where 'CLUSNOD3' is the name of one of the cluster nodes. See Step #6 in the 1st method of correction suggested by MSFT in this Kb:

Easy fix. Just "replace the computer node name by using the virtual server name for the cluster instance".  Ok.  But how do you know for sure what the virtual server name is? Run this on the instance where the failure occurred:

     SELECT SERVERPROPERTY('MachineName')     

The 'MachineName' property returns the computer name on which the SQL Server instance is running. If it is a clustered instance, it returns the name of the virtual server.  My result is 'SQLPROD'. 

Exactly as outlined in the 955726 Kb, I edited step #3 of the sysolicy_purge_history job, replacing 'CLUSNODE3' with 'SQLPROD', like this:

   (Get-Item SQLSERVER:\SQLPolicy\SQLPROD\VISION).EraseSystemHealthPhantomRecords()

The job now completes without error.

Not sure how the incorrect servername got in there, but that's another issue entirely... The syspolicy_purge_history Agent job was introduced with v2008 with Policy Based Management.  Take a look at these for details regarding syspolicy_purge_history, and Policy Based Management in general: