VSTS Azure deploy error

StephanBis

Hello,

We've followed the documentation on how to deploy the database via VSTS CI/CD. We are trying to deploy to SQL Azure, the release deploy step is configured and points towards the correct database.
However when we want to deploy our database the deploy step fails with the following error message:

Azure SQL Database does not support switching between databases. Connect to [{dbname}] and then re-run the script.

Thank you in advance,
Stephan

dnlnln

Hi Stephan,

I'm sorry to hear you're continuing to run into issues with your deployment. At this stage you may have to try and debug the deployment script against the Azure environment line-by-line to find out where the problem is arising. For information on how to execute a package script in SQL Server Management Studio, please see this article.

dnlnln

Hi Stephan,

Sorry to hear you've run into a problem with deploying your database in VSTS. Would you mind confirming which  Target platform is selected in your project file? This can be done by opening the project designer (right-click Project in the Solution Explorer and select Properties).


Either of the Azure options should work. If you needed to change the selection, you may need to close and re-open the project in order for the setting to take effect within IntelliSense, but the project should then deploy successfully.

StephanBis

Hello Daniel,

I've tried both Azure platforms (normal and V12) but both give me the exact same error on deploy. The weird part is that the generated SQL files do not even contain any USE statements, is this something that gets generated on the background while deploying?

Best regards,
Stephan

dnlnln

Hi Stephan,

That's very unusual, I don't believe that any code is inserted at deployment time. Would you mind sending me your full deployment log as a private message (with any important details redacted) so I can investigate further?

Thanks!

StephanBis

Hello Daniel,

We re-added the ReadyRoll project and built the solution again. It seems that the error described above has been solved!

We're now receiving this error: There is already an object named '{name}' in the database. We had some trial and error and think we know where the problem lies: the generated script contains a couple of create schema lines, we think these are the culprit. As there are some create table statements afterwards which also use the same schemas.
However when we delete these, the build fails because the script cannot be validated (The specified schema name "{name}" either does not exist or you do not have permission to use it.).

Best regards,
Stephan

dnlnln

Hi Stephan,

Great to hear that you managed to get the build working.

In terms of why the script is failing, this may be difficult to determine without knowledge of the design of your target database and setup of your project. However may I offer a couple of thoughts on how you might go about diagnosing the problem:

• To make it easier to debug the validation step, include the shadow db deployment as part of your local build. To do so, in Visual Studio, open the project designer. On the Debug tab, under "Shadow database", uncheck "Skip shadow database deployment when building inside Visual Studio". When you Build the solution, the shadow db will deploy and display any errors with validating the project. To ensure you're using a fresh copy of the shadow with each build, Clean the solution or perform a Rebuild.
• To determine why the deployment itself is failing, take a backup of the schema from the database from Azure and restore it onto your machine (e.g. using via a DACPAC). If you deploy the solution and there are errors, then it's possible that the project is not in-sync with the target database. If this is the case, the baseline scripts may need tweaking so that they correctly reflect the current state of the target.

As a last resort, I'd suggest starting with a new ReadyRoll project and going through the import wizard again. Note that, to reset your target database on Azure, you may need to drop the [__MigrationLog] table manually to allow ReadyRoll to reset the baseline.

For more information, please see Deploying to an existing database.

StephanBis

Hello Daniel,

We've tried your suggestions and found out that deploying the shadow database locally works as expected, it deploys and then says the target (development) database is in sync with the shadow.
However when we deploy the to the target Azure database we still get the exact same error.

2018-05-02T06:21:41.3135389Z ***** EXECUTING MIGRATION "Migrations\1.0.0-Baseline\001_20180502-0810_username.sql", ID: {06d2371d-562c-4c24-9251-8c3830090786} *****

2018-05-02T06:21:41.3198498Z Creating schemas

2018-05-02T06:21:41.4371392Z Msg 2714, Level 16, State 6, Server db, Line 2

2018-05-02T06:21:41.4371671Z There is already an object named 'proc' in the database.

2018-05-02T06:21:41.4371878Z Msg 2759, Level 16, State 1, Server db, Line 2

2018-05-02T06:21:41.4372047Z CREATE SCHEMA failed due to previous errors.

2018-05-02T06:21:42.3988336Z ##[error]A deployment error occurred: sqlcmd.exe exited with a non-zero exit code.

2018-05-02T06:21:42.4983187Z ##[section]Finishing: ReadyRoll Deploy Database

The target Azure database is completely empty and does not contain any tables so it's strange to us that it "complains" about the object already being there. We re-created the ReadyRoll project and tried again, still the same error message.

Best regards,
Stephan

StephanBis

Hello Daniel,

We've re-created the Azure database and now everything is working as expected... our guess is that something went wrong with the first few deployments and caused our problems. 
Thank you for your help!

dnlnln

Hi Stephan,

I'm very glad to hear that! (Incidentally, I did post an additional suggestion of debugging the package script in SSMS, but unfortunately it seems to have been lost).

If you encounter any further issues please let me know.