I have just seen an issue in one of my database release scripts where I’m getting a load of statements like the following….
PRINT N'Altering [dbo].[TestQueue]...'; GO PRINT N'No script could be generated for operation 2 on object ''[dbo].[TestQueue]''.'; GO
This is because of the “Target Platform” setting on the properties of the project. For this database it was set to 2008 as follows:
This needs to be set to the version of the SQL Server that the database sits on in live. In this case it was SQL Server 2014.
When I changed this it automatically updated the compatibility level of the database to 2014. However, although sitting on a SQL Server 2014 instance this database is still on 2008 R2 compatibility level so I had to click on “Database Settings” then click on the “Miscellaneous” tab and change it back to 2008 as follows.
Then when I generated the scripts again all the service broker queue alter statements were gone.
In summary, the “Target Platform” needs to be the version of the SQL Server Instance and the “Compatibility level” is the compatibility level of the database itself.