By Donovan Sobrero - 9/24/2008
Here are my tables:
CREATE TABLE TestCustomer
(
CustomerID INTEGER IDENTITY CONSTRAINT IX_TestCustomer_00 PRIMARY KEY CLUSTERED,
CustomerCode VARCHAR(10),
CustomerName VARCHAR(100),
Active CHAR(1)
)
GO
CREATE UNIQUE INDEX IX_Customer_01 ON TestCustomer(CustomerCode,CustomerID)
go
CREATE TABLE TestCustomer1
(
CustomerID INTEGER IDENTITY CONSTRAINT IX_TestCustomer1_00 PRIMARY KEY CLUSTERED,
CustomerCode VARCHAR(10),
CustomerName VARCHAR(100),
Active CHAR(1)
)
GO
CREATE UNIQUE INDEX IX_Customer_01 ON TestCustomer1(CustomerCode)
After I import the tables I get more than one primary key  . It seems to be a bug in the import tool. I am still using v1.6.0 
|
By Donovan Sobrero - 9/24/2008
Just adding a few details to this post.We are able to create database definitions from both examples using 'Import from SQL Database' feature. However when we look at the index definitions, in the 1st example it lists both indexes as type 'primary'. In the 2nd example it lists the 1st index as primary and the 2nd index as unique and correctly so. When we use this definition package to synchronize a target database, the 1st example fails because the SQL Server refuses to create a 2nd primary key. The 2nd example works. In our case about 80% of our tables have the issue we face in the 1st example. We appreciate your responsive support.
|
By Trent L. Taylor - 9/24/2008
Well, there have been a phenominal number of changes to the import and the DDT between 1.6.0 and 1.6.6...so before we even get into any of this it is important that we are all on the same version here, otherwise this is just an effort that will go in circles.Secondly, the import tool is intended to get you most of the way there and then for you to tweak the meta-data. Finally, in 1.6.6 there is a pre and post import procedure that can be implemented to create any T-SQL logic for pre and post deployments which allows any "sins" to be covered for pre-existing deployments.
|
By Donovan Sobrero - 9/25/2008
Hello,
I have updated to v1.6.6 and am still getting the import error.
Here is the SQL script:
CREATE TABLE TestCustomer
(
CustomerID INTEGER IDENTITY CONSTRAINT IX_TestCustomer_00 PRIMARY KEY CLUSTERED,
CustomerCode VARCHAR(10),
CustomerName VARCHAR(100),
Active CHAR(1)
)
GO
CREATE UNIQUE INDEX IX_TestCustomer_01 ON TestCustomer(CustomerCode,CustomerID)
I have attached the results after the import.
As you can see the TestCustomer table now has two primary keys. I manually created a new index using the same Fields that the imported index had and the DDT tool allowed me to create it. So I think that this maybe an import bug.
|
By Trent L. Taylor - 9/25/2008
Posting the error doesn't do me any good. Now that you are on the most recent version we can at least test. First of all, like I mentioned, the import is to get you close and then you are to tweak the meta-data. So once you fix this one time you will never have this issue again. At this point I am pretty confident that you could have probably fixed all of your changes and been further down the road. Now I do appreaciate you letting us know that there may be an issue, but in order for me to do anything about this I have to be able to reproduce it, so I will need a copy of the database that you are importing so that I can get it in a reproducable state on this side. Thanks.
|
By Donovan Sobrero - 9/25/2008
I have attached our test DB for you to test with.
|
By Dustin Taylor - 9/26/2008
It's not an import bug, that is just how the indexes are flagged on an import. If, on an import, the index contains an identity field and is set to unique, it will be marked as a primary index. Your customers table has two indexes, both contain the identify field, both are marked as unique, so both come in as primary indexes. Your customer1 table has two indexes, but only one contains the indentiy field and, as such, it is the only one that comes in as a primary index.The 'primary' marker is only used for automatically generated indexes or indexes brought in via an import to indicate indexes identified as primary by SMO or unique indexes that include identity fields. You can have more than one of them in some scenarios (as in your Customers table), and after testing the import on your sample table, they came in as expected based on the way you defined the tables in SQL. It is important to note that these are indexes, not fields. The table didn't come in with two primary keys. It came in with two 'primary' indexes, which would be expected based on the indexes you have defined on your tables.
|
By Donovan Sobrero - 9/26/2008
Hi,
When you try to deploy the imported tables as is you get this error:
X 9/26/2008 9:11:46 AM -> Creating index 'IX_TestCustomer_01' failed.
X 9/26/2008 9:11:46 AM -> FailedOperationException
X 9/26/2008 9:11:46 AM -> ExecuteNonQuery failed for Database 'TestDB'.
X 9/26/2008 9:11:46 AM -> ExecutionFailureException
X 9/26/2008 9:11:46 AM -> An exception occurred while executing a Transact-SQL statement or batch.
X 9/26/2008 9:11:46 AM -> SqlException
X 9/26/2008 9:11:46 AM -> Table 'TestCustomer' already has a primary key defined on it.
This is our main concern we have hundreds of tables that get imported like these test tables and to manually fix them all would be too time consuming. How can I get around this problem?
|
By Donovan Sobrero - 10/2/2008
Any update on whether you were able to reproduce the issue ?
|
By Dustin Taylor - 10/3/2008
I do see what you are talking about on the deployment now. The best solution here would be to fix your meta-data in the DDT to line up as desired so that your deployments can proceed without issue. We try to get you as close as possible with the automation on the import, but in your specific scenario the DDT can't decern which index you want to be primary and, as such, is creating two primary index on two tables. In that case, simply delete the one you don't wish to be a primary index and recreate it manually. This would be a one time fix on your part, once you have all the indexes limited to one primary index per table within the DDT, your deployments should work without issue from that point forward.
|
By Donovan Sobrero - 10/3/2008
We appreciate you taking time in looking into this. We considered implementing your solution. But in our schema, it is not going to be practical. We have 1700 indexes to manually delete and recreate. Is there any other option you have to automate this process of deleting an index and recreating as a non-primary index?We are even open to manually changing your DDT files (if they are in XML format or any such format?) We would love to use your tool but for this initial hiccup of getting a clean definition. 
|
By Donovan Sobrero - 10/3/2008
Since we have 1700 indexes to mark as unique, not as primary, we are trying to automatically correct the definition This is what we tried. 1. We packaged the definition to a .pkg File. 2. We extracted the Indexes.xml file alone to an output folder 3. We deleted the Indexes.xml from the package 4. In the extracted indexes.xml, we manually corrected the value of idx_type element to 1 from 0. (We inferred that 0-Primary, 1-Unique, 0-Index) 5. We added the edited indexes.xml into the package 6. Now when we try to extract the same file again or import a new database defintion from this deployment package, We get an error. (Key already added to a dictionary..blah blah blah..) Could you please confirm is this a plausible way of accomplishing what we want? Are we close? What are we missing? Any Help is deeply appreciated!
|
By Dustin Taylor - 10/6/2008
Yeah, fixing 1700 manually would take a while, wouldn't it  . Trent is re-building the install to include a change we just made to the DDT to handle this for you. We added a check box to the SQL import that you can uncheck to prevent that second index from being flagged as primary. The problem manifests because we check both on the IndexKeyType of the imported sql schema, and on the column states (since the IndexKeyType isn't 100% reliable.) Since your second indexed "looked" like a primary index, it was getting brought in as a second primary index, which caused the issue on the deployment. In your scenario, unchecking the new "Unique Inddex w/ Entitiy Column is Primary" check box in the SQL Database Import will prevent this from happening. 
We'll post the new build in the Posted Releases section once it is finished packaging, and I'll put a reply here as soon as it is up.
|
By Dustin Taylor - 10/6/2008
The new release is posted [url="http://forum.strataframe.net/Topic19902-22-1.aspx"]here[/i]. Load that and re-try your import. Be sure to uncheck the "Unique Inddex w/ Entitiy Column is Primary" check box from the SQL database options when you run the import wizard.
|
By Donovan Sobrero - 10/6/2008
Thanks very much for the quick turnaround. We downloaded the fix and tested it on a test database and it worked!! We appreciate your support.
|
By Donovan Sobrero - 10/6/2008
Hello,I know have the DB imported correctly. My next test was to deploy the newly imported structure to an empty SQL db. I now get an IndexOutofRange error. I have attached a screen shot. Thanks,
|
By Trent L. Taylor - 10/7/2008
This actually is not a bug but rather how you are attempting to gather data in one of your data deployment packages. The first thing I would recommend is removing them (data deployment packages) or look at your queries and make sure that you are pulling legal data that matches the strucures, etc. If you do not know which one is causing your problem then just remove them one by one until the error goes away and then add it back so that you have a firm understanding of which data deployment package is causing your problem.
|