EN_to_TFS_2 - SQL script to call the powershell script based off of your Event Notifications table which has the changes.

EN_To_TFS_2

USE eventnotificationrec
GO

CREATE PROCEDURE [dbo].[EN_TFS_Checkin]
AS
/*
Purpose - using Event Notifications, determines which objects have changed (sub-database-level, for now).
Once list has been generated, repeatedly call a custom powershell script that will
	"get" and "checkout" the object from TFS if it exists, overwrite it with a current copy (scripted via SMO),
	then "checkin" the new object with a comment based on the loginname

mdb 2013/10/04 1.00  Works well (albeit a little slowly)and handled multiple days' worth of checkins.  
						Error items were due because of short-term objects that are created then dropped. 
mdb 2013/10/11 1.10  Using TFSsecurity to find valid users.  If user is valid, use their name to checkin.
mdb 2013/10/22 1.11  Changing INDEX to include table.  Makes it easier to read in history.
mdb 2013/11/08 1.12  Let's handle extended properties!  
mdb 2013/12/03 1.13  the PS code can now script out database creation.  Adding things like ALTER_DATABASE,
						 and dealing with full-database checkins.
mdb 2013/12/04 1.14 fixing author bug

To be done still: 
	* Script server-level
	* Database-level: perms and ALTER/CREATE_SCHEMA
	* Logic for Drop and Renames.  How do we want to handle that?  (TF.exe RENAME, either to new name or "DROPPED - oldname")
	* Better logic to skip events? We would conceivably need more table(s) to hold the exception types. 

sample of items not yet done
add_role_member  --must be at the database level(?)
ADD_SERVER_ROLE_MEMBER
ALTER_ASSEMBLY
ALTER_CREDENTIAL
*/


SET NOCOUNT ON 
DECLARE @cmd VARCHAR(8000), @min INT, @max INT, @EventType VARCHAR(128), @Time_of_last_run datetime, @now DATETIME
		, @loginname VARCHAR(128), @loginname_temp VARCHAR(128), @TargetObjectType VARCHAR(128)
--mdb 2013/11/08 we didn't add ObjectType as it would screw up our dupe-elimination, but I need TargetObjectType for ExtendedProperties
DECLARE @change_list TABLE (id INT IDENTITY, ServerName VARCHAR(128), EventType VARCHAR(128), LoginName VARCHAR(128)
					, DatabaseName VARCHAR(128), SchemaName VARCHAR(128), ObjectName VARCHAR(128)
					, TargetObjectName VARCHAR(128), TargetObjectType VARCHAR(128))
DECLARE @valid_tfs_logins TABLE (id INT IDENTITY, loginname VARCHAR(128))

IF OBJECT_ID('tempdb..#holding_tank_for_errors') IS NOT NULL
    DROP TABLE #holding_tank_for_errors
CREATE TABLE #holding_tank_for_errors (id INT IDENTITY, resultant VARCHAR(800))

if object_id('tempdb..#tfssecurity') is not null
    DROP TABLE #tfssecurity
CREATE TABLE #tfssecurity (id INT IDENTITY, resultant nVARCHAR(800))

--TFSSecurity.  Ensures the user is valid by querying for "Team Foundation Valid Users" (though you may have other groups).
--					If valid, uses their name for checkin.  If not, uses a default (set in PS) and adds it to the Comment
INSERT INTO #tfssecurity
        (resultant)
EXEC master..xp_cmdshell 'C:\tfs_cli\app_2013\tfssecurity /imx "Team Foundation Valid Users" /collection:http://yourtfserver:8080/tfs/DefaultCollection'

--find only users, parsing into a comparable field.
INSERT INTO @valid_tfs_logins (loginname)
SELECT 
	SUBSTRING(resultant,  CHARINDEX(']',resultant)+2, CASE CHARINDEX('(', resultant) WHEN 0 THEN 200 ELSE CHARINDEX('(', resultant) - CHARINDEX(']',resultant)-3 end)
FROM #tfssecurity 
WHERE resultant LIKE '%[[]U]%'


--go back 1 day, the first time you run.
IF (SELECT COUNT(*) FROM eventnotificationrec.dbo.TFS_Last_Time_Run) = 0
BEGIN 
INSERT INTO eventnotificationrec.dbo.TFS_Last_Time_Run VALUES (GETDATE()-1)
END 

