All geek questions in one place

VS2010 database project generates SQL03070 error with ALTER statements

I struggled for a while with the newly created database project for SQL 2008 R2 database server in VS2010. I am not a database developer / administrator, but, as is often the case in IT, this turned out to be on my desk.

I created scripts for tables, views, stored procedures and functions using SSMS => Tasks => Generate script functions. No matter what options I choose to generate the script, I always have some problems, that is, as soon as I use the IF NOT EXISTS option, I get error SQL03070: "This statement is not recognized in this context."

I also get the same error when I change CREATE [...] to ALTER [...] . After publication on the Microsoft Connect Website, this is to be expected, since "the database design does not allow changes, since it will dynamically determine whether ALTER or CREATE is needed for a specific database purpose." Good with me, but when I select Execute SQL from the context menu, I get an error message if a specific object already exists in the database - I was expecting the ALTER expression to be dynamically generated in this case.

Unfortunately, due to the large amount of obsolete code / objects in the database, I cannot create or run a full deployment of the script, so the main idea is to use VS2010 with TFS in order to have at least some of the benefits of version control when changing objects in the database data.

It always works in error SQL03070, although this is not an option, and importing a database using Import Database Objects and Settings from VS2010 also does not work as I expected, since I do not have a single file, for example. table definition (with keys, indexes, etc.), but several files that are difficult to process.

The last parameter setting the build action for Not in Build scripts makes it impossible to parse the code in these files, which we would also like to do.

So basically, my question is what would be the best approach for our work on using database projects / TFS from VS2010 without SQL03070 errors, but with code analysis? I feel rather confused and apologize if some aspects of my problem may not be clear enough, please feel free to ask for details and I will try to answer as best as possible.

Thank you in advance

G.

+4
source share
1 answer

The idea of ​​database projects is that scripts generate an SQL file when you build Deploy (available from the build menu). Before you do this, you need to "tune" a specific database. This can be done through the "Deployment" tab in the project properties. Then it gets the Target database schema, and then it gets your project schema and generates the differences script to make the target database the same as your project. Hope this makes sense.

This is a good article that explains it a little better.

http://blogs.msdn.com/b/gertd/archive/2009/06/05/declarative-database-development.aspx

Best of luck

WITH

+1
source

Source: https://habr.com/ru/post/1390066/

More articles:


All Articles