WHERE did this data come from?

TRMNDC — Sun, 20 Dec 2009 16:23:20 +0000

Just the facts, ma’am

This is a late entry for T-SQL Tuesday #001 (Date/Time Tricks).

One of the consistencies over my several decades in IT has been how most of us IT professionals are very good at retrieving bits of arcane data as required to demonstrate a point. What we (myself included) seem not to be so good at is keeping track of where and when we got the piece of information.

In countless conversations with entrenched developers I have had to prove, over and over to them, details surrounding data from a database. Testers would run a series of tests, expecting X in the database and the results either were not there or the data in the database was Y. The most common response over the decades has been “You are not looking at the correct database.”

More than a decade ago, after one such disagreement, I decided to write a stored procedure that I could call from anywhere and could demonstrate precisely the server and database from which the data was retrieved as well as the time. The result was one of the most useful procedures I have ever written, sp_Stamp. I typically call sp_Stamp from most procs I write that are designed to produce a result set for possible distribution so the ambiguity of where and when I gathered the data presented are clarified at the outset.

The premise is quite simple; provide the date, time, server and database as part of the result set. End of argument.

Below is what the result looks like:

sp_Stamp was run on Saturday, Dec 5 2009 1:40:09:007PM.

This SCRIPT is being run against SERVER.DATABASE: SQL2008TEST.OperationsManager

Like every script I write, it automatically performs it’s own UNIT TEST after the proc is created. It also contains my typical threats of DEATH and/or DISMEMBERMENT to junior DBAs who decide to mess with my code. This is NOT rocket science but it will sure save some arguments.

Here’s the SQL:

/*

sp_Stamp.sql

*/

USE MASTER -- <------- USE master for this SYSTEM LEVEL Stored Procedure.-----<<

GO

IF EXISTS (SELECT * FROM sysobjects

            WHERE id = OBJECT_ID('dbo.sp_Stamp')

              AND sysstat &amp; 0xf = 4)

     DROP PROCEDURE dbo.sp_Stamp

GO

/*

********************************************************************************

** USE master for this SYSTEM LEVEL Stored Procedure.  This allows this SP to **

** be invoked from any Database on this Server.                               **

********************************************************************************

*/

/*

********************************************************************************

** The step above ("if exists"), is automatically generated by SQL Server -

** the procedure is droppped before recreation.  

********************************************************************************

*/

/*

********************************************************************************

**

** File:            sp_Stamp.sql

**

** Author:          Michael G. Nelson

**

** Description:     This SP adds Timestamp/SERVER:DATABASE output to the

**                  SERVER OUTPUT.  The SP can be called by a script, a FRONT

**                  END application or from a query in an ISQL or ISQLw

**                  window.

**

** Called By:       Any number of other SPs and SQL Scripts

**

** SPs Called :     None

**

** Input

**   Parameters:    None

**

** Creation Date:   01/22/99

**

** Modifications:

**   (Date,    By,  Description)

**   03/02/99  MGN  Added Comments.

********************************************************************************

*/

/*

********************************************************************************

**                          DECLARE Input Parameters                          **

********************************************************************************

*/

CREATE PROCEDURE sp_Stamp

/*

********************************************************************************

**                  DECLARE Stored Procedure Internal Variables               **

********************************************************************************

*/

AS

DECLARE

@ReturnMessage                     VARCHAR(255) -- Used for display strings

/*

********************************************************************************

**                             Procedure Body                                 **

********************************************************************************

*/

/*

********************************************************************************

**                   Record the DATE/TIME this script runs                    **

**                 and the SERVER.DATABASE on which it was run                **

********************************************************************************

*/

BEGIN

SET NOCOUNT ON

PRINT ' '

SELECT @ReturnMessage =

        (SELECT 'sp_Stamp was run on '

        + CONVERT(VARCHAR(19),DATENAME(dw,GETDATE()))

        + ', '

        + CONVERT(VARCHAR(40),GETDATE(),109)

        + '.'

        )

PRINT @ReturnMessage -- PRINTs when script was run

SELECT @ReturnMessage =

       (SELECT 'This SCRIPT is being run against SERVER.DATABASE: '

        + @@servername + '.' + DB_NAME()

        )

PRINT @ReturnMessage -- PRINTs the SERVER.DATABASE against which it was run

PRINT ' '

SET NOCOUNT OFF

/*

********************************************************************************

**                                Main Section                                **

********************************************************************************

*/

END

/*

********************************************************************************

**                                 Make Grants                                **

**        ---->       Make SURE the GRANTs are APPROPRIATE!       <----       **

********************************************************************************

*/

GO                                          

-- This GO Statement Creates the SP

PRINT 'Make GRANTs'

GO

GRANT  EXECUTE  ON dbo.sp_Stamp  TO PUBLIC

GO

PRINT 'GRANTs complete!'

GO

/*

********************************************************************************

**                                 SP UNIT Test                               **

********************************************************************************

*/

IF EXISTS (SELECT * FROM sysobjects

            WHERE id = OBJECT_ID('dbo.sp_Stamp')

              AND sysstat &amp; 0xf = 4)

     BEGIN

      PRINT ' '

      PRINT ' '

      PRINT '################################################################################'

      PRINT '############################### BEGIN UNIT Test  ###############################'

      PRINT '################################################################################'

      PRINT ' '

      PRINT ' '

      PRINT 'The following command will be EXECuted:'

      PRINT 'EXEC sp_Stamp'

       EXEC sp_Stamp

      PRINT ' '

      PRINT '################################################################################'

      PRINT '###############################   END UNIT Test  ###############################'

      PRINT '################################################################################'

     RETURN

     END

PRINT '################################################################################'

PRINT '################################   ERROR!!!  ###################################'

PRINT '################################   ERROR!!!  ###################################'

PRINT '################################   ERROR!!!  ###################################'

PRINT '################################   ERROR!!!  #############################'

PRINT '################################   ERROR!!!  ###################################'

PRINT '################################################################################'

PRINT ' '

PRINT 'Stored Procedure sp_Stamp DOES NOT EXIST IN THIS DATABASE!!!!'

PRINT ' '

PRINT 'UNIT Test ABORTED!!!!!!!'

PRINT ' '

PRINT '################################################################################'

PRINT '################################   ERROR!!!  ###################################'

PRINT '################################################################################'

GO

I Wonder

TRMNDC — Sun, 20 Dec 2009 15:28:52 +0000

Is Live Writer actually still working?

Hello world!

admin — Sun, 13 Dec 2009 16:54:17 +0000

Welcome to WordPress. This is your first post. Edit or delete it, then start blogging!

theReal MikeNelson

WHERE did this data come from?

Just the facts, ma’am

This is a late entry for T-SQL Tuesday #001 (Date/Time Tricks).

I Wonder

Hello world!