We have an automated build process via TFS, TFS database projects, and vsdbcmd. When deploying the database project to our database server, the generated SQL script attempts to 'ALTER' certain stored procedures, even though those stored procedures do not net yet exist in the target database. Instead, the SQL script schould contain 'CREATE' statements for those stored procedures. This obviously causes the database deployment to fail, as there is no way to 'ALTER' a stored procedure that does not exist.
Does anyone have any ideas about what may be causing this, or how it can be fixed?
Found out what was wrong: In TFS, the table definition did not have a schema prefix. So instead of (for example)
CREATE TABLE [dbo][TableName]
it was
CREATE TABLE [TableName]
The lack of a schema specified meant that when QA ran vsdbcmd, the schema assigned to the table was the default schema of the individual running vsdbcmd. So what was actually created was effectively as if we had specified:
CREATE TABLE [QAUser_SCHEMA].[TableName]
This caused vsdbcmd, when later run by another individual whose default schema was [dbo] to receive the error that we saw, basically generating an ALTER statement because the stored procedure had already been created, although under a different schema.
One would think that even with the incorrect schema specified initially, that once the [dbo] schema was specified for the procedure that it would be considered a "different" procedure, however that was not the case. Dropping the original, version of the procedure (the one with the [QAUser_SCHEMA]), and then re-running vsdbcmd solved the problem.
TLDR; Always prefix database objects in your database projects with a schema name.