StrataFrame Forum

Deploy data changes fails after first table

http://forum.strataframe.net/Topic9680.aspx

By Charles R Hankey - 6/20/2007

I created a profile from my VFP database and successfully deployed all these tables into a SQL Server 2005 database, including a PK and default value of NEWID() on the field CID ( UID )



There are about 100 tables, all have a pk named CID



As a test of my understanding of the DDT, I added a timestamp to the first 5 tables in the profile ( Timestamp datatype Timestamp NULL )



I did not see any option for only deploying some tables so I answered yes to create a package and deployed to server.



It appears the timestamp was added to the first table but after that the alter failed saying there was a constraint problem CID already existed ( see screenshot attached. ) and this error was repeated for every table in the database.



Obviously I'm missing a key concept here. Is there a problem with key fields of the same name with DDT or am I doing something else wrong ?



TIA
By Charles R Hankey - 6/20/2007

In order to simplify testing I created a new DDT package, creating a Database TestDDT2 with two tables: customers ( pk_Customers UID, cname VARCHAR(50)) and sales (pk_sales UID, dsale_date DT, yamount MONEY )



Deployed to server - new Database created successfully with PKs and NEWID as default



Now added timestamps to both tables



Sales table update fails with error saying pk_sales already exists.



2
2007 10:52:43 PM-> Deploying meta-data for database 'TestDDT2'

• 6/20/2007 10:52:43 PM-> Ensuring Filegroups exist on database 'TestDDT2'

• 6/20/2007 10:52:43 PM-> Filegroup verification complete.

• 6/20/2007 10:52:43 PM-> Ensuring Tables exist on database 'TestDDT2'

• 6/20/2007 10:52:43 PM-> The table 'Customers' is being prepared to be modified.

X 6/20/2007 10:52:44 PM-> Altering table 'Customers' failed.

X 6/20/2007 10:52:44 PM-> FailedOperationException

X 6/20/2007 10:52:44 PM-> Alter failed for Table 'dbo.Customers'.

X 6/20/2007 10:52:44 PM-> ExecutionFailureException

X 6/20/2007 10:52:44 PM-> An exception occurred while executing a Transact-SQL statement or batch.

X 6/20/2007 10:52:44 PM-> SqlException

X 6/20/2007 10:52:44 PM-> There is already an object named 'pk_customers' in the database.

X 6/20/2007 10:52:44 PM-> Could not create constraint. See previous errors.

X 6/20/2007 10:52:44 PM->

X 6/20/2007 10:52:44 PM-> Source : Microsoft.SqlServer.Smo

X 6/20/2007 10:52:44 PM->

X 6/20/2007 10:52:44 PM-> Stack Trace:

X 6/20/2007 10:52:44 PM-> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

X 6/20/2007 10:52:44 PM-> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

X 6/20/2007 10:52:44 PM-> at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

X 6/20/2007 10:52:44 PM-> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

X 6/20/2007 10:52:44 PM-> at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

X 6/20/2007 10:52:44 PM-> at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

X 6/20/2007 10:52:44 PM-> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

X 6/20/2007 10:52:44 PM-> at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

X 6/20/2007 10:52:44 PM-> at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)

X 6/20/2007 10:52:44 PM-> at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)

X 6/20/2007 10:52:44 PM-> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)

X 6/20/2007 10:52:44 PM-> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplFinish(StringCollection alterQuery, ScriptingOptions so)

X 6/20/2007 10:52:44 PM-> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplWorker()

X 6/20/2007 10:52:44 PM-> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()

X 6/20/2007 10:52:44 PM-> at Microsoft.SqlServer.Management.Smo.Table.Alter()

X 6/20/2007 10:52:44 PM-> at MicroFour.StrataFrame.DBEngine.SQL.DatabaseMigrator.AlterSmoObject(IAlterable ObjectToAlter, String ObjectName, DBEObjectType ObjectType)

X 6/20/2007 10:52:44 PM->

