To Get An Unique Sequence Number (record Locking)
Aug 27, 2007
can someone pls show me a way to get an unique sequence at below senario:
PC1 & PC2 using their own local client progam to access to Database Server at SERVER1.
In the SERVER1, there is a table SEQUENCE in a database DATABASE1.
And the table's structure of SEQUENCE are SeqType & SeqNo.
Here is the sample data:
SeqType SeqNo
Invoice 100
DeliveryOrder 200
Now, how to prevent PC1 & PC2 to get a same Invoice No. if they request the Invoice No. at the same time?
Is it possible to lock the record Invoice when i perform a SELECT statement, then i update the Invoice to 101, lastly release the lock for Invoice?
pls advise. thanks.
View 1 Replies
ADVERTISEMENT
Jul 23, 2005
Hallo,Hot to get unique, sequential number during executionof stored procedure ?I can create table with autoincrement column,add record, get ident_current and delete recordeach time i need the number.However its not elegant i guess.best regardspluton
View 3 Replies
View Related
Jul 19, 2000
Is there wa way to generate unique sequence numbers in SQL server?
(just like the way it is in Oracle i.e. seqeuence and then use nextval)
View 2 Replies
View Related
Sep 23, 2006
I have a situation where for a given customer, their invoices need to be sequentially numbered, without gaps.
Customer A Invoice 1,2,3,4 ...
A-1
A-2
A-3
A-4
Customer B:
B-1
B-2
etc.
The issue is 2 people creating an invoice for the same customer at the same time. Currently I don't assign the Invoice number until the user hits 'Save'. At that time I query for max(invoiceno) against the customer key and simply add 1. it's the last operation prior to saving against the backend.
If 2 users hit Save at the exact time, I'm getting the same (duplicated) Invoice Number.
What suggestions/techniques do you have to resolve this? Would "locking" of the customer record and storing the last highest invoice there play a part?
Thanks,
Peter
View 5 Replies
View Related
Apr 7, 2008
Hi,
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks,
Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
Code snippet - getting the record thru JDBC:
DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();
// wsCon.setAutoCommit(false); //have tried with and without this flag - same results
Statements stmt = wsCon.createStatement();
String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.println(rs.getString("lastName"));
}
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (wsCon != null) {
wsCon.close();
}
View 1 Replies
View Related
Jun 5, 2012
I have created a view based on joining 3 tables, however, it is not possible to have a unique field in the view which I must need it and I must create index on some other fields. Is there any way to create sequence number or uniqie field in mssql view.
View 13 Replies
View Related
Jul 23, 2005
How do I get the next int value for a column before I do an insert inMY SQL Server 2000? I'm currently using Oracle sequence and doingsomething like:select seq.nextval from dual;Then I do my insert into 3 different table all using the same uniqueID.I can't use the @@identity function because my application uses aconnection pool and it's not garanteed that a connection won't be usedby another request so under a lot of load there could be major problemsand this doens't work:insert into <table>;select @@identity;This doesn't work because the select @@identity might give me the valueof an insert from someone else's request.Thanks,Brent
View 4 Replies
View Related
Oct 11, 2007
I have a procedure which updates a sequence number in a table such as the one below.
Seq Sequence_Id
------ ------------------
NextNum 1
This is the procedure ...
create procedure DBO.MIG_SYS_NEXTVAL(@sequence varchar(10), @sequence_id int)
as
begin
update mig_sys_sequences
set
@sequence_id = sequence_id = sequence_id + 1
where
seq = 'CSN'
return(@sequence_id)
end
The purpose of this is to generate a sequential number each time the procedure is called. This number would then be used in a number of different tables to allocate a unique id so that the id is unique across the different tables.
1). What is the most efficient way of allocating these unique ids? The tables that I plan to update will already be populated with data.
2). How would I call the above procedure from an UPDATE statement?
Many thanks,
Fred
View 1 Replies
View Related
Apr 20, 2015
GENERATE 8 CHARACTER ALPHANUMERIC SEQUENCES
Requirements
• ALPHANUMERIC FORMAT – > AA00AA00………..ZZ99ZZ99
Last 8 bytes will alternate between 2 byte alpha/2 byte numeric
• Generate from Alphabets – A through Z Numbers -0 to 9
• Generate Unique Sequence (No Duplicates).
• Must Eliminate letters I and O
Output Expected
• AA00AA00………..ZZ99ZZ99
• Using 24 alphabets & 10 digits ,
24*24*10*10*24*24 = 3 317 760 000 records
Below is my Sql Function -
CREATE function [dbo].[SequenceComplexNEW]
(
@Id BIGINT
)
Returns char(8)
[Code] .....
View 9 Replies
View Related
Apr 16, 2002
Hi, I've a question, if I've the LSN (Log Sequence Number) of a transaction, keep with the program "Log Explorer", can I know which is the IP of the user that have do the transaction (perhaps serching in a log file of the Win 2000 Server) ?
10x,
Clara
View 1 Replies
View Related
Dec 29, 1998
In SQL 6.5 object dependencies window, what does the sequence number means?
Thanks,
Sam
View 1 Replies
View Related
Nov 17, 2005
I have the following stored procedure:
CREATE PROCEDURE dbo.ABR_HDR_INSERT
@id int output,
@status int,
@mode int,
@sessid varchar(100)
AS
declare @ay char(4)
declare @ddo char(4)
declare @abrid varchar(50)
declare @seq_no int
SELECT @ddo = a.DDO_DSCR_SHORT
FROM dbo.DIM_DDO a
JOIN dbo.Temp_ABR_HDR b
ON a.DDO_ID = b.DDO
WHERE b.SESSIONID = @sessid
SELECT @ay = AY
FROM dbo.Temp_ABR_HDR
WHERE SESSIONID = @sessid
-- set the default seq_no
SELECT @seq_no = 1
-- get the max abrid. if no record return the seq_no will be 1
SELECT @seq_no = convert(integer, max(right(abrid, 4)))
FROM dbo.ABR_HDR
WHERE left(abrid, 7) = @ay + @ddo
-- convert @seq_no to string prefix by 0
SELECT @abrid = @ay + @ddo + right('0000' + rtrim(convert(char(4), @seq_no)), 4)
Insert into dbo.ABR_HDR (ABRID, HDR_MODE, HDR_DDO, HDR_AY, HDR_REQUESTOR, HDR_DT, HDR_SUBJECT, HDR_DESCRIPTION, HDR_STATUS)
SELECT
@abrid,
@mode,
DDO,
AY,
REQUESTOR,
DT,
SUBJECT,
DESCRIPTION,
@status
FROM dbo.Temp_ABR_HDR
SELECT @id = @@identity
return @id
GO
ABRID gets inserted as a <NULL> value. I can't figure out why? If I comment out the following then ABRID will insert without the sequence number:
CREATE PROCEDURE dbo.ABR_HDR_INSERT
@id int output,
@status int,
@mode int,
@sessid varchar(100)
AS
declare @ay char(4)
declare @ddo char(4)
declare @abrid varchar(50)
declare @seq_no int
SELECT @ddo = a.DDO_DSCR_SHORT
FROM dbo.DIM_DDO a
JOIN dbo.Temp_ABR_HDR b
ON a.DDO_ID = b.DDO
WHERE b.SESSIONID = @sessid
SELECT @ay = AY
FROM dbo.Temp_ABR_HDR
WHERE SESSIONID = @sessid
-- set the default seq_no
--SELECT @seq_no = 1
-- get the max abrid. if no record return the seq_no will be 1
--SELECT @seq_no = convert(integer, max(right(abrid, 4)))
--FROM dbo.ABR_HDR
--WHERE left(abrid, 7) = @ay + @ddo
-- convert @seq_no to string prefix by 0
--SELECT @abrid = @ay + @ddo + right('0000' + rtrim(convert(char(4),@seq_no)), 4)
SELECT @abrid = @ay + UPPER(@ddo)
Insert into dbo.ABR_HDR (ABRID, HDR_MODE, HDR_DDO, HDR_AY, HDR_REQUESTOR, HDR_DT, HDR_SUBJECT, HDR_DESCRIPTION, HDR_STATUS)
SELECT
@abrid,
@mode,
DDO,
AY,
REQUESTOR,
DT,
SUBJECT,
DESCRIPTION,
@status
FROM dbo.Temp_ABR_HDR
SELECT @id = @@identity
return @id
GO
So, the code that sets the sequence number is what is causing the <NULL> value.
Any help is appreciated.
Thanks,
-D-
View 4 Replies
View Related
Oct 3, 2007
suppose I have the following table grouped by memid
memiddx
3455
3322
3232
433
43434
I want to attach sequence number for each unique value of dx per memid as
below
memiddxSEQ
34551
33222
32323
4331
434342
I am using a cursor right now and it takes a lot of time if my table is large.
Is there a more efficient way of doing this.
Thanks much.
View 3 Replies
View Related
Jan 11, 2008
is it possible to generate a number sequence in a query (without using loop). I want the output to look as
-------
ID
-------
1
2
3
4
5
6
7
8
9
....
upto the last number I give in the query
View 13 Replies
View Related
Apr 4, 2006
Hello all,I´m currently using a SQL Serve 2K. Would like to do a selectwhich returns the row number - this should not be physically stored inthe database. So for example, I would like to do a query against theCUSTOMER table and receive:* rowID || name1 Evander2 Ron3 Scoth4 JaneI don´t want to store the ID, because if I change the order byclause, the sequence may modifiy, and, for another example, having thesame set of data, I would receive:* rowID || name1 Scoth2 Ron3 Jane4 Evander could someone help me ?best regards,Evandro
View 3 Replies
View Related
Oct 5, 2000
Hello,
I need help in record locking. As soon as user retrieve records I want to lock those records in database. When other user try to retrieve records from the same table i want to retrieve records those are not locked by other user. How do I do this? Please help...
Thanks in a millian.
Sarika
View 1 Replies
View Related
Jul 26, 2001
Hello,
I have a database programmer who in his latest application has deployed manual record locking into code to release certain records to certain users of the application at specified times.
Does anyone know how manual record locking will affect the SQL 2K system? If at all? Are there some standard practices with manual record locking that should be enforced?
Thanks,
Brent.
View 3 Replies
View Related
Sep 7, 2007
Project spec:
If user is making Delivery Order for sales order,
the other user cannot make Delivery order for same sales order
How to lock a sales-order record, so other user cant use it, except for reporting (read only). I dont want to use a field since it has a lot of weakness. I am using VB6 and connect with ADO
Thanks
View 3 Replies
View Related
Aug 29, 2001
Hello,
I need to know how to generating a sequence number, for example, from 300,000 to 900,000 without skipping any number due to failure. For example, if user 1 request a number then he/she will get 300000 in a transaction. User 2 will get 300001. How ever user 1's transaction fails, then the next request should get 300000. Is it possible to do this in SQL2K? If so, how do I create a table that and stored procedure that can do this.
Thank you so much.
NK
View 3 Replies
View Related
Mar 15, 2005
Hi,
I have written the following StoredProcedure
Code:
create Procedure spCreateQuestion(
@QuestionName varchar(30)
)
as
declare @newAnswerId int
declare @newQuestionId int
set @QuestionName='New Question'
BEGIN TRANSACTION Q1
--Creates New QuestionId with AnswerId 0
INSERT INTO Questions(QuestionId,Name,AnswerId)
SELECT 1 + COALESCE(MAX(QuestionId), 0),RTRIM(@QuestionName),0
FROM Questions
--QuestionId just now created
SELECT @newQuestionId=QuestionId FROM Questions WHERE Name=@QuestionName
BEGIN TRANSACTION QA1
--Create an AnswerId
INSERT INTO Answers(AnswerId)
SELECT 1 + COALESCE(MAX(AnswerId), 0)
FROM Answers
--AnswerId just now created(I hope not the best way to do like this)
SELECT @newAnswerId=MAX(AnswerId) from Answers --is it the best way to call statement like this or any other way better than this
--update Questions Table with this new Answerid
UPDATE Questions
set
AnswerId=@newAnswerId
where QuestionId=@newQuestionId
COMMIT TRANSACTION QA1
COMMIT TRANSACTION Q1
I think the second Transaction is not locking the table.so some how i should be
able to get the newly create AnswerId
i can't use the identity column in my tables
Can some one please have a look at it and suggest me how do we go about it..
View 8 Replies
View Related
Feb 3, 2005
Does anyone know an efficient method for generating a sequence number in the following form?
Starting with 2 columns
1 2
----
A X
A Y
B X
B Y
B Z
C X
I want to then generate a third column as follows:
1 2 3
-------
A X 1
A Y 2
B X 1
B Y 2
B Z 3
C X 1
The purpose being so that I can easily identify the previous row within a Column1 group. So given column1=A and column2=Y I know that the previous row is Column3 - 1 where column1 = A. Therefore I will be able to join to the previous result of any row within any group quickly for future calculations.
Any ideas? Thanks.
View 10 Replies
View Related
Oct 3, 2007
Hi,
I got stuck with a trivial issue. I have a table named T1 having column as Col1 (INT). I have another table named T2 having columns Col1(Int), COl2(Int). Following are the reacords in my table T1.
Col1
----
1
1
1
1
2
2
3
3
3
3
3
4
5
6
6
7
And I want to migrate the data from T1 to T2 where the Col1 data of T1 will get migrated to Col1 of T1 and after the migration is done the T2 should have the data like this.........
Col1 Col2
---- -----
1 1
1 2
1 3
1 4
2 1
2 2
3 1
3 2
3 3
3 4
3 5
4 1
5 1
6 1
6 2
7 1
Thanks In Advance,
Rahul Jha
View 14 Replies
View Related
Nov 14, 2007
Hello folks,
I have a table where the records are like followings.
ID Value
---------------
1 aa
1 aa
1 aa
1 bb
1 bb
1 bb
1 bb
1 cc
2 pp
2 dd
2 dd
3 qq
4 aa
I need to include one column "SeqId" which will be having value based on the 1st 2 columns (ID + Value). I am struglling with this since last few days. Can I request you guys to help me out in this reagard.
ID Value SeqId
-----------------------------
1 aa 1
1 aa 2
1 aa 3
1 bb 1
1 bb 2
1 bb 3
1 bb 4
1 cc 1
2 pp 1
2 dd 1
2 dd 2
3 qq 1
4 aa 1
Thanks,
Rahul Jha
View 14 Replies
View Related
Oct 13, 2013
I have the following data:
POL# POL_EFF_DATE
123 1-1-2012
123 1-1-2012
123 1-1-2012
123 1-1-2013
123 1-1-2013
456 1-1-2012
456 1-1-2012
456 1-1-2013
456 1-1-2013
I want to add sequence number to each group with same Pol# and Effective Date like this:
POL# POL_EFF_DATE Seq
123 1-1-2012 1
123 1-1-2012 1
123 1-1-2012 1
123 1-1-2013 2
123 1-1-2013 2
456 1-1-2012 1
456 1-1-2012 1
456 1-1-2013 2
456 1-1-2013 2
is there a set based function to accomplish this in SQL 2008?
View 2 Replies
View Related
Apr 18, 2008
I have a table like this
create table
#invoice (invoice_no varchar(5), invoice_amount int)
INSERT INTO #invoice
SELECT 'A', 100 UNION
SELECT 'A', 200 UNION
SELECT 'A', 300 UNION
SELECT 'B', 400 UNION
SELECT 'B', 500 UNION
SELECT 'B', 600 UNION
SELECT 'B', 700
Now I want a output like this
INVOICE_NO AMOUNT SEQUENCE_NO
A 100 1
A 200 2
A 300 3
B 400 1
B 500 2
B 600 3
B 700 4
The sequence_no should set back to 1 on change of invoice_no
these are items in a an invoice. they just want a sequence_no
I can do this using cursors, but if anyone can help with WHILE LOOP
would be great
THANKS
-----------------------------------------------------------------------------------------------
Ashley Rhodes
View 10 Replies
View Related
May 31, 2006
I am newbie to SQL Server 2005.
Can any experts please give me code for how to generate automatic sequence numbers in sql server 2005?
I want to use this generated sequence number as a primary key to update the records.
Thanks in advance,
Vani
View 2 Replies
View Related
Jan 25, 2008
Hi,
I have an error table that is to be updated by more than one package.
There is a sequence number generated in the error table. It is generated by using the max value of the previous data present in the table.
When more than one package runs parallely, conflict occurs in generating the sequence number.
How can this be handled?
View 1 Replies
View Related
Oct 6, 2006
How can I create a number sequence starting at a certain number and continue on for the number of records I have.
For example I have 3000 records in my table and a field named I created called RecordId which I'd like to start at number 1 and goto 3000 (or maybe even start at 9000 and goto 12000 or however many records there are).
In my pseudo SQL code Im guessing it would be something like...
select * from Incident
update Incident
set RecordId( i=9000; i<=Number of Records in Table; i++)
Whats the easiest way to do this?
View 7 Replies
View Related
Jun 29, 2015
Why log sequence number gets broken in SQL server?
View 4 Replies
View Related
Nov 15, 2007
Hi...
I have Sql statement more like this
SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeA a
UNION
SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeB a
rec_num employeeID employeeName employeeDepartment
1 777 Mike HR
2 888 Susy HR
1 111 Smith TECH
2 222 John TECH
3 333 Lenny TECH
How do i get sequence number for all of this records. The rec_num reset for every statement. I want the records numbering for second statement continue from first statement so that it can be like this :
rec_num employeeID employeeName employeeDepartment
1 777 Mike HR
2 888 Susy HR
3 111 Smith TECH
4 222 John TECH
5 333 Lenny TECH
View 4 Replies
View Related
Mar 27, 2008
Hi,
Does any one have information about how 'large' the SQL Server Sequence Number generator can go,
like 1 to 1 billion, or to 12 zeros, etc?
Thanks.
--John
View 1 Replies
View Related
Sep 17, 2007
Hi all, I need some your help.
I want to add sequence number to data flow below:
Cust_Name | Month
=======================
Chonnathan | 5
Chonnathan | 4
Chonnathan | 7
Derec | 3
Derec | 9
and the result i need is:
SEQ_nbr | Cust_Name | Month
================================
1 | Chonnathan | 4
2 | Chonnathan | 5
3 | Chonnathan | 7
1 | Derec | 3
2 | Derec | 9
How can I do it in Integration Service to show like the above?
Thank you for your respones,
Chonnathan
View 1 Replies
View Related
Feb 18, 2005
Currently I am working on asp.net Intranet system.I need some suggestion on records locking for Sqlserver & ASP.net.
I want to prevent two user to open the same record for modify.Means if some one has open one record (id=xxxxx) for modify then others should not be able to open same same record (id=xxxxx) for modify .otherwise if both user will open/update same record then information in database will not be updated correctly.
Any idea how we can do the record locking so that not two users can open same record(id=xxxxx) for modify.
Thanks in Advance
Arvind
View 2 Replies
View Related
