|
Charles R Hankey
|
|
|
Group: Forum Members
Posts: 524,
Visits: 30K
|
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
|
|
|
|
|
Charles R Hankey
|
|
|
Group: Forum Members
Posts: 524,
Visits: 30K
|
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
|
|
|
|
|
Trent Taylor
|
|
|
Group: StrataFrame Developers
Posts: 6.6K,
Visits: 6.9K
|
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.
|
|
|
|
|
Charles R Hankey
|
|
|
Group: Forum Members
Posts: 524,
Visits: 30K
|
Here's the test package
I tried to keep the example simple. The two pks are NOT NULL and NEWID as default value
TIA
|
|
|
|
|
StrataFrame Team
|
|
|
Group: StrataFrame Developers
Posts: 3K,
Visits: 2.5K
|
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.
|
|
|
|
|
StrataFrame Team
|
|
|
Group: StrataFrame Developers
Posts: 3K,
Visits: 2.5K
|
I guess it would help if I posted the attachment, right?
|
|
|
|
|
Charles R Hankey
|
|
|
Group: Forum Members
Posts: 524,
Visits: 30K
|
Ben Thank you very much. This particular bug was one that was biting me hard  I'll install your post as instructed and let you know. Charles
|
|
|
|
|
Charles R Hankey
|
|
|
Group: Forum Members
Posts: 524,
Visits: 30K
|
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
|
|
|
|
|
Randy Jean
|
|
|
Group: StrataFrame Users
Posts: 105,
Visits: 641
|
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)
|
|
|
|
|
Randy Jean
|
|
|
Group: StrataFrame Users
Posts: 105,
Visits: 641
|
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.
|
|
|
|