<div>
&gt;can i just manually edit the table and assign unique ROWID's to them?<br />
Yes, but what's the data type and existing logic for ROWID? &nbsp;int identity(1,1), uniqueidentifier, number some T-SQL code makes up based on custom logic, ? <br />
<br />
&gt;i.e. does SQL use these rowid's as foreign keys?<br />
You tell us, as you have the database and can open SSMS, open the table, and open the Constraints folder to see what foreign keys there are on this table that include ROWID. <br />
<br />
Did these ROWID's make it's way to foreign keys on other tables? &nbsp;If so this can make the cleanup process a lot longer.
</div>


<div>
logic for ROWID, &nbsp;int &nbsp; is identity, 1, 1<br />
<br />
i opened SSMS and douple clicked on the first of 7 FKey references.<br />
within that screen i checked 44 foreign keys listed (not sure why 44 listed here and only 7 listed on the table) tables and specfications non have rowid as a field<br />
<br />
then i ran this<br />

<pre><code class="code-1 nolinks" id="code-20-41437631-1"><span>select t.name as TableWithForeignKey, fk.constraint_column_id as FK_PartNo , c.name as ForeignKeyColumn </span>
<span>from sys.foreign_key_columns as fk</span>
<span>inner join sys.tables as t on fk.parent_object_id = t.object_id</span>
<span>inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id</span>
<span>where fk.referenced_object_id = (select object_id from sys.tables where name = 'PART')</span>
<span>order by TableWithForeignKey, FK_PartNo</span>
</code></pre>
<br />
it listed 36 tables, but none referenced the ROWID field &nbsp;(again not sure why it list 36 and not 44)
</div>


<div>
&gt;A user at my company used access to backdoor adding &quot;PART&quot; records to SQLServer 2008R2 ERP database. &nbsp;Unfortuantly he used 1 for all the ROWID's. <br />
&gt;logic for ROWID, &nbsp;int &nbsp; is identity, 1, 1<br />
For starters, the person that performed this action should be taken outside and shot, as they just blew away the uniqueness of a primary key value that is propogated in foreign keys throughout the database. &nbsp; Were there any production failures? &nbsp;Curiosity overwhelms me.<br />
<br />
&gt;it listed 36 tables, but none referenced the ROWID field<br />
Okay. &nbsp;If that's correct then your problem is not that huge. &nbsp;If not, enjoy your wild goose chase. <br />
<br />
Getting back to the original problem, assuming you can handle the related table issues, I recommend..<br />

<div class="bullet">
Create a new table whose schema matches your table, with the rowid but only as an int, not identity
</div>
<div class="bullet">
INSERT all rows from your table into new table WHERE rowid=1
</div>
<div class="bullet">
(Holy God make a backup before doing this) DELETE all rows in your table WHERE rowid=1
</div>
<div class="bullet">
Verify that the identity(1,1) is set on your table
</div>
<div class="bullet">
INSERT from new table into your table all columns EXCEPT FOR rowid, and the identity(1,1) will assign a new value.
</div>
</div>


<div>
so I am guessing there is no way to just set the rowid to a number using sql, even though it may be one at a time? &nbsp;or maybe change the ROWID to int make the numbers uniquie and then set it back to is identity?<br />
<br />
note: I did try to open for updates and manually try to change the ROWID of one of the records and it did not allow it.
</div>


<div>
&gt;so I am guessing there is no way to just set the rowid to a number using sql<br />
Yes, using SET <a href="https://msdn.microsoft.com/en-us/library/ms188059.aspx" rel="ugc">IDENTITY_INSERT</a> OFF, then insert your rows (as the person who should be shot apparently did), then SET IDENTITY_INSERT ON to set it back. &nbsp;Guessing the last step was not done if there are multiple 1's in the column, as it would have generated an error message.
</div>


<div>
in a sandbox, &nbsp;I removed the set identity on the rowid field. &nbsp;I manually entered in unique values for each record. &nbsp;I set identity to yes and set the seed to 1+ the max rowid, clicked save. &nbsp;it sat for a good while, then I got: <br />
LOCATION' table<br />
- Unable to delete relationship 'FKEY0264'. &nbsp;<br />
Timeout expired. &nbsp;The timeout period elapsed prior to completion of the operation or the server is not responding.<br />
<br />
why would it be trying to delete foreign key relationships that do not use rowid?
</div>


<div>
I spoke to the person and he does not know anything about sql so I do not believe he turned off the set identity to add the records in access. &nbsp;what I do believe is that access let him add the records all with the same rowid.
</div>


<div>
&lt;Wild guess&gt; &nbsp;Eyeballeth thy ROWID values to make sure all values are unique. &nbsp;Or run this &lt;air code&gt; and see if anything is returned<br />

<pre><code class="code-1 nolinks" id="code-20-41437772-1"><span>SELECT ROWID, COUNT(ROWID) </span>
<span>FROM YourTable</span>
<span>GROUP BY ROWID</span>
<span>HAVING COUNT(ROWID) &gt; 1</span>
<span>ORDER BY ROWID</span>
</code></pre>
</div>


<div>
&gt;I spoke to the person and he does not know anything about sql so I do not believe he turned off the set identity to add the records in access. <br />
Okay, in that case...<br />

<div class="bullet">
Somebody had the identity turned off, then this person opened an Access database with a linked table to this table, and happily typed away not knowing about the unique ROWID constraint.
</div>
<div class="bullet">
Maybe somebody re-created the table in a way that the identity constraint did not copy over? 
</div>
<div class="bullet">
A senior developer needs to look at what happened and fix it. 
</div>
</div>


<div>
Jim, I ran your script - no records have the same ROWID now, but I can't turn back on identity .<br />
<br />
also you suggested the steps to copy the records and then delete them, &nbsp;I can't delete the records, they have been used in numerous transactional records.
</div>


<div>
thanks Jim. &nbsp;I was able to set the identity back to on once I closed all connections to the database.<br />
<br />
the erp was designed in SQLBase 19 years ago and they retrofitted it to work with oracle and sql server, so I believe the rowid is not used anywhere in any other tables.<br />
<br />
thank you so much for you help. &nbsp;will start testing my updated sandbox to see if anything fails.
</div>


<div>
Thanks for the grade. &nbsp;Good luck with your project. &nbsp;-Jim
</div>


<div>
<div class="content wysiwyg-content">
A user at my company used access to backdoor adding &quot;PART&quot; records to SQLServer 2008R2 ERP database. &nbsp;Unfortuantly he used 1 for all the ROWID's. &nbsp; These records have been used all over the place in the database. &nbsp;My question to you guys is, can i just manually edit the table and assign uniquie ROWID's to them? &nbsp;i.e. does SQL use these rowid's as foreign keys?<br />
<br />
I am trying to upgrade a sandbox of our production database and it's failing due to duplicate keys in other tables.<br />
<br />
Any help is greatly appreciated.
</div>
</div>


A user at my company used access to backdoor adding "PART" records to SQLServer 2008R2 ERP database.  Unfortuantly he used 1 for all the ROWID's.   These records have been used all over the place in the database.  My question to you guys is, can i just manually edit the table and assign uniquie ROWID's to them?  i.e. does SQL use these rowid's as foreign keys?

I am trying to upgrade a sandbox of our production database and it's failing due to duplicate keys in other tables.

Any help is greatly appreciated.

sqlserver 2008r2 table with records with duplicate rowid

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.