The Baking DBA - select * from flour: October 2013

I'll be presenting this at SQLSaturday 255 in Dallas, on November 2nd 2013.

Two note to begin, because this is a lengthy post: there are two parts of this, and two scripts.
* The Powershell script will script out objects and check them into TFS. No prereqs other than TFS and powershell. You could just run this on servers daily and it would give you a basic "here's what the code is like". Basic but functional.
* The SQL script will look for new changes using an existing Event Notifications setup, then call the PoSH script for those objects. More work, but IMHO more reward; you get real-time monitoring, information such as the LoginName of the user who changed it, and better comments.

So, I've been trying to figure out better ways to present code to our developers, as well as giving our group the ability to look back at code changes in production. Our devs have Source Control for code that's deployed, but our Operations team has to look at objects that have been changed over time, be it via indexes, tweaks made in production for emergencies, permission changes, etc.

There is one 3rd party app I know of that does this, but we had issues getting it to work.
So I decided to write my own.

And for me it seemed a natural to tie it in with Event Notifications. My original idea was to just use EN to check the changes in, but that's not always feasible - if a table gets a new column, how do you represent that? So I moved to a different model - EN detects the changes, and then a separate SP kicks off a powershell script that deals with TFS, by scripting out a new copy of the affected object(s) and checking it in. And because we're using EN, we get information like the username of the person who changed it, what exactly they did, etc.

While this method can be used with any source control server, our company uses TFS to track changes.
However, I was trying to set it up on an existing server and didn't want a "heavy" client on that server. Fortunately, I found on stack overflow a post (http://stackoverflow.com/questions/5503858/how-to-get-tf-exe-tfs-command-line-client, answer by ijprest) describing how to do this. Alternatively, you can install Team Explorer or Visual Studio with Team Explorer or use the Java TFS client. The way licensing works, as long as you have a CAL, they don't care what client you use.

The next step was the scripting-to-disk. I settled on using Phil Factor's code which uses powershell & SMO, then modified it for my use, including the ability to feed a server/database and have it script out the entire database and check it into TFS. All the credit goes to him, all the blame goes to me. Right now it's scripting "under" the database-level - you get all the changes within the database, but not changes ON the database, like changes to DB settings, files, and logins/permissions. Version 1.1 will script at the server level and below.

So, at this point we have TFS, a SMO object scripter, next up is code that runs, looks at our Event Notification table, and call the Powershell script. Our TFS does not allow every user to CHECKIN code, so rather than risk it failing I add their name to the COMMENT instead. We are calling the powershell script via XP_CMDSHELL; there's probably a better way and I'll look at it for a future version, once PASS 2013 is over.

Overall, it seems to work well. I need to work on some sort of dynamic exclusion list and a couple other tweaks, but overall I'm very happy with it. As are our auditors.

Now, the install, which is intimidating. But think of it the other way. Create 3 directories, copy over a total of 11 files, run one script, create two tables and add one job. The hardest part, personally, was getting the TFS workspace working.

First, create 3 folders:

C:\TFS_CLI

C:\TFS_CLI\App

C:\TFS_CLI\TF_Workspace

Next, copy the following 10 files from a valid TFS install into the \App folder.

(Alternatively, install Team Explorer, which includes TF.EXE; you’ll need to change the commands as I’ve hardcoded the location of TF.EXE)

Microsoft.TeamFoundation.Build.Controls.dll

Microsoft.TeamFoundation.Client.dll

Microsoft.TeamFoundation.Common.dll

Microsoft.TeamFoundation.Common.Library.dll

Microsoft.TeamFoundation.dll

Microsoft.TeamFoundation.VersionControl.Client.dll

Microsoft.TeamFoundation.VersionControl.Common.dll

Microsoft.TeamFoundation.VersionControl.Controls.dll

TF.exe

TF.exe.config

