By Donovan Sobrero - 2/1/2008
We have an ERP software package with a number of database definiitons that we are hoping to correct. We're trying to use the Database Deployment Toolkit to deploy new definitions to our legacy databases used in the field by our customers. Our first attempt at converting the database schema to an updated schema has not been very sucessful. Primarily, one of the issues that is trying to be corrected is converting PK datatypes from Decimal(10,0) to Int. It appears based on the log error messages that the PK constraints are not dropped prior to the type change being applied. Is this something that the tool is meant to support? Thanks, Aaron
|
By Trent L. Taylor - 2/1/2008
Generally databases that have been updates with the DDT were initially created with the DDT...however, what you are attempting to accomplish can be done, but there are limitations (even in SQL Server) when converting certain data types from one to another. I would have to setup the Decimal to Int scenario to see if this produces any type of error. In some cases SQL Server Management Studio does some "smoke and mirrors" to convert certain things versus just relying on SMO. But in response to dropping the contraints, we actually do this so that PKs and structure changes can take place. So this would really be a matter of us getting our hands on your existing structure and the package file so that we can see what is actually going on and if there is something that we can do in the deployment phase to take some unique circumstance into account.
|
By Donovan Sobrero - 2/1/2008
How do we go about getting this to you (sorry, newbie here)? What do you require the Package definition and a backup of the legacy database to be converted?Thanks, Aaron
|
By Trent L. Taylor - 2/1/2008
You can just post it as an attachment here on the forum if you are comfortable with that. If not, then just PM (Private Message) me and send it to me that way. Just package up the legacy database as a backup or mdf and then include your PKG file as well.
|
By Donovan Sobrero - 2/1/2008
Here is the pkg file.
|
By Donovan Sobrero - 2/1/2008
Here is a backup of the SQL db.
|
By Trent L. Taylor - 2/1/2008
You did not attach your SQL Server backup.
|
By Donovan Sobrero - 2/1/2008
I have tried to attach both a .rar and .zip but the browser keeps timing out. I will try and send it to you via PM.
|
By Trent L. Taylor - 2/1/2008
Sounds good.
|
By Trent L. Taylor - 2/5/2008
OK...you have a massive database which made it difficult to even test what you were talking about. So I created a sample profile that was initially decimal PKs and FKs, created some records, then changed the PKs and FKs to integer and deployed. Everything worked as it should...so....before I even try to get into your structures, as this was going to take an inordinate amount of time to try and discern what is going on with your DB, try deploying these package files to see if you they work. First deploy the DecToIn.pkg file, open up the database and add some records through SQL Server Management Studio, then deploy the DecToInt_IntegerPks.pkg file. One thing that we have always done is drop the foreign keys before making changes...this is a requirement before we make any changes otherwise you could never update a PK or FK field.Next, I would see if you package file deploys to a new instance without issue. If it does, then next, get one or two tables that fail for your existing database and let's start there instead of trying to "eat the whole elephant at once." To work through the issue a smaller test sample would be preferred as this is such a large sample that even getting to a point of failure and setting up a test scenario will take a lot of time and we may be chasing our tail versus really focusing on the issue at hand. So let's start from here and see what we can determine. Thanks for your patience. 
|
By Donovan Sobrero - 2/11/2008
Hello,I tried to run the pkg you post and am still seeing errors that I am confused about. I have attached the log files. Please advise.
|
By Trent L. Taylor - 2/12/2008
This issue has nothing to do with you decimal to integer stuff. You have deletions in your Object Historh which is removing file groups and/or databases which is causing the issue. In the DDT, we keep up with a history of the deletions and renames. So if you deleted a file group, database, table, or renamed a field and then named it back, you can run into this type of error. Check the Object Name History and remove anything that is not supposed to be there.In the next update, the DDT is going to have a checking mechanism to warn you about this type of thing since so many people have a hard time with this. It is a required component as it allows structures to be updated from version 1 to 3 in a single step withou having to load multiple updates sequentially. But this is one of those side effects. Also, be sure to get the most recent version of 1.6.5 as there were quite a few enhancements made to it the other day (there will be more in the next update as well). http://forum.strataframe.net/FindPost14258.aspx
|
By Keith Chisarik - 2/12/2008
heh, I cant count the number of times I forgot to check that since is usually happens months apart when designing new databases.
|
By Trent L. Taylor - 2/12/2008
Yeah, it happened to me the other day too...so we are going to add a warning when you build giving you a heads up!
|
By Donovan Sobrero - 2/28/2008
Hello,How do I do this: So if you deleted a file group, database, table, or renamed a field and then named it back, you can run into this type of error. Check the Object Name History and remove anything that is not supposed to be there. Cheers
|
By Trent L. Taylor - 2/28/2008
First, download the latest 1.6.5 beta, it will tell you where all of your problems are if they exist (http://forum.strataframe.net/FindPost14525.aspx). When you build your package, it now perform a check and show you if there are object name history items that could "hurt" you . If so, simply go to the Object Name History node in the profile treeview and select the items in the list (on the right). Then right-click the items and delete them...be sure not to clear the history altogether if this is not what you need. Delete them one at a time in this case.
|