Tag Archives: SQL

Log Parser Lizard

Posted on by

Recently I’ve been working a bit with Log Parser Lizard and it’s been very helpful.

You can take log files, import them, pick the expected format, and then query them in a SQL style manner.

So for example, I took a bunch of IIS log files and was able to easily run this query against it:

SELECT *
FROM  'C:\Users\Chris\Downloads\www-logs\*.log'
where sc-status = 404   and cs-uri-stem <> '/AzureFileHandler.ashx' 
and cs-uri-stem <> '/js/jquery.1.11.1.min.js'

 

Pretty cool

image

Advertisement

Interesting problem with SQL Server, ARITHABORT, filtered indexes, calculated columns, and compatibility mode of 80

Posted on by

I recently ran into a problem in a legacy application.  Someone had applied a unique index on a column in a SQL Server database table.  Shortly after, we started seeing some errors in our logs relating to that table:

UPDATE failed because the following SET options have incorrect settings: ‘ARITHABORT’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

After a good amount of investigation, I discovered that it was related to the fact that the unique index as a filtered index (it needed to be a filtered unique index because the business rules allowed for duplicate null values, but not duplicate non-null values) and it turns out that if you use a filtered index in SQL Server while running with Compatibility Mode = 80, then any updates to that table need to specify SET ARITHABORT ON, or it will fail with this error.

Well, this is a legacy application, which can’t have the compatibilty mode changed for numerous reasons, and there are many places where we would need to set ARITHABORT, so that wasn’t really a good option either.

First I tried a work around using a computed column that would never be null, and then I applied a unique index to that column, but the same ARITHABORT problem exists with computed columns as it does with filtered indexes.

After a few other attempts at a workround, my friend Phil @ XDev Group mentioned a method he used to solve a similar problem.  I created a trigger that would create a poor man’s calculated column, without the imposed limitations of using a real calculated column in compat80 mode.

CREATE TRIGGER [dbo].UniqueJobNumberGenerator ON [dbo].jobdata
    FOR INSERT, UPDATE
AS
    BEGIN
        SET NOCOUNT ON;

        UPDATE  dbo.jobdata
        SET     UniqueJobNumber = ( CASE WHEN [num] IS NULL
                                         THEN CONVERT([varchar], [id], ( 0 ))
                                              + '00000000'
                                         ELSE [num]
                                    END )
        WHERE   id IN ( SELECT   id
                               FROM     INSERTED )

    END

Then I applied a unique index on that column (without a filter) and … success!

List of years in SQL Server

Posted on by

Here is a CTE from my friend Phil that returns a list of years in an efficient manner. Good reference.

/*Return a list of years since 2009*/

WITH    yearlist
      AS ( SELECT   2009 AS year
           UNION ALL
           SELECT   yl.year + 1 AS year
           FROM     yearlist yl
           WHERE    yl.year + 1 <= YEAR(GETDATE())
         )
SELECT  year
FROM    yearlist
ORDER BY year DESC;