Deploying to server - error when creating stored procedures


Author
Message
Jiri Brazda
Jiri Brazda
StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)
Group: StrataFrame Users
Posts: 78, Visits: 356
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

Keith Chisarik
Keith Chisarik
StrataFrame VIP (1K reputation)StrataFrame VIP (1K reputation)StrataFrame VIP (1K reputation)StrataFrame VIP (1K reputation)StrataFrame VIP (1K reputation)StrataFrame VIP (1K reputation)StrataFrame VIP (1K reputation)StrataFrame VIP (1K reputation)StrataFrame VIP (1K reputation)
Group: StrataFrame Users
Posts: 939, Visits: 40K
Please try to delete your "Object Name History" in the DDT, that sounds like the problem or at least where to start.

Keith Chisarik
Jiri Brazda
Jiri Brazda
StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)
Group: StrataFrame Users
Posts: 78, Visits: 356
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 ->        

Trent Taylor
Trent Taylor
StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)
Group: StrataFrame Developers
Posts: 6.6K, Visits: 6.9K
Is there any possibility to include something like "If exists then drop..."? Or is there any workaround?

This logic already exists Smile  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.

Jiri Brazda
Jiri Brazda
StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)
Group: StrataFrame Users
Posts: 78, Visits: 356
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

Attachments
RCEL_Protocol.pkg (301 views, 18.00 KB)
RCEL_Protocol_results.txt (169 views, 39.00 KB)
Trent Taylor
Trent Taylor
StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)
Group: StrataFrame Developers
Posts: 6.6K, Visits: 6.9K
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.
Jiri Brazda
Jiri Brazda
StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)
Group: StrataFrame Users
Posts: 78, Visits: 356
Thank You for info. I'm going to wait for the new version :-)
Trent Taylor
Trent Taylor
StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)
Group: StrataFrame Developers
Posts: 6.6K, Visits: 6.9K
Jiri Brazda
Jiri Brazda
StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)StrataFrame Novice (78 reputation)
Group: StrataFrame Users
Posts: 78, Visits: 356
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!

Trent Taylor
Trent Taylor
StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)StrataFrame Developer (7.1K reputation)
Group: StrataFrame Developers
Posts: 6.6K, Visits: 6.9K
Yeah, you got if figured out.  Glad to help Smile
GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...




Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search