--find the last time we ran, so we're only getting in that range
SET @time_of_last_run = (select MAX(last_time_run)
FROM eventnotificationrec.dbo.TFS_Last_Time_Run)

--located here, as soon as possible before the query to get the list of events.  We could change it to use the ID field instead.
SELECT @now = GETDATE()

SELECT @time_of_last_run AS last_run_time, @now AS now_run_time

-- can simplify this further.
-- Multiple changes to the same object don't need multiple checkins.  Indexes, for instance, only need 1 checkin of the table.
-- will probably need a case statement since (for indexes, in this case) each would be a different object w/same targetobject
-- as of 1.1, each index change requires a separate checkin.
INSERT INTO @change_list
SELECT ServerName, EventType, LoginName, DatabaseName, SchemaName, ObjectName, TargetObjectName, TargetObjectType
	FROM eventnotificationrec.dbo.ENAudit_View
WHERE EventType IN 
(
--these lines are blocked out by the groupings below, use them to make it easier to read this.
 'ALTER_FUNCTION','ALTER_PROCEDURE','ALTER_TABLE','ALTER_VIEW','CREATE_FUNCTION', 
 'CREATE_PARTITION_FUNCTION', 'CREATE_PARTITION_SCHEME', 'CREATE_PROCEDURE', 'CREATE_TABLE', 'CREATE_VIEW'
 ,'ALTER_TABLE'
 ,'Create_Type', 'Alter_Type'
 ,'ALTER_TRIGGER', 'ALTER_INDEX', 'ALTER_SPATIAL_INDEX'
 ,'CREATE_TRIGGER', 'CREATE_INDEX', 'CREATE_SPATIAL_INDEX'
 ,'ALTER_EXTENDED_PROPERTY','CREATE_EXTENDED_PROPERTY','DROP_EXTENDED_PROPERTY'
 ,'ALTER_AUTHORIZATION_DATABASE','ALTER_DATABASE'
)
AND NOT (EventType = 'ALTER_INDEX' AND commandtext LIKE '%reorg%')  --ignore reorgs
AND NOT (EventType = 'ALTER_INDEX' AND commandtext LIKE '%REBUILD%' AND commandtext NOT LIKE '%fillfactor%')
AND insert_datetime > @time_of_last_run
AND insert_datetime <= @now
GROUP BY ServerName, EventType, LoginName, DatabaseName, SchemaName, ObjectName, TargetObjectName, TargetObjectType
ORDER BY MAX(insert_datetime)

select count(*) from @change_list AS number_of_items_to_do

