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

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.

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.

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.

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.

I have attached our test DB for you to test with.

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.

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?

Any update on whether you were able to reproduce the issue ?

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.