|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
64-bit SQLto an existing table. The column is set as primary key, identity and clustered. On the 32-bit version of SQL Server 2005 it works fine. It fails on the 64-bit version of SQL Server 2005 intermittently with a "could not create unique index because duplicate values were found". Kind of odd, considering as an identity field it's creating the values. I was able to recreate the problem with the following schema: Create table test1 (col1 varchar(20), col2 varchar(20), col3 uniqueidentifier default newid()) -- insert data Declare @counter int set @counter=1 While @counter < 1000000 BEGIN insert into test1 values ('Joe','Smith',default) Set @counter=@counter+1 END -- add column Alter table test1 add col4 int constraint PK_test1 primary key clustered identity After Running this, I get this error: CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.Test1' and index name 'PK_test1'. The duplicate key value is (28). Anybody else run into this? Why would this be happening, and is there any way to fix it? I'm running the 64-bit version of Windows 2003 and the the 64-bit version of SQL Server 2005. Thanks in advance, Mark I don't have 64-bit system handy to test this on but you might see if you
can recreate the problem with parallelism off. ALTER TABLE test1 ADD col4 int CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED WITH (MAXDOP = 1) IDENTITY -- Show quoteHope this helps. Dan Guzman SQL Server MVP "MAS" <mark_stric***@hotmail.com> wrote in message news:efXagtwBGHA.2356@tk2msftngp13.phx.gbl... >I have run into an interesting problem. I have some code that adds a >column to an existing table. The column is set as primary key, identity >and clustered. On the 32-bit version of SQL Server 2005 it works fine. It >fails on the 64-bit version of SQL Server 2005 intermittently with a "could >not create unique index because duplicate values were found". Kind of odd, >considering as an identity field it's creating the values. I was able to >recreate the problem with the following schema: > > Create table test1 > > (col1 varchar(20), > > col2 varchar(20), > > col3 uniqueidentifier default newid()) > > -- insert data > > Declare @counter int > > set @counter=1 > > While @counter < 1000000 > > BEGIN > > insert into test1 > > values ('Joe','Smith',default) > > Set @counter=@counter+1 > > END > > -- add column > > Alter table test1 add col4 int constraint PK_test1 primary key clustered > identity > > After Running this, I get this error: > > CREATE UNIQUE INDEX terminated because a duplicate key was found for > object name 'dbo.Test1' and index name 'PK_test1'. The duplicate key value > is (28). > > Anybody else run into this? Why would this be happening, and is there any > way to fix it? I'm running the 64-bit version of Windows 2003 and the the > 64-bit version of SQL Server 2005. > > Thanks in advance, > > Mark > > > > I appreciate the help Dan. I had the same thought, (that it may be a
parellelism problem), but I hadn't tried your syntax. Unfortunately, I got the same error after using your suggestion. Show quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:%23ys8E5wBGHA.3528@TK2MSFTNGP12.phx.gbl... >I don't have 64-bit system handy to test this on but you might see if you >can recreate the problem with parallelism off. > > ALTER TABLE test1 ADD col4 int > CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED > WITH (MAXDOP = 1) > IDENTITY > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > The problem is intermittent. The first run of your syntax failed, the
second succeeded, the third failed. Show quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:%23ys8E5wBGHA.3528@TK2MSFTNGP12.phx.gbl... >I don't have 64-bit system handy to test this on but you might see if you >can recreate the problem with parallelism off. > > ALTER TABLE test1 ADD col4 int > CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED > WITH (MAXDOP = 1) > IDENTITY > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "MAS" <mark_stric***@hotmail.com> wrote in message > news:efXagtwBGHA.2356@tk2msftngp13.phx.gbl... >>I have run into an interesting problem. I have some code that adds a >>column to an existing table. The column is set as primary key, identity >>and clustered. On the 32-bit version of SQL Server 2005 it works fine. >>It fails on the 64-bit version of SQL Server 2005 intermittently with a >>"could not create unique index because duplicate values were found". Kind >>of odd, considering as an identity field it's creating the values. I was >>able to recreate the problem with the following schema: >> >> Create table test1 >> >> (col1 varchar(20), >> >> col2 varchar(20), >> >> col3 uniqueidentifier default newid()) >> >> -- insert data >> >> Declare @counter int >> >> set @counter=1 >> >> While @counter < 1000000 >> >> BEGIN >> >> insert into test1 >> >> values ('Joe','Smith',default) >> >> Set @counter=@counter+1 >> >> END >> >> -- add column >> >> Alter table test1 add col4 int constraint PK_test1 primary key clustered >> identity >> >> After Running this, I get this error: >> >> CREATE UNIQUE INDEX terminated because a duplicate key was found for >> object name 'dbo.Test1' and index name 'PK_test1'. The duplicate key >> value is (28). >> >> Anybody else run into this? Why would this be happening, and is there >> any way to fix it? I'm running the 64-bit version of Windows 2003 and the >> the 64-bit version of SQL Server 2005. >> >> Thanks in advance, >> >> Mark >> >> >> >> > > Even though the MAXDOP 1 didn't work around the problem, this information is
useful. I suggest you report this apparent bug using the feedback center at http://lab.msdn.microsoft.com/productfeedback/. Include your repro script and be sure to specify that the problem is intermittent, occurs only on 64-bit and that the MAXDOP 1 hint didn't help. If you need a workaround before the problem is resolved, you might try adding the identity column and primary key as separate statements. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "MAS" <mark_stric***@hotmail.com> wrote in message news:eAlN3MxBGHA.984@tk2msftngp13.phx.gbl... > The problem is intermittent. The first run of your syntax failed, the > second succeeded, the third failed. > > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message > news:%23ys8E5wBGHA.3528@TK2MSFTNGP12.phx.gbl... >>I don't have 64-bit system handy to test this on but you might see if you >>can recreate the problem with parallelism off. >> >> ALTER TABLE test1 ADD col4 int >> CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED >> WITH (MAXDOP = 1) >> IDENTITY >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "MAS" <mark_stric***@hotmail.com> wrote in message >> news:efXagtwBGHA.2356@tk2msftngp13.phx.gbl... >>>I have run into an interesting problem. I have some code that adds a >>>column to an existing table. The column is set as primary key, identity >>>and clustered. On the 32-bit version of SQL Server 2005 it works fine. >>>It fails on the 64-bit version of SQL Server 2005 intermittently with a >>>"could not create unique index because duplicate values were found". >>>Kind of odd, considering as an identity field it's creating the values. >>>I was able to recreate the problem with the following schema: >>> >>> Create table test1 >>> >>> (col1 varchar(20), >>> >>> col2 varchar(20), >>> >>> col3 uniqueidentifier default newid()) >>> >>> -- insert data >>> >>> Declare @counter int >>> >>> set @counter=1 >>> >>> While @counter < 1000000 >>> >>> BEGIN >>> >>> insert into test1 >>> >>> values ('Joe','Smith',default) >>> >>> Set @counter=@counter+1 >>> >>> END >>> >>> -- add column >>> >>> Alter table test1 add col4 int constraint PK_test1 primary key clustered >>> identity >>> >>> After Running this, I get this error: >>> >>> CREATE UNIQUE INDEX terminated because a duplicate key was found for >>> object name 'dbo.Test1' and index name 'PK_test1'. The duplicate key >>> value is (28). >>> >>> Anybody else run into this? Why would this be happening, and is there >>> any way to fix it? I'm running the 64-bit version of Windows 2003 and >>> the the 64-bit version of SQL Server 2005. >>> >>> Thanks in advance, >>> >>> Mark >>> >>> >>> >>> >> >> > > Dan,
I'll report the bug. By the way, I replaced the alter table component with the following code: Alter table test1 add col4 int identity go Alter table test1 add constraint pk_test1 primary key(col4) And I still get the same error message. Show quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:Oz98RYxBGHA.1092@TK2MSFTNGP09.phx.gbl... > Even though the MAXDOP 1 didn't work around the problem, this information > is useful. I suggest you report this apparent bug using the feedback > center at http://lab.msdn.microsoft.com/productfeedback/. Include your > repro script and be sure to specify that the problem is intermittent, > occurs only on 64-bit and that the MAXDOP 1 hint didn't help. > > If you need a workaround before the problem is resolved, you might try > adding the identity column and primary key as separate statements. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "MAS" <mark_stric***@hotmail.com> wrote in message > news:eAlN3MxBGHA.984@tk2msftngp13.phx.gbl... >> The problem is intermittent. The first run of your syntax failed, the >> second succeeded, the third failed. >> >> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message >> news:%23ys8E5wBGHA.3528@TK2MSFTNGP12.phx.gbl... >>>I don't have 64-bit system handy to test this on but you might see if you >>>can recreate the problem with parallelism off. >>> >>> ALTER TABLE test1 ADD col4 int >>> CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED >>> WITH (MAXDOP = 1) >>> IDENTITY >>> >>> -- >>> Hope this helps. >>> >>> Dan Guzman >>> SQL Server MVP >>> >>> "MAS" <mark_stric***@hotmail.com> wrote in message >>> news:efXagtwBGHA.2356@tk2msftngp13.phx.gbl... >>>>I have run into an interesting problem. I have some code that adds a >>>>column to an existing table. The column is set as primary key, identity >>>>and clustered. On the 32-bit version of SQL Server 2005 it works fine. >>>>It fails on the 64-bit version of SQL Server 2005 intermittently with a >>>>"could not create unique index because duplicate values were found". >>>>Kind of odd, considering as an identity field it's creating the values. >>>>I was able to recreate the problem with the following schema: >>>> >>>> Create table test1 >>>> >>>> (col1 varchar(20), >>>> >>>> col2 varchar(20), >>>> >>>> col3 uniqueidentifier default newid()) >>>> >>>> -- insert data >>>> >>>> Declare @counter int >>>> >>>> set @counter=1 >>>> >>>> While @counter < 1000000 >>>> >>>> BEGIN >>>> >>>> insert into test1 >>>> >>>> values ('Joe','Smith',default) >>>> >>>> Set @counter=@counter+1 >>>> >>>> END >>>> >>>> -- add column >>>> >>>> Alter table test1 add col4 int constraint PK_test1 primary key >>>> clustered identity >>>> >>>> After Running this, I get this error: >>>> >>>> CREATE UNIQUE INDEX terminated because a duplicate key was found for >>>> object name 'dbo.Test1' and index name 'PK_test1'. The duplicate key >>>> value is (28). >>>> >>>> Anybody else run into this? Why would this be happening, and is there >>>> any way to fix it? I'm running the 64-bit version of Windows 2003 and >>>> the the 64-bit version of SQL Server 2005. >>>> >>>> Thanks in advance, >>>> >>>> Mark >>>> >>>> >>>> >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics