gistfile1.sql

/*=============================================================================================================
Ticket Number      : 12776
Ticket Description : Re-assign leads from Jeff Anderson to SarahMit for closed leads

NOTE: This will take about 6-8 minutes or so to run, but almost 2-3 minutes of it will be in a WAIT delay.
                     
Revision History
----------------------------------------------------------------------------------------------------------
Date        Name                 Description
----------------------------------------------------------------------------------------------------------
12/27/2011  Bruce Pinto          Script Created

=============================================================================================================*/

/*
--RESEARCH

-----------------------------------------------------------------------------
TO CREATE "SarahMit" USER IN DEV, I ran the following
-----------------------------------------------------------------------------
EXEC dbo.proc_admin_user_acct 'create',1,'brucepinto','SarahMit',NULL,'Sarah','Mitchell, BSW','','','','','KS','66027','','','1-866-614-6716','','','','sarahm@aplaceformom.com','','','','0','7/11/2000','7/11/2000',13,'0',2,0,NULL,NULL

-----------------------------------------------------------------------------
Other helpful queries
-----------------------------------------------------------------------------
SELECT * 
FROM apfm.Client C
INNER JOIN dbo.ref_Lead_Status S ON C.status_id = S.status_id
WHERE C.username = 'JeffAH'

SELECT * 
FROM dbo.Users
WHERE username = 'sarahmit'

SELECT * 
FROM apfm.Client C
INNER JOIN dbo.ref_Lead_Status S ON C.status_id = S.status_id
WHERE C.username = 'sarahmit'
AND   S.closed_flag IN (3)

SELECT *
FROM apfm.Client C
INNER JOIN dbo.ref_Lead_Status S ON C.status_id = S.status_id
INNER JOIN apfm.Task T ON C.contact_id = T.parent_id
                       AND T.owner_username = 'JeffAH'
                       AND T.is_complete = 0
WHERE C.username = 'JeffAH'

*/

--------------------------------------------------------
-- Return generic header
--------------------------------------------------------
PRINT '/*************************************'
PRINT 'DB Name     : ' + cast( serverproperty ( 'ServerName' ) AS VARCHAR ) + '.' + DB_NAME()
IF SERVERPROPERTY ( 'MachineName' ) <> SERVERPROPERTY ( 'ServerName' )
BEGIN
   PRINT 'MachineName : ' + CAST( SERVERPROPERTY ( 'MachineName' ) AS VARCHAR )
END
PRINT 'DB User     : ' + CURRENT_USER
PRINT 'System User : ' + SYSTEM_USER
PRINT 'Host        : ' + HOST_NAME()
PRINT 'Application : ' + APP_NAME()
PRINT 'Started at  : ' + CONVERT( VARCHAR(23), GETDATE(), 121 )
PRINT '*************************************/'
PRINT ''
PRINT 'RESULTS:'
PRINT ''

GO
------------------------------------------------------------------------------------
--Set up local variables
------------------------------------------------------------------------------------
--Variables for status information
DECLARE @status    VARCHAR(255)
DECLARE @row_count INT

DECLARE @batch_update      INT      --size of each update
DECLARE @iteration         INT      --when processing loop, this is a counter for each loop iteration
DECLARE @batch_update_time DATETIME --used for updating apfm.Client's last_updated field

SET @batch_update = 100
SET @batch_update_time = GETDATE()

BEGIN TRY

   ---------------------------
   BEGIN TRAN
   ---------------------------
   --Run in own transaction since all code below will be in their own transactions
   ------------------------------------------------------------------------------------
   SET @status = 'Re-assign tasks to SarahMit which are currently incomplete and assigned to JeffAH for leads assigned to JeffAH';
   ------------------------------------------------------------------------------------      
   UPDATE apfm.Task
   SET updated_by = 'jeffme'
      ,updated_on = @batch_update_time
      ,owner_username = 'carolkalm'
   FROM apfm.Client C
   WHERE C.username = 'carolkal'
      
   SET @row_count = @@ROWCOUNT ;
   EXEC dbo.sp_DBAOperationsDebugging NULL, @status, @row_count;
   
   ---------------------------
   COMMIT 
   ---------------------------
      
   --------------------------------------------------------------------
   --Set values for loop so updates can be done in batches
   --------------------------------------------------------------------
   SET @iteration = 1
   SET @row_count = 1
   SET ROWCOUNT @batch_update

   WHILE @row_count > 0 
   BEGIN
      --Run small batches within their own transaction to minimize locking/blocking on production
      ---------------------------
      BEGIN TRAN
      ---------------------------
      ------------------------------------------------------------------------------------
      SET @status = 'Iteration: ' + CAST(@iteration AS VARCHAR(10)) + ' - Update apfm.Client username to "SarahMit"';
      ------------------------------------------------------------------------------------
      UPDATE apfm.Client
      SET    username      = 'carolkalm'
            ,last_updated  = @batch_update_time
            ,updated_by    = 'jeffme'
      FROM apfm.Client C
      WHERE C.username = 'carolkal'
      
      SET @row_count = @@ROWCOUNT ;
      EXEC dbo.sp_DBAOperationsDebugging NULL, @status, @row_count;
      
      ---------------------------
      COMMIT 
      ---------------------------
      ------------------------------------------------------------------------------------
      SET @status = 'Wait for 1 second';
      ------------------------------------------------------------------------------------      
      WAITFOR DELAY '00:00:05'

      EXEC dbo.sp_DBAOperationsDebugging NULL, @status, NULL;

      SET @iteration = @iteration + 1
   END

   SET ROWCOUNT 0
   
END TRY
BEGIN CATCH
   ------------------------------------------------------------
   -- Rollback changes
   ------------------------------------------------------------
   IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

   RAISERROR('There was an error during "%s"', 16, 1, @status)

   PRINT '--------------------------------------------------'
   PRINT 'Error Message: ' + ERROR_MESSAGE() 
   PRINT 'Error Line:    ' + CAST(ERROR_LINE() AS VARCHAR(50))
   PRINT 'Error Number:  ' + CAST(ERROR_NUMBER() AS VARCHAR(50))
   PRINT '--------------------------------------------------'

END CATCH

PRINT ''
PRINT '--Completed at ' + CONVERT(VARCHAR(23),GETDATE(),121)

--------------------------------------------------------------------------------------------------------------
-- Please   COMMIT   or    ROLLBACK
--------------------------------------------------------------------------------------------------------------
IF @@TRANCOUNT > 0
BEGIN
   RAISERROR('There is an open transaction.  Please COMMIT or ROLLBACK before closing Connection', 14, 1)
END

GO