• 6/20/2007 10:52:44 PM-> The table 'Sales' is being prepared to be modified.

X 6/20/2007 10:52:45 PM-> Altering table 'Sales' failed.

X 6/20/2007 10:52:45 PM-> FailedOperationException

X 6/20/2007 10:52:45 PM-> Alter failed for Table 'dbo.Sales'.

X 6/20/2007 10:52:45 PM-> ExecutionFailureException

X 6/20/2007 10:52:45 PM-> An exception occurred while executing a Transact-SQL statement or batch.

X 6/20/2007 10:52:45 PM-> SqlException

X 6/20/2007 10:52:45 PM-> There is already an object named 'pk_Sales' in the database.

X 6/20/2007 10:52:45 PM-> Could not create constraint. See previous errors.

X 6/20/2007 10:52:45 PM->

X 6/20/2007 10:52:45 PM-> Source : Microsoft.SqlServer.Smo

X 6/20/2007 10:52:45 PM->

X 6/20/2007 10:52:45 PM-> Stack Trace:

X 6/20/2007 10:52:45 PM-> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

X 6/20/2007 10:52:45 PM-> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

X 6/20/2007 10:52:45 PM-> at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

X 6/20/2007 10:52:45 PM-> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

X 6/20/2007 10:52:45 PM-> at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

X 6/20/2007 10:52:45 PM-> at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

X 6/20/2007 10:52:45 PM-> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

X 6/20/2007 10:52:45 PM-> at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

X 6/20/2007 10:52:45 PM-> at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)

X 6/20/2007 10:52:45 PM-> at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)

X 6/20/2007 10:52:45 PM-> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)

X 6/20/2007 10:52:45 PM-> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplFinish(StringCollection alterQuery, ScriptingOptions so)

X 6/20/2007 10:52:45 PM-> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplWorker()

X 6/20/2007 10:52:45 PM-> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()

X 6/20/2007 10:52:45 PM-> at Microsoft.SqlServer.Management.Smo.Table.Alter()

X 6/20/2007 10:52:45 PM-> at MicroFour.StrataFrame.DBEngine.SQL.DatabaseMigrator.AlterSmoObject(IAlterable ObjectToAlter, String ObjectName, DBEObjectType ObjectType)

X 6/20/2007 10:52:45 PM->

• 6/20/2007 10:52:45 PM-> Table verification complete.

• 6/20/2007 10:52:45 PM-> Dropping unneeded filegroups for 'TestDDT2'

• 6/20/2007 10:52:45 PM-> Unneeded filegroups dropped on database 'TestDDT2'

• 6/20/2007 10:52:45 PM-> Ensuring Views exist on database 'TestDDT2'

• 6/20/2007 10:52:45 PM-> View verification complete.

• 6/20/2007 10:52:45 PM-> Ensuring Indexes exist on database 'TestDDT2'

• 6/20/2007 10:52:45 PM-> Index verification complete.

• 6/20/2007 10:52:45 PM-> Ensuring Foreign Keys exist on database 'TestDDT2'

• 6/20/2007 10:52:45 PM-> Foreign Key verification complete.

• 6/20/2007 10:52:45 PM-> Ensuring Assemblies exist on database 'TestDDT2'

• 6/20/2007 10:52:45 PM-> Assembly verification complete.

• 6/20/2007 10:52:45 PM-> Ensuring Stored Procedures exist on database 'TestDDT2'

• 6/20/2007 10:52:45 PM-> Stored Procedure verification complete.

• 6/20/2007 10:52:45 PM-> Finished verifying databases on server.

• 6/20/2007 10:52:45 PM-> The meta data conversion to the SQL Server has been completed.







What am I doing wrong ?



TIA
By Trent L. Taylor - 6/21/2007

Yeah, it sounds like you may not be providing a default value or NULL support.  You are correct in that something is not being specified since SMO does not like the results.  Can you post your test PKG file?

