1. I Wish SQL Server Had FOREACH

    Know what would be awesome? If, instead of the usual cursor-and-local-variables ceremony, T-SQL would let you just do something like this:

    FOREACH logrow IN (SELECT logdate, logtime, event_type, user_name, result FROM LogData) FAST_FORWARD
    BEGIN
        INSERT INTO failure_log SELECT logrow.logdate, logrow.logtime, logrow.user_name WHERE logrow.result != 'SUCCESS'
    
        EXEC do_something_with_log_data @date = logrow.date, @time = logrow.time, @result = logrow.result
    END
    

    But alas, it does not. I'll have to continue to put up with a bunch of DECLAREs and 'FETCH NEXT INTO...'

    Tagged as : sql-server
  2. Fiscal Year Functions for SQL Server

    These aren't particularly sophisticated, but we had a sudden need to do some reporting based on a vendor's fiscal year, and this should get the job done. Use freely (at your own risk).

    CREATE FUNCTION dbo.first_day_of_fiscal_year(@date datetime, @fiscal_year_start_month int, @fiscal_year_start_day int)
    RETURNS date
    AS
    BEGIN
        DECLARE @fy_start datetime = CAST(DATETIMEFROMPARTS(YEAR(@date), @fiscal_year_start_month, @fiscal_year_start_day, 0, 0, 0, 0) AS date)
        IF @fy_start > @date
            SET @fy_start = DATEADD(year, -1, @fy_start)
        RETURN @fy_start
    END
    
    GO
    
    CREATE FUNCTION dbo.last_day_of_fiscal_year(@date datetime, @fiscal_year_start_month int, @fiscal_year_start_day int)
    RETURNS date
    AS
    BEGIN
        RETURN DATEADD(dy, -1, DATEADD(year, 1, dbo.first_day_of_fiscal_year ...
    Tagged as : sql-server
  3. Linked Report Drill-Through With SQL Server Reporting Services

    We've got a fairly large Reporting Services installation in our organization. We've been using it for about eight or nine years, so we've accumulated several hundred reports in that time. Without going into undue detail about the server organization, we have all the deployed reports in a directory structure underneath "/Base Reports" that makes it easy for developers to see where (i.e. project, solution) a given report came from. All of these reports are then presented in separate directory structures where all the security is defined, e.g. a directory for the sales team, purchasing team ...

    Tagged as : ssrs sql-server
  4. Quick And Easy Audit Tables

    Thu 03 October 2013
    By Dave

    I find myself often needing to have some kind of audit trail for a table in SQL Server, i.e. some way to look at before-and-after data, who changed it, when, etc. I've set up enough of these manually that they were becoming formulaic, so I figured why not put together a procedure that creates them for me and writes the necessary trigger?

    Attached is the version I'm currently using. The syntax is pretty simple. Here's an example, a real one that I just set up:

    EXEC sp_CreateDataAudit
            'dbo',
            'IV00200',
            'audit',
            'IV00200',
            NULL,
            'tr_IV00200_Audit',
            'SERLNMBR,UNITCOST,RCTSEQNM ...
    Tagged as : sql-server
  5. Quick Tip - Hybrid SELECT/INSERT/UPDATE/DELETE Scripts

    Mon 02 April 2012
    By Dave

    If you're a DBA, especially one that has to deal with an ERP system that has no concept of silly things like "transactions", then you've probably got a collection of scripts to update and fix data as necessary.

    And you probably (hopefully) run a SELECT before actually changing data, to visually confirm what will happen. You might do this by either having both a SELECT and UPDATE query in the script, or by commenting out the SELECT or UPDATE portions of the script as needed, or some other technique.

    Here's an approach I've used for a ...

    Tagged as : sql-server
  6. Writing Well-Behaved Triggers

    Thu 29 March 2012
    By Dave

    Triggers are an indispensible feature of SQL Server. Essentially stored procedures that are invoked automatically, a trigger can be used to perform logging, data validation and cleaning, synchronization, and notification, and implement referential integrity constraints that are too complicated for foreign keys, among, no doubt, plenty of other things.

    But alas, any seasoned SQL Server DBA is probably familiar with the minefield of performance-related and other issues that can very easily stem from misuse or overuse of triggers. I've frequently been victim to (and perpetrator of) such issues myself.

    So what does it take to write a good, well-behaved ...

    Tagged as : sql-server