WHERE did this data come from?
- December 20th, 2009
- Write comment
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 & 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 & 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