August 22, 2017 at 9:39 am
I have a situation and I need some internal advice. Here is the history of the situation. We have a production system with internal security tables inside the production database. Same old stuff. Username, password, etc. Metadata that drives the application level security. This presents problems when trying to maintain multiple environments and their individual security peculiarities so I implemented an external security database with those same security tables. In order to make this transparent to the application the restore process into non-production environments looks something like this:
Restore production backup to non-production server.
Run code to drop the tables and create the views.
This seems simple and we have two systems that use this process. We'll call them database apples and database oranges. Apples has been in production for a few years now. Oranges is in development and has never been promoted to production yet. Both Apples and Oranges have external security databases with identical structures so in this case we CAN compare Apples to Oranges in a security context.
The security application for Apples and Oranges is also the same and uses an insert procedure (stored in the Apples and Oranges database and NOT THE SECURITY DATABASE) which is a very simple insert into. Let's call the table, that I'm having an issue with, Users. Users has two columns:
[CREATE TABLE dbo.Users
(
UserID INT IDENTITY(1, 1)
PRIMARY KEY ,
UserName VARCHAR(50) NOT NULL
);
The view is a simple:SELECT UserID, UserName FROM USERS
Now the problem:
When I run the insert procedure (which does NOT include the UserID because it's an Identity and will auto-increment) from Oranges it inserts into the Oranges-Security database without an issue.
When I run the insert procedure (which does NOT include the UserID because it's an Identity and will auto-increment) from Apples I MAY get the following error:
Msg 2627, Level 14, State 1, Procedure insUsers, Line 69
Violation of PRIMARY KEY constraint 'PKUsers'. Cannot insert duplicate key in object 'dbo.Users'. The duplicate key value is (1).
Each time I attempt to run that insert procedure the duplicate key value will increment (like an Identity NextVal). The max UserID in the security table is 275 so I would presume that the insert statement would grab that tables NextVal and use it to increment the Identity. This is what happens if I do the insert directly in the Apples-Security database. This doesn't happen when I execute the procedure from within the Apples database against the Apples-Security table.
By the way, I DON'T get the above error when the insert value hits a hole in the sequential Identity values. If UserID 65 doesn't exist for some reason, it will insert a new UserID at that number. Then I will continue to get errors until it finds another hole in the sequence.
My question then becomes "Where is this phantom Identity next value coming from? And why is it only in happening in Apples and not in Oranges?"
Can anyone tell me where the Identity nextvalue comes from? Is it context specific? If I'm inserting from Apples via insert statement into Apples-Security.dbo.Users does it not look at THAT tables Identity nextvalue?\
If you have any questions or need more information let me know. This is driving me insane.
August 22, 2017 at 9:47 am
You have obfuscated this problem so much it is really hard to tell what the issue is. Does the Users table exist in both databases? I can't even get close to wrapping my head around your actual setup here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 22, 2017 at 10:02 am
Sorry Sean. Here's a briefer summary of the objects.
Apples
procedure - insertUsers
view - Users (points to Apples.dbo.Users)
Apples-Security
table - Users (contains Identity column UserID and primary key on UserD)
Oranges has the same set up.
Apples errors on the called insert more often than not. Oranges works every time. Both SQL Server 2014 sp2.
August 22, 2017 at 10:23 am
I believe replication has bitten me once again. I haven't taken into consideration Replication Identity Management. This is what happens when the Development team wants to play with replicated tables.
Feel free to offer any advise. From my side, the code that recreates the views will have to reseed the identity value to take the non-production development into consideration.
August 22, 2017 at 10:29 am
You have a procedure in Apples that inserts into the Users table on another database correct? I don't see how that would cause an issue with identity values. Or maybe you need to move the insert procedure to the Apples-Security database and call that procedure remotely. You mentioned something about replication but not sure how that is in play here. And no idea what you mean about reseeding the identity here either.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 22, 2017 at 10:43 am
The table Users is replicated from production to all the non-production Apples-Seucrity.dbo.Users tables. The UserID in the non-production table is created with NOT FOR REPLICATION which means that the seed value (current Identity value) for that column is automatically set to 1 rather than the max(Identity value)+(increment value) of the table. That needs to be done manually.
You would manually reseed or set the Identity current value to max(Identity value) + (increment value) with something along the lines of:
DBCC CHECKIDENT('Apples', RESEED, 275)
I'll just need to add the code to my view recreation code.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply