Error: 'CREATE VIEW' must be the first statement in a query batch.

[DanielOverdevest]

Type

What kind of issue is this?

[x] Bug report.
[ ] Feature request.

Current Behavior

It's not possible to deploy a view into another database, using ssc push production Gives this error code:

Error: 'CREATE VIEW' must be the first statement in a query batch.

It looks like the current situation doesn't allow to run two statements in a single batch.

Example view:

if exists (select * from sys.objects where object_id = object_id('[sys].[database_firewall_rules]') and type = 'V')
drop view [sys].[database_firewall_rules]
go

CREATE VIEW sys.database_firewall_rules AS SELECT id, name, start_ip_address, end_ip_address, create_date, modify_date FROM sys.database_firewall_rules_table

Expected behavior

Expected to create the view for me.

Steps to Reproduce

Environment: Azure SQL Database (latest version) ssc pull development ssc push production

Other Information

[DanielOverdevest]

@justinlettau do you think this is a bug? I still get the error using SSC PUSH production. I think it's because it starts with 'if exists' .... then GO.. And on fifth line it have the CREATE VIEW. Do you have already a solution for this?

[DanielOverdevest]

I found out that I can change the IF exists lines and CREATE VIEW with 'CREATE OR ALTER VIEW'. This works fine for me!

[justinlettau]

@DanielOverdevest Yeah, I do think it's a bug. I believe it has to do with how the queries are executed on push. I'm glad the CREATE OR ALTER works for you. In addition to fixing this bug, i'd like to add CREATE OR ALTER as an idempotency option.

[justinlettau]

@DanielOverdevest Can you reproduce this with in the latest version? The push command is designed to separate statements by the GO and execute each segment separately; So in theory this error shouldn't occur.

[DanielOverdevest]

Hi Justin, I will, next week. Ok? GR. Daniel

On 16 Jan 2019, at 22:42, Justin Lettau [email protected] wrote:

@DanielOverdevest https://github.com/DanielOverdevest Can you reproduce this with in the latest version? The push command is designed to separate statements by the GO and execute each segment separately; So in theory this error shouldn't occur.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/justinlettau/sql-source-control/issues/42#issuecomment-454954057, or mute the thread https://github.com/notifications/unsubscribe-auth/ARsGeDRlFmKer0WNg4-E816pk2jXdPFQks5vD5y2gaJpZM4Wt8nZ.

[justinlettau]

@DanielOverdevest Thanks!

[DanielOverdevest]

Hi Justin,I installed the latest version (2.1.0 and 2.1.1) and both are not working anymore with Azure SQL database (basic tier). I think you’re using functions which are not implemented in that version of SQL server.If you can give me some hints where to look at, I’m willing to dive a bit deeper in the problem and test for a solution.GR.Daniel

[justinlettau]

@DanielOverdevest The only issue I am aware of with Azure SQL is regarding jobs (#65). Is the error your getting look related to jobs? If so, this config should address it:

{
 "output": {
   "jobs": false
 }
}

If not, could you provide some details about the error you are seeing? Thanks.

[DanielOverdevest]

I tried it out with the latest version (I had to wait until I need to push anything into production). I still get the same error: ✖ RequestError: 'CREATE VIEW' must be the first statement in a query batch.

When I find time I'm gonna change the pull request which fixed this problem using this statement: CREATE OR ALTER VIEW

Thanks for the 2.1.1. update by the way. Clustered Indexes of multiple columns are now better captured.

UPDATE: changing it to CREATE OR ALTER also returns the same error. So solution must be found in the way it't sent to the server and GO's, what you already suggested.