--now that we have a list to process, invoke the powershell script for each.
--The powershell script does the work; this just tells it what's changed and needs to be done.
SELECT @min = MIN(id), @max = MAX(id) FROM @change_list
WHILE @min <= @max
BEGIN 
	SET @EventType = NULL 
	SET @loginname_temp = NULL 
	SET @loginname = NULL
	SET @TargetObjectType = NULL

	--Using TFSSecurity block above, change the loginname to be the actual loginname if they're a valid user
	SELECT @loginname_temp = loginname FROM @change_list WHERE id = @min
	IF EXISTS (SELECT * FROM @valid_tfs_logins WHERE loginname = @loginname_temp)
		BEGIN
			SET @loginname = ' -author "' + @loginname_temp + '" -comment "' 
		END
		ELSE
		BEGIN
			SET @loginname = ' -comment "' + ISNULL(@Loginname_Temp,'blank.username') + '  -----  ' 
		END 

	--clear the error trap - we have 2, one transient and one permanent
	TRUNCATE TABLE #holding_tank_for_errors

	--easier to select this once; makes below code more readable.  
	SELECT @EventType = EventType, @TargetObjectType = TargetObjectType FROM @change_list WHERE id = @min

	--basic call, comment has loginname & eventtype
	IF @EventType IN ('ALTER_FUNCTION','ALTER_PROCEDURE','ALTER_VIEW','CREATE_FUNCTION', 
	 'CREATE_PARTITION_FUNCTION', 'CREATE_PARTITION_SCHEME', 'CREATE_PROCEDURE', 'CREATE_TABLE', 'CREATE_VIEW')
	or (@EventType IN ('ALTER_EXTENDED_PROPERTY','CREATE_EXTENDED_PROPERTY','DROP_EXTENDED_PROPERTY') AND @TargetObjectType = '')
	BEGIN
		SELECT @cmd = 'powershell -file c:\tfs_cli\sql_script_to_tfs.ps1 -server ' + servername 
		+ ' -database "' + databasename + '" -schematoscript "' + SchemaName + '" -objecttoscript "' + ObjectName + '"'
		+ @LoginName + EventType + ' ' + ObjectName 
		+ CASE 
			WHEN TargetObjectName = '' THEN ''
			WHEN TargetObjectName IS NOT NULL THEN ' on ' + TargetObjectName 
			ELSE '' END +  '"' 
		FROM @change_List WHERE id = @min
	END

	--when we want to include the actual change in the comment, to get specifics like ALTER TABLE ADD COLUMN
	ELSE 
	IF @EventType IN ('ALTER_TABLE')
	BEGIN
		SELECT @cmd = 'powershell -file c:\tfs_cli\sql_script_to_tfs.ps1 -server ' + servername 
		+ ' -database "' + databasename + '" -schematoscript "' + SchemaName + '"  -objecttoscript "' + ObjectName + '"'
		+ @LoginName + EventType + ' ' + ObjectName  + '"' 
		FROM @change_List WHERE id = @min
	END

	--objects without schemas, so searching the SMO to match SchemaName won't find anything
	ELSE 
	IF @EventType IN ('CREATE_TYPE', 'ALTER_TYPE') 
	BEGIN
		SELECT @cmd = 'powershell -file c:\tfs_cli\sql_script_to_tfs.ps1 -server ' + servername 
		+ ' -database ' + databasename + ' -objecttoscript ' + ObjectName 
		+ @LoginName + EventType + ' ' + ObjectName  + '"' 
		FROM @change_List WHERE id = @min --when scripted, they have no schema
	END

	--objects that are actually "part" of another object, and thus use the TargetObject to determine what to script out.
	ELSE 
	IF @EventType IN ('ALTER_TRIGGER', 'ALTER_INDEX', 'ALTER_SPATIAL_INDEX', 'CREATE_TRIGGER', 'CREATE_INDEX', 'CREATE_SPATIAL_INDEX')
	or (@EventType IN ('ALTER_EXTENDED_PROPERTY','CREATE_EXTENDED_PROPERTY','DROP_EXTENDED_PROPERTY') AND @TargetObjectType <> '')
	BEGIN
		SELECT @cmd = 'powershell -file c:\tfs_cli\sql_script_to_tfs.ps1 -server ' + servername 
		+ ' -database "' + databasename + '" -schematoscript "' + SchemaName + '" -objecttoscript "' + TargetObjectName  + '"'
		+ @Loginname + EventType + ' ' + ISNULL(ObjectName,'') + '.' + TargetObjectName  + '"' 
		FROM @change_List WHERE id = @min
	END 

	--database-level objects, such as CREATE or changes to a database
	ELSE 
	IF @EventType IN ('ALTER_AUTHORIZATION_DATABASE','ALTER_DATABASE','CREATE_DATABASE')
	BEGIN
		SELECT @cmd = 'powershell -file c:\tfs_cli\sql_script_to_tfs.ps1 -server ' + servername 
		+ ' -database "' + databasename + '"'
		+ @LoginName + EventType + ' ' + databasename  + '"' 
		FROM @change_List WHERE id = @min
	END 
	--run the powershell command, saving errors to a temp table
	RAISERROR (@cmd, 0, 1) WITH NOWAIT
	INSERT INTO #holding_tank_for_errors
		EXEC master..xp_cmdshell @cmd

	--if any errors during execution, save to a permanent table to look at later.
	IF EXISTS 
	(
	SELECT *
	FROM #holding_tank_for_errors 
	WHERE resultant LIKE '%sql_script_to_tfs.ps1%'
	)
	BEGIN 
INSERT INTO eventnotificationrec.dbo.TFS_Checkin_Errors (time_job_run, original_id, resultant)
		SELECT GETDATE(), id, resultant
		FROM #holding_tank_for_errors ORDER BY id
	END 
	
	SET @min = @min + 1
END 

--and now that we're successful, add a record so we know when we ran. 
INSERT INTO eventnotificationrec.dbo.TFS_Last_Time_Run (last_time_run) VALUES (@now)



GO