How can you create a view as a post deployment script in a SQL Server Database Project?

Question

I've create a SQL Server Database Project and imported my database. I've got a few static data tables that I want to recreate every time it's redeployed, so these get recreated as part of the post deployment script.

To ensure this drops and recreates them I've changed these static data tables to have a build action of 'None' so they are dropped as part of the build and only recreated as part of post deployment steps.

My problem is that I have a view that references these tables. Obviously this shows syntax errors as the tables are no longer part of the build as their build action is 'None'. My workaround was to try and create the view also as part of the post deployment script. I do this with the following code:

:r .\PostDeploymentScripts\Views\myView.sql

The actual script looks like this:

CREATE VIEW [CompTotalByType] AS
SELECT
    c.Id,
    t.id AS TypeId,  
    SUM(c.total) AS CompTotal,
FROM CompNumber c
    INNER JOIN Type t 
    ON t.ProdId =  c.ProdId
GROUP BY
    c.Id,
    t.id

However, now I get a syntax error under 'CREATE VIEW' as follows:

Severity Code Description Project File Line Suppression State Error SQL72007: The syntax check failed 'Incorrect syntax near CREATE.' in the batch near: 'CREATE VIEW [CompTotalByType] AS' ....path name....

However, when I put this code into SSMS it has no syntax errors. What's causing this?

sr28 sr28 · Accepted Answer · 2019-08-09T14:36:03

I found the answer here: SQL72007: The syntax check failed 'Unexpected end of file occurred.' in batch near :

It was the second answer that said you needed to add a 'GO' to the previous script. This makes sense as the CREATE VIEW needs to be in its own batch. So I added a 'GO' to the bottom of my previous script before creating the view and that fixed it.

How can you create a view as a post deployment script in a SQL Server Database Project?

2 Answers