Originally asked this on SO but that question seems to be populated only by tumbleweed so wondered if this might be a better forum for it.

I have a VS2103 solution which includes a database project for which we have a number of publish profiles. One of these is for the purposes of testing some functionality that requires an actual database to be in place so it is supposed to publish the database to (localDb), add any lookup data and then run some post deployment scripts to add some fake data.

The test project has a pair of pre-build events to try and ensure that the DB project has been built and is then deployed:

"$(MSBuildBinPath)\msbuild.exe" "$(SolutionDir)CUIA Database\CUIA Database.sqlproj" /t:Rebuild

"$(PROGRAMFILES)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe" /Action:Publish /SourceFile:"$(SolutionDir)CUIA Database\$(OutDir)CUIAWorkspace.dacpac" /Profile:"$(SolutionDir)CUIA Database\UNIT-TEST.publish.xml" /Variables:test_run="True" /p:CreateNewDatabase="True"

The SQLCMD variable is what determines if the test data is inserted into the newly created database, and is defaulted to False.

The problem is that it does not seem to work every time and I cannot find a reason why. If I start with nothing on the local db instance, then it works as expected, the DB is created and the tests have something to use. However, if I then change some of the test data using SSMS and re-run the tests, it seems to be a crapshoot as to whether or not the DB is recreated and mostly it is not. I can see this because my amended test data is still there so it has not been dropped. I have also verified the same behavious on a SQL Express instance. Database logs show no problems and the build outputs from successful runs are the same as for those where the DB is not re-created.

I haven't been able to find much in the way of information on what I am trying to achieve so I'm have to go with some hunches and guesswork so I appreciate that my pre-build event idea is possibly not the best solution. If anyone knows a better one then please speak up and share. However, the primary concern is why is the /p:CreateNewDatabase="True" flag not being respected? Surely this should demand that the database is dropped and re-created every single time?

  • Hi Steve, I would have commented sooner but I was enjoying the sound of tumbleweed! I had a play around with the flag and it should cause an ALTER DATABASE SET SINGLE USER with ROLLBACK IMMEDIATE so it shouldn't be affected by open transactions or anything like that which is my first guess - if I was you I would open a connect item, link it in the SSDT forum and someone on the SSDT team will see it - also check sys.dm_exec_connections and see if any open connections have txn's or something the times it doesn't work, I would also run profiler and capture the output of a working/failing run – Ed Elliott Jul 6 '15 at 10:28
  • profile, rpc and sql batch startig and ending and user error messages - perhaps something is going wrong?? – Ed Elliott Jul 6 '15 at 10:29
  • Thanks for that. I'll have a look when I next get a chance - we were in a period of time between projects but we're now well into a new series of sprints and this has gone on the back burner. If/when I revisit I'll be sure to post anything I find here. – Steve Pettifer Jul 8 '15 at 13:29
  • Cool, if you go back to it also try setting /p:DeployDatabaseInSingleUserMode=True – Ed Elliott Jul 8 '15 at 13:32
  • Did you ever find a solution to this? If so feel free to post a self answer. I use database projects all the time and I've never had any problems. I've never used LocalDB though. I do occasionally notice if I trigger my build on the Visual Studio Online hosted build server too close together the database project won't deploy properly. I assumed this was something to do will Azure rate limiting database recreations. I'd be interested to see if our problems are related. – Erik Nov 13 '15 at 22:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.