Not copying primary keys

[MarkGreen]

Hi,
A similiar question has been posted to this but as its slighty different i thought i'd make a new post.

I have an access database with around 50 tables in. Around 30 of them have primary keys.
I need to transfer all the tables/data to SQL server. I am using the create DTS import wizard on enterprise manager.

This works fine except the primary keys are been missed out. When i look in the resulting DTS package it has not saved any identity fields at all. (Well it has saved the fields, but not as identity fields)

I have read that it should transfer the PKs, but I cant work out why mine isn't!
Any help would be greatly appreciated.
Mark.

 

[tlbroadbent]

DTS doesn't transfer the primary keys. That is one reason to export from Access to SQL Server rather than import to SQL Server using DTS. Of course, you can always add the Primary key with the Alter Table command.

See thread961-534506 to learn how to import Access autonumber columns to SQL Server identity columns.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[MarkGreen]

Thanks for your feedback,
I edited the SQL for each of my tables to includes the IDENTITY keyword where appropriate and also used this syntax where a primary key is defined:
CONSTRAINT [] PRIMARY KEY CLUSTERED
(
[]
) ON [PRIMARY]

When a ran this DTS package only 1 out of about 30 tables copied across. The other tables give an unspecified error.
On closer inspection, any ONE table would copy across on its own so long as there was not tables already in the server database.
As soon as there is a table in the database, no more tables will copy across.

Any ideas what could be going wrong?

 

[tlbroadbent]

If you are using the DTS package to create the server tables, the package will abort if the tables already exist. If you want to run the complete package, you must drop the existing tables. However, my preference would be to create the tables as needed and then transfer only data.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[MarkGreen]

Thank you for your help. Think its almost sorted now. The only thing left is how do you make the DTS package put default values for bits into the tables?
As server is creating the bit fields as allow nulls = no, but no default value, this is causing problems for my front end.
I would go through and alter them by hand, but I need a complete DTS so when we do the proper migration it is all in one package.

thanks again,
Mark.