Also, there is no need to deploy only one or two tables.  The DDT has the intelligence to determine which tables need to be updated when deploying and leaves the others alone.

By Charles R Hankey - 6/21/2007

Here's the test package



I tried to keep the example simple. The two pks are NOT NULL and NEWID as default value



TIA
By StrataFrame Team - 6/22/2007

OK, here's the deal, the default constraint was never being added the first time because of a bug.  Then, when you re-deployed the package, it tried to add the default constraint, but we explicitly specified the constraint's name as the name of the field (rather than letting SMO create the name for us, like DF_{tablename}_{fieldname}, and the way you named your primary key, it was pk_{tablename}, which just happens to be the same name as the primary key constraint.  So, it wouldn't add both constraints.

So, I fixed it so that you can set the default value now on a PK (so it will be deployed the first time) and I fixed it so that when it re-deploys, it doesn't explicitly specify the name for the constraint.  Here are the DLLs that you'll need.  The only change was made to DBEngine, but the others are the same version and it might need the updated assembly meta-data.  The databasedtk.exe didn't change (all of the deployment is in DBEngine).

Just copy these to your C:\Program Files\Common Files\MicroFour\StrataFrame\ folder and then drag them into the C:\Windows\Assembly folder and you should be good to go.

By StrataFrame Team - 6/22/2007

I guess it would help if I posted the attachment, right?
By Charles R Hankey - 6/22/2007

Ben



Thank you very much. This particular bug was one that was biting me hard BigGrin



I'll install your post as instructed and let you know.



Charles
By Charles R Hankey - 6/22/2007

BigGrin



Worked like a charm. This is a very impressive tool. Allowed me to add columns, rename columns ( even PKs ) and add default values etc. This is good stuff.



One question : I notice that in creating a PK index the index is clustered. If I am using UID for a pk, do I want that ? I had understood that inserts would be much slower in large tables with clustered indexes on UIDs ( which would always be out of order ) and there would otherwise be no advantage in retrieval. True? And if so is there a way to change that behavior in DDTs creation of the initial database ?



TIA



Charles
By Randy Jean - 4/28/2008

I am having the same issue with defaults. I have the same field name in multiple tables. I simply add the default literal value I want in DDT. When I deploy the changes I get errors saying the default constraint already exists. None of the default constraints are being created with the DF_tablename_ prefix.



I really need a fix for this but I am already using the 1.61 dlls (dbengine.dll dated 11/19/07)



We haven't gone to 1.6.5 yet as we are in the process of trying to go live.



Snipped from log:



X 4/28/2008 6:42:39 PM-> Alter failed for Table 'dbo.AtlasData'.

X 4/28/2008 6:42:39 PM-> ExecutionFailureException

X 4/28/2008 6:42:39 PM-> An exception occurred while executing a Transact-SQL statement or batch.

X 4/28/2008 6:42:39 PM-> SqlException

X 4/28/2008 6:42:39 PM-> There is already an object named 'SetupKey' in the database.

X 4/28/2008 6:42:39 PM-> Could not create constraint. See previous errors.

X 4/28/2008 6:42:39 PM->

X 4/28/2008 6:42:39 PM-> Source : Microsoft.SqlServer.Smo

X 4/28/2008 6:42:39 PM->

X 4/28/2008 6:42:39 PM-> Stack Trace:

X 4/28/2008 6:42:39 PM-> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

X 4/28/2008 6:42:39 PM-> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

X 4/28/2008 6:42:39 PM-> at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

X 4/28/2008 6:42:39 PM-> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

X 4/28/2008 6:42:39 PM-> at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

X 4/28/2008 6:42:39 PM-> at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

X 4/28/2008 6:42:39 PM-> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

X 4/28/2008 6:42:39 PM-> at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

X 4/28/2008 6:42:39 PM-> at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)

X 4/28/2008 6:42:39 PM-> at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)

X 4/28/2008 6:42:39 PM-> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)

