Create sequencial serial numbers
-
hi everybody,
I have a app that the previous tech develop for generating QR labels
This app was created to have multiple partnumber's generate the qr string.
Since in the beginning was only one workstation for partnumber the sequence generate was fine because he use the following string in the app to get and generate the next number every time the operator click in the button print.
begin transaction;
declare @xx as nvarchar(50)
declare @xa as nvarchar(50)
declare @x1 as nvarchar(50)
declare @snpn as nvarchar (50)
select * from [EtiquetasKLC].[dbo].[etiquetas]with (updlock) where partnumber like @partnumber order by id desc
set @xx = (SELECT top 1 serialnumber FROM [EtiquetasKLC].[dbo].[etiquetas] where partnumber like @partnumber order by id desc)
set @x1 = @xx+1
set @x = (select format(convert(int,@x1),'0000000'))
set @qrgerado = @x+@partnumber+@versao+@indexnumber+@semana
set @snpn = @x+@partnumber
insert into [EtiquetasKLC].[dbo].[etiquetas] (nome_posto, data, lote,serialnumber, partnumber, versao,indexnumber,semana,qrgerado,operador,snpn) Values (@posto, @data, @lote,@x, @partnumber, @versao,@indexnumber,@semana,@qrgerado,@operador,@snpn)
commit transaction;its reads/write in the table:
my problem now is:
i have the need to have more than one workstation for partnumber labeling, and if they work simultaneous they often repeate the serial.
can i still use this table to from some way only gets one serial to each workstation.
i'm not very good at sql. can do basic query's, i understant that the query above states thar she reads the last id from a partnumber and retireves it's serialnumber and them adds up one, and writes it on the database.
but somehow the second workstation can genereate the same serial.
can some one point me a solution.
i have the source code from the app. it's on vb .net and i can code reasonly well in .net but sql it's my aquiles heal 🙂
- This topic was modified 4 years, 10 months ago by Steve Jones - SSC Editor. Reason: formatted code
-
OK. So, first question, does it matter that the numbers are perfectly sequential? Is it OK if there are gaps occasionally? If so, you should look to the SEQUENCE operator. You can set that up pretty easily and have it generating numbers. You may get gaps on failed transactions occasionally, but it'll still get the job done.
I'm assuming when you say more than one workstation, this is still with a single database, right? If it's with more than one database, we have other problems.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I think the line:
select * from [EtiquetasKLC].[dbo].[etiquetas]with (updlock) where partnumber like...
was put there to lock all the rows with that partnumber so they can't be read by another session. However, I think this is a shared lock that won't block reads.
You could try changing the hint to:
with (rowlock,updlock,xlock)
I think you could also rewrite the SQL to do all of the script in just one statement:
INSERT INTO [EtiquetasKLC].[dbo].[etiquetas] with (tablockx)
(
nome_posto,
data,
lote,
serialnumber,
partnumber,
versao,
indexnumber,
semana,
qrgerado,
operador,
snpn
)
SELECT TOP (1)
@posto,
@data,
@lote,
T.x,
@partnumber,
@versao,
@indexnumber,
@semana,
T.x+@partnumber+@versao+@indexnumber+@semana,
@operador,
T.x+@partnumber
FROM [EtiquetasKLC].[dbo].[etiquetas] x
CROSS APPLY (VALUES (format(convert(int,x.serialnumber) + 1,'0000000'))) T(x)
WHERE partnumber LIKE @partnumber
ORDER BY id DESC; -
You should be able to do it all in a single statement, and therefore won't need an explicit transaction. Something like this.
INSERT EtiquetasKLC.dbo.etiquetas
(
nome_posto
,data
,lote
,serialnumber
,partnumber
,versao
,indexnumber
,semana
,qrgerado
,operador
,snpn
)
SELECT TOP (1)
@posto
,@data
,@lote
,@x
,@partnumber
,@versao
,@indexnumber
,@semana
,qrgerado = CONCAT(x.x, @partnumber, @versao, @indexnumber, @semana)
,@operador
,snpn = CONCAT(x.x, @partnumber)
FROM EtiquetasKLC.dbo.etiquetas (UPDLOCK)
CROSS APPLY
(SELECT x = FORMAT(CONVERT(INT, serialnumber + 1), '0000000')) x
WHERE partnumber LIKE @partnumber
ORDER BY id DESC;Notice the UPDLOCK hint. Some testing is required to determine whether that is necessary, but it should do the job.
You should also put a UNIQUE index/constraint on the column, or columns, which must not be duplicated. Better to make the insert fail than to have duplicates which need to be cleaned up later.
If this is within a stored proc in the database, please consider removing the EtiquetasKLC. prefix from the table names. This is a bad practice, unless you are executing the script outside the context of the EtiquetasKLC database.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
+1 for single statement.
-
Thanks for your help.
Yes we could skip one or another serial.
I will rewrite the code with the suggestion and give feedback soon.
-
I have alter the app code with:
INSERT EtiquetasKLC.dbo.etiquetas
(
nome_posto
,data
,lote
,serialnumber
,partnumber
,versao
,indexnumber
,semana
,qrgerado
,operador
,snpn
)
SELECT TOP (1)
@posto
,@data
,@lote
,@x
,@partnumber
,@versao
,@indexnumber
,@semana
,qrgerado = CONCAT(x.x, @partnumber, @versao, @indexnumber, @semana)
,@operador
,snpn = CONCAT(x.x, @partnumber)
FROM EtiquetasKLC.dbo.etiquetas (UPDLOCK)
CROSS APPLY
(SELECT x = FORMAT(CONVERT(INT, serialnumber + 1), '0000000')) x
WHERE partnumber LIKE @partnumber
ORDER BY id DESC;and in my laptop it generates and writes alright.
tomorrow i'll try on production and give feedback
many thanks
- nrdroque wrote:
I have alter the app code with:
INSERT EtiquetasKLC.dbo.etiquetas
(
nome_posto
,data
,lote
,serialnumber
,partnumber
,versao
,indexnumber
,semana
,qrgerado
,operador
,snpn
)
SELECT TOP (1)
@posto
,@data
,@lote
,@x
,@partnumber
,@versao
,@indexnumber
,@semana
,qrgerado = CONCAT(x.x, @partnumber, @versao, @indexnumber, @semana)
,@operador
,snpn = CONCAT(x.x, @partnumber)
FROM EtiquetasKLC.dbo.etiquetas (UPDLOCK)
CROSS APPLY
(SELECT x = FORMAT(CONVERT(INT, serialnumber + 1), '0000000')) x
WHERE partnumber LIKE @partnumber
ORDER BY id DESC;and in my laptop it generates and writes alright. tomorrow i'll try on production and give feedback many thanks
I'm just wondering how it works with @x in the select statement?
-
hi,
In the column SNPN i will make it UNIQUE index/constraint on the week because it doesnt allow me to do it we workstation labeling.
I think this column was created for that reason because it's the concatenation of serial number and partnumber, making this unique in all table.
I've removed EtiquetasKLC. prefix from all other querys in source code.
By now i have two workstations using the improved query and waiting to see if everything runs smooth.
- Phil Parkin wrote:
If this is within a stored proc in the database, please consider removing the EtiquetasKLC. prefix from the table names. This is a bad practice, unless you are executing the script outside the context of the EtiquetasKLC database.
Perhaps this is not the right location for the question, but can you explain why this is a bad practice? Is is simply a style issue, or does it take longer to locate the assets by referencing the database explicitly rather than using the current database, or something else altogether?
- fahey.jonathan wrote:Phil Parkin wrote:
If this is within a stored proc in the database, please consider removing the EtiquetasKLC. prefix from the table names. This is a bad practice, unless you are executing the script outside the context of the EtiquetasKLC database.
Perhaps this is not the right location for the question, but can you explain why this is a bad practice? Is is simply a style issue, or does it take longer to locate the assets by referencing the database explicitly rather than using the current database, or something else altogether?
I can provide a scenario which should persuade you.
Imagine a developer who has been notified that there is a problem with DatabaseA.
The developer restores a copy of DatabaseA to DatebaseA_20190702 and starts running some tests on it. One of those tests includes running a stored proc which updates various tables. The proc references tables using three-part naming (db.schema.table) and therefore the proc proceeds to update tables in DatabaseA. Big problem!
Another (lesser) issue is that the code is more ungainly if every referenced object includes its database name, and experienced developers will automatically assume that fully qualified references refer to objects outside of the current database.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Thank for providing a scenario. To follow on, a procedure that lives on DatabaseA and references DatabaseA and DatabaseB, which are restored as DatabaseA_20190702 and DatabaseB_20190702, would have a similar problem with the fully qualified name of DatabaseB that is required. For that reason and others, I would never think of testing on the same server as production data. Databases are always restored to a separate Dev or Test server for testing and debugging, then the revised code is promoted back to Prod from source control.
I agree that three-part naming is more ungainly. The fewer words I have to type every time, the better.
- fahey.jonathan wrote:
Thank for providing a scenario. To follow on, a procedure that lives on DatabaseA and references DatabaseA and DatabaseB, which are restored as DatabaseA_20190702 and DatabaseB_20190702, would have a similar problem with the fully qualified name of DatabaseB that is required. For that reason and others, I would never think of testing on the same server as production data. Databases are always restored to a separate Dev or Test server for testing and debugging, then the revised code is promoted back to Prod from source control. I agree that three-part naming is more ungainly. The fewer words I have to type every time, the better.
In this situation - it would be much better to use synonyms for access to database B within database A. This allows for changing the name of the database or even moving that database to another server and just updating the synonyms.
This also allows for moving code to different databases without having to modify the code. Using 3-part naming - and 4-part naming - will eventually cause a tremendous amount of work to be done and is completely avoidable.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - fahey.jonathan wrote:
Databases are always restored to a separate Dev or Test server for testing and debugging,.....
Good to see.
But what about making a copy of an existing database in Dev, for whatever reason? With three-part naming, you can't even do that. You cannot create copies of the database with different names, nor can you ever change the name of the database without accompanying code changes.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply