SQL 2012 :: Missing Identity Numbers
Jun 2, 2015
I have inherited a system which uses Identity numbers for primary key fields, but also displays them for the user to see. The user has noticed missing numbers in the sequence, sometimes just a few numbers and sometimes as many as 1000 numbers missing.
As far as I can tell the few numbers missing could possibly be explained by users deleting records or the number being generated on a save, but the save then failing. But I can't explain the huge jump of 1000.
I can find no reference to the RESEED in the code...
View 9 Replies
ADVERTISEMENT
Sep 26, 2007
Is there a way to check for missing identity numbers in a Primary Key column? I have some databases that are not fully normalized and want to check on tables that might have had some records deleted. Thank you.
View 3 Replies
View Related
Dec 19, 2013
CategoryNos
A 1
A 2
A 3
A 5
A 6
A 9
A 10
B 23
B 24
B 25
B 27
B 28
B 29
B 31
Consider the above data...
I need following output... query ..
CategoryMin RangeMax Range
A 1 3
A 5 6
A 9 10
B 23 25
B 27 29
B 31 31
Logic Behind :
if any number is missing in the continues series the return the min and max of number in that range.
View 1 Replies
View Related
Sep 4, 2004
Hi All
I am using SQL 2000 on win 2003 server and I have been using the query analyzer and a stored procedure to add records to a particular table.
For some reason the ID column which is an identity column and is seeded to increment by 1, skips numbers every once in a while. I haven't used any deletes and there have been no errors when loading the info.
Has this happened to anyone else? Where do I start looking for possible causes
Thanks
P
View 3 Replies
View Related
Jul 29, 2015
In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?
UPDATE LKC
SET LKC.combo = lockCombo2
FROM [LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
[Code] ....
View 10 Replies
View Related
Mar 4, 2008
Hello,
I have an ID column which is an Identity column, and it counts from 1 upwards.
If I have 10 records, I have ID's number 1 to 10.
If I then add another record, it will give the ID number 11.
Imagine I then delete ID number 9
Is it possible to get the next record I create to take number 9 instead of 12 ?
View 5 Replies
View Related
Aug 19, 1999
After moving our database between servers, one table had a hiccup in it's
identity column. The number jumped from 761 to 1886863475 on the next
insert. This is a production server and I didn't catch it until yesterday.
So I have several days worth of numbers in a central table. I can't clean
them out, but I wondered if I could do the following:
DBCC CHECKIDENT ('table_name', RESEED, 800)
I tested it on our development server and it doesn't seem to cause any
problems. I know that when we get to 1.8 billion on the identity column
again we'll get an error, but I'm willing to risk it.
A few questions. 1) Am I insane for trying this? 2) Has anyone else ran into
a similar problem and what did you do to fix it? 3) If I do this, is there
anything in particular to watch out for? 4) What caused the jump in the
first place?
My other option is to change the datatype from int to decimal(19) or
something along those lines. This will upset our programmers and I'll have
to change all my foreign keys (not a big deal, just a pain).
Any input at all here would be appreciated.
Thanks,
Grant
View 1 Replies
View Related
Aug 24, 2006
I guess this is a fairly common topic but couldn't find the right words to find anything in a search.
What I'm getting at, is there any tsql functions or combination of commands for the following.
You have identity columns in your tables, if you set the a seed and autoincrement, I enter in rows 1 -10 and then I delete 4, 6, 7, 8.
My next new record uses 11. Is there any logic that allows you to check and reuse 4, 6, 7 & 8 described above? Not looking for something that consists of having to create an extra ID table for each table and handle configuring what the next available number is everytime an Insert or delete is called.
Thanks.
View 4 Replies
View Related
May 29, 2012
We are facing the following issue, several machines/users that are executing very often a command similar to :
INSERT INTO TableName (FieldOne,FieldTwo) VALUES ('ValueOne','ValueTwo');
SELECT SCOPE_IDENTITY() AS Table_ID;
Where TableName has a primary key defined as identity(1,1).and that Table_ID is being used as reference in others tables
These queries are executed using different dababase users and among several diffrent apps..The Problem is that we are detecting lost block of "Table_ID's" as the other tables shows the InsertedID as a reference, but the TableName table lacks of this ID record. In other words, the INSERT seems to work, the SCOPE_Identity returns an InsertedID, and the other tables are populated using this number. However, when we query the TableName table the mentioned record does not exist. We are profiling the server and we're sure that there are no DELETE statement on the TableName table. This seems to be happening when the are either deadlocks or blocked processes. Whenever the deadlocks and locks disappear/solved, everything works as expected.why the Scope_Identity returns the Inserted ID if the INSERT action had failed.
View 4 Replies
View Related
Nov 28, 2005
Greetings,If I use a "select into" to clone a table, all attributes are createdcorrectly, however, if I use the same statement across a linked server,my identity column loses its IDENTITY specification.Is this a known issue or basic functionality of using "select into"with linked servers?Kurt
View 2 Replies
View Related
Feb 1, 2007
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
View 1 Replies
View Related
Jul 21, 2006
I re-created a publication that was having problems and it gives this error when I start the snapshot agent from SQL Server Management Studio: I am stuck on how to resolve - any ideas?
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. Transaction count after EXECUTE indicates that a commit or ROLLBACK TRANSACTION statement is missing. Previous Count = 1, current count = 2."
select * from MSmerge_identity_range returns 19 entries but I don't know how to fix.
I have tried deleting and re-creating but always get this error.
My other publications and subscriptions are working fine and I was able to create a new test publication that worked but can not get this one to work that worked fine up until today.
Any ideas?
View 2 Replies
View Related
Feb 18, 2014
I'm trying to create a column of numbers that increment by one.
I'm not able to use a #temptable in the application I'm using so I cannot use IDENTITY(int,1,1).
I want to add an Id column to this query:
Select distinct sd.name,ic.TABLE_SCHEMA,ic.TABLE_NAME from sys.databases sd
cross join INFORMATION_SCHEMA.COLUMNS ic
where sd.name = 'ODS1stage'
order by TABLE_SCHEMA,TABLE_NAME
How can I accomplish this without creating a temp table? I would just alter the table and insert the numbers but there are 2000 rows.
View 7 Replies
View Related
Apr 8, 2014
I have a string and i want to get only the numbers from right.
For example if I have the string Like '123756zxfggr123456' then it will show me only 123456 or if i have the string like
'4vbz67xfggr123dfd' then it will show me only 123 or if i have the string like '123756zxfgg43r5' then it will show me only 5.
I got a function where it gives me all the numbers in a string but I don't need that
CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
[Code] ....
If I ran the select statement it gives me the result 111123456 but i want only 123456 or if i select
SELECT dbo.udf_GetNumeric('111zxfggr6587fhhfkwee') AS 'Num' it will show me 6587.
View 8 Replies
View Related
Oct 20, 2014
I have a field which contains something like prj(5616) .
I have been assigned to display the actual name and not the text with the number.
Example: if prj(8616) is called Soccer , then I want display Soccer instead of prj(8616).
View 9 Replies
View Related
Jun 25, 2014
Given a Table1 with two columns 'Name' with some N rows of data and another Table2 with one column 'SeqNo' with N rows, each of which contains a unique integer which can be ordered monotonically, I want to do an INSERT into some Table3 with two columns 'Name' and 'SeqNo' such that each INSERT'd row gets one of the unique integers.
E.g. -
Table1 contains 'Fred','Tom','Mary','Larry'
Table2 contains 6000978,6000979,6000980,6000981
INSERT INTO Table3
SELECT Table1.Name
Table2.SeqNo
FROM Table1
And I want to get Table3
'Fred',6000978
'Tom'.6000979
'Mary',6000980
'Larry',6000981
How can I reference Table2 so that Table2.SeqNo will 'line up' properly? Note that the ordering of the SeqNo values isn't mandatory as long as each SeqNo is assigned to one and only one row.
On edit: Table2 isn't required, it's just the way I started thinking about it. It would be nicer to just have two integer vars, @StartSeqNo = 6000978 and @EndSeqNo = 6000981 for he example above. Either way is fine.
View 3 Replies
View Related
Mar 17, 2015
I want to assign consecutive numbers to a block of data, where block of data is based on days consecutive to each other i.e., one day apart.
Date format is: YYYY-MM-DD
Data:
TestId TestDate
----------- -----------------------
1 2011-07-21 00:00:00.000
1 2011-07-22 00:00:00.000
1 2011-07-27 00:00:00.000
[Code]....
The OrderId is the column I am trying to obtain using my following cte code, but I can't work around it.
View 4 Replies
View Related
Oct 7, 2015
If Exists ( Select c.name from sys.columns c where object_id = object_id('HH835HP') and C.name = 'ID_1' )
Begin
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
End;
Obviously... The stuff inside the IF is wrong syntax...I mean
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
View 4 Replies
View Related
Sep 3, 2014
I have a Contact table where I enter a "Parent" (Mother or Father) with IsSubscriber = 1. I also enter all of their children in this same table, with IsDependent = 1.
I then have a Relationship table that relates each child to the appropriate parent record in the Contact table.
I need to assign a sequence number to each child ONLY if they were a multiple birth (twins, triplets, etc.; all have the same DOB). I've been successful at writing a query using ROW_NUMBER(), but it includes the single births (no other child of the same parent has the same DOB).
Stripped down version of Tables and Data and my failed attempt to write a query to do what I want:
IF OBJECT_ID('TempDB..#Contact','U') IS NOT NULL
DROP TABLE #Contact
CREATE TABLE #Contact (
ContactId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, IsSubscriber BIT
[Code] ....
This is as close as I can seem to get.
View 5 Replies
View Related
Jul 23, 2015
I am importing an excel spreadsheet into a MS SQL database table. When the spreadsheet is finished importing, I am noticing that some values that were brought in resemble something like this 1.41666666666667. Other values may be shorter or only have 1 digit. The problem is another web application that pulls this data for use in online forms only allows up to 2 digits. How can I round all of the numbers like the above to 2 decimals and replace the existing values?
I know there is the rounding function that could be used like so:
SELECT ROUND ([Hrs Total 2],2)
FROM AnnualClassifiedPAFs
How do I then take that rounded value and insert it back into the records?
View 2 Replies
View Related
Oct 8, 2015
The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).
While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.
What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.
Declare @ID int
If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' )
Begin
--Adding a sequence of numbers to the ServiceLineID column.
SET @id = 0
UPDATE tmpMHPCLMDET
SET @id = ServiceLineID = @id + 1;
End;
View 2 Replies
View Related
Feb 5, 2015
Say you have a table that has records with numbers sort of like lottery winning numbers, say:
TableWinners
num1, num2, num3, num4, num5, num6
33 52 47 23 17 28
... more records with similar structure.
Then you have another table with chosen numbers, same structure as above, TableGuesses.
How could you do the following comparisons between TableGuesses and TableWinners:
1. Compare a single record in TableGuesses to a single record in TableWinners to get a count of the number of numbers that match (kind of a typical lottery type of thing).
2. Compare a single record in TableGuessess to ALL records in TableWinners to see which record in TableWinners is the closest match to the selected record in TableGuesses.
View 8 Replies
View Related
Apr 20, 2015
My team is starting to implement error handling in our sprocs. One question we have is whether or not to use unique error numbers for custom errors (ie Errors we throw after doing some sort of validity check, not SQL Server errors). For example, we might check the value of a parameter and then throw an error that says "Parameter State_Date must be less than today, please retry".
We are using SQL Server 2012 and will be using the THROW statement, not RAISERROR, so we don't HAVE to put the numbers in sys.messages. Also, we are going to log the errors in a table, along with the error message, sproc name, line number, etc.
Is it useful to maintain a custom list of error numbers and messages? Or is it just as useful to use one standard error number and add a custom error message (which we can then search for in our code, or use the sproc name & line number we logged)? And if it is worth maintaining a list of numbers plus messages, should we go ahead and put them in sys.messages?
View 2 Replies
View Related
Dec 23, 2014
Trying to build a list of order numbers based on stock availability.
The data looks something like this:
OrderNumber Stockcode quantityordered quantityinstock
123 code1 10 5
123 code2 5 10
124 code3 15 20
124 code4 10 10
In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.
View 1 Replies
View Related
Dec 18, 2012
I have a server which is not running optimally and I checked the default trace. I have around 600 entries in the default trace which are all Missing Column Statistics and the database is tempdb.is_auto_create_stats_on and is_auto_update_stats_on are both 1 for tempdb.
View 2 Replies
View Related
Feb 25, 2013
Recently migrated from 2008 to 2012. Everything is working fine; however went to take my first set of backups today and one of them blew up:
Executing the query ...
Backup and restore errors: Unexpected number of disk files associated with database object. Possible cause for that is missing or corrupt cryptokey.bin or detach.log file.
Execution complete
Not finding much online on the specific error.
- Data source is valid
- Can process the cube
- Can query / navigate cube
Next step would be drop/recreate; however this is an older cube that I'd prefer to leave alone at this stage if possible.
View 1 Replies
View Related
Jul 15, 2014
Today I have a very similar situation, only today I am dealing with missing text data, not numeric data.
DECLARE @MissingTextData TABLE
(
RowID int
,UserID int
, EmailAddress varchar(20)
,StreetAddress varchar(20)
[code]...
I would like to fill in the NULL columns with data from the other row, and then select the one row that is filled with all data. I was able to use MAX() for a numeric value, but I am really stumped on the text data. Everything that I have tried is not working.
View 1 Replies
View Related
Aug 15, 2014
I would like synchronizing all the missing objects (logins, agent jobs, SSIS, and anything else that I've missed) across SQL 2012 Availability Groups.
I would like to be able to able to automate this process....
View 2 Replies
View Related
Oct 12, 2015
I have a database of 900+ tables with around 3000 SPs, and views. Manually I reviewed few tables and found that tables are not referenced with FK and I applied few. There are lots of tables and SPs using them in join statement, Is there any way with which I can get each tables missing references, any DMV or other manual script which tells about this?
View 2 Replies
View Related
Mar 20, 2014
Create Table Sample (ID int not null primary key, RefID int , SeqNo int , Name varchar(10) )
insert into Sample
select 1, 1000, 1, 'Mike'
union
select 2, 1000, 2, 'Mikey'
union
select 3, 1000, 3, 'Michel'
union
select 4, 1001, 1, 'Carmel'
union
[code]....
select * from SampleI have here sample data given. What I want to do is, I want to check the RefID which is not having proper order of sequence number. If you see the RefID 1000, 1001 they are having properly sequence order in SeqNo field. But it is not in RefID 1002. RefID 1002 does not have proper order. It is because user has deleted a row which was having seqno 2. So i want to get what are all the RefID's are not having properly sequenced. So that I would be able to know these are all the RefID's are affected by delete statement that was done by user.
View 8 Replies
View Related
Jul 23, 2014
Getting events in the default trace saying missing column statistics on a column...
1.The column is the primary key column ( identity )
View 2 Replies
View Related
Jan 20, 2015
I am trying to get a count by product, month, year even if there are is no record for that particular month.
Current outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ April 2014 34
...
Desired outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ March 2014 0
XYZ April 2014 34
...
The join statement is simple:
Select Product, Month, Year, Count(*) As Count
From dbo.Products
Group By Product, Month, Year
I have also tried the following code and left joining it with my main query but the product is left out as is seen:
DECLARE @Start DATETIME, @End DATETIME;
SELECT @StartDate = '20140101', @EndDate = '20141231';
WITH dt(dt) AS
(
SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Start), 0))
FROM ( SELECT TOP (DATEDIFF(MONTH, @Start, @End) + 1)
n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
FROM sys.all_objects ORDER BY [object_id] ) AS n
)
2nd attempt:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
NULL March 2014 0
XYZ April 2014 34
...
What I want is this (as is shown above). Is this possible?
Desired outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ March 2014 0
XYZ April 2014 34
...
View 7 Replies
View Related
Apr 27, 2015
Write the query that produces the below results. I'm not ale to join the two sets in a way so that it displays NULLs if no purchase was made on a given day for a particular product. I need NULLs or s so that it shows up correctly on my SSRS report.
-- declare @from DATE='2015-1-5',@to DATE='2015-1-10'
-- test data
;with testdata as(
SELECT 1 AS Id,'1/6/2014' AS Date, 21 As Amount UNION ALL
SELECT 1 ,'1/8/2014', 25 UNION ALL
SELECT 1 ,'1/9/2014', 30 UNION ALL
SELECT 1 ,'1/10/2014', 60 UNION ALL
SELECT 1 ,'1/5/2015', 3800 UNION ALL
SELECT 1 ,'1/6/2015', 7120 UNION ALL
[code]....
View 2 Replies
View Related