At this point, set up your TFS workspace for this account/server. It assumes your TFS project is named $/YourENproject (I created a separate project just for this, so you don't risk overwriting new code), and the server is named your-tf-server-name. The workspace will be called TF_Workspace, and will use c:\TFS_CLI\TF_Workspace

--unsure if this first line is needed or not. Run from command line in that folder

c:\TFS_CLI\app\>tf.exe workspace /new tf_workspace /location:c:\TFS_CLI\TF_Workspace

Unable to determine the source control server.

c:\TFS_CLI\App>tf workspace /new /noprompt /s:http://your-tf-server-name:8080 TF_Workspace

c:\TFS_CLI\App>tf workfold $/YourENproject c:\TFS_CLI\EN_Workspace /workspace:TF_Workspace /s:http://your-tf-server-name:8080

c:\TFS_CLI\App>tf workfold /unmap $/

Now, try and check out an object - I put a test object in mine to make sure this worked.

c:\TFS_CLI\App> tf get $/Randolph/ftw-sv-db-03/assetexplorer/Tables/dbo.AaaAce.sql

Now, save the following script as sql_script_to_TFS.ps1 and place it in C:\TFS_CLI.

Since it's Powershell you'll either need to sign it or run in an execution mode of unrestricted.

Personally, I'd sign it.

https://gist.github.com/mbourgon/6873071 (in case it isn't directly below.

	<#
	.SYNOPSIS
	Will script an object from SQL Server and CHECKIN/ADD to TFS.
	.EXAMPLE
	sql_script_to_TFS.ps1 -server yourservername -Database yourdatabasname -ScriptType "FULL" -Author yourTFSname -Comment "full checkin of database"

	#>
	# Purpose - given parameters, script out an object from a SQL Server, using SMO, then check it into TFS.
	#Scripting code gleefully copied from Phil Factor - the great parts that work are his, the errors are mine
	# https://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-and-smo/
	#Other code from others copied and attributed below.

	#mdb 2013/12/03 adding ScriptType so that we can add "create database"-specific changes ONLY.
	# To do FULLs you will have to have -ScriptType = 'Full' parameter. By default it will do objects.
	#mdb 2013/12/05 trying to better bullet-proof the app against TF failures, which appear to occur randomly
	#mdb 2013/12/07 works great, though occasionally it cant script out an item. Trying to bulletproof that
	#mdb 2013/12/16 broke the individual-item portion, fixed.
	#mdb 2013/12/17 more cleanup and dealing with no objects found.
	#mdb 2014/09/08 removing .exe references, and changing folder name, so 2013 works.
	#mdb 2018/03/06 the -notmatch doesn't include usp_M* under certain circumstances. Changing to notlike

	#param has to be the first line of the script.
	param(
	[Parameter(Mandatory=$true,Position=1)]
	[string]$Server,
	[Parameter(Mandatory=$true,Position=2)]
	[string]$Database,
	[string]$ScriptType ='Object',
	[string]$SchemaToScript,
	[string]$ObjectToScript,
	[string]$Author,
	[string]$Comment
	)
	#make sure not ObjectToScript blank if scripting out the entire DB; makes checkin better

	#these are the parameters for testing
	#$Server='sql_repository'# the server it is on
	#$Database='model' # the name of the database you want to script as objects
	#$SchemaToScript = 'dbo'
	#$ObjectToScript = 'spy_tempdef'
	#$Author = 'michael.bourgon'
	#$ScriptType ='Object'
	#$Comment = 'bourgon_test'

	#setting up an error code for later
	$myerror = 0

	cd c:\tfs_cli\en_workspace

	if ($comment -eq '')
	{ $comment = "generic EN checkin"}

	if ($author -eq '')
	{ $author = "erxnetwork\sqlservice"}
	#field is mandatory, if we dont know, use a known-valid.

	$ServerNameClean = "$($Server -replace '[\\\/]','__')"
	clear

	#writing this out for logging and troubleshooting. These are all the parameters except ScriptType
	write-host $Server, $Database, $SchemaToScript, $ObjectToScript, $Author, $Comment

	#TFS workspace folder - whatever you set it up as on your server
	$DirectoryToSaveTo='C:\TFS_CLI\EN_Workspace' # the directory where you want to store them

	# Load SMO assembly, and if we are running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
	$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
	if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
	[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') \| out-null
	}
	[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoEnum') \| out-null
	set-psdebug -strict # catch a few extra bugs
	$ErrorActionPreference = "stop"
	$My='Microsoft.SqlServer.Management.Smo'
	$srv = new-object ("$My.Server") $Server # attach to the server
	if ($srv.ServerType-eq $null) # if it managed to find a server
	{
	Write-Error "Sorry, but I could not find Server '$Server' "
	return
	}

	$scripter = new-object ("$My.Scripter") $srv # create the scripter
	#Add the various options we care about
	$scripter.Options.ToFileOnly = $true
	$scripter.Options.ExtendedProperties= $true # yes, we want these
	$scripter.Options.DRIAll= $true # and all the constraints
	$scripter.Options.Indexes= $true # Yup, these would be nice
	$scripter.Options.Triggers= $true # This should be includede
	$scripter.Options.AppendToFile = $False
	$scripter.Options.AllowSystemObjects = $False
	$scripter.Options.ClusteredIndexes = $True
	$scripter.Options.DriAll = $True
	$scripter.Options.ScriptDrops = $False
	$scripter.Options.IncludeHeaders = $False #so you do not get the one line "scripted at..." which would be NOW.
	#$scripter.Options.ToFileOnly = $True
	#$scripter.Options.Indexes = $True
	$scripter.Options.Permissions = $True
	$scripter.Options.WithDependencies = $False
	$scripter.Options.Bindings = $true
	$scripter.Options.IncludeDatabaseRoleMemberships = $true


	#################################################
	#First, script out the database "create" itself.#
	#################################################
	if (($Database) -and $ObjectToScript -eq '') #if database has a value but there is no object, script out the DB.
	{
	$db_scripter = new-object ("$My.Scripter") $srv # script out the database creation
	$db_scripter.options=$scripter.options # with the same options
	$db_scripter.options.filename="$($DirectoryToSaveTo)\$($ServerNameClean)\$($Database)\$($Database)_database_create.sql" # with the same options

	#explcitly creating the path for the DB script here. We still need to do it for all the sub-types.
	# Could probably move the $d declaration up here, but leaving it here for readability.
	$SavePath="$($DirectoryToSaveTo)\$($ServerNameClean)\$($Database)"
	# create the directory if necessary (SMO does not).
	if (!( Test-Path -path $SavePath )) # create it if not existing
	{Try { New-Item $SavePath -type directory \| out-null }
	Catch [system.exception]{
	Write-Error "error while creating '$SavePath' $_"
	return
	}
	}
	#Use TF to see if the object exists on our TFS server.
	# Optimization idea: DIR the entire subfolder on a FULL and compare all at once.
	$tf = &cmd /c "c:\TFS_CLI\App_2013\TF dir $/Randolph/$ServerNameClean/$Database/$($Database)_database_create.sql 2>&1"
	# Running the TF calls this way as per Simon Ejsing to ignore the error state and capture the actual error message
	# http://stackoverflow.com/questions/2095088/error-when-calling-3rd-party-executable-from-powershell-when-using-an-ide
	# However, that also means we need

	#Note that if the database create has not changed, it will still attempt to CHECKIN, but TFS will ignore as it is the same.
	if ($tf -like "No items match" -or $tf -like "is not found or not supported*")
	{
	"database script does not exist; scripting out and ADDing to TFS"

	if(Test-Path -Path $db_scripter.options.filename)
	{
	#delete the file manually, since we have seen permission issues where the $script cannot overwrite.
	$deleteme = "$SavePath\$($Database)_database_create.sql"
	$deleteme
	try
	{
	remove-item "$SavePath\$($Database)_database_create.sql" -force
	}
	catch
	{
	$error[0].Exception
	}
	}

	#putting in a try/catch so we get error messages if it breaks, and it can continue.
	try
	{
	$db_scripter.Script($srv.Databases[$Database]) # do it
	}
	Catch
	{
	"Error Message trying to script out $SavePath\$Filename"
	$error[0].Exception
	}

	"database create script done"
	$tf = &cmd /c "c:\TFS_CLI\App_2013\TF add $/Randolph/$ServerNameClean/$Database/$($Database)_database_create.sql 2>&1"
	$tf
	#use mass checkin at the end
	}
	else
	{
	"database script exists; get, check out, script to override, check in"
	$tf = &cmd /c "c:\TFS_CLI\App_2013\TF get ""$/Randolph/$ServerNameClean/$Database/$($Database)_database_create.sql"" /noprompt 2>&1"
	"database script GET results"
	$tf
	$tf = &cmd /c "c:\TFS_CLI\App_2013\TF checkout ""$/Randolph/$ServerNameClean/$Database/$($Database)_database_create.sql"" 2>&1"
	"database script CHECKOUT results"
	$tf
	"database checkout done"

	#If the file exists, manually delete; we have seen permission issues where $script cannot overwrite.
	if(Test-Path -Path $db_scripter.options.filename)
	{
	$deleteme = "$SavePath\$($Database)_database_create.sql"
	$deleteme
	try
	{
	#bug exists with standard remove - if there are read-only items in the same folder, -force is required
	remove-item "$SavePath\$($Database)_database_create.sql" -force
	}
	catch
	{
	$error[0].Exception
	}
	}

	#putting in a try/catch so we get error messages if it breaks, and it can continue.
	try
	{
	$db_scripter.Script($srv.Databases[$Database]) # do it
	}
	Catch
	{
	"Error Message trying to script out $SavePath\$Filename"
	$error[0].Exception
	}
	"database script out done"
	#use mass checkin at the end
	}
	}


	###########################
	## Scripting out Objects ##
	###########################
	# we now get all the object types except extended stored procedures

	# first we get the bitmap of all the object types we want
	$all =[long] [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::all `
	-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ExtendedStoredProcedure
	# and we store them in a datatable
	$d = new-object System.Data.Datatable

	# get almost everything; skipping most service broker, information_schema, system_views, certificates (cannot be scripted)
	# there are other items that may need to be skipped, like SymmetricKeys in SSISDB
	# Yes, I realize the irony in skipping SB given that it is powering this.

	#putting in a try/catch so we get error messages if it breaks, and it can continue.
	try
	{
	$d=$srv.databases[$Database].EnumObjects([long]0x1FFFFFFF -band $all) \| `
	Where-Object {$_.Schema -ne 'sys'-and $_.Schema -ne "information_schema" -and $_.DatabaseObjectTypes -ne 'ServiceBroker' `
	-and $_.DatabaseObjectTypes -ne 'Certificate' `
	-and $_.DatabaseObjectTypes -ne 'MessageType' `
	-and $_.DatabaseObjectTypes -ne 'ServiceContract' `
	-and $_.DatabaseObjectTypes -ne 'ServiceQueue' `
	-and $_.DatabaseObjectTypes -ne 'ServiceRoute' `
	-and ($SchemaToScript -eq '' -or $_.Schema -eq $SchemaToScript) `
	-and (($ObjectToScript -eq '' -and $ScriptType -eq 'Full') -or $_.Name -eq $ObjectToScript) `
	-and ($_.Name -notlike 'sp_MS*') }
	# mdb 2013/11/07 previous line skips replication objects. This comment below code as comment lines break extended 1-liner.
	}
	Catch
	{
	"Error Message trying to enumerate the database - may be logshipped or being restored"
	$myerror = 1
	$error[0].Exception
	}


	# List every item that we are going to do
	$d = $d \| sort -Property DatabaseObjectTypes,Schema,Name
	$d \| select databaseobjecttypes, schema, name

	if ($d.Count -gt 10000)
	{
	"skipping the database objects - more than 10000"
	}

	# Now write out each scriptable object as a file in the directory you specify
	#it appears that an empty array never actually enters the FOREACH, leaving variables unset
	# -and -$d.Count -ne 0
	if ($myerror -eq 0 -and $d.Count -lt 10001) #20k of objects takes up 5gb of RAM in the PS script and causes problems
	{
	$d\| FOREACH-OBJECT { # for every object we have in the datatable.
	"" #blank line so each block of error messages is separated out
	$SavePath="$($DirectoryToSaveTo)\$ServerNameClean\$Database\$($_.DatabaseObjectTypes)"

	# create the directory if necessary (SMO does not).
	if (!( Test-Path -path $SavePath )) # create it if not existing
	{Try { New-Item $SavePath -type directory \| out-null }
	Catch [system.exception]{
	Write-Error "error while creating '$SavePath' $_"
	return
	}
	}
	# tell the scripter object where to write it, and make sure it is actually writeable
	if ($_.schema)
	{
	$Filename = "$($_.schema -replace '[\\\/\:\.]','-').$($_.name -replace '[\\\/\:\.\ ]','-').sql";
	}
	else
	{
	$Filename = "$($_.name -replace '[\\\/\:\.]','-').sql";
	}

	$scripter.Options.FileName = "$SavePath\$Filename"
	$scripter.Options.FileName #print it out so we know what is being done

	$UrnCollection = new-object ('Microsoft.SqlServer.Management.Smo.urnCollection')
	$URNCollection.add($_.urn)


	############################
	# TFS code for each object #
	############################
	#Use TF to see if the object exists on our TFS server
	"checking to see if object exists"
	$tf = &cmd /c "c:\TFS_CLI\App_2013\TF dir $/Randolph/$ServerNameClean/$Database/$($_.DatabaseObjectTypes)/$Filename 2>&1"
	# Running all the TF commands this way as per Simon Ejsing to ignore the error state and capture the actual error message.
	# http://stackoverflow.com/questions/2095088/error-when-calling-3rd-party-executable-from-powershell-when-using-an-ide

	if ($tf -like "No items match" -or $tf -like "is not found or not supported*")
	{
	"no items match; scripting out and ADDing to TFS"

	try
	{
	$scripter.script($URNCollection)
	}
	Catch
	{
	"Error Message trying to script out $SavePath\$Filename"
	$error[0].Exception
	}

	"script done"

	$tf = &cmd /c "c:\TFS_CLI\App_2013\TF add /noprompt ""$/Randolph/$ServerNameClean/$Database/$($_.DatabaseObjectTypes)/$Filename"" 2>&1"
	$tf

	#mdb 2013/11/07 only do ONE checkin at the end if we are doing an entire database; all will have the same comment
	if ($ObjectToScript -ne '')
	{
	$tf = &cmd /c "c:\TFS_CLI\App_2013\TF checkin /author:$author /comment:""$comment"" /noprompt 2>&1"
	$tf
	}
	}
	else
	{
	"item exists; get, check out, script to override, check in"
	#noprompt causes it to crash, virtually every time
	$tf = &cmd /c "c:\TFS_CLI\App_2013\TF get ""$/Randolph/$ServerNameClean/$Database/$($_.DatabaseObjectTypes)/$Filename"" 2>&1"
	$tf
	$tf = &cmd /c "c:\TFS_CLI\App_2013\TF checkout ""$/Randolph/$ServerNameClean/$Database/$($_.DatabaseObjectTypes)/$Filename"" 2>&1"
	$tf

	#Delete file before scripting; we have seen permission issues.
	if(Test-Path -Path $scripter.options.filename)
	{
	try
	{
	remove-item "$SavePath\$Filename" -force
	}
	catch
	{
	$error[0].Exception
	}
	}

	try
	{
	$scripter.script($URNCollection)
	}
	Catch
	{
	"Error Message trying to script out $SavePath\$Filename"
	$error[0].Exception
	}

	#mdb 2013/12/03 making this part only run if it is a specific object; that way we can rerun an entire database
	if ($ObjectToScript -ne '')
	{
	$tf = &cmd /c "c:\TFS_CLI\App_2013\TF checkin /author:$author /noprompt /comment:""$comment"" ""$/Randolph/$ServerNameClean/$Database/$($_.DatabaseObjectTypes)/$Filename"" 2>&1"
	$tf
	}
	}


	}
	}

	#If it is a mass add or a database-specific, CHECKIN now.
	if ($ObjectToScript -eq '')
	{
	"final mass checkin"
	$tf = &cmd /c "c:\TFS_CLI\App_2013\TF checkin /author:$author /comment:""$comment"" /noprompt 2>&1"
	#$tf we do not need this one here because it will be shown below
	#$tf
	"mass checkin done"
	}
	"--------------------------------------------------------------------------"
	#if the checkin failed, UNDO so the next one does not make it worse.
	#The next checkin would probably fix it, but I have had to go back and manually undo. Not fun.
	#mdb 20131107 If there were any items to check in, get the results. Throws an error otherwise.

	#using Stej code to verify the variable exists; if no objects, $tf is never set, so it bombs here
	# http://stackoverflow.com/questions/3159949/in-powershell-how-do-i-test-whether-or-not-a-specific-variable-exists-in-global

	if (Test-Path variable:local:tf)
	{
	if ($tf -like "Changeset * checked in." -or $tf -like "There are no remaining changes to check in.")
	{
	#mdb 20131107 If there were any items to check in, get the results. Throws an error otherwise.
	if ((Test-Path variable:local:d) -and $ObjectToScript -eq '')
	{
	$tf
	}
	}
	else
	{
	"changes not made - errorlog follows"
	$tf
	"================================UNDO BEGINS=================================="
	$tf = &cmd /c "c:\TFS_CLI\App_2013\TF undo ""$/Randolph/$ServerNameClean/$Database/."" /recursive 2>&1"
	$tf
	"=================================UNDO ENDS==================================="
	}
	}
	else
	{
	"No objects found, nothing done"
	}

	#TFS rollback code, should look something like this
	#c:\TFS_CLI\App_2013\TF undo $/Randolph/$ServerNameClean/VOAgent/DatabaseRole/.
	#No files checked in due to conflicting changes. These conflicting changes have been automatically resolved. Please try the check-in again.

	#rolling back code: c:\TFS_CLI\App_2013\TF undo $/Randolph/cm-01/VOAgent/DatabaseRole/.
	#for some reason "override:" did not work.

	#C:\TFS_CLI\EN_Workspace\server\file.sql
	#item exists; get, check out, script to override, check in
	#All files are up to date.

view raw sql_script_to_TFS.ps1 hosted with ❤ by GitHub

Once you have it saved, try adding an object!

--SSMS, on my_personal_server

use somedatabase

create table blah (id int identity)

Now from powershell:

C:\TFS_CLI\sql_script_to_TFS.ps1 -Server my_personal_server -Database somedatabase -objecttoscript blah -Author mydomain\validTFSuser

On mine, I get:

item exists; get, check out, script to override, check in

C:\TFS_CLI\YourENproject\my_personal_server\somedatabase\Table:
Checking in edit: dbo.blah.sql

(followed by a confirmation that it checked in)

All good? Good. Now for the final step - using EN to kick it off.

Create a couple tables to hold the details. Mine are in my EN database, since that's where the data is.

CREATE TABLE TFS_Checkin_Errors (id BIGINT IDENTITY PRIMARY key, time_job_run DATETIME, original_id INT, resultant VARCHAR(8000))

CREATE TABLE TFS_Last_Time_Run (last_time_run DATETIME PRIMARY KEY )

Save out this script, then run it on the server.

	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

view raw EN_To_TFS_2 hosted with ❤ by GitHub

Run once to make sure it's acting as expected, then set up a job to run it every 5 minutes.

....and..... you're done!

Check the error tables for issues; right now it doesn't handle spaces in the object name, I'm fixing that this week.

The Baking DBA - select * from flour

Wednesday, October 23, 2013

#SQLSaturday255 - I'm presenting In Dallas on November 2nd!

Monday, October 7, 2013

[Event Notifications] Automatically check in code changes to TFS

Blog Archive