X 4/28/2008 6:42:39 PM-> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplFinish(StringCollection alterQuery, ScriptingOptions so)

X 4/28/2008 6:42:39 PM-> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplWorker()

X 4/28/2008 6:42:39 PM-> at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()

X 4/28/2008 6:42:39 PM-> at Microsoft.SqlServer.Management.Smo.Table.Alter()

X 4/28/2008 6:42:39 PM-> at MicroFour.StrataFrame.DBEngine.SQL.DatabaseMigrator.AlterSmoObject(IAlterable ObjectToAlter, String ObjectName, DBEObjectType ObjectType)


By Randy Jean - 4/28/2008

Hold on... I missed copying the updated dlls to my GAC I think... Let me try again tomorrow and let you know if I'm still having problems. Sorry if this was a false alarm.
By Randy Jean - 4/28/2008

Well, I couldn't wait so I remoted in to my box at the office, copied the dlls to the GAC, tried again and its still trying to create defaults with field name.



Let me back up.... this is for a database that we originally created using default objects bound to the columns. When we realized that these type of defaults were not supported by DDT I created a script to remove these defaults and bindings, imported the db into DDT again and got all the defaults set as literal constraints. Now I am having this problem trying to push the new type of default constraints out to our dev/staging db. The PK's seem OK as I don't have PK in my field names for those so when PK_ is added those are already unique. So its just normal column defaults I'm trying to get set correctly. We are in the habit of assigning defaults to every column rather than knowing for certain anything adding records will have defaults for each column (thus avoiding NULL constraint errors). Its pretty critical we get this working because this is for an app that will be using local SQL Express databases and we don't want to be making special scripts or having to have someone visit each workstation to apply db changes. We already have this app in the clients test environment on a couple workstations so its a good time to make sure these deployment issues are taken care of.

So I think I'm down to just these default constraint issues at the moment.
By Trent L. Taylor - 4/28/2008

Hmmmm.....give it a shot because I don't think that you are having a DDT related issue.  It sounds more like a permissions/missing or out of sync assmebly issue.  Let me know if you don't get it working.
By Randy Jean - 4/29/2008

Just tried again and still getting name conflicts.



Would the databaseDTK.exe have anything to do with this? Is there a maintenance release for this as well?



Again, just to clarify, in SQL if I create a default the constraint is created with DF_TableName_Fieldname - which is what I want. But, if I'm creating the default in DDT and then push my changes out using the package deployment wizard it tries to create the constraints just using fieldname and then has name conflicts. See attached.



Primary keys and their default constraints seem to be working fine. These appear to just be the defaults on normal columns and foreign keys.



Thanks,

Randy
By Randy Jean - 4/29/2008

I take that back about the PK defaults working correctly. I dropped the newid() defaults from one of these which must have already existed from my original db script, re-ran the deployment wizard and it re-created these constraints with only the fieldname as well. The PK_ key/index seems to be named correctly, however. So basically, all defaults are not creating the names the way SQL Management studio does. This is a pretty huge issue for us at the moment... I guess I'm willing to go to 1.6.5 if you think that's my best option at this point. We have 3 other SF developers working on different projects using the same strataframe project db so I have to make sure I co-ordinate with them. Just was hoping would not have to do this since it will force us to do full regression testing. This project falls under FDA regulation guidelines so lots of red tape for every change.
By Trent L. Taylor - 4/29/2008

I think that you are mistaken on how we set defaults.  We do in fact set defaults in the same manner as SQL Server.  So I was a bit confused what you meant by that and how you think that this is part of your deployment issue.

I don't know that moving to 1.6.5 would make a difference right now in this case.  We have a version of our medical software in the field that has been installed to over 500 sites using the version of the DDT and DtabaseMigrator (DBEngine) to which you are referring...so I know that it works.  Now, with that being said, like anything else, there are times when we have to deal with certain IT guys who just want to be difficult and think that the server is "theirs" instead of the customers and will tighten down the permissions, play with the structures, etc. and things like this may crop up.

