Auto Generation of Stored Procs

Geoff Hirst — Tue, 22 Apr 2008 09:48:22 GMT

Guys,

Got an issue with the DDT that came about because I wanted to change clustering on some tables to some new indexed columns. Got myself into a right state with it. However, I have discovered this and I don't know if this is by design.

In the DDT create a new table and check boxes for auto proc generation.

Add an ID, integer, not PK, but enable Identity with standard 1,1 incrementation.

Add a couple of varchar 10 columns.

Save structure, then try to deploy.

For me, it all goes south when it tries to create the sprocs for some reason it thinks the Identity column is being updated.

Now, I found a patch version of DDT on another post that looked like it might solve my issues, but I got errors when I launched it.

Any advice would be appreciated.

thanks

Geoff Hirst

Log File Extract

20/04/2008 17:00:46 -> An error occurred while creating the stored procedure [dbo].[zzz_TestTable_Delete].
X 20/04/2008 17:00:46 -> SqlException
X 20/04/2008 17:00:46 ->   Incorrect syntax near ';'.
X 20/04/2008 17:00:46 ->
X 20/04/2008 17:00:46 -> Source     : .Net SqlClient Data Provider
X 20/04/2008 17:00:46 ->
X 20/04/2008 17:00:46 -> Stack Trace:
X 20/04/2008 17:00:46 ->    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
X 20/04/2008 17:00:46 ->    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
X 20/04/2008 17:00:46 ->    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
X 20/04/2008 17:00:46 ->    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
X 20/04/2008 17:00:46 ->    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
X 20/04/2008 17:00:46 ->    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
X 20/04/2008 17:00:46 ->    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
X 20/04/2008 17:00:46 ->    at MicroFour.StrataFrame.DBEngine.SQL.DatabaseMigrator.DeploySprocsOfPriority(Database parent, DatabaseInfo dbInfo, Int32 priority)
X 20/04/2008 17:00:46 ->
X 20/04/2008 17:00:46 -> An error occurred while creating the stored procedure [dbo].[zzz_TestTable_Update].
X 20/04/2008 17:00:46 -> SqlException
X 20/04/2008 17:00:46 ->   Cannot update identity column 'ID'.
X 20/04/2008 17:00:46 ->
X 20/04/2008 17:00:46 -> Source     : .Net SqlClient Data Provider
X 20/04/2008 17:00:46 ->
X 20/04/2008 17:00:46 -> Stack Trace:
X 20/04/2008 17:00:46 ->    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
X 20/04/2008 17:00:46 ->    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
X 20/04/2008 17:00:46 ->    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
X 20/04/2008 17:00:46 ->    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
X 20/04/2008 17:00:46 ->    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
X 20/04/2008 17:00:46 ->    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
X 20/04/2008 17:00:46 ->    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
X 20/04/2008 17:00:46 ->    at MicroFour.StrataFrame.DBEngine.SQL.DatabaseMigrator.DeploySprocsOfPriority(Database parent, DatabaseInfo dbInfo, Int32 priority)
X 20/04/2008 17:00:46 ->

RE: Auto Generation of Stored Procs

Trent L. Taylor — Tue, 22 Apr 2008 09:48:22 GMT

Necessity is the mother of invention :D Thanks!

RE: Auto Generation of Stored Procs

Geoff Hirst — Tue, 22 Apr 2008 09:07:38 GMT

1.6.6. ROCKS!!!!

Thanks guys.

Pre-deployment scripts - GENIUS!

RE: Auto Generation of Stored Procs

Trent L. Taylor — Mon, 21 Apr 2008 09:58:28 GMT

Send me your package file. There is something wrong within the package file. I don't think that this is a DDT bug but rather something within your package file causing the issue. We have some profiles that create hundreds of automated sprocs so I know that this part works, but there could be something within your meta-data that it doesn't like.

RE: Auto Generation of Stored Procs

Geoff Hirst — Mon, 21 Apr 2008 05:01:47 GMT

Trent,

Attached is a screenshot of my version screen.

thanks

Geoff.

RE: Auto Generation of Stored Procs

Geoff Hirst — Mon, 21 Apr 2008 04:43:49 GMT

Hi Trent,

Thanks for response.

Have now updated to 1.6.6.Beta. Issue still exists.

Please advise what I need to do next.

best regards

Geoff Hirst

RE: Auto Generation of Stored Procs

Trent L. Taylor — Sun, 20 Apr 2008 13:26:38 GMT

This is a very common practice and I know that it will work. So instead of trying to figure out your version, what assembly you are running, etc. Load the 1.6.6 beta which will ensure that we are on the same playing field when talking about all of this. This is a very stable build and is what we have been using in the field for some time, so you shouldn't have any issues. You can get it here: http://forum.strataframe.net/FindPost15687.aspx

StrataFrame Forum » StrataFrame Database Deployment Toolkit » Issues » Auto Generation of Stored Procs

Auto Generation of Stored Procs

RE: Auto Generation of Stored Procs

RE: Auto Generation of Stored Procs

RE: Auto Generation of Stored Procs

RE: Auto Generation of Stored Procs

RE: Auto Generation of Stored Procs

RE: Auto Generation of Stored Procs