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

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.

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

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.

Here is the pkg file.

Here is a backup of the SQL db.

You did not attach your SQL Server backup.

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.

Sounds good.

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.