ELASTIC_POOL not recognised by Visual Studio Database Project

Question

I am creating a Database project for a SQL Database in Azure.

The Target Platform is Microsoft Azure SQL Database V12 which I have set in the project settings.

I then have a post deployment script containing the following code to add the database to an elastic pool

ALTER DATABASE [$(DatabaseName)] MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = mypoolname ) )

Ref: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-azure-sql-database?view=azuresqldb-current#b-moving-a-database-to-a-different-elastic-pool

Because my Post Deployment script is set to build (because it has to as part of being a post deployment script) I get the following error:

Error: SQL72007: The syntax check failed 'Incorrect syntax near ELASTIC_POOL.' in the batch near:

But I know the syntax is ok, it is obviously not recognising the Azure TSQL.

Is it possible to do this as part of my Visual Studio Database Project?

Answer 1

I'm not sure how you're actually sending the query over to the database server, but in order to bypass a preliminary clientside syntax check you could simply use

exec sp_executesql N'ALTER DATABASE [$(DatabaseName)] MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = mypoolname ) )'