Author |
Topic |
sqlslick
Yak Posting Veteran
83 Posts |
Posted - 2013-04-05 : 16:07:28
|
Hey guys,I am trying to convert an Access Database Table to SQL Server but am getting the error below from SQL Server during the conversion:SQL that Caused Error(s):CREATE UNIQUE INDEX ID ON Transonic(ID, Date, Time)Error(s):Server Error 1505: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Transonic' and the index name 'ID'. The duplicate key value is 6.84621e+009, Aug 31 2010 12:00AM, Dec 30 1899 7:58PM).Server Error 3621: The Statement has been terminatedI don't understand why the conversion is trying to execute that CREATE UNIQUE INDEX statement anyways because the ID columned is not indexed. The only column that is indexed in this table is the PK, which is RecID and I've already checked to make sure there are no duplicates. The ID, Date and Time fields are not indexed at all so I don't understand why this is occurring.Thanks in advanced!John |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-09 : 00:56:22
|
the reason is there are already duplicate data existing in the table which is why it cant create the index. You can solve this in two ways1. Remove duplicate data in table using the below sqlDELETE tFROM(SELECT ROW_NUMBER() OVER (PARTITION BY ID,Date,Time) ORDER BY (SELECT 1)) AS SeqFROM table)tWHERE Seq>1 and then create the index2. create the index as a constraint with NOCHECK clauseALTER TABLE Transonic WITH NOCHECK ADD CONSTRAINT constraintname UNIQUE (ID, Date, Time) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sqlslick
Yak Posting Veteran
83 Posts |
Posted - 2013-04-09 : 11:03:29
|
Thanks for the tip, but I still don't understand why SQL Server is trying to create this Index. Please see data below:ID, Date, Time, RecID1234, 2010-08-31, 7:58:00 PM, 368881234, 2010-08-31, 7:58:00 PM, 39808If you only look at the ID, Date and Time columns, then yes, these are duplicates, but the Table Primary Key is the RecID field, which is unique so these are not duplicate rows. The Table design in MS Access does not have any Indexing options enabled for the ID, Date and Time field, so it does not make sense why SQL Server is doing this.Thanks,J |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-04-09 : 11:20:25
|
Something is telling it to...How are you doing this? Using the upsizing wizard in Access?I'd use SSIS for this. |
|
|
sqlslick
Yak Posting Veteran
83 Posts |
Posted - 2013-04-09 : 11:32:55
|
Yes, I am using Upsizing Wizard in MS Access, but I can look into testing it with SSIS. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-04-09 : 11:33:03
|
I don't see RecID in your index creation script:CREATE UNIQUE INDEX ID ON Transonic(ID, Date, Time) ID, Date, Time, RecID1234, 2010-08-31, 7:58:00 PM, 368881234, 2010-08-31, 7:58:00 PM, 39808 If ID, Date, Time are not unique, then you are going to get a violation. Do you just what a regular index instead? |
|
|
sqlslick
Yak Posting Veteran
83 Posts |
Posted - 2013-04-09 : 11:48:48
|
I did not write the Index creation script. SQL Server is doing this automatically, not sure why, because the Table Index is RecID. Like I said, ID, Date and Time fields are not indexed in MS Access so not sure where SQL Server is getting this information from.Thanks all!J |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-04-09 : 12:02:25
|
Oh, I ws typing when you posted that the MS Access wizard is generating that for you. That is odd. Are there forign keys associated with those columns? |
|
|
sqlslick
Yak Posting Veteran
83 Posts |
Posted - 2013-04-09 : 13:48:24
|
Hey all,The MS Access developer just got back to me and pointed out additinoal indexing options available in MS Access that I didn't know about. This is where SQL Server is getting the code. My apologies for your wasted efforts, but I am very appreciative for all of your time and support!J |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-09 : 14:35:22
|
hope you now got it sorted out------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sqlslick
Yak Posting Veteran
83 Posts |
Posted - 2013-04-10 : 08:04:04
|
Yes, thanks again! |
|
|
|