Hi,

I'm using trial version of Database Deployment Toolkit. I'm using automatical creation of stored procedures. When deploying to SQL server for first time, everything is running correctly. When deploying the same database to server again, I receive an error stating that "An error occurred while creating the stored procedure XY" and that "There is already an object named XY" - for all stored procedures. When I delete these procedures from SQL server, it runs correctly.

Is there any possibility to include something like "If exists then drop..."? Or is there any workaround?

Thanks

George

Please try to delete your "Object Name History" in the DDT, that sounds like the problem or at least where to start.

Keith Chisarik

I have cleared history, then tried deploy ones more but the result is the same - for each stored procedure I receive result similar to this on:

X 20.3.2008 14:55:21 -> An error occurred while creating the stored procedure [M01].[ProductType_Delete].
X 20.3.2008 14:55:21 -> SqlException       
X 20.3.2008 14:55:21 ->   There is already an object named 'ProductType_Delete' in the database.       
X 20.3.2008 14:55:21 ->        
X 20.3.2008 14:55:21 -> Source     : .Net SqlClient Data Provider       
X 20.3.2008 14:55:21 ->        
X 20.3.2008 14:55:21 -> Stack Trace:        
X 20.3.2008 14:55:21 ->    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)       
X 20.3.2008 14:55:21 ->    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)       
X 20.3.2008 14:55:21 ->    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)       
X 20.3.2008 14:55:21 ->    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)       
X 20.3.2008 14:55:21 ->    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)       
X 20.3.2008 14:55:21 ->    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)       
X 20.3.2008 14:55:21 ->    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()       
X 20.3.2008 14:55:21 ->    at MicroFour.StrataFrame.DBEngine.SQL.DatabaseMigrator.DeploySprocsOfPriority(Database parent, DatabaseInfo dbInfo, Int32 priority)       
X 20.3.2008 14:55:21 ->        

This logic already exists   You are fighting something else.

First, as Keith mentioned, you can check your Object Name History.  However, I do not think that is your issue here.  It sounds to me like you have one schema name deployed on the server (i.e. dbo) and are trying to deploy a different schema (M01).  This can cause an issue.

In the 1.6.5 build we changed the logic of the DDT so that IT people can add their own sprocs, views, and UDFs to a database and they will be left alone byt eh DDT.  Previously we deleted them all.  We now set an extended property on the object so that we can tell which were deployed by the DDT.  The other problem you could be fighting here is that the sproc ProductType_Delete was manually added on the database and then added into the DDT...which this could potentially cause the error.  Finally, the last thing to check, is if you have a table name called ProductType and are allowing the DDT to created the DELETE sproc for you and you also have a sproc of this name...this would create a conflict and could present this error.