In your case, I think that it is not the latter, but rather something within your package file.  If you just open the AtlasData table and resave the file, it should re-create your PK index.  I would be curious as to what it looked like before and after the save.  If you'll notice, there is a error regarding the SetupKey...is that your PrimaryKey field name?  It acts as though the column is defined more than once within the PK...so that would be the first thing I would look at. 

Past that, I will just need your package file so that I can see what is wrong for myself.  Obviously I would be importing it into a more recent version of the DDT, but I don't think that is what you are fighting.

By Randy Jean - 4/29/2008

This is not a permissions issue. And again, the PKs indexes are OK and I know about going in and touching these in DDT first, but the default constraint is being created without the DF_tablename as well.



I have a SQL2005 database in development. It matches EXACTLY what I have in DDT (because I imported from it) except for it doesn't have defaults on the non-PK fields. So, in DDT I simply went through and assigned default values. 0 for int, decimal. empty string for varchar, etc.

When I try to deploy back to the same DB that I imported from in development, I get these errors because I have field names that are the same name but in different tables. Maybe this isn't the best design, but these values do represent the same "thing" as far as the application is concerned, so it makes sense to me. Setupkey is a PK on one table but a foreign key in others. But, because of this, the constraint names are not unique - it doesn't matter that they are in different tables. SQL will not allow a constraint to have the same name as another object in the database.



DDT is NOT work like SQL. In SQL Management studio if I put a default value on any of these column it creates the corresponding constraint with DF_tablename_fieldname, thereby making the object name unique across the database. DDT just uses the field name. I don't think I should have to rename my fields just to use DDT. Again, the idea is to not have to touch these local SQL Express databases with scripts, etc. going forward so I'm trying my darndest to get DDT to work for us before this app goes into production.



I would prefer to email you the SQL scripts and DDT package. There are 2 scripts. One to create the DB as we have it deployed in our clients test at the moment, and another to strip the old type of defaults out (we were using the programmable default types before which apparently is not best practice anymore) Then, if you deploy the DDT package I have against this where I have added the literal defaults you will see what I mean. I don't know what else I can do at this point but if we can't resolve this and I have to script stuff still then DDT doesn't seem like it will work for us long term which would be a shame because other than this problem it seems promising that it could really cut down on deployment issues.
By Trent L. Taylor - 4/29/2008

Just FYI for those reading this post, there were issues with the profile and how the database from which the profile was imported came in.  We have been dealing with this via email and PMs.  I figured that there would be some curious individuals Smile
By Ivan George Borges - 4/29/2008

Can't believe it... you caught me! How do you do that? Tongue
By Randy Jean - 4/30/2008

Trent is trying to help me but we don't seem to be getting to the heart of the problem. If anyone else cares to test the following scenario (I've re-produced in 1.6.6) and can confirm or explain the issue better than I maybe we can get this resolved with some community help:



Create a new database in SQL 2005.



Create 2 tables with a PK and a few other fields – name the non-pk fields the SAME name and make the same type in both tables. DO NOT ADD defaults to the non-PK columns. Import this database into a new DDT project.



Add defaults to all of the non-PK fields in DDT, such as (‘’), etc.



Attempt to deploy this package to the existing SQL database.



You WILL get an error on the 2nd table when it tries to create the default constraints due to it using the field name rather than df_tablename_fieldname. Now, if you deploy this same DDT package to a new database, the constraints are created using the proper naming convention and these errors do not occur. However, we are trying to update an existing deployed database (actually, multiple local SQL Express databases) so re-creating the database to add the defaults is not an option and we're trying to avoid having to create scripts to do this.
By Trent L. Taylor - 4/30/2008

What are you defining as a PK field here...that could be the problem here.  Yesterday when I looked at your structures, you had unique clustered indexes, but no primary keys.  Do you have your PKs set as identity columns?  Is this the step I am missing here?
By Randy Jean - 4/30/2008

