InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 23:44:05 GMT20http://www.sqlservercentral.com/Forums/Topic446494-361-1.aspxGrant, thanks for your reply I also found your article you posted interesting today.I need to read your last post again to make total sense of it as I am fairly new to TFS.Thanks againThu, 24 Jan 2008 07:25:47 GMTaaa-322853http://www.sqlservercentral.com/Forums/Topic446494-361-1.aspxWhat about adding a statement from TFS using the tf.exe to get the the version of the project, add that to a variable and pass that variable into the command line that does the build & deploy? I'm not sure of the specific syntax to do that, but I think the command prompt properties will work:[code]tf properties project /version[/code]I'd have to do some experimentation to find the precise mechanism to capture it into a variable, but once you have it, you'd just run the build & deployment like this:[code]msbuild my.dbproj /t:sqlbuild /p:Configuration="myconfig";TFSVersion=OutputVariablemsbuild my.dbproj /t:sqldeploy /p:Configuration="myconfig";TFSVersion=OutputVariable[/code]Then you can set the database property in one of the post-deployment scripts like this:[code]exec sys.sp_addextendedproperty @name = N'TFSVersion', @value = $(TFSVersion)[/code]That will capture and put it into the completion of each build. You may have to adjust for incremental builds (look to see if the property is there first, update it if it is, add it if it isn't). But that should get you what you need. All this assumes you're using the full on Team Edition for Database Professionals and not simply adding a database project (two totally different things) into TFS.The more I think about this, I may add this to our process...Thu, 24 Jan 2008 06:22:39 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic446494-361-1.aspxGuys, thanks for your suggestions. In reference to Grant's post this solution would still require user intervention to set the variable at each command line, wouldn't it? Or am I missing something.I want to try to steer clear of any user intervention wherever possible. Ideally I would want DFS to dynamically recognise which version is being deployed and set the extended properties as such.Please ask me for more information if you think it would help in finding a solution?Wed, 23 Jan 2008 15:10:04 GMTaaa-322853http://www.sqlservercentral.com/Forums/Topic446494-361-1.aspxAlso - if you've installed the "original" Team suite, you probably don't have access to the nice new database projects which unlock so much more than the "default" database template. In that case (if you don't happen to have a SQL2005 and/or SQL2000 database project type, which allows for all downloading of the entire database schema), you should download and update your VSTS install with this add-on:[url=http://www.microsoft.com/downloads/details.aspx?familyid=7de00386-893d-4142-a778-992b69d482ad&displaylang=en]Visual Studio 2005 Team Edition for Database Professionals Add-on for Visual Studio 2005 Team Suite Edition[/url]Wed, 23 Jan 2008 13:33:05 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic446494-361-1.aspxNot a problem. Provide a variable, say the TFS version number or something, in the build command line and set the extended property for the database in a Post-Deployment script.Actually, when you're doing incremental deployments from DBPro, it does this for you, although since that's an internally generated number, I doubt it's available to you for documentation:[code]IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)BEGIN DECLARE @stampValue nvarchar(128), @stmt nvarchar(4000), @parm nvarchar(4000) SELECT @stmt = N'USE [$(DatabaseName)] SELECT @valueOUT = CAST(value as nvarchar(128)) FROM ::fn_listextendedproperty(''microsoft_database_tools_deploystamp'', null, null, null, null, null, null)', @parm = N'@valueOUT nvarchar(128) OUTPUT' EXEC sp_executesql @stmt, @parm, @valueOUT = @stampValue OUTPUT IF (@stampValue = CAST(N'c4b9a607-e198-4907-b0e0-edef35034203' AS nvarchar(128))) BEGIN RAISERROR(N'Deployment has been skipped because the script has already been deployed to the target server.', 16, 100) WITH NOWAIT RETURN ENDENDGO[/code]Wed, 23 Jan 2008 13:02:14 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic446494-361-1.aspxHi thanks for your reply. This is actually what we currently use - Team Foundation Server we call it!I would like to set the db extended property value to something that relates to the release we are deploying automatically?Wed, 23 Jan 2008 12:56:44 GMTaaa-322853http://www.sqlservercentral.com/Forums/Topic446494-361-1.aspxYes, absolutely. Visual Studio Team Edition for Database Professionals, AKA DBPro, AKA DataDude. This tool has turned around our build & deployments. As a matter of fact, I think the article I wrote on multi-environment deployments is coming out tomorrow. It's expensive, but it's been great.Wed, 23 Jan 2008 11:08:27 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic446494-361-1.aspxHi All,I was hoping you could share your experiences on the following issue and what strategies you have adopted in the workplace.I work as part of a development team and we find ourselves working on multiple servers and instances. Quite often we will be developing different upgrades simultaneously, i.e. one version will be in live, another in test and possibly a further two releases in development.We use TFS and regularly deploy new builds on to the array of various servers available to us. The main problem we have is identifying which versions are on what servers.Do any of you guys find yourself in a similar scenario and have found an way, (ideally an automated one!) to get round this?Thanks GuysWed, 23 Jan 2008 08:55:10 GMTaaa-322853