Hi,
I am facing following error in transaction replication.
Cannot insert duplicate key row in object 'TableName'
with unique index 'PKConstraintName
Can any one help on this?
Thanks,
Manoj
Is it the distribution agent that is failing with that error? Then it means
that the subscriber already has a row, but the distribution agent is trying
to insert it again.
Have you been making any changes to the subscribing tables? Could it be that
someone inserted a row to the subscribing table, before that row existed on
the publishing table?
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:2e1ad01c46af7$0a435df0$a501280a@.phx.gbl...
> Hi,
> I am facing following error in transaction replication.
> Cannot insert duplicate key row in object 'TableName'
> with unique index 'PKConstraintName
> Can any one help on this?
> Thanks,
> Manoj
>
|||Thanks Vyas,
The subscriber has more number of records than publisher.
Thanks,
Manoj
>--Original Message--
>Is it the distribution agent that is failing with that
error? Then it means
>that the subscriber already has a row, but the
distribution agent is trying
>to insert it again.
>Have you been making any changes to the subscribing
tables? Could it be that
>someone inserted a row to the subscribing table, before
that row existed on
>the publishing table?
>--
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in
message
>news:2e1ad01c46af7$0a435df0$a501280a@.phx.gbl...
>
>.
>
Showing posts with label duplicate. Show all posts
Showing posts with label duplicate. Show all posts
Friday, February 24, 2012
Cannot insert duplicate key row in object 'MSmerge_tombstone'
Hi,
I have made a replication since 2004-08-21. it works well, but recently i
got a problem. When i want to delete some records in a table, an error
occur. the message is:
Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique
index 'uc1MSmerge_tombstone'
The statement has been terminated.
whatever how i do, even clear up all the records in table MSmerge_tombstone.
but when i try to delete the records in the table. i still got the error
message. i have searched more web pages for try to got some explain and
solution. but my work is useless. my boss is pressing me to solve this
problem in this week. Friends. Please help and save my life.
Regards
Jackson Chan
My personal opinion is that your unique index 'uc1MSmerge_tombstone' has gone
a bit funny.
Couple of sugestions, delete your unique index
run a checkdb on your database
Re-establish your index.
Peter
"We are all worms. But I believe that I am a glow-worm."
Winston Churchill
"Jackson BF" wrote:
> Hi,
> I have made a replication since 2004-08-21. it works well, but recently i
> got a problem. When i want to delete some records in a table, an error
> occur. the message is:
> Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique
> index 'uc1MSmerge_tombstone'
> The statement has been terminated.
> whatever how i do, even clear up all the records in table MSmerge_tombstone.
> but when i try to delete the records in the table. i still got the error
> message. i have searched more web pages for try to got some explain and
> solution. but my work is useless. my boss is pressing me to solve this
> problem in this week. Friends. Please help and save my life.
> Regards
> Jackson Chan
>
>
>
I have made a replication since 2004-08-21. it works well, but recently i
got a problem. When i want to delete some records in a table, an error
occur. the message is:
Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique
index 'uc1MSmerge_tombstone'
The statement has been terminated.
whatever how i do, even clear up all the records in table MSmerge_tombstone.
but when i try to delete the records in the table. i still got the error
message. i have searched more web pages for try to got some explain and
solution. but my work is useless. my boss is pressing me to solve this
problem in this week. Friends. Please help and save my life.
Regards
Jackson Chan
My personal opinion is that your unique index 'uc1MSmerge_tombstone' has gone
a bit funny.
Couple of sugestions, delete your unique index
run a checkdb on your database
Re-establish your index.
Peter
"We are all worms. But I believe that I am a glow-worm."
Winston Churchill
"Jackson BF" wrote:
> Hi,
> I have made a replication since 2004-08-21. it works well, but recently i
> got a problem. When i want to delete some records in a table, an error
> occur. the message is:
> Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique
> index 'uc1MSmerge_tombstone'
> The statement has been terminated.
> whatever how i do, even clear up all the records in table MSmerge_tombstone.
> but when i try to delete the records in the table. i still got the error
> message. i have searched more web pages for try to got some explain and
> solution. but my work is useless. my boss is pressing me to solve this
> problem in this week. Friends. Please help and save my life.
> Regards
> Jackson Chan
>
>
>
Cannot insert duplicate key row in object MSmerge_tombstone
MSMerge_tombstone is a System Table used for Replication
information. It would appear as though you arent getting
rid of Replication totally after the first time you run
it. Are you dropping the Publication after the first run?
>--Original Message--
>I am working with a VB.Net Windows app that creates a
>publication/subscription on the fly. Essentially, it
takes a database backup
>in a zip file, unzips it, restores the database to the
same server as the
>other database in the setup, creates a publication to the
first database,
>then a subscription from the first database to the new
one, and attempts to
>run it.
>The first time this runs, on a fresh setup of all
databases involved, the
>process works fine. On subsequent runs, however, when we
get to the data
>synchronization part, the following error message comes
up in the agent's
>session detail:
>The schema script \\ROSTERDEV\C$\Program Files\Microsoft
SQL
>Server\MSSQL\ReplData\unc\ROSTERDEV_Case_mgt_HQ_c ase_mgt_H
Q\20040924000502\O
>ffice_78.sch could not be propagated to the subscriber.
>(Source: Merge Replication Provider (Agent); Error
number: -2147201001)
>----
--
>--
>Cannot insert duplicate key row in object
MSmerge_tombstone with unique
>index uc1MSmerge_tombstone.
>(Source: ROSTERDEV (Data source); Error number: 2601)
>----
--
>--
>The schema script file might be different, but the
message itself is the
>same. We're currently testing, so the exact same zip file
is getting reused
>on both first and subsequent attempts. I wouldn't think
this would cause a
>data conflict, but it appears to be.
>Any assistance would be welcome. Thanks.
>--
>Kenneth S. McAndrew
>Software Developer, Information Concepts
>kmcandrew@.infoconcepts.com
>
>.
>
The pull subscription, publication, and database published from are all
being removed, in that order, by the vb.net code. I don't see any left-over
tendrils.
Kenneth S. McAndrew
Software Developer, Information Concepts
kmcandrew@.infoconcepts.com
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:239d01c4a241$308282d0$a401280a@.phx.gbl...[vbcol=seagreen]
> MSMerge_tombstone is a System Table used for Replication
> information. It would appear as though you arent getting
> rid of Replication totally after the first time you run
> it. Are you dropping the Publication after the first run?
>
> takes a database backup
> same server as the
> first database,
> one, and attempts to
> databases involved, the
> get to the data
> up in the agent's
> SQL
> Q\20040924000502\O
> number: -2147201001)
> --
> MSmerge_tombstone with unique
> --
> message itself is the
> is getting reused
> this would cause a
information. It would appear as though you arent getting
rid of Replication totally after the first time you run
it. Are you dropping the Publication after the first run?
>--Original Message--
>I am working with a VB.Net Windows app that creates a
>publication/subscription on the fly. Essentially, it
takes a database backup
>in a zip file, unzips it, restores the database to the
same server as the
>other database in the setup, creates a publication to the
first database,
>then a subscription from the first database to the new
one, and attempts to
>run it.
>The first time this runs, on a fresh setup of all
databases involved, the
>process works fine. On subsequent runs, however, when we
get to the data
>synchronization part, the following error message comes
up in the agent's
>session detail:
>The schema script \\ROSTERDEV\C$\Program Files\Microsoft
SQL
>Server\MSSQL\ReplData\unc\ROSTERDEV_Case_mgt_HQ_c ase_mgt_H
Q\20040924000502\O
>ffice_78.sch could not be propagated to the subscriber.
>(Source: Merge Replication Provider (Agent); Error
number: -2147201001)
>----
--
>--
>Cannot insert duplicate key row in object
MSmerge_tombstone with unique
>index uc1MSmerge_tombstone.
>(Source: ROSTERDEV (Data source); Error number: 2601)
>----
--
>--
>The schema script file might be different, but the
message itself is the
>same. We're currently testing, so the exact same zip file
is getting reused
>on both first and subsequent attempts. I wouldn't think
this would cause a
>data conflict, but it appears to be.
>Any assistance would be welcome. Thanks.
>--
>Kenneth S. McAndrew
>Software Developer, Information Concepts
>kmcandrew@.infoconcepts.com
>
>.
>
The pull subscription, publication, and database published from are all
being removed, in that order, by the vb.net code. I don't see any left-over
tendrils.
Kenneth S. McAndrew
Software Developer, Information Concepts
kmcandrew@.infoconcepts.com
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:239d01c4a241$308282d0$a401280a@.phx.gbl...[vbcol=seagreen]
> MSMerge_tombstone is a System Table used for Replication
> information. It would appear as though you arent getting
> rid of Replication totally after the first time you run
> it. Are you dropping the Publication after the first run?
>
> takes a database backup
> same server as the
> first database,
> one, and attempts to
> databases involved, the
> get to the data
> up in the agent's
> SQL
> Q\20040924000502\O
> number: -2147201001)
> --
> MSmerge_tombstone with unique
> --
> message itself is the
> is getting reused
> this would cause a
Sunday, February 19, 2012
Cannot insert duplicate key row in object
i am trying to replicate a database in SQL 2000 using transactional
replication and on one of the tables, i recieve the following error
Cannot insert duplicate key row in object 'F98611' with unique index
'F98611_PK'.
(Source: JDEBACKUP (Data source); Error number: 2601)
------
Function sequence error
(Source: JDEBACKUP (ODBC); Error number: S1010)
------
Can anyone tell me what i need to do to have replication succeed, i am not
very knowledgable of SQL as i am a network admin,...so if you could explain
in basic terms i would greatly appreciate...you can email me at
malejandro@.armadillohomes.com
Thanks in advance
Mark,
this is because the subscriber has been treated in a read-write fashion, and
this needs to be explored to ensure it doesn't recur and to check the
differences between publisher and subscriber data. To get things up and
going though, you can add 2601 to the SkipErrors parameter of the
distribution agent profile and restart it.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
thank you i have gone ahead and added 2601 to skiperrors in the distribution
profile. what do you mean by the 'subscriber has been treated in a readwrite
fashion' . i know what the subscriber is, but the rest is a little foreign to
me. I greatly appreciate your help and knowledge
"Paul Ibison" wrote:
> Mark,
> this is because the subscriber has been treated in a read-write fashion, and
> this needs to be explored to ensure it doesn't recur and to check the
> differences between publisher and subscriber data. To get things up and
> going though, you can add 2601 to the SkipErrors parameter of the
> distribution agent profile and restart it.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Mark,
for normal transactional replication, there is an implication that the
subscriber is read-only. If users start adding, deleting or updating data on
the subscriber in this type of replication, that can mess things up for when
data from the publisher is synchronized, as you have seen. If the subscriber
needs to be changed directly, normally a different subscription model is
chosen - immediate updating subscribers or queued updating subscribers.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Ok, got that, but nobody is accessing the subscriber, we are using it for
failover purposes, Ive tried transactional and snapshot. Both fail at the
same table...
i have even tried to delete the table and have the snapshot recreate it, but
i get the same result.
"Paul Ibison" wrote:
> Mark,
> for normal transactional replication, there is an implication that the
> subscriber is read-only. If users start adding, deleting or updating data on
> the subscriber in this type of replication, that can mess things up for when
> data from the publisher is synchronized, as you have seen. If the subscriber
> needs to be changed directly, normally a different subscription model is
> chosen - immediate updating subscribers or queued updating subscribers.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Mark,
in the name comflicts section of the article properties, is it set to drop
the table if it exists? It should be, but perhaps in this case it isn't.
Also, have a look at this article:
http://support.microsoft.com/default...b;en-us;813494 which may be
relevant.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Pual,
Yes, it was set to 'drop the existing table and recreate it'. anything else
i can look into?
"Paul Ibison" wrote:
> Mark,
> in the name comflicts section of the article properties, is it set to drop
> the table if it exists? It should be, but perhaps in this case it isn't.
> Also, have a look at this article:
> http://support.microsoft.com/default...b;en-us;813494 which may be
> relevant.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Mark,
unless the article helps, I'm almost out of ideas
In your situation, I'd remove the publication and delete the table then
recreate the publication. I'd enable logging
(http://support.microsoft.com/?id=312292) then run the distribution agent to
see at what stage things go wrong (if they do in this case).
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <52127098-74C0-4993-A721-BB3E07BD3716@.microsoft.com>,
MarkAlejandro@.discussions.microsoft.com says...
> Pual,
> Yes, it was set to 'drop the existing table and recreate it'. anything else
> i can look into?
> "Paul Ibison" wrote:
>
I have had the exact same error when by mistake, i put the same table in
two different publications to the same subscriber db.
You need to right click each publication and look at the articles in
it. Make sure no publication has the same table in it.
select * from sysobjects where replinfo <>0 will give you a list of
replicated tables but not if they are in two different publications.
replication and on one of the tables, i recieve the following error
Cannot insert duplicate key row in object 'F98611' with unique index
'F98611_PK'.
(Source: JDEBACKUP (Data source); Error number: 2601)
------
Function sequence error
(Source: JDEBACKUP (ODBC); Error number: S1010)
------
Can anyone tell me what i need to do to have replication succeed, i am not
very knowledgable of SQL as i am a network admin,...so if you could explain
in basic terms i would greatly appreciate...you can email me at
malejandro@.armadillohomes.com
Thanks in advance
Mark,
this is because the subscriber has been treated in a read-write fashion, and
this needs to be explored to ensure it doesn't recur and to check the
differences between publisher and subscriber data. To get things up and
going though, you can add 2601 to the SkipErrors parameter of the
distribution agent profile and restart it.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
thank you i have gone ahead and added 2601 to skiperrors in the distribution
profile. what do you mean by the 'subscriber has been treated in a readwrite
fashion' . i know what the subscriber is, but the rest is a little foreign to
me. I greatly appreciate your help and knowledge
"Paul Ibison" wrote:
> Mark,
> this is because the subscriber has been treated in a read-write fashion, and
> this needs to be explored to ensure it doesn't recur and to check the
> differences between publisher and subscriber data. To get things up and
> going though, you can add 2601 to the SkipErrors parameter of the
> distribution agent profile and restart it.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Mark,
for normal transactional replication, there is an implication that the
subscriber is read-only. If users start adding, deleting or updating data on
the subscriber in this type of replication, that can mess things up for when
data from the publisher is synchronized, as you have seen. If the subscriber
needs to be changed directly, normally a different subscription model is
chosen - immediate updating subscribers or queued updating subscribers.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Ok, got that, but nobody is accessing the subscriber, we are using it for
failover purposes, Ive tried transactional and snapshot. Both fail at the
same table...
i have even tried to delete the table and have the snapshot recreate it, but
i get the same result.
"Paul Ibison" wrote:
> Mark,
> for normal transactional replication, there is an implication that the
> subscriber is read-only. If users start adding, deleting or updating data on
> the subscriber in this type of replication, that can mess things up for when
> data from the publisher is synchronized, as you have seen. If the subscriber
> needs to be changed directly, normally a different subscription model is
> chosen - immediate updating subscribers or queued updating subscribers.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Mark,
in the name comflicts section of the article properties, is it set to drop
the table if it exists? It should be, but perhaps in this case it isn't.
Also, have a look at this article:
http://support.microsoft.com/default...b;en-us;813494 which may be
relevant.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Pual,
Yes, it was set to 'drop the existing table and recreate it'. anything else
i can look into?
"Paul Ibison" wrote:
> Mark,
> in the name comflicts section of the article properties, is it set to drop
> the table if it exists? It should be, but perhaps in this case it isn't.
> Also, have a look at this article:
> http://support.microsoft.com/default...b;en-us;813494 which may be
> relevant.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Mark,
unless the article helps, I'm almost out of ideas
In your situation, I'd remove the publication and delete the table then
recreate the publication. I'd enable logging
(http://support.microsoft.com/?id=312292) then run the distribution agent to
see at what stage things go wrong (if they do in this case).
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <52127098-74C0-4993-A721-BB3E07BD3716@.microsoft.com>,
MarkAlejandro@.discussions.microsoft.com says...
> Pual,
> Yes, it was set to 'drop the existing table and recreate it'. anything else
> i can look into?
> "Paul Ibison" wrote:
>
I have had the exact same error when by mistake, i put the same table in
two different publications to the same subscriber db.
You need to right click each publication and look at the articles in
it. Make sure no publication has the same table in it.
select * from sysobjects where replinfo <>0 will give you a list of
replicated tables but not if they are in two different publications.
cannot insert duplicate key row in msrepl_transactions
We have an issue that our SQL Replication between our Application Server (holding 13 months of data) and our Reporting Server (holding 5-6 years of data).
Both systems are running on NT4, SQL Server 7 Ent SP3.
Our issue is that we used to run SQL Replication but then stopped using it, and used another method of replicating our data from the App Svr to Rpt Svr. However, we have now been told that we have to use SQL Replication.
We set it up as usual, but when the log reader starts up, it finds the 1st transaction and moves it into the distribution database, but it then fails when attempting to do the 2nd transaction with the error "Cannot insert duplicate key row in object MSrepl_transactions with unique index ucMSrepl_transactions.
We have checked the tables in the publisher db and they don't hold any duplicates, so I can only assume the key it is talking about is the key field in the distribution database, but this is a Binary field which I believe is populated by replication itself!! Is this correct?
If so, why is it trying to enter duplicates? And can we resync replication to fix it?Are you doing transactional or merge? Either way it will kick off a snapshot first to sync it. I believe you probably unchecked the initialization when you set it up. But the bottomline is that you have two server contain different data right now so the replication will not work, yet if you sync it, it will erase the historic data on your report server. My take will be to let the snapshot replicate down to a new database on your report server and load the historic data into the database(not a easy task).
Both systems are running on NT4, SQL Server 7 Ent SP3.
Our issue is that we used to run SQL Replication but then stopped using it, and used another method of replicating our data from the App Svr to Rpt Svr. However, we have now been told that we have to use SQL Replication.
We set it up as usual, but when the log reader starts up, it finds the 1st transaction and moves it into the distribution database, but it then fails when attempting to do the 2nd transaction with the error "Cannot insert duplicate key row in object MSrepl_transactions with unique index ucMSrepl_transactions.
We have checked the tables in the publisher db and they don't hold any duplicates, so I can only assume the key it is talking about is the key field in the distribution database, but this is a Binary field which I believe is populated by replication itself!! Is this correct?
If so, why is it trying to enter duplicates? And can we resync replication to fix it?Are you doing transactional or merge? Either way it will kick off a snapshot first to sync it. I believe you probably unchecked the initialization when you set it up. But the bottomline is that you have two server contain different data right now so the replication will not work, yet if you sync it, it will erase the historic data on your report server. My take will be to let the snapshot replicate down to a new database on your report server and load the historic data into the database(not a easy task).
Cannot insert duplicate key into ...... EXTREME problem
Hi Astra,
I do not have any 6.5 experience however I have seen this
problem on 2000.
Within 2000 if you run the command
SELECT TOP 100 PERCENT name, IDENT_SEED(name) AS [ID
Start], IDENT_INCR(name) AS [ID Increment], IDENT_CURRENT
(name) AS [Current ID]
FROM dbo.sysobjects
WHERE xtype = 'U'
ORDER BY IDENT_SEED(name)
You will get the current identity values for the user
tables in your database.
I think that somehow one on your 6.5 has got out of
sequence i.e. the current identity is 4, but internally it
thinks it 3, so it trys to insert 4 in again.
How we solved this in 2000 was with the DBCC CHECKIDENT
command.
Peter
"Although prepared for martyrdom, I preferred that it be
postponed."
Winston Churchill
>--Original Message--
>Hi All
>Really need your urgent help on this!
>Created a system that uses an SQL database as the data
store. Clients have
>been using this same DDL since SQL 6.5 and I've got lots
of users running
>SQL 6.5, 7, 2000 and MSDE with the same DDL and they
don't have a problem.
>However, one user, who has been running the system on SQL
6.5 for years now,
>keeps getting cannot insert duplicate key into PK_ blah
blah. This error is
>being generated on SQL 6.5's own auto-generated ID so I'm
sure it is a
>problem that their SQL 6.5 has created in the db. The
problem is after
>running the usual CHECKDB, CHECKALLOC, NEWALLOC,
CHECKTABLE and UPDATEUSAGE
>DBCC commands none of these have corrected the fault.
>I'm sure I've had this problem crop up once before and
doing the CHECKDB
>fixed it, but I've run the CHECKDB 3 times now and it
doesn't correct it.
>Can you give me any pointers on how I can check and more
importantly resolve
>this issue?
>Thanks Robbie
>
>.
>
Guys
You've made my weekend!!!
Many thanks.
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:1ad701c51b53$2a8f0a20$a601280a@.phx.gbl...
Hi Astra,
I do not have any 6.5 experience however I have seen this
problem on 2000.
Within 2000 if you run the command
SELECT TOP 100 PERCENT name, IDENT_SEED(name) AS [ID
Start], IDENT_INCR(name) AS [ID Increment], IDENT_CURRENT
(name) AS [Current ID]
FROM dbo.sysobjects
WHERE xtype = 'U'
ORDER BY IDENT_SEED(name)
You will get the current identity values for the user
tables in your database.
I think that somehow one on your 6.5 has got out of
sequence i.e. the current identity is 4, but internally it
thinks it 3, so it trys to insert 4 in again.
How we solved this in 2000 was with the DBCC CHECKIDENT
command.
Peter
"Although prepared for martyrdom, I preferred that it be
postponed."
Winston Churchill
>--Original Message--
>Hi All
>Really need your urgent help on this!
>Created a system that uses an SQL database as the data
store. Clients have
>been using this same DDL since SQL 6.5 and I've got lots
of users running
>SQL 6.5, 7, 2000 and MSDE with the same DDL and they
don't have a problem.
>However, one user, who has been running the system on SQL
6.5 for years now,
>keeps getting cannot insert duplicate key into PK_ blah
blah. This error is
>being generated on SQL 6.5's own auto-generated ID so I'm
sure it is a
>problem that their SQL 6.5 has created in the db. The
problem is after
>running the usual CHECKDB, CHECKALLOC, NEWALLOC,
CHECKTABLE and UPDATEUSAGE
>DBCC commands none of these have corrected the fault.
>I'm sure I've had this problem crop up once before and
doing the CHECKDB
>fixed it, but I've run the CHECKDB 3 times now and it
doesn't correct it.
>Can you give me any pointers on how I can check and more
importantly resolve
>this issue?
>Thanks Robbie
>
>.
>
I do not have any 6.5 experience however I have seen this
problem on 2000.
Within 2000 if you run the command
SELECT TOP 100 PERCENT name, IDENT_SEED(name) AS [ID
Start], IDENT_INCR(name) AS [ID Increment], IDENT_CURRENT
(name) AS [Current ID]
FROM dbo.sysobjects
WHERE xtype = 'U'
ORDER BY IDENT_SEED(name)
You will get the current identity values for the user
tables in your database.
I think that somehow one on your 6.5 has got out of
sequence i.e. the current identity is 4, but internally it
thinks it 3, so it trys to insert 4 in again.
How we solved this in 2000 was with the DBCC CHECKIDENT
command.
Peter
"Although prepared for martyrdom, I preferred that it be
postponed."
Winston Churchill
>--Original Message--
>Hi All
>Really need your urgent help on this!
>Created a system that uses an SQL database as the data
store. Clients have
>been using this same DDL since SQL 6.5 and I've got lots
of users running
>SQL 6.5, 7, 2000 and MSDE with the same DDL and they
don't have a problem.
>However, one user, who has been running the system on SQL
6.5 for years now,
>keeps getting cannot insert duplicate key into PK_ blah
blah. This error is
>being generated on SQL 6.5's own auto-generated ID so I'm
sure it is a
>problem that their SQL 6.5 has created in the db. The
problem is after
>running the usual CHECKDB, CHECKALLOC, NEWALLOC,
CHECKTABLE and UPDATEUSAGE
>DBCC commands none of these have corrected the fault.
>I'm sure I've had this problem crop up once before and
doing the CHECKDB
>fixed it, but I've run the CHECKDB 3 times now and it
doesn't correct it.
>Can you give me any pointers on how I can check and more
importantly resolve
>this issue?
>Thanks Robbie
>
>.
>
Guys
You've made my weekend!!!
Many thanks.
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:1ad701c51b53$2a8f0a20$a601280a@.phx.gbl...
Hi Astra,
I do not have any 6.5 experience however I have seen this
problem on 2000.
Within 2000 if you run the command
SELECT TOP 100 PERCENT name, IDENT_SEED(name) AS [ID
Start], IDENT_INCR(name) AS [ID Increment], IDENT_CURRENT
(name) AS [Current ID]
FROM dbo.sysobjects
WHERE xtype = 'U'
ORDER BY IDENT_SEED(name)
You will get the current identity values for the user
tables in your database.
I think that somehow one on your 6.5 has got out of
sequence i.e. the current identity is 4, but internally it
thinks it 3, so it trys to insert 4 in again.
How we solved this in 2000 was with the DBCC CHECKIDENT
command.
Peter
"Although prepared for martyrdom, I preferred that it be
postponed."
Winston Churchill
>--Original Message--
>Hi All
>Really need your urgent help on this!
>Created a system that uses an SQL database as the data
store. Clients have
>been using this same DDL since SQL 6.5 and I've got lots
of users running
>SQL 6.5, 7, 2000 and MSDE with the same DDL and they
don't have a problem.
>However, one user, who has been running the system on SQL
6.5 for years now,
>keeps getting cannot insert duplicate key into PK_ blah
blah. This error is
>being generated on SQL 6.5's own auto-generated ID so I'm
sure it is a
>problem that their SQL 6.5 has created in the db. The
problem is after
>running the usual CHECKDB, CHECKALLOC, NEWALLOC,
CHECKTABLE and UPDATEUSAGE
>DBCC commands none of these have corrected the fault.
>I'm sure I've had this problem crop up once before and
doing the CHECKDB
>fixed it, but I've run the CHECKDB 3 times now and it
doesn't correct it.
>Can you give me any pointers on how I can check and more
importantly resolve
>this issue?
>Thanks Robbie
>
>.
>
Cannot insert duplicate key into ...... EXTREME problem
Hi All
Really need your urgent help on this!
Created a system that uses an SQL database as the data store. Clients have
been using this same DDL since SQL 6.5 and I've got lots of users running
SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
However, one user, who has been running the system on SQL 6.5 for years now,
keeps getting cannot insert duplicate key into PK_ blah blah. This error is
being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
problem that their SQL 6.5 has created in the db. The problem is after
running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and UPDATEUSAGE
DBCC commands none of these have corrected the fault.
I'm sure I've had this problem crop up once before and doing the CHECKDB
fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
Can you give me any pointers on how I can check and more importantly resolve
this issue?
Thanks Robbie
I cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try using
it.
Keith
"Astra" <info@.noemail.com> wrote in message
news:OQdxVA1GFHA.3128@.TK2MSFTNGP10.phx.gbl...
> Hi All
> Really need your urgent help on this!
> Created a system that uses an SQL database as the data store. Clients
have
> been using this same DDL since SQL 6.5 and I've got lots of users running
> SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
> However, one user, who has been running the system on SQL 6.5 for years
now,
> keeps getting cannot insert duplicate key into PK_ blah blah. This error
is
> being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
> problem that their SQL 6.5 has created in the db. The problem is after
> running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and
UPDATEUSAGE
> DBCC commands none of these have corrected the fault.
> I'm sure I've had this problem crop up once before and doing the CHECKDB
> fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
> Can you give me any pointers on how I can check and more importantly
resolve
> this issue?
> Thanks Robbie
>
|||Yes, it was.
SQL Server 6.5 has a few issues with Identity columns. Those issues went
away with SQL server 7.0.
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
As an aside, SQL Server 6.5 is no longer supported by Microsoft, so those
clients would be well advised to move to SQL Server 2000.
Regards
Mike
"Keith Kratochvil" wrote:
> I cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try using
> it.
> --
> Keith
>
> "Astra" <info@.noemail.com> wrote in message
> news:OQdxVA1GFHA.3128@.TK2MSFTNGP10.phx.gbl...
> have
> now,
> is
> UPDATEUSAGE
> resolve
>
Really need your urgent help on this!
Created a system that uses an SQL database as the data store. Clients have
been using this same DDL since SQL 6.5 and I've got lots of users running
SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
However, one user, who has been running the system on SQL 6.5 for years now,
keeps getting cannot insert duplicate key into PK_ blah blah. This error is
being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
problem that their SQL 6.5 has created in the db. The problem is after
running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and UPDATEUSAGE
DBCC commands none of these have corrected the fault.
I'm sure I've had this problem crop up once before and doing the CHECKDB
fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
Can you give me any pointers on how I can check and more importantly resolve
this issue?
Thanks Robbie
I cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try using
it.
Keith
"Astra" <info@.noemail.com> wrote in message
news:OQdxVA1GFHA.3128@.TK2MSFTNGP10.phx.gbl...
> Hi All
> Really need your urgent help on this!
> Created a system that uses an SQL database as the data store. Clients
have
> been using this same DDL since SQL 6.5 and I've got lots of users running
> SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
> However, one user, who has been running the system on SQL 6.5 for years
now,
> keeps getting cannot insert duplicate key into PK_ blah blah. This error
is
> being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
> problem that their SQL 6.5 has created in the db. The problem is after
> running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and
UPDATEUSAGE
> DBCC commands none of these have corrected the fault.
> I'm sure I've had this problem crop up once before and doing the CHECKDB
> fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
> Can you give me any pointers on how I can check and more importantly
resolve
> this issue?
> Thanks Robbie
>
|||Yes, it was.
SQL Server 6.5 has a few issues with Identity columns. Those issues went
away with SQL server 7.0.
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
As an aside, SQL Server 6.5 is no longer supported by Microsoft, so those
clients would be well advised to move to SQL Server 2000.
Regards
Mike
"Keith Kratochvil" wrote:
> I cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try using
> it.
> --
> Keith
>
> "Astra" <info@.noemail.com> wrote in message
> news:OQdxVA1GFHA.3128@.TK2MSFTNGP10.phx.gbl...
> have
> now,
> is
> UPDATEUSAGE
> resolve
>
Cannot insert duplicate key into ...... EXTREME problem
Hi All
Really need your urgent help on this!
Created a system that uses an SQL database as the data store. Clients have
been using this same DDL since SQL 6.5 and I've got lots of users running
SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
However, one user, who has been running the system on SQL 6.5 for years now,
keeps getting cannot insert duplicate key into PK_ blah blah. This error is
being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
problem that their SQL 6.5 has created in the db. The problem is after
running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and UPDATEUSAGE
DBCC commands none of these have corrected the fault.
I'm sure I've had this problem crop up once before and doing the CHECKDB
fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
Can you give me any pointers on how I can check and more importantly resolve
this issue?
Thanks RobbieI cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try using
it.
Keith
"Astra" <info@.noemail.com> wrote in message
news:ex1KtA1GFHA.3088@.tk2msftngp13.phx.gbl...
> Hi All
> Really need your urgent help on this!
> Created a system that uses an SQL database as the data store. Clients
have
> been using this same DDL since SQL 6.5 and I've got lots of users running
> SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
> However, one user, who has been running the system on SQL 6.5 for years
now,
> keeps getting cannot insert duplicate key into PK_ blah blah. This error
is
> being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
> problem that their SQL 6.5 has created in the db. The problem is after
> running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and
UPDATEUSAGE
> DBCC commands none of these have corrected the fault.
> I'm sure I've had this problem crop up once before and doing the CHECKDB
> fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
> Can you give me any pointers on how I can check and more importantly
resolve
> this issue?
> Thanks Robbie
>
>|||Yes, it was.
SQL Server 6.5 has a few issues with Identity columns. Those issues went
away with SQL server 7.0.
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
As an aside, SQL Server 6.5 is no longer supported by Microsoft, so those
clients would be well advised to move to SQL Server 2000.
Regards
Mike
"Keith Kratochvil" wrote:
> I cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try usi
ng
> it.
> --
> Keith
>
> "Astra" <info@.noemail.com> wrote in message
> news:ex1KtA1GFHA.3088@.tk2msftngp13.phx.gbl...
> have
> now,
> is
> UPDATEUSAGE
> resolve
>
Really need your urgent help on this!
Created a system that uses an SQL database as the data store. Clients have
been using this same DDL since SQL 6.5 and I've got lots of users running
SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
However, one user, who has been running the system on SQL 6.5 for years now,
keeps getting cannot insert duplicate key into PK_ blah blah. This error is
being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
problem that their SQL 6.5 has created in the db. The problem is after
running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and UPDATEUSAGE
DBCC commands none of these have corrected the fault.
I'm sure I've had this problem crop up once before and doing the CHECKDB
fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
Can you give me any pointers on how I can check and more importantly resolve
this issue?
Thanks RobbieI cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try using
it.
Keith
"Astra" <info@.noemail.com> wrote in message
news:ex1KtA1GFHA.3088@.tk2msftngp13.phx.gbl...
> Hi All
> Really need your urgent help on this!
> Created a system that uses an SQL database as the data store. Clients
have
> been using this same DDL since SQL 6.5 and I've got lots of users running
> SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
> However, one user, who has been running the system on SQL 6.5 for years
now,
> keeps getting cannot insert duplicate key into PK_ blah blah. This error
is
> being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
> problem that their SQL 6.5 has created in the db. The problem is after
> running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and
UPDATEUSAGE
> DBCC commands none of these have corrected the fault.
> I'm sure I've had this problem crop up once before and doing the CHECKDB
> fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
> Can you give me any pointers on how I can check and more importantly
resolve
> this issue?
> Thanks Robbie
>
>|||Yes, it was.
SQL Server 6.5 has a few issues with Identity columns. Those issues went
away with SQL server 7.0.
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
As an aside, SQL Server 6.5 is no longer supported by Microsoft, so those
clients would be well advised to move to SQL Server 2000.
Regards
Mike
"Keith Kratochvil" wrote:
> I cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try usi
ng
> it.
> --
> Keith
>
> "Astra" <info@.noemail.com> wrote in message
> news:ex1KtA1GFHA.3088@.tk2msftngp13.phx.gbl...
> have
> now,
> is
> UPDATEUSAGE
> resolve
>
Cannot insert duplicate key into ...... EXTREME problem
Hi All
Really need your urgent help on this!
Created a system that uses an SQL database as the data store. Clients have
been using this same DDL since SQL 6.5 and I've got lots of users running
SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
However, one user, who has been running the system on SQL 6.5 for years now,
keeps getting cannot insert duplicate key into PK_ blah blah. This error is
being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
problem that their SQL 6.5 has created in the db. The problem is after
running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and UPDATEUSAGE
DBCC commands none of these have corrected the fault.
I'm sure I've had this problem crop up once before and doing the CHECKDB
fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
Can you give me any pointers on how I can check and more importantly resolve
this issue?
Thanks RobbieI cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try using
it.
--
Keith
"Astra" <info@.noemail.com> wrote in message
news:OQdxVA1GFHA.3128@.TK2MSFTNGP10.phx.gbl...
> Hi All
> Really need your urgent help on this!
> Created a system that uses an SQL database as the data store. Clients
have
> been using this same DDL since SQL 6.5 and I've got lots of users running
> SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
> However, one user, who has been running the system on SQL 6.5 for years
now,
> keeps getting cannot insert duplicate key into PK_ blah blah. This error
is
> being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
> problem that their SQL 6.5 has created in the db. The problem is after
> running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and
UPDATEUSAGE
> DBCC commands none of these have corrected the fault.
> I'm sure I've had this problem crop up once before and doing the CHECKDB
> fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
> Can you give me any pointers on how I can check and more importantly
resolve
> this issue?
> Thanks Robbie
>|||Hi Astra,
I do not have any 6.5 experience however I have seen this
problem on 2000.
Within 2000 if you run the command
SELECT TOP 100 PERCENT name, IDENT_SEED(name) AS [ID
Start], IDENT_INCR(name) AS [ID Increment], IDENT_CURRENT
(name) AS [Current ID]
FROM dbo.sysobjects
WHERE xtype = 'U'
ORDER BY IDENT_SEED(name)
You will get the current identity values for the user
tables in your database.
I think that somehow one on your 6.5 has got out of
sequence i.e. the current identity is 4, but internally it
thinks it 3, so it trys to insert 4 in again.
How we solved this in 2000 was with the DBCC CHECKIDENT
command.
Peter
"Although prepared for martyrdom, I preferred that it be
postponed."
Winston Churchill
>--Original Message--
>Hi All
>Really need your urgent help on this!
>Created a system that uses an SQL database as the data
store. Clients have
>been using this same DDL since SQL 6.5 and I've got lots
of users running
>SQL 6.5, 7, 2000 and MSDE with the same DDL and they
don't have a problem.
>However, one user, who has been running the system on SQL
6.5 for years now,
>keeps getting cannot insert duplicate key into PK_ blah
blah. This error is
>being generated on SQL 6.5's own auto-generated ID so I'm
sure it is a
>problem that their SQL 6.5 has created in the db. The
problem is after
>running the usual CHECKDB, CHECKALLOC, NEWALLOC,
CHECKTABLE and UPDATEUSAGE
>DBCC commands none of these have corrected the fault.
>I'm sure I've had this problem crop up once before and
doing the CHECKDB
>fixed it, but I've run the CHECKDB 3 times now and it
doesn't correct it.
>Can you give me any pointers on how I can check and more
importantly resolve
>this issue?
>Thanks Robbie
>
>.
>|||Yes, it was.
SQL Server 6.5 has a few issues with Identity columns. Those issues went
away with SQL server 7.0.
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
As an aside, SQL Server 6.5 is no longer supported by Microsoft, so those
clients would be well advised to move to SQL Server 2000.
Regards
Mike
"Keith Kratochvil" wrote:
> I cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try using
> it.
> --
> Keith
>
> "Astra" <info@.noemail.com> wrote in message
> news:OQdxVA1GFHA.3128@.TK2MSFTNGP10.phx.gbl...
> > Hi All
> >
> > Really need your urgent help on this!
> >
> > Created a system that uses an SQL database as the data store. Clients
> have
> > been using this same DDL since SQL 6.5 and I've got lots of users running
> > SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
> >
> > However, one user, who has been running the system on SQL 6.5 for years
> now,
> > keeps getting cannot insert duplicate key into PK_ blah blah. This error
> is
> > being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
> > problem that their SQL 6.5 has created in the db. The problem is after
> > running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and
> UPDATEUSAGE
> > DBCC commands none of these have corrected the fault.
> >
> > I'm sure I've had this problem crop up once before and doing the CHECKDB
> > fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
> >
> > Can you give me any pointers on how I can check and more importantly
> resolve
> > this issue?
> >
> > Thanks Robbie
> >
> >
>|||Guys
You've made my weekend!!!
Many thanks.
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:1ad701c51b53$2a8f0a20$a601280a@.phx.gbl...
Hi Astra,
I do not have any 6.5 experience however I have seen this
problem on 2000.
Within 2000 if you run the command
SELECT TOP 100 PERCENT name, IDENT_SEED(name) AS [ID
Start], IDENT_INCR(name) AS [ID Increment], IDENT_CURRENT
(name) AS [Current ID]
FROM dbo.sysobjects
WHERE xtype = 'U'
ORDER BY IDENT_SEED(name)
You will get the current identity values for the user
tables in your database.
I think that somehow one on your 6.5 has got out of
sequence i.e. the current identity is 4, but internally it
thinks it 3, so it trys to insert 4 in again.
How we solved this in 2000 was with the DBCC CHECKIDENT
command.
Peter
"Although prepared for martyrdom, I preferred that it be
postponed."
Winston Churchill
>--Original Message--
>Hi All
>Really need your urgent help on this!
>Created a system that uses an SQL database as the data
store. Clients have
>been using this same DDL since SQL 6.5 and I've got lots
of users running
>SQL 6.5, 7, 2000 and MSDE with the same DDL and they
don't have a problem.
>However, one user, who has been running the system on SQL
6.5 for years now,
>keeps getting cannot insert duplicate key into PK_ blah
blah. This error is
>being generated on SQL 6.5's own auto-generated ID so I'm
sure it is a
>problem that their SQL 6.5 has created in the db. The
problem is after
>running the usual CHECKDB, CHECKALLOC, NEWALLOC,
CHECKTABLE and UPDATEUSAGE
>DBCC commands none of these have corrected the fault.
>I'm sure I've had this problem crop up once before and
doing the CHECKDB
>fixed it, but I've run the CHECKDB 3 times now and it
doesn't correct it.
>Can you give me any pointers on how I can check and more
importantly resolve
>this issue?
>Thanks Robbie
>
>.
>
Really need your urgent help on this!
Created a system that uses an SQL database as the data store. Clients have
been using this same DDL since SQL 6.5 and I've got lots of users running
SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
However, one user, who has been running the system on SQL 6.5 for years now,
keeps getting cannot insert duplicate key into PK_ blah blah. This error is
being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
problem that their SQL 6.5 has created in the db. The problem is after
running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and UPDATEUSAGE
DBCC commands none of these have corrected the fault.
I'm sure I've had this problem crop up once before and doing the CHECKDB
fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
Can you give me any pointers on how I can check and more importantly resolve
this issue?
Thanks RobbieI cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try using
it.
--
Keith
"Astra" <info@.noemail.com> wrote in message
news:OQdxVA1GFHA.3128@.TK2MSFTNGP10.phx.gbl...
> Hi All
> Really need your urgent help on this!
> Created a system that uses an SQL database as the data store. Clients
have
> been using this same DDL since SQL 6.5 and I've got lots of users running
> SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
> However, one user, who has been running the system on SQL 6.5 for years
now,
> keeps getting cannot insert duplicate key into PK_ blah blah. This error
is
> being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
> problem that their SQL 6.5 has created in the db. The problem is after
> running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and
UPDATEUSAGE
> DBCC commands none of these have corrected the fault.
> I'm sure I've had this problem crop up once before and doing the CHECKDB
> fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
> Can you give me any pointers on how I can check and more importantly
resolve
> this issue?
> Thanks Robbie
>|||Hi Astra,
I do not have any 6.5 experience however I have seen this
problem on 2000.
Within 2000 if you run the command
SELECT TOP 100 PERCENT name, IDENT_SEED(name) AS [ID
Start], IDENT_INCR(name) AS [ID Increment], IDENT_CURRENT
(name) AS [Current ID]
FROM dbo.sysobjects
WHERE xtype = 'U'
ORDER BY IDENT_SEED(name)
You will get the current identity values for the user
tables in your database.
I think that somehow one on your 6.5 has got out of
sequence i.e. the current identity is 4, but internally it
thinks it 3, so it trys to insert 4 in again.
How we solved this in 2000 was with the DBCC CHECKIDENT
command.
Peter
"Although prepared for martyrdom, I preferred that it be
postponed."
Winston Churchill
>--Original Message--
>Hi All
>Really need your urgent help on this!
>Created a system that uses an SQL database as the data
store. Clients have
>been using this same DDL since SQL 6.5 and I've got lots
of users running
>SQL 6.5, 7, 2000 and MSDE with the same DDL and they
don't have a problem.
>However, one user, who has been running the system on SQL
6.5 for years now,
>keeps getting cannot insert duplicate key into PK_ blah
blah. This error is
>being generated on SQL 6.5's own auto-generated ID so I'm
sure it is a
>problem that their SQL 6.5 has created in the db. The
problem is after
>running the usual CHECKDB, CHECKALLOC, NEWALLOC,
CHECKTABLE and UPDATEUSAGE
>DBCC commands none of these have corrected the fault.
>I'm sure I've had this problem crop up once before and
doing the CHECKDB
>fixed it, but I've run the CHECKDB 3 times now and it
doesn't correct it.
>Can you give me any pointers on how I can check and more
importantly resolve
>this issue?
>Thanks Robbie
>
>.
>|||Yes, it was.
SQL Server 6.5 has a few issues with Identity columns. Those issues went
away with SQL server 7.0.
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
As an aside, SQL Server 6.5 is no longer supported by Microsoft, so those
clients would be well advised to move to SQL Server 2000.
Regards
Mike
"Keith Kratochvil" wrote:
> I cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try using
> it.
> --
> Keith
>
> "Astra" <info@.noemail.com> wrote in message
> news:OQdxVA1GFHA.3128@.TK2MSFTNGP10.phx.gbl...
> > Hi All
> >
> > Really need your urgent help on this!
> >
> > Created a system that uses an SQL database as the data store. Clients
> have
> > been using this same DDL since SQL 6.5 and I've got lots of users running
> > SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
> >
> > However, one user, who has been running the system on SQL 6.5 for years
> now,
> > keeps getting cannot insert duplicate key into PK_ blah blah. This error
> is
> > being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
> > problem that their SQL 6.5 has created in the db. The problem is after
> > running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and
> UPDATEUSAGE
> > DBCC commands none of these have corrected the fault.
> >
> > I'm sure I've had this problem crop up once before and doing the CHECKDB
> > fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
> >
> > Can you give me any pointers on how I can check and more importantly
> resolve
> > this issue?
> >
> > Thanks Robbie
> >
> >
>|||Guys
You've made my weekend!!!
Many thanks.
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:1ad701c51b53$2a8f0a20$a601280a@.phx.gbl...
Hi Astra,
I do not have any 6.5 experience however I have seen this
problem on 2000.
Within 2000 if you run the command
SELECT TOP 100 PERCENT name, IDENT_SEED(name) AS [ID
Start], IDENT_INCR(name) AS [ID Increment], IDENT_CURRENT
(name) AS [Current ID]
FROM dbo.sysobjects
WHERE xtype = 'U'
ORDER BY IDENT_SEED(name)
You will get the current identity values for the user
tables in your database.
I think that somehow one on your 6.5 has got out of
sequence i.e. the current identity is 4, but internally it
thinks it 3, so it trys to insert 4 in again.
How we solved this in 2000 was with the DBCC CHECKIDENT
command.
Peter
"Although prepared for martyrdom, I preferred that it be
postponed."
Winston Churchill
>--Original Message--
>Hi All
>Really need your urgent help on this!
>Created a system that uses an SQL database as the data
store. Clients have
>been using this same DDL since SQL 6.5 and I've got lots
of users running
>SQL 6.5, 7, 2000 and MSDE with the same DDL and they
don't have a problem.
>However, one user, who has been running the system on SQL
6.5 for years now,
>keeps getting cannot insert duplicate key into PK_ blah
blah. This error is
>being generated on SQL 6.5's own auto-generated ID so I'm
sure it is a
>problem that their SQL 6.5 has created in the db. The
problem is after
>running the usual CHECKDB, CHECKALLOC, NEWALLOC,
CHECKTABLE and UPDATEUSAGE
>DBCC commands none of these have corrected the fault.
>I'm sure I've had this problem crop up once before and
doing the CHECKDB
>fixed it, but I've run the CHECKDB 3 times now and it
doesn't correct it.
>Can you give me any pointers on how I can check and more
importantly resolve
>this issue?
>Thanks Robbie
>
>.
>
Cannot insert duplicate key into ...... EXTREME problem
Hi Astra,
I do not have any 6.5 experience however I have seen this
problem on 2000.
Within 2000 if you run the command
SELECT TOP 100 PERCENT name, IDENT_SEED(name) AS [ID
Start], IDENT_INCR(name) AS [ID Increment], IDENT_CURRENT
(name) AS [Current ID]
FROM dbo.sysobjects
WHERE xtype = 'U'
ORDER BY IDENT_SEED(name)
You will get the current identity values for the user
tables in your database.
I think that somehow one on your 6.5 has got out of
sequence i.e. the current identity is 4, but internally it
thinks it 3, so it trys to insert 4 in again.
How we solved this in 2000 was with the DBCC CHECKIDENT
command.
Peter
"Although prepared for martyrdom, I preferred that it be
postponed."
Winston Churchill
>--Original Message--
>Hi All
>Really need your urgent help on this!
>Created a system that uses an SQL database as the data
store. Clients have
>been using this same DDL since SQL 6.5 and I've got lots
of users running
>SQL 6.5, 7, 2000 and MSDE with the same DDL and they
don't have a problem.
>However, one user, who has been running the system on SQL
6.5 for years now,
>keeps getting cannot insert duplicate key into PK_ blah
blah. This error is
>being generated on SQL 6.5's own auto-generated ID so I'm
sure it is a
>problem that their SQL 6.5 has created in the db. The
problem is after
>running the usual CHECKDB, CHECKALLOC, NEWALLOC,
CHECKTABLE and UPDATEUSAGE
>DBCC commands none of these have corrected the fault.
>I'm sure I've had this problem crop up once before and
doing the CHECKDB
>fixed it, but I've run the CHECKDB 3 times now and it
doesn't correct it.
>Can you give me any pointers on how I can check and more
importantly resolve
>this issue?
>Thanks Robbie
>
>.
>Guys
You've made my weekend!!!
Many thanks.
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:1ad701c51b53$2a8f0a20$a601280a@.phx.gbl...
Hi Astra,
I do not have any 6.5 experience however I have seen this
problem on 2000.
Within 2000 if you run the command
SELECT TOP 100 PERCENT name, IDENT_SEED(name) AS [ID
Start], IDENT_INCR(name) AS [ID Increment], IDENT_CURRENT
(name) AS [Current ID]
FROM dbo.sysobjects
WHERE xtype = 'U'
ORDER BY IDENT_SEED(name)
You will get the current identity values for the user
tables in your database.
I think that somehow one on your 6.5 has got out of
sequence i.e. the current identity is 4, but internally it
thinks it 3, so it trys to insert 4 in again.
How we solved this in 2000 was with the DBCC CHECKIDENT
command.
Peter
"Although prepared for martyrdom, I preferred that it be
postponed."
Winston Churchill
>--Original Message--
>Hi All
>Really need your urgent help on this!
>Created a system that uses an SQL database as the data
store. Clients have
>been using this same DDL since SQL 6.5 and I've got lots
of users running
>SQL 6.5, 7, 2000 and MSDE with the same DDL and they
don't have a problem.
>However, one user, who has been running the system on SQL
6.5 for years now,
>keeps getting cannot insert duplicate key into PK_ blah
blah. This error is
>being generated on SQL 6.5's own auto-generated ID so I'm
sure it is a
>problem that their SQL 6.5 has created in the db. The
problem is after
>running the usual CHECKDB, CHECKALLOC, NEWALLOC,
CHECKTABLE and UPDATEUSAGE
>DBCC commands none of these have corrected the fault.
>I'm sure I've had this problem crop up once before and
doing the CHECKDB
>fixed it, but I've run the CHECKDB 3 times now and it
doesn't correct it.
>Can you give me any pointers on how I can check and more
importantly resolve
>this issue?
>Thanks Robbie
>
>.
>
I do not have any 6.5 experience however I have seen this
problem on 2000.
Within 2000 if you run the command
SELECT TOP 100 PERCENT name, IDENT_SEED(name) AS [ID
Start], IDENT_INCR(name) AS [ID Increment], IDENT_CURRENT
(name) AS [Current ID]
FROM dbo.sysobjects
WHERE xtype = 'U'
ORDER BY IDENT_SEED(name)
You will get the current identity values for the user
tables in your database.
I think that somehow one on your 6.5 has got out of
sequence i.e. the current identity is 4, but internally it
thinks it 3, so it trys to insert 4 in again.
How we solved this in 2000 was with the DBCC CHECKIDENT
command.
Peter
"Although prepared for martyrdom, I preferred that it be
postponed."
Winston Churchill
>--Original Message--
>Hi All
>Really need your urgent help on this!
>Created a system that uses an SQL database as the data
store. Clients have
>been using this same DDL since SQL 6.5 and I've got lots
of users running
>SQL 6.5, 7, 2000 and MSDE with the same DDL and they
don't have a problem.
>However, one user, who has been running the system on SQL
6.5 for years now,
>keeps getting cannot insert duplicate key into PK_ blah
blah. This error is
>being generated on SQL 6.5's own auto-generated ID so I'm
sure it is a
>problem that their SQL 6.5 has created in the db. The
problem is after
>running the usual CHECKDB, CHECKALLOC, NEWALLOC,
CHECKTABLE and UPDATEUSAGE
>DBCC commands none of these have corrected the fault.
>I'm sure I've had this problem crop up once before and
doing the CHECKDB
>fixed it, but I've run the CHECKDB 3 times now and it
doesn't correct it.
>Can you give me any pointers on how I can check and more
importantly resolve
>this issue?
>Thanks Robbie
>
>.
>Guys
You've made my weekend!!!
Many thanks.
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:1ad701c51b53$2a8f0a20$a601280a@.phx.gbl...
Hi Astra,
I do not have any 6.5 experience however I have seen this
problem on 2000.
Within 2000 if you run the command
SELECT TOP 100 PERCENT name, IDENT_SEED(name) AS [ID
Start], IDENT_INCR(name) AS [ID Increment], IDENT_CURRENT
(name) AS [Current ID]
FROM dbo.sysobjects
WHERE xtype = 'U'
ORDER BY IDENT_SEED(name)
You will get the current identity values for the user
tables in your database.
I think that somehow one on your 6.5 has got out of
sequence i.e. the current identity is 4, but internally it
thinks it 3, so it trys to insert 4 in again.
How we solved this in 2000 was with the DBCC CHECKIDENT
command.
Peter
"Although prepared for martyrdom, I preferred that it be
postponed."
Winston Churchill
>--Original Message--
>Hi All
>Really need your urgent help on this!
>Created a system that uses an SQL database as the data
store. Clients have
>been using this same DDL since SQL 6.5 and I've got lots
of users running
>SQL 6.5, 7, 2000 and MSDE with the same DDL and they
don't have a problem.
>However, one user, who has been running the system on SQL
6.5 for years now,
>keeps getting cannot insert duplicate key into PK_ blah
blah. This error is
>being generated on SQL 6.5's own auto-generated ID so I'm
sure it is a
>problem that their SQL 6.5 has created in the db. The
problem is after
>running the usual CHECKDB, CHECKALLOC, NEWALLOC,
CHECKTABLE and UPDATEUSAGE
>DBCC commands none of these have corrected the fault.
>I'm sure I've had this problem crop up once before and
doing the CHECKDB
>fixed it, but I've run the CHECKDB 3 times now and it
doesn't correct it.
>Can you give me any pointers on how I can check and more
importantly resolve
>this issue?
>Thanks Robbie
>
>.
>
Cannot insert duplicate key into ...... EXTREME problem
Hi All
Really need your urgent help on this!
Created a system that uses an SQL database as the data store. Clients have
been using this same DDL since SQL 6.5 and I've got lots of users running
SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
However, one user, who has been running the system on SQL 6.5 for years now,
keeps getting cannot insert duplicate key into PK_ blah blah. This error is
being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
problem that their SQL 6.5 has created in the db. The problem is after
running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and UPDATEUSAGE
DBCC commands none of these have corrected the fault.
I'm sure I've had this problem crop up once before and doing the CHECKDB
fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
Can you give me any pointers on how I can check and more importantly resolve
this issue?
Thanks RobbieI cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try using
it.
Keith
"Astra" <info@.noemail.com> wrote in message
news:OQdxVA1GFHA.3128@.TK2MSFTNGP10.phx.gbl...
> Hi All
> Really need your urgent help on this!
> Created a system that uses an SQL database as the data store. Clients
have
> been using this same DDL since SQL 6.5 and I've got lots of users running
> SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
> However, one user, who has been running the system on SQL 6.5 for years
now,
> keeps getting cannot insert duplicate key into PK_ blah blah. This error
is
> being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
> problem that their SQL 6.5 has created in the db. The problem is after
> running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and
UPDATEUSAGE
> DBCC commands none of these have corrected the fault.
> I'm sure I've had this problem crop up once before and doing the CHECKDB
> fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
> Can you give me any pointers on how I can check and more importantly
resolve
> this issue?
> Thanks Robbie
>|||Yes, it was.
SQL Server 6.5 has a few issues with Identity columns. Those issues went
away with SQL server 7.0.
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
As an aside, SQL Server 6.5 is no longer supported by Microsoft, so those
clients would be well advised to move to SQL Server 2000.
Regards
Mike
"Keith Kratochvil" wrote:
> I cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try usi
ng
> it.
> --
> Keith
>
> "Astra" <info@.noemail.com> wrote in message
> news:OQdxVA1GFHA.3128@.TK2MSFTNGP10.phx.gbl...
> have
> now,
> is
> UPDATEUSAGE
> resolve
>
Really need your urgent help on this!
Created a system that uses an SQL database as the data store. Clients have
been using this same DDL since SQL 6.5 and I've got lots of users running
SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
However, one user, who has been running the system on SQL 6.5 for years now,
keeps getting cannot insert duplicate key into PK_ blah blah. This error is
being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
problem that their SQL 6.5 has created in the db. The problem is after
running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and UPDATEUSAGE
DBCC commands none of these have corrected the fault.
I'm sure I've had this problem crop up once before and doing the CHECKDB
fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
Can you give me any pointers on how I can check and more importantly resolve
this issue?
Thanks RobbieI cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try using
it.
Keith
"Astra" <info@.noemail.com> wrote in message
news:OQdxVA1GFHA.3128@.TK2MSFTNGP10.phx.gbl...
> Hi All
> Really need your urgent help on this!
> Created a system that uses an SQL database as the data store. Clients
have
> been using this same DDL since SQL 6.5 and I've got lots of users running
> SQL 6.5, 7, 2000 and MSDE with the same DDL and they don't have a problem.
> However, one user, who has been running the system on SQL 6.5 for years
now,
> keeps getting cannot insert duplicate key into PK_ blah blah. This error
is
> being generated on SQL 6.5's own auto-generated ID so I'm sure it is a
> problem that their SQL 6.5 has created in the db. The problem is after
> running the usual CHECKDB, CHECKALLOC, NEWALLOC, CHECKTABLE and
UPDATEUSAGE
> DBCC commands none of these have corrected the fault.
> I'm sure I've had this problem crop up once before and doing the CHECKDB
> fixed it, but I've run the CHECKDB 3 times now and it doesn't correct it.
> Can you give me any pointers on how I can check and more importantly
resolve
> this issue?
> Thanks Robbie
>|||Yes, it was.
SQL Server 6.5 has a few issues with Identity columns. Those issues went
away with SQL server 7.0.
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
As an aside, SQL Server 6.5 is no longer supported by Microsoft, so those
clients would be well advised to move to SQL Server 2000.
Regards
Mike
"Keith Kratochvil" wrote:
> I cannot remember. Is DBCC CHECKIDENT included with 6.5? If it is try usi
ng
> it.
> --
> Keith
>
> "Astra" <info@.noemail.com> wrote in message
> news:OQdxVA1GFHA.3128@.TK2MSFTNGP10.phx.gbl...
> have
> now,
> is
> UPDATEUSAGE
> resolve
>
Cannot insert duplicate key in object 'dbo.RunningJobs'
I'm using SSRS 2005 on a Server 2003 machine.
Some of my reports run fine but others take a long time to run and sometimes fail with an http error 503.
After a report errror happens I find entries in the event log that says: cannot open a connection to the report server
When I look at the log files for Reporting services I see an error caused by a primary key violation on the Running Jobs Table:
Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'
Does anyone know why do I get such errors?
Bellow is the error message in the logs:
w3wp!library!6!21/08/2006-09:30:22:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. > System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Library.RunningJobsDb.AddRunningJobs(Hashtable runningJobs)
This error message is not really a critical one. For now as a workaround try to restart the SQL Server Reporting Services Web Service in IIS (e.g. by using iisreset).
When the RS Web service restarts and there is no immediate load on the service during the actual service startup, it will work just fine after the restart.
-- Robert
|||The error is critical for my system because reports are crashing and running very slow.
Is this a known bug? Why would RServices generate primary key conflicts on its own tables? I don't have any subscriptions at all on the server. It just runs 2 or 3 on-demand reports. Anyone has had this problem before? It would be good to at least have a better solution than the workaround of restarting the service.
|||Yes - I get this problem intermittently. It seems to happen when we are processing some of the large, resource intense reports (reports with 20+ subreports and complex SQL). This problem occurs in RS2005 - never experienced it in RS2003.
It does not cleanly correlate to subscriptions running either, as far as I can tell.
I have found no workaround and occasionally our reports do fail.
Any insight would be appreciated, please?
Matt
|||There is a bug right now that produces PK violation error when your app pool is started under load. You can verify this by looking at the log file, and notice that there are multiples of the same entry. The work around is to not start under load. Notice sometimes IIS decides to recycle worker process, so you may get this error in the middle of your tests.
We are currently considering putting the fix in SP2. It is very likely to go through.
|||The problem I have is that I've got thousands of reports that hit our servers weekly and when this started occurring, I have to drop this server out of our externally load balanced cluster so that no clients can access it, then I have to recycle the app pool and then all of my customers reports fail that were in process. Then I put it back in the cluster so that it can be accessed again. Further, it doesn't fix the problem. Just today I did this procedure and it wenr right along dropping reports.Is there a hotfix available for this outside of SP2? We've got some pretty serious impact with this bug.|||Is there any solution to this issue? We too are experiencing this. A hotfix?|||
Hi Mattyb83,
The issue regarding this:
Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'.
is fixed in SQL 2005 Reporting Services SP2.
Before SP2 was shipped the workaround used by many people was iisreset.
Mihaela
|||Hi Mihaela,
I'm facing such issue as well on my SQL 2005 server. As you have mentioned, it is fixed in SQL 2005 SP2. Is it confirmed? I did browse through the fixes but did not find any fix related to this issue. Appreciate if you could help verify.
Thanks
Barry
Cannot insert duplicate key in object 'dbo.RunningJobs'
I'm using SSRS 2005 on a Server 2003 machine.
Some of my reports run fine but others take a long time to run and sometimes fail with an http error 503.
After a report errror happens I find entries in the event log that says: cannot open a connection to the report server
When I look at the log files for Reporting services I see an error caused by a primary key violation on the Running Jobs Table:
Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'
Does anyone know why do I get such errors?
Bellow is the error message in the logs:
w3wp!library!6!21/08/2006-09:30:22:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. > System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Library.RunningJobsDb.AddRunningJobs(Hashtable runningJobs)
This error message is not really a critical one. For now as a workaround try to restart the SQL Server Reporting Services Web Service in IIS (e.g. by using iisreset).
When the RS Web service restarts and there is no immediate load on the service during the actual service startup, it will work just fine after the restart.
-- Robert
|||The error is critical for my system because reports are crashing and running very slow.
Is this a known bug? Why would RServices generate primary key conflicts on its own tables? I don't have any subscriptions at all on the server. It just runs 2 or 3 on-demand reports. Anyone has had this problem before? It would be good to at least have a better solution than the workaround of restarting the service.
|||Yes - I get this problem intermittently. It seems to happen when we are processing some of the large, resource intense reports (reports with 20+ subreports and complex SQL). This problem occurs in RS2005 - never experienced it in RS2003.
It does not cleanly correlate to subscriptions running either, as far as I can tell.
I have found no workaround and occasionally our reports do fail.
Any insight would be appreciated, please?
Matt
|||There is a bug right now that produces PK violation error when your app pool is started under load. You can verify this by looking at the log file, and notice that there are multiples of the same entry. The work around is to not start under load. Notice sometimes IIS decides to recycle worker process, so you may get this error in the middle of your tests.
We are currently considering putting the fix in SP2. It is very likely to go through.
|||The problem I have is that I've got thousands of reports that hit our servers weekly and when this started occurring, I have to drop this server out of our externally load balanced cluster so that no clients can access it, then I have to recycle the app pool and then all of my customers reports fail that were in process. Then I put it back in the cluster so that it can be accessed again. Further, it doesn't fix the problem. Just today I did this procedure and it wenr right along dropping reports.Is there a hotfix available for this outside of SP2? We've got some pretty serious impact with this bug.|||Is there any solution to this issue? We too are experiencing this. A hotfix?|||
Hi Mattyb83,
The issue regarding this:
Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'.
is fixed in SQL 2005 Reporting Services SP2.
Before SP2 was shipped the workaround used by many people was iisreset.
Mihaela
|||Hi Mihaela,
I'm facing such issue as well on my SQL 2005 server. As you have mentioned, it is fixed in SQL 2005 SP2. Is it confirmed? I did browse through the fixes but did not find any fix related to this issue. Appreciate if you could help verify.
Thanks
Barry
Cannot insert duplicate key in object 'dbo.RunningJobs'
I'm using SSRS 2005 on a Server 2003 machine.
Some of my reports run fine but others take a long time to run and sometimes fail with an http error 503.
After a report errror happens I find entries in the event log that says: cannot open a connection to the report server
When I look at the log files for Reporting services I see an error caused by a primary key violation on the Running Jobs Table:
Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'
Does anyone know why do I get such errors?
Bellow is the error message in the logs:
w3wp!library!6!21/08/2006-09:30:22:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. > System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Library.RunningJobsDb.AddRunningJobs(Hashtable runningJobs)
This error message is not really a critical one. For now as a workaround try to restart the SQL Server Reporting Services Web Service in IIS (e.g. by using iisreset).
When the RS Web service restarts and there is no immediate load on the service during the actual service startup, it will work just fine after the restart.
-- Robert
|||The error is critical for my system because reports are crashing and running very slow.
Is this a known bug? Why would RServices generate primary key conflicts on its own tables? I don't have any subscriptions at all on the server. It just runs 2 or 3 on-demand reports. Anyone has had this problem before? It would be good to at least have a better solution than the workaround of restarting the service.
|||Yes - I get this problem intermittently. It seems to happen when we are processing some of the large, resource intense reports (reports with 20+ subreports and complex SQL). This problem occurs in RS2005 - never experienced it in RS2003.
It does not cleanly correlate to subscriptions running either, as far as I can tell.
I have found no workaround and occasionally our reports do fail.
Any insight would be appreciated, please?
Matt
|||There is a bug right now that produces PK violation error when your app pool is started under load. You can verify this by looking at the log file, and notice that there are multiples of the same entry. The work around is to not start under load. Notice sometimes IIS decides to recycle worker process, so you may get this error in the middle of your tests.
We are currently considering putting the fix in SP2. It is very likely to go through.
|||The problem I have is that I've got thousands of reports that hit our servers weekly and when this started occurring, I have to drop this server out of our externally load balanced cluster so that no clients can access it, then I have to recycle the app pool and then all of my customers reports fail that were in process. Then I put it back in the cluster so that it can be accessed again. Further, it doesn't fix the problem. Just today I did this procedure and it wenr right along dropping reports.Is there a hotfix available for this outside of SP2? We've got some pretty serious impact with this bug.|||Is there any solution to this issue? We too are experiencing this. A hotfix?|||
Hi Mattyb83,
The issue regarding this:
Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'.
is fixed in SQL 2005 Reporting Services SP2.
Before SP2 was shipped the workaround used by many people was iisreset.
Mihaela
|||Hi Mihaela,
I'm facing such issue as well on my SQL 2005 server. As you have mentioned, it is fixed in SQL 2005 SP2. Is it confirmed? I did browse through the fixes but did not find any fix related to this issue. Appreciate if you could help verify.
Thanks
Barry
Cannot insert duplicate key in object 'dbo.RunningJobs'
I'm using SSRS 2005 on a Server 2003 machine.
Some of my reports run fine but others take a long time to run and sometimes fail with an http error 503.
After a report errror happens I find entries in the event log that says: cannot open a connection to the report server
When I look at the log files for Reporting services I see an error caused by a primary key violation on the Running Jobs Table:
Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'
Does anyone know why do I get such errors?
Bellow is the error message in the logs:
w3wp!library!6!21/08/2006-09:30:22:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. > System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Library.RunningJobsDb.AddRunningJobs(Hashtable runningJobs)
This error message is not really a critical one. For now as a workaround try to restart the SQL Server Reporting Services Web Service in IIS (e.g. by using iisreset).
When the RS Web service restarts and there is no immediate load on the service during the actual service startup, it will work just fine after the restart.
-- Robert
|||The error is critical for my system because reports are crashing and running very slow.
Is this a known bug? Why would RServices generate primary key conflicts on its own tables? I don't have any subscriptions at all on the server. It just runs 2 or 3 on-demand reports. Anyone has had this problem before? It would be good to at least have a better solution than the workaround of restarting the service.
|||Yes - I get this problem intermittently. It seems to happen when we are processing some of the large, resource intense reports (reports with 20+ subreports and complex SQL). This problem occurs in RS2005 - never experienced it in RS2003.
It does not cleanly correlate to subscriptions running either, as far as I can tell.
I have found no workaround and occasionally our reports do fail.
Any insight would be appreciated, please?
Matt
|||There is a bug right now that produces PK violation error when your app pool is started under load. You can verify this by looking at the log file, and notice that there are multiples of the same entry. The work around is to not start under load. Notice sometimes IIS decides to recycle worker process, so you may get this error in the middle of your tests.
We are currently considering putting the fix in SP2. It is very likely to go through.
|||The problem I have is that I've got thousands of reports that hit our servers weekly and when this started occurring, I have to drop this server out of our externally load balanced cluster so that no clients can access it, then I have to recycle the app pool and then all of my customers reports fail that were in process. Then I put it back in the cluster so that it can be accessed again. Further, it doesn't fix the problem. Just today I did this procedure and it wenr right along dropping reports.Is there a hotfix available for this outside of SP2? We've got some pretty serious impact with this bug.
|||Is there any solution to this issue? We too are experiencing this. A hotfix?|||
Hi Mattyb83,
The issue regarding this:
Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'.
is fixed in SQL 2005 Reporting Services SP2.
Before SP2 was shipped the workaround used by many people was iisreset.
Mihaela
|||Hi Mihaela,
I'm facing such issue as well on my SQL 2005 server. As you have mentioned, it is fixed in SQL 2005 SP2. Is it confirmed? I did browse through the fixes but did not find any fix related to this issue. Appreciate if you could help verify.
Thanks
Barry
Cannot insert duplicate key
I am using SQL Server 2000. I have a table (tbDupes) with duplicate records. I want to populate another table (tbUnique) with unique records.
My tbDupes has about 50 columns and three of them make up my primary key in tbUnique. I thought I could get unique records by using the following SQL statement.
Code Snippet
INSERT INTO tbUnique
SELECT * FROM tbDupes
I received this error: Cannot insert duplicate key
Is there a database setting that will allow me to insert the first record and prevent any duplicates from being inserted? I thought this was the default behavior and think that maybe the DBA changed it.
I know I can achieve this by creating a cursor with tbDupes and then something like "IF NOT EXISTS(...) THEN INSERT". However, I would prefer to avoid using a cursor.
Try the below query,
INSERT INTO tbUnique
SELECT Distinct * FROM tbDupes
|||That will not work because my primary key is made up of 3 columns. The remaining 47 columns may make the record unique. However, I only want 1 record for each primary key.|||Hi Danny,
Let us assume that the primary key is conformed by (c1, c2, c3), then you can use:
insert into dbo.t1(c1, ..., cn)
select a.c1, ..., a.cn
from dbo.t2 as a
where not exists (
select *
from dbo.t1 as b
where b.c1 = a.c1 and b.c2 = a.c2 and b.c3 = a.c3
)
AMB
|||Ok. One simple & easy way is adding the identity column temporarily, fetch the data using the following query and drop the temp identity column from the table,
Code Snippet
Create Table #duplicates (
[ID1] int ,
[ID2] int ,
[ID3] int ,
[Name1] Varchar(100) ,
[Name2] Varchar(100)
);
Insert Into #duplicates Values('1','1','1','SQLServer','2000');
Insert Into #duplicates Values('1','1','1','SQLServer','2005');
Insert Into #duplicates Values('1','1','2','SQLServer','2005');
Insert Into #duplicates Values('1','1','2','.NET','1.1');
Insert Into #duplicates Values('1','1','3','.NET','1.1');
--Temp Identity Column
Alter table #duplicates add tempid int identity(1,1);
--Required Unique data
select data.id1,data.id2,data.id3,data.Name1,data.Name2 from #duplicates data
Join
(
Select id1, id2, id3, min(tempid) tempid from #duplicates
Group by id1, id2, id3
) required
On required.tempid = data.tempid
--Droping temp identity column
Alter table#duplicatesdrop column tempid
|||Manivannan & Hunchback, thank you for your help.
cannot insert data from one table to another
Hello:
All I want to do is simply copy a column of data from one table to another.
The first time I tried it, I got that blame "cannot insert duplicate primary
KEY constraint" business or whatever. So, I decided to create a duplicate
table (backup table). I did that sucessfully. The point is that I created
a
backup table of the table that I want to copy into so that I could get aroun
d
this error.
I got around that error. But, now, when I try to copy the column from a
table into this backup table, I get a message saying that it cannot insert
the value "NULL" into some other column in the table that I do not care abou
t
anyway.
Is there any way to get around these errors? I just want to copy data from
one column for goodness sakes!!!
Thanks!
childofthe1980sThere is no way for anyone to help unless you provide quite a bit more
information. The error messages are symptoms, but to understand them
required the entire picture.
So, show the table structures (including those pesky keys), and show
what the data looks like, and describe more precisely what you are
trying to accomplish. Before and after views of the data help a lot.
Roy
On Sun, 26 Feb 2006 10:18:26 -0800, "childothe1980s"
<childothe1980s@.discussions.microsoft.com> wrote:
>Hello:
>All I want to do is simply copy a column of data from one table to another.
>The first time I tried it, I got that blame "cannot insert duplicate primar
y
>KEY constraint" business or whatever. So, I decided to create a duplicate
>table (backup table). I did that sucessfully. The point is that I created
a
>backup table of the table that I want to copy into so that I could get arou
nd
>this error.
>I got around that error. But, now, when I try to copy the column from a
>table into this backup table, I get a message saying that it cannot insert
>the value "NULL" into some other column in the table that I do not care abo
ut
>anyway.
>Is there any way to get around these errors? I just want to copy data from
>one column for goodness sakes!!!
>Thanks!
>childofthe1980s|||"childothe1980s" <childothe1980s@.discussions.microsoft.com> wrote in message
news:3C895F9B-8A38-4991-AD42-9B43F5EDFCB8@.microsoft.com...
> Hello:
> All I want to do is simply copy a column of data from one table to
> another.
> The first time I tried it, I got that blame "cannot insert duplicate
> primary
> KEY constraint" business or whatever. So, I decided to create a duplicate
> table (backup table). I did that sucessfully. The point is that I
> created a
> backup table of the table that I want to copy into so that I could get
> around
> this error.
> I got around that error. But, now, when I try to copy the column from a
> table into this backup table, I get a message saying that it cannot insert
> the value "NULL" into some other column in the table that I do not care
> about
> anyway.
> Is there any way to get around these errors? I just want to copy data
> from
> one column for goodness sakes!!!
> Thanks!
> childofthe1980s
DDL and sample data please if you expect a full working solution. See:
http://www.aspfaq.com/etiquette.asp?id=5006
In your INSERT you have to supply a value for the non-nullable columns that
don't have defaults.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
All I want to do is simply copy a column of data from one table to another.
The first time I tried it, I got that blame "cannot insert duplicate primary
KEY constraint" business or whatever. So, I decided to create a duplicate
table (backup table). I did that sucessfully. The point is that I created
a
backup table of the table that I want to copy into so that I could get aroun
d
this error.
I got around that error. But, now, when I try to copy the column from a
table into this backup table, I get a message saying that it cannot insert
the value "NULL" into some other column in the table that I do not care abou
t
anyway.
Is there any way to get around these errors? I just want to copy data from
one column for goodness sakes!!!
Thanks!
childofthe1980sThere is no way for anyone to help unless you provide quite a bit more
information. The error messages are symptoms, but to understand them
required the entire picture.
So, show the table structures (including those pesky keys), and show
what the data looks like, and describe more precisely what you are
trying to accomplish. Before and after views of the data help a lot.
Roy
On Sun, 26 Feb 2006 10:18:26 -0800, "childothe1980s"
<childothe1980s@.discussions.microsoft.com> wrote:
>Hello:
>All I want to do is simply copy a column of data from one table to another.
>The first time I tried it, I got that blame "cannot insert duplicate primar
y
>KEY constraint" business or whatever. So, I decided to create a duplicate
>table (backup table). I did that sucessfully. The point is that I created
a
>backup table of the table that I want to copy into so that I could get arou
nd
>this error.
>I got around that error. But, now, when I try to copy the column from a
>table into this backup table, I get a message saying that it cannot insert
>the value "NULL" into some other column in the table that I do not care abo
ut
>anyway.
>Is there any way to get around these errors? I just want to copy data from
>one column for goodness sakes!!!
>Thanks!
>childofthe1980s|||"childothe1980s" <childothe1980s@.discussions.microsoft.com> wrote in message
news:3C895F9B-8A38-4991-AD42-9B43F5EDFCB8@.microsoft.com...
> Hello:
> All I want to do is simply copy a column of data from one table to
> another.
> The first time I tried it, I got that blame "cannot insert duplicate
> primary
> KEY constraint" business or whatever. So, I decided to create a duplicate
> table (backup table). I did that sucessfully. The point is that I
> created a
> backup table of the table that I want to copy into so that I could get
> around
> this error.
> I got around that error. But, now, when I try to copy the column from a
> table into this backup table, I get a message saying that it cannot insert
> the value "NULL" into some other column in the table that I do not care
> about
> anyway.
> Is there any way to get around these errors? I just want to copy data
> from
> one column for goodness sakes!!!
> Thanks!
> childofthe1980s
DDL and sample data please if you expect a full working solution. See:
http://www.aspfaq.com/etiquette.asp?id=5006
In your INSERT you have to supply a value for the non-nullable columns that
don't have defaults.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Subscribe to:
Posts (Atom)