As I mentioned to you before the missing PK's in that database was a different issue in how that particular db was created. We have multiple copies of this DB floating around and in my rush to get you the MDF I didn't realize I was attaching a "bad" one. But, my problem again is not the PKs - the PK issues I had before seem to be resolved with the newest DDT version - my PKs are all UID and have defaults of newid() already in my current db and I don't need DDT to create those for me. I'm just trying to add defaults to existing fields using DDT but because some of my fields were named the same between multiple tables when I deploy the updated DDT package back to the existing db that doesn't have default values defined on the non-PK fields I get conflict errors and the defaults do not get added to some of the tables.
By Charles R Hankey - 4/30/2008

I've taken a lot of interest in this thread since I'm very interested in DDT and Randy Jean is a long-time (and highly accomplished) VFE user who I know to be saner than he sounds Tongue

I can reproduce the errors that Randy is talking about consistently.  They aren't the result of a 'bug' in DDT so much as the result of what I think might be a bug in SQL Server that could easily be worked around in DDT by using the same naming convention for constraints on default values that SQL uses and that DDT itself uses when creating the constraint at the same time as the field (  i.e. DF_<tablename> prepended to the field name )

First, this error can be reproduced entirely outside DDT.  Create database junk.  Create tables
table1 and table2.  pkfield (autoint) , cname (char(10), ncount (int) for both tables.

script constraints for table1 where ('') and (0) are applied to the non pk fields naming the constraint the same as the fieldname - no prepending

use same script only changing tablename to create constraints for the second table and you will get the error that an object named cname already exists in the database.

Probably shouldn't happen, but without the prepending it does.

When a constraint is added in DDT to a field that does not previously have a constraint DDT names the constraint exactly the same as the field name.  If two constraints are added to two different tables in the package on fields of the same name, the error will occur. 

I've reproduced this numerous times with numerous scenarios.  I get slightly different result from Randy in that when I follow his test steps of creating two tables with identical fields, no constraints, import them to DDT, add constraints, deploy I don't get the error.  But if I add two identical fields to the two tables in DDT, deploy, then add constraints and deploy I get the error every time when the second table tries to create the constraint.

I'm sure just changing the naming convention DDT uses in these scenarios to the standard SQL convention would fix it and if that's not a real big deal I'd say it would be greatly appreciated.

Meanwhile, is there a spot in the strataframe database (or in the package) where the constraint name that is going to be created can be tweaked prior to deployment?

I can see where this might be a showstopper for deploying these kinds of changes.

I'd be happy to reproduce all this in a 3 minute demo via logmein if anybody at Microfour would like to see it.  Smile

By Trent L. Taylor - 4/30/2008

Don't worry abou the video, we made a change to the DatabaseMigrator that should prevent this from happening.

In a scenario where the DDT is deploying to a database that existing before the DDT was used for deployment, an issue could occur where the default contraints are not properly created and try to use the name of the column name.  A workaround has been implemented within the DDT that will prevent this from happening.

Note: You will need to be on the 1.6.6 beta for this to work.

By Randy Jean - 4/30/2008

Thanks Trent!



For any lurkers there was some difficulty getting to where Trent could readily re-produce the problem which probably had a lot to do with I was sending him invalid scripts and MDF files to look at from my mucked up development environment which I have multiple copies of my database in various states of disrepair. Once we got through a simple example of how to re-produce the problem from scratch the fix was posted in minutes! Now that is what I call top notch service and support.
By Charles R Hankey - 4/30/2008

I completely agree with Randy.  This was a tough one because of the somewhat strange behavior of SQL server itself.  Very impressive that Microfour was able to resolve this so quickly.

I think DDT is a very significant tool and it just keeps getting better !

By Trent L. Taylor - 4/30/2008

Thanks for your comments, guys!  And glad we got to the bottom of it! Tongue