0

I have a DB project I created by connecting to a SQL 2016 instance and importing the database into visual studio.

I created a deployment by creating a publish file and checking it into the source control. Among the settings I had to change were to not deploy any user accounts or permission changes as it wanted to drop several accounts from my system.

When TFS executes MSBuild to do the deployment, here's the command line arguments that are passed: /p:VisualStudioVersion=14.0 /t:Build;Publish /p:SqlPublishProfilePath=MyDBProj.publish.xml

The problem lies in that if I do not exclude the CLR user defined types and also exclude assemblies, the deployment fails with the following (among many other similar) errors:

C:\Builds\1\MyDBProj\MyDBProj - Prod\bin\MyDBProj.publish.sql (0, 0)
[System.Web] has an unresolved dependency to [System.Design]. A deployment script with correct ordering cannot be created.

C:\Builds\1\MyDBProj\MyDBProj - Prod\bin\MyDBProj.publish.sql (0, 0)
[System.Runtime.Remoting] has an unresolved dependency to [System.Web]. A deployment script with correct ordering cannot be created.

C:\Builds\1\MyDBProj\MyDBProj - Prod\bin\MyDBProj.publish.sql (0, 0)
[System.Design] has an unresolved dependency to [System.Web]. A deployment script with correct ordering cannot be created.

C:\Builds\1\MyDBProj\MyDBProj - Prod\bin\MyDBProj.publish.sql (0, 0)
An error occurred during deployment plan generation. Deployment cannot continue.
(-1, 0)
Exception Message: MSBuild error 1 has ended this build. You can find more specific information about the cause of this error in above messages. (type BuildProcessTerminateException)
Exception Stack Trace: at System.Activities.Statements.Throw.Execute(CodeActivityContext context)
at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.ActivityInstance.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

I've tried referencing master as a database reference. I'm not sure what else to do though. I need the CLR components deployed with the database when built on TFS.

SpaceCowboy74
  • 1,367
  • 1
  • 23
  • 46

1 Answers1

0

TFS invoke MSBuild command to build and deploy solution on build agent machine by default. So you can build and deploy your solution using VS2015(or MSBuild command) manually on build agent machine and local, ensure everything works fine when run build agent machine manually. More info related to how to Deploying CLR Database Objects from MSDN.

Moreover, according to the error info, this may cause by a circular dependency. VS couldn't create a deployment script with correct ordering because every time it worked its way up the dependency tree, it ended back where it started. Please double check this to narrow the issue.

PatrickLu-MSFT
  • 49,478
  • 5
  • 35
  • 62
  • Any idea how to find the circular dependency? Oddly enough, all is ok if I deploy manually from the Build Agent Machine/TFS Server (They are the same box) if I load the solution into VS on it and deploy from there. – SpaceCowboy74 Aug 08 '16 at 18:00
  • Did you think this may related to SQL2016? What's the version of your TFS? If it's older than TFS2015RTM, then there maybe not so compatibility with SQL2016. You can update to the latest version TFS2015update3(https://www.visualstudio.com/en-us/news/releasenotes/tfs2015-update3-vs) first and try it again. – PatrickLu-MSFT Aug 09 '16 at 09:55
  • I updated everything to the latest TFS (Update 3). Now I can't get any builds to run at all. I queue them up (using the xaml config) and they just sit at "Running for 0 seconds" and nothing updates. – SpaceCowboy74 Aug 11 '16 at 18:52
  • Seems there are something wrong with your build agent. After the update you need to reconfigure your XAML build control and build agent. And repoint to the new control and agent in your build definition. You can also try to use the new web-based vNxet build system. Which is more powerful , more simple, more customized. – PatrickLu-MSFT Aug 12 '16 at 13:13
  • I need to get someone to reconfigure the XAML build system on my server (they won't give me the password). I tried using the vNext build by creating a standard build and adding /p:VisualStudioVersion=14.0 /t:Build;Publish /p:SqlPublishProfilePath=MyApp.publish.xml to the MSBuild Arguments section. It still gives me the D:\Agent\_work\2\s\Production\MyDB\bin\Debug\MyDB.publish.sql : Deploy error SQL72009: [Microsoft.Transactions.Bridge] has an unresolved dependency to [System.ServiceModel]. A deployment script with correct ordering cannot be created. – SpaceCowboy74 Aug 15 '16 at 16:42
  • A little more info. I tried doing the vNext using SqlPackage.exe and am still failing. It didn't give specifics, so I enabled event logging and captured everything if I ran it manually. It's giving me `TSQL Model: Dependency [System.Web]` among other things in the event log, leading me to think it's running into the same problem. – SpaceCowboy74 Aug 16 '16 at 21:09
  • Seems still the dependency error. Did you also tried referencing master as a database reference with vNext build http://stackoverflow.com/questions/18096029/unresolved-reference-to-object-information-schema-tables . For "Any idea how to find the circular dependency? " This often occurs when two two functions referenced each other. You can check this. – PatrickLu-MSFT Aug 18 '16 at 02:17