DTS-Less and Jobless Approach to Table Freshness

/***
    *   Date:         4/23/2002
    *   Author:       <mailto:mikemcw@4segway.biz>
    *   Project:      Data Refresh Project
    *   Location:     Any User Database
    *   Permissions:  PUBLIC EXECUTE
    *   
    *   Description:  Determines if a table has been updated today.
    *                 Truncates MyTable and refreshes it from
    *                 another table.
    *
    *   Restrictions:  SQL Server 2000 ONLY
    *                   property.
    *   
    ***//*
   Directions:
      Create a table, any old table will work. (remember to 
         put the name of the table @MyTable Variable)
         This will be the table to accept the extended property.

      Create a data source table.  Most likely a production table
      you would normally do a DTS job from. I use a linked production 
      server as a remote data source. Then I use the following:
       (also see code below)

      TRUNCATE TABLE MYTABLE
      SELECT * INTO MyTable FROM OPENQUERY(MyLinkedServer, 
         'select  col1, col2, col3, col4 FROM SomeTable WHERE col1 =1')

      Make sure all TODO sections are complete.

      Enjoy!   www.4segway.biz

*/SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE FUNCTION udf_NeedsUpdate(
   @ObjectType VARCHAR(255), 
   @ObjectName VARCHAR(255), 
   @Today VARCHAR(10))
RETURNS INT
AS
   /***
    *   Date:         4/23/2002
    *   Author:       <mailto:mikemcw@4segway.biz>
    *   Project:      Data Refresh
    *   Location:     User Database
    *   Permissions:  PUBLIC EXECUTE
    *   
    *   Description:  Determines if a table has been updated today.
    *   
    *   
    *   Restrictions:  Object calling this must remove and add extended
    *                   property.  SQL Server 2000 ONLY
    *   
    ***/BEGIN

   --Usage:  select dbo.udf_NeedsUpdate('table','MyTableName',getdate())
   --Function Returns 0 if no update is needed
   --                 1 if the property needs to be added
   --                 3 if the property needs to be dropped, then re-added

   DECLARE @LastUpdate   VARCHAR(10)
   DECLARE @bNeedsUpdate INT

   SET @bNeedsUpdate = 0

  --Test to see if the table has been updated using Extended Properties
   IF EXISTS(SELECT VALUE FROM ::fn_listextendedproperty('LastUpdated', 'user', 'dbo', 
            @ObjectType, @ObjectName, NULL, NULL))
      BEGIN   --The property exists

        --Test to see if the table was updated today
         SELECT @LastUpdate = CAST(VALUE AS VARCHAR) 
            FROM ::fn_listextendedproperty('LastUpdated', 'user', 'dbo', 
                  @ObjectType, @ObjectName, NULL, NULL)

         IF RTRIM(@LastUpdate) <> RTRIM(@Today)
            BEGIN
               --Nope, the table's old, tell the 'client' to delete the property and
               -- update the data
               SET @bNeedsUpdate = 3
            END  
      END
   ELSE
      BEGIN
         --No Valid Extended Property, tell the client to create the property
         SET @bNeedsUpdate = 1
      END
  RETURN @bNeedsUpdate
END
GO
GRANT EXECUTE ON udf_NeedsUpdate TO PUBLIC
GO 
ALTER PROC usp_GetLatestMyTableInfo
AS
BEGIN
   /***
    *   Date:         4/23/2002
    *   Author:       <mailto:mikemcw@4segway.biz>
    *   Project:      Data Refresh Project
    *   Location:     Any User Database
    *   Permissions:  PUBLIC EXECUTE
    *   
    *   Description:  Gets the latest data from a table if table
    *                  has not been updated today.
    *
    *   Restrictions:  SQL Server 2000 ONLY
    *                   property.
    *   
    ***/
   DECLARE @Today        VARCHAR(10)
   DECLARE @bNeedsUpdate INT
   DECLARE @MyTable      NVARCHAR(255)
   DECLARE @SQL          VARCHAR(300)
   SET NOCOUNT ON
   SET @MyTable = 'MyTable' --TODO: Change this value to the table you wish to use

   --If you run into trouble or want to test more than once daily, you can use this to remove the property.  
   --  Just replace @mytable with the a quoted table name. ie.  'mytable' 
   --EXEC sp_dropextendedproperty N'LastUpdated', N'user', N'dbo', 'table', @MyTable,NULL,NULL

   --Construct a date ie: 4-23-2002
   SET @Today = CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '-' + CAST(DAY(GETDATE()) AS VARCHAR(2)) + 
         '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))

   --See if the table has been updated today
   SELECT @bNeedsUpdate = dbo.udf_NeedsUpdate('table', @MyTable, @Today)
   --DEBUG: SELECT @bNeedsUpdate
   IF @bNeedsUpdate > 0
      BEGIN
        PRINT 'Table needs updated'
         --No the table has not been updated
         IF @bNeedsUpdate = 3 --3 Means there is a value, we need to drop the old one first
            BEGIN
               PRINT 'Property already exists... dropping property'
               EXEC sp_dropextendedproperty N'LastUpdated', N'user', N'dbo', 'table', @MyTable,NULL,NULL 
            END         

         --Update mytable from other source. (Copy the data)
         SET @SQL = 'TRUNCATE TABLE ' + @MyTable  --Trash the old
         EXEC(@SQL)

         --Get the New! TODO: put your copy statement here, these are examples
         --   INSERT INTO mytable (SELECT * FROM OtherTable)
         --OR
         --  SELECT * INTO MyTable FROM OPENQUERY(MyLinkedServer, 
         --  'select  col1, col2, col3, col4 FROM SomeTable WHERE col1 =1')

         PRINT 'Copying data from source to ' + @MyTable

         --Add the lastupdated the property
         EXEC sp_addextendedproperty N'LastUpdated', @Today, N'user', N'dbo', N'table', @MyTable
      End
   ELSE
      BEGIN
        PRINT 'Table has already been updated today, just select the data'
      END
   PRINT 'Results:'
   --TODO: Create your query for the results
   SET @SQL = 'SELECT COUNT(*) FROM ' + @MyTable
   EXEC(@SQL)

END
GO
GRANT EXECUTE ON usp_GetLatestMyTableInfo TO PUBLIC
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO