CREATE OR ALTER PROCEDURE in VS 2017 Sql server database project throws SQL70001: This statement is not recognized in this context
Question
Wednesday, February 14, 2018 9:52 PM
Hi,
I created a sql server database project with target platform set to sql server 2017. I got the latest SSDT release 15.5.2. When trying to create a procedure using "CREATE OR ALTER PROCEDURE". However it throws SQL70001 error saying this statement is not recognized in this context.
I can connect to my Sql 2017 and create a procedure using "CREATE OR ALTER PROCEDURE". Why can't I use it in the database project. Am I missing anything here?
Thanks,
Jay
All replies (16)
Thursday, February 15, 2018 5:00 AM
Hi Jay，
Since it was related to the SSDT, I help you move this case to the correct forum.
Have a nice day,
Jack
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Thursday, February 15, 2018 6:43 AM
Hello,
You can't use ALTER Statements in a database project at all, we always only have CREATE statements; the ALTER statements are automatically created in build & deployment process.
Only in pre- and post deploy scripts you can use ALTER.
Olaf Helper
Thursday, February 15, 2018 6:47 AM
The database project describes the desired state, so no ALTER ststements are supported or needed.
Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com
Thursday, February 15, 2018 9:14 AM
The question is about the "create or alter procedure" statement, not a separate "alter procedure" statement. I would expect the "create or alter procedure" statement to be recognized in SSDT.
Thursday, February 15, 2018 9:30 AM
I would NOT expect it to be recognized
Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com
Thursday, February 15, 2018 3:47 PM
Thanks for your replies.
What I am trying to solve here is to overwrite existing procedures in core database reference in implementation db project. I don't want to create an object in deployment script because I like it to be part of build process.
Is there another way to go about it?
Thanks,
Jay
Friday, February 16, 2018 7:02 AM
Hello Jay,
On build SSDT runs a schema compare, for existing objects it generate a CREATE script and for existing ones an ALTER script, so there is really no Need to consider the existing state, SSDT do it for you.
Olaf Helper
Tuesday, May 1, 2018 11:24 PM | 1 vote
Same here. This is a new syntax and the question is whether VS2017 SSDT implemented it or not.
Apparently not, unless there is a trick somewhere...
Wednesday, May 2, 2018 11:06 AM | 1 vote
This is a new syntax and the question is whether VS2017 SSDT implemented it or not.
Apparently not, unless there is a trick somewhere...
CREATE OR ALTER is not implemented in SSDT for database object source code. T-SQL scripts with CREATE OR ALTER can be included as pre/post deployment scripts as needed for custom deployment use cases.
An important concept with SSDT database projects is the distinction between T-SQL source code and T-SQL scripts. T-SQL source code DDL and DCL items with the Build action property "Build" are used to build the project, not deploy it. The build process compiles the T-SQL source into a data model (dacpac) and optionally T-SQL script to create a new database. CREATE OR ALTER serves no purpose as data model source code.
Dan Guzman, Data Platform MVP, http://www.dbdelta.com
Wednesday, May 2, 2018 12:17 PM
In SSDT, you always write CREATE. The deployment automatically determines if the object exists, and converts your CREATE statement into an ALTER, dynamically.
Wednesday, May 30, 2018 5:32 PM | 1 vote
This is quite lame explanation why new DDL syntax available in SQL Server 2017 and 2016 (SP1) is not supported in SSDT.
I see no valid excuse why SSDT couldn't treat "CREATE OR ALTER" statement as "CREATE" when building dacpac data model, and allow new syntax to be used in VS DB projects.
Thursday, May 31, 2018 12:12 PM | 2 votes
I see no valid excuse why SSDT couldn't treat "CREATE OR ALTER" statement as "CREATE" when building dacpac data model, and allow new syntax to be used in VS DB projects.
Agreed. Similarly, I see no valid excuse why one would use `CREATE OR ALTER` in the project data model source code.
Dan Guzman, Data Platform MVP, http://www.dbdelta.com
Saturday, June 2, 2018 6:59 PM | 6 votes
It would be very useful when making quick changes to the local database by altering an existing object without having to publish the whole project.
Wednesday, June 19, 2019 11:28 AM
I see no valid excuse why SSDT couldn't treat "CREATE OR ALTER" statement as "CREATE" when building dacpac data model, and allow new syntax to be used in VS DB projects.
Agreed. Similarly, I see no valid excuse why one would use `CREATE OR ALTER` in the project data model source code.
Dan Guzman, Data Platform MVP, http://www.dbdelta.com
There is no contradiction in allowing "create or alter" to work. Why bother oppose the thought just because you do not work this way, nor understand it's value?
Sunday, June 23, 2019 2:15 PM
There is no contradiction in allowing "create or alter" to work. Why bother oppose the thought just because you do not work this way, nor understand it's value?
I am not completely opposed to CREATE or ALTER in database project source code (or even ALTER alone), which is why I included the word "agreed" in my response. However, I do see this is as a contradiction for the reasons I mentioned earlier with the mindset that T-SQL files in database projects are actually database model source code in familiar T-SQL format instead of scripts that one executes directly.
Without database project support of CREATE OR ALTER, one can still run T-SQL DDL scripts (CREATE, DROP, ALTER, CREATE OR ALTER) directly against a physical database (using SSMS or ADS) and then sync the target database project source code using SSDT schema compare. I've found this process works well with iterative local database development styles like TDD without needing a full build after each change and the same source code can be used in CI/CD pipelines.
Dan Guzman, Data Platform MVP, http://www.dbdelta.com
Thursday, May 28, 2020 1:18 PM
Agreed. Similarly, I see no valid excuse why one would use `CREATE OR ALTER` in the project data model source code.
Because you could then simply execute the very same script in SSMS or other clients.