How can I deploy to a serverless SQL pool using Azure DevOps Pipelines?

Question

I'm attempting to deploy to a Synapse serverless SQL pool using Azure DevOps Pipelines, specifically using SqlAzureDacpacDeployment@1. This uses SQLPackage, which is supposed to be compatible with serverless. I'm getting an error message that a Database Scoped Credential could not be imported. I have removed all DSC's from my SQL Project and from the target serverless SQL pool. When I turn on diagnostics, it looks like what's actually happening is that SQLPackage is trying to access sys tables that don't exist in serverless.

An error occurred while attempting to reverse engineer elements of type Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlDatabaseCredential: Batch Command during reverse engineer failed with Error Code: -2146232060 Line Number: 1455 Error Message: FUNCTION 'OBJECT_SCHEMA_NAME' is not supported.

##[error]*** An error occurred during deployment plan generation. Deployment cannot continue.> ##[error]Error SQL72018: Database Scoped Credential could not be imported but one or more of these objects exist in your source.> ##[error]System.Management.Automation.RemoteException> ##[error]Errors occurred while modeling the target database. Deployment can not continue.> ##[error]The Azure SQL DACPAC task failed. SqlPackage.exe exited with code 1.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-

Answer 1

Thanks for posting your question in the Microsoft Q&A forum.

As described in error message the OBJECT_SCHEMA_NAME is being used in your SQL script, but this function is not supported in Azure Synapse serverless SQL pools. you will need to find an alternative approach to achieve the same functionality.

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful **

Answer 2

Hi Joshua Persons ,

Thanks for using Microsoft Q&A platform and thanks for posting your query here.

It seems like you are facing an issue while deploying to a Synapse serverless SQL pool using Azure DevOps Pipelines. The error message you are seeing indicates that SQLPackage is trying to access sys tables that don't exist in serverless.

Serverless SQL pools in Azure Synapse Analytics have some limitations compared to dedicated SQL pools. One of these limitations is that some system tables are not available in serverless SQL pools. This can cause issues when deploying SQL projects that reference these system tables.

To resolve this issue, you can try the following steps:

1. Make sure that you have removed all references to Database Scoped Credentials from your SQL project and from the target serverless SQL pool.
2. Check if there are any other system tables or features that are not supported in serverless SQL pools and remove any references to them from your SQL project.
3. If none of the above steps work, you can try using a dedicated SQL pool instead of a serverless SQL pool.

I hope this helps you resolve the issue you are facing. Kindly accept the answer by clicking on `Accept answer button. Thankyou