One thing to always try and do when you run into things like this is to deploy to a fresh database name (one that doesn't exist) or server so that you can troubleshoot the problem more easily.

If you can't get any of this to work, post your package file here and we will take a look at it and tell you where your issue is.

First, as Keith mentioned, you can check your Object Name History.  However, I do not think that is your issue here.  It sounds to me like you have one schema name deployed on the server (i.e. dbo) and are trying to deploy a different schema (M01).  This can cause an issue.

- I have created whole database design by DDT - I haven't set anything on SQL server side. Actually my database contains table in 4 different schemas (correspoding to logic application modules)

In the 1.6.5 build we changed the logic of the DDT so that IT people can add their own sprocs, views, and UDFs to a database and they will be left alone byt eh DDT.  Previously we deleted them all.  We now set an extended property on the object so that we can tell which were deployed by the DDT.  The other problem you could be fighting here is that the sproc ProductType_Delete was manually added on the database and then added into the DDT...which this could potentially cause the error. 

- No, I haven't created any SP manually - all stored procedures has been created by DDT when checking "Create ... Stored procedure" in table properties

Finally, the last thing to check, is if you have a table name called ProductType and are allowing the DDT to created the DELETE sproc for you and you also have a sproc of this name...this would create a conflict and could present this error.

- As I have mentioned before, I have created tables and everything else was created automatically. I do not know what and where to set.

One thing to always try and do when you run into things like this is to deploy to a fresh database name (one that doesn't exist) or server so that you can troubleshoot the problem more easily.

- Well, I have deleted the database from SQL server. Then used DDT to deploy the project to database - everything succesfull, no error. The I tried to deploy the same project again - everything successfull (tables, indexes, primary keys etc.) - except stored procedures. The behavior is the same that I have described before.

If you can't get any of this to work, post your package file here and we will take a look at it and tell you where your issue is.

- I have attached my project and resulting log.

Thank You for Your assistence

Apparently a new bug has been introduced with 1.6.5 when using funny schema names.  We made a change to prevent all views, sprocs, and UDFs from being deleted and only remove the ones deployed by the DDT.  The problem is that the extended properties are not repsecting the schema name.  We have reproduced the problem and will post a fix once we get to the bottom of the issue.

Thank You for info. I'm going to wait for the new version :-)

Here is the fix: http://forum.strataframe.net/FindPost15092.aspx 

Thank You for the fix. I have applied it and tried to run deployment again. However - new error has occurred when applying on the existing database:

• 22.3.2008 10:19:04 -> Deploying meta-data for database 'RCEL_Protocols'
• 22.3.2008 10:19:05 -> Ensuring Filegroups exist on database 'RCEL_Protocols'
• 22.3.2008 10:19:05 -> Filegroup verification complete.
• 22.3.2008 10:19:05 -> Ensuring Tables exist on database 'RCEL_Protocols'
• 22.3.2008 10:19:05 -> Table verification complete.
• 22.3.2008 10:19:05 -> Dropping unneeded filegroups for 'RCEL_Protocols'
• 22.3.2008 10:19:05 -> Unneeded filegroups dropped on database 'RCEL_Protocols'
• 22.3.2008 10:19:05 -> Ensuring Indexes exist on database 'RCEL_Protocols'
• 22.3.2008 10:19:05 -> Index verification complete.
• 22.3.2008 10:19:05 -> Ensuring Foreign Keys exist on database 'RCEL_Protocols'
• 22.3.2008 10:19:06 -> Foreign Key verification complete.
• 22.3.2008 10:19:06 -> Ensuring Assemblies exist on database 'RCEL_Protocols'
• 22.3.2008 10:19:06 -> Assembly verification complete.
• 22.3.2008 10:19:06 -> Ensuring Views, Stored Procedures, and UDFs exist on database 'RCEL_Protocols'
X 22.3.2008 10:19:06 -> An error occurred while deploying the meta-data to the server.
X 22.3.2008 10:19:06 -> ArgumentException       
X 22.3.2008 10:19:06 ->   Column 'Type' does not belong to table .       
X 22.3.2008 10:19:06 ->        
X 22.3.2008 10:19:06 -> Source     : System.Data       
X 22.3.2008 10:19:06 ->        
X 22.3.2008 10:19:06 -> Stack Trace:        
X 22.3.2008 10:19:06 ->    at System.Data.DataRow.GetDataColumn(String columnName)       
X 22.3.2008 10:19:06 ->    at System.Data.DataRow.get_Item(String columnName)       
X 22.3.2008 10:19:06 ->    at MicroFour.StrataFrame.DBEngine.SQL.DatabaseMigrator.DropAllDeployedViewsSprocsAndUdfs(DatabaseInfo dbInfo)       
X 22.3.2008 10:19:06 ->    at MicroFour.StrataFrame.DBEngine.SQL.DatabaseMigrator.EnsureViewsSprocsUdfs(Database parent, DatabaseInfo dbInfo)       
X 22.3.2008 10:19:06 ->    at MicroFour.StrataFrame.DBEngine.SQL.DatabaseMigrator.EnsureDatabases()       
X 22.3.2008 10:19:06 ->    at MicroFour.StrataFrame.DBEngine.SQL.DatabaseMigrator.DeployMetaDataThread()       
X 22.3.2008 10:19:06 -> 

Then the execution doesn't stop but doesn't continue too.

I have then dropped the old database and tried new deployment - succesfully. Then again and again succesfully :-)

Thank You for this fix, it works!

Yeah, you got if figured out.  Glad to help