SQL Server 2008 :: Generate 8 Char Alphanumeric Unique Sequence
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
ADVERTISEMENT
Apr 20, 2015
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)
AS
BEGIN
DECLARE @OUT AS CHAR(8)--,@Id as Bigint
[Code] ....
View 1 Replies
View Related
May 6, 2011
How do I convert (n)(var)char to time? I tried several ways including a simple:
SELECT CONVERT(time,'143825',108)But it always gives me this error:
Conversion failed when converting date and/or time from character string.
View 9 Replies
View Related
Apr 13, 2015
We are trying to upgrade a SQL server 2000 to a SQL 2008 R2 (SP1) server. After migrating, the developer test code, and got an error: ERROR [HY010] [Microsoft][ODBC SQL Server Driver]Function sequence error...But this is a release for 2008, ours is already 2008 R2 SP1, so that hot fix should already be included since it is always cumulative.
View 2 Replies
View Related
Jun 30, 2015
In my asp.net project there are about 100 drop down list.I created a table to store data for drop down list in which including [DropdownID],[Order Sequence] and [Description] three columns. The sample like below. Data was input manually by a user. How to code to find out duplicate [OrderSequence]?
DropdownID--OrderSequence--Description
1-------------0--------------AAA
1-------------1--------------BBB
2-------------0--------------YYY
2-------------1--------------XXX
2-------------2--------------QQQ 'DUPLICATE OrderSequence
2-------------2--------------WWW 'DUPLICATE OrderSequence
2-------------3--------------RRR
View 2 Replies
View Related
Oct 29, 2007
Dear all,
I'm new in writing t-sql. Now I want to perform the following task:
1) pass a non zero integer to sql procedure, the procedure will select fields from tableA, for example,
if input parameter is 5, the result is (the column seq no is not in tableA, I want to generate sequence no):
seq no Customer code Customer name
------------------------------------------------------------------
6 abcde Peter
7 efghi John
8 jklmn Mary
:
etc
How to do this?
View 5 Replies
View Related
Mar 5, 2015
I want to make a query with the result like this, where Seqno is the result query which has repeating sequence number for same Field1
Field1Field2SeqNo
BVFUSBVFUS011
BVFUSBVFUS021
BVFUSBVFUS031
BVFRPBVFRP012
BVFRPBVFRP022
[Code] ....
I want the result also no ordered by Field1. it just as the natural order of the table..
View 3 Replies
View Related
May 29, 2015
How can I get time difference of the following record :
STARTTIME ENDTIME
3:30 PM 4:30PM
7:30 PM 8:30PM
I have tried it by below query,
SELECT CONVERT(TIME,STARTTIME,108) - CONVERT(TIME,ENDTIME,108) FROM BATCH_MASTER
but it gives following error message
[color=red]Operand data type time is invalid for subtract operator.[/color]
View 6 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
Mar 20, 2007
helen writes "I have a sql table:
NAME AGE
Anna 10
Susan 5
Dina 12
Please help me to come up with a QUERY STATEMENT with this result:
SEQNO NAME AGE
1 Anna 10
2 Susan 5
3 Dina 12 "
View 2 Replies
View Related
Mar 28, 2008
Hi all
anbody can help me writing sql code for this. All i need is to generate sequence basing on id_no
Ex: if ID=ABC(twice) in seq_col as abc --1
abc ---2
Tables which I have
Uniques_No ID_NO SEQ
---------------------------------------------
1 ABC
2 ABC
3 ABC
4 BBC
5 BBC
Expected results as below :
------------------
Uniques_No ID_NO SEQ
---------------------------------------------
1 ABC 1
2 ABC 2
3 ABC 3
4 BBC 1
5 BBC 2
Thanks in advance
View 4 Replies
View Related
Sep 11, 2015
I am working on SQL SERVER schemas using a plug-in in SQL developer itself now my requirement is to generate DDL for a table which is part of SQL SERVER schema...
View 3 Replies
View Related
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
Sep 3, 2014
I'm trying to do a simple insert into a table, something like this:
insert into sometable (ID, somecolumn)
select 'Task-ID', somevalue from SomeOtherTable
where something = 'someothervalue'
(or something to that effect)
So, the SELECT would generate something that looks like this:
ID somecolumn
-- ----------
Task-ID somevalue1
Task-ID somevalue2
Task-ID somevalue3
(etc.)
Here's where my problem comes in: ID is a PK, and needs to be unique. What I need it to do is this:
ID somecolumn
-- ----------
Task-ID.1 somevalue1
Task-ID.2 somevalue2
Task-ID.3 somevalue3
(etc.)
What I don't know is, how do I programatically generate the number sequence? Note: I do not have admin rights to the table, i.e. I cannot just change a column to IDENTITY.
Also the 'Task-ID' must remain part of the ID; in other words, I can't just generate a GUID, and it needs to be easily identifiable.
What I'm hoping to do is rewrite my SQL like this:
insert into sometable (ID, somecolumn)
select 'Task-ID.' + (generated seq #), somevalue from SomeOtherTable
where something = 'someothervalue'
Is there an easy way to do this?
View 9 Replies
View Related
Jan 27, 2015
Our end users is getting below error, when they try to connect to our database:
"Cannot generate SSPI Context."
That is Windows based application and we have done everything like restart our DB Server , reinstall exe in users system, but still issue is same.The issue has occured from when DB Server has restarted and at the same time few users are connected. before that it was working fine. we could not find what is issue.
View 1 Replies
View Related
Feb 26, 2015
It might be an old question but wanted to see, if we have any latest techniques (other than bcp).
SELECT Field1, Field2 FROM MyTable
If I want to export the output of the above query to a csv on a network folder? I would like to avoid usage of SSIS package or BCP (as user needs to get additional rights to execute bcp).
View 3 Replies
View Related
May 28, 2015
I need to generate the week ranges like this format :
Here from date and to date would be picked up from the table but just to make you understand i have hardcoded it but this is the real date which is falling inside the table.
Note : Week should be generated from Monday to Sunday within desired date range
View 9 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
Feb 12, 2015
I need to generate data models of all databases for an instance. How can I accomplish this?
View 3 Replies
View Related
Aug 2, 2015
I have data in Sql table , I want to convert it to xml using xsd using script component in ssis.
View 0 Replies
View Related
Sep 25, 2015
i need to generate documents for customers to sign automatically as sales staff enter their data into SQL. These are invoice style documents. I currently have word templates of the invoice documentation, i just need to be able to add the clients names, address etc into the relevant spaces for them to print off and sign.
I am good with TSQL and writing Stored Procs etc and can easily get the data ready - i just need to find a way to populate the templates in the right places and then save a copy for emailing.
View 9 Replies
View Related
Sep 29, 2015
I've been tasked to generate some test data (a few thousand rows) into a new table in a new database. This database is a whole new idea, so I can't write a query to pull pieces of data from other databases. I cannot consider any third party tools, such as what Redgate or Idera has to offer. I can't consider free tools such as what I've found on GitHub. I've been instructed to restrict myself to Visual Studio 2013 and whatever I can get that works within that.
View 5 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
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
View Related
Jan 9, 2011
Just wondering if there is any query which can give me report of locked account in SQL 2008. I know there are options like:-
1) Can go in ssms--security--account and right click and select status. This is applicable to individual account.
2)SELECT LOGINPROPERTY('accountid', 'IsLocked'). Also applicable to individual account
3) SELECT name,type_desc,is_disabled,modify_date,default_database_name from sys.server_principals order by type_desc
Generate the report but give the informatio as is_disabled. But I think is_disabled <> is_locked.
View 5 Replies
View Related
May 22, 2015
I need to create a script that adds an incrementing suffix to two columns, but restarts based on the value of another column. I found a similar question in the SQL Server 2000 forum, but it doesn't quite fit and also I'm working with SQL Server 2008 R2. The code below both creates a table with test data and tries to carry out the task. If you run this, you will see that the VISITNUM column has a value of UNS in row 4, UNS.1 in row 5 and UNS.2 in row 6. In row 7 it's V200, then in rows 8 and 9 it's UNS.3 for both. The same suffix gets applied to the VISIT column, but of course if I can solve this for VISITNUM then adding the suffix to VIST as well will be easy.
What I need is for row 8 to have UNS and row 9 to have UNS.1. In other words, any time the VISITNUM is UNS several times in a row, I need to add that ".X" suffix, but if a row has something other than UNS, I need to start over again the next time it's UNS again.
CREATE TABLE #testing(
KitID varchar(20),
SubjID varchar(20),
VISIT varchar(60),
VISITNUM varchar(20),
[code]....
View 8 Replies
View Related
May 14, 2015
I've a excel spreadsheet with 650 records with unique PONumbers. I need to pull data from SQL server based on the PONumbers. I don't want to run select statement 650 times. How do I retrieve the records in efficient way?
View 9 Replies
View Related
Oct 30, 2015
Give a user table ‘MyTable’. How to know whether the table contains a non-unique clustered index by using SQL query?
View 2 Replies
View Related
Oct 22, 2015
There is a SQL Server 2008 R2 SP3 Clustered Instance that has Transactional Replication. It is by no means a large replication setup in terms of data/article count. SQL Server was recently patched to SP3 and is current on Windows 2008 R2 Patches.
When I added a new article to replication (via 2014 SSMS GUI) it seems to add everything correctly (replication tables/procs show the new article as part of the publication).
The Publication is set to allow the snapshot to generate for just new articles (setting immediate_sync & allow_anonymous to false).
When the snapshot agent is run, it runs without error and claims to have generated a snapshot of 1 article. However the snapshot folder only contains a folder for the instance (that does have the modified time of the snapshot agent execution) and none of the regular bcp/schema files.
The tables never make it to the subscribers and replication continues on without error for the existing articles. No agents produce any errors and running the snapshot agent w/ verbose output provides no errors or insight into any possible issues.
I have tried:
- dropping/re-adding the article in question.
- Setting up a new Snapshot Folder
- Validated all the settings and configurations
I'm hesitant to reinitialize a subscriber since I am not confident a snapshot can be generated. Also wondering if this is related to the SP3 Upgrade, every few months new articles are added to the publication and this is the first time since the upgrade to SP3 that it has been done.
View 0 Replies
View Related
Mar 23, 2009
I have a SQL2008 database, running Standard Edition 64-bit, database owns by sa, connected to Management Studio using Windows Authentication mode. When I tried to generate scripts from a database, I got the following error messages:-
[Operation is not valid due to the current state of the object. (SqlManagerUI)]
which happened at one particular table. I have reviewed this table definitions are normal, and I could select data from it.
I couldn't find any information anywhere relating to this error messages, except that someone got it when they were trying to change the authentication mode or sa password.
View 5 Replies
View Related
Apr 3, 2015
I have the following insert statement:
INSERT INTO [User].User_Profile
(UniqueId, Username, EmailAddress,
Password, BirthDay, BirthMonth,
BirthYear, Age, AccountType, DateCreated,
DeletedDate, DeletedReason, ProfileStatus)
VALUES
(NEWID(), @Username, @EmailAddress,
@Password, @BirthDay, @BirthMonth,
@BirthYeat, @Age, 1, SYSDATETIME(),
null, null, 2)
SELECT @@IDENTITY
As you can I have a uniqueidentifier (UniqueId) column which I populate with NewID() I'm trying to return this as I need it for other functionality of the website but I can't figure out how I can get it after the insert has completed?
View 3 Replies
View Related