SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Brain teaser -- query to construct password field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

smithm55
Starting Member

9 Posts

Posted - 08/01/2012 :  16:09:35  Show Profile  Reply with Quote
I'm trying to enhance a query used to build a nightly file of new student accounts in our school district. I have a table of 6-letter words, called password_list. I want my query to build a CSV file each night of new students that contains (A) basic demographic information collected from various tables, and (B) a password. I'd like to build a password for each student as follows:

(1) Pick a random number, and use the password from that row in table password_list
(2) Append to that a random 2-digit number

So, some examples of passwords would be Rabbit24, Orange19, etc.

Could this be done within a select query?

I've tried to incorporate subqueries with some ideas to randomize things that I've found on other sites, such as:
select top 1 password from password_list order by newid()

and
select password from password_list where pwno=ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 1000 + 1

but I can't quite seal the deal.

Any advice would be greatly appreciated!!

Matt Smith
Information Services
DeSoto County (FL) School District

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/01/2012 :  16:21:28  Show Profile  Reply with Quote
What version of SQL are you using? maybe this will help?
SELECT
	<Column List>,
	(select top 1 password from password_list order by newid())
	+ LEFT('0' + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 100 + 1 AS VARCHAR(2)), 2) AS Password
FROM
	<Student Info>
...


If not:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

smithm55
Starting Member

9 Posts

Posted - 08/02/2012 :  12:03:37  Show Profile  Reply with Quote
Lamprey, thanks much for the reply! I'm using SQL Server 2008 R2. Your code is really close, but apparently the "order by newid()" only seeds (if that's the right term) once at the beginning of the query. The code I used is as follows:

SELECT
	first_name,
	(select top 1 password from password_list order by newid())
	+ right('0' + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 99 + 1 AS VARCHAR(2)), 2) AS Password
FROM
	student_info_core

and the results I get are as follows:

ABIGAIL    quacks88
BRANDON	   quacks21
CAMERON	   quacks83
CAPONE	   quacks48
CHEYENNE   quacks20
CHLOE      quacks84
DAMARION   quacks56
DANIELLE   quacks25
DONTAVIAN  quacks49

Every time I re-run the query, I get a different word, but it's always the same word for every record. We're close, but that's where I'm stuck! Anyone know how to make sure we get a new word each time?

Edited by - smithm55 on 08/02/2012 12:09:58
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/02/2012 :  12:46:01  Show Profile  Reply with Quote
Humm, I'm not seeing the same behavior:
DECLARE @Student TABLE (Name VARCHAR(50))
INSERT @Student (Name)
VALUES
('ABIGAIL'),    
('BRANDON'),	   
('CAMERON'),	   
('CAPONE'),	   
('CHEYENNE'),   
('CHLOE'),      
('DAMARION'),   
('DANIELLE'),   
('DONTAVIAN')

DECLARE @Password_list TABLE (Password VARCHAR(50))
INSERT @Password_list (Password)
VALUES

('Orange'),
('Rabbit'),
('Quacks'),
('Fudges'),
('Happy')

  
SELECT
	Name,
	(select top 1 password from @password_list order by newid())
	+ right('0' + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 99 + 1 AS VARCHAR(2)), 2) AS Password
FROM
	@student
Results
Name	Password
ABIGAIL		Fudges46
BRANDON		Quacks35
CAMERON		Fudges50
CAPONE		Rabbit67
CHEYENNE	Quacks33
CHLOE		Happy84
DAMARION	Happy65
DANIELLE	Happy34
DONTAVIAN	Orange37
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/02/2012 :  12:47:46  Show Profile  Reply with Quote
May try an OUTER APPLY?
SELECT
	Name,
	A.Password
FROM
	@student AS s
OUTER APPLY
(
	select top 1 
		password + right('0' + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 99 + 1 AS VARCHAR(2)), 2) AS Password
	from @password_list order by newid()
	
) AS A
Go to Top of Page

smithm55
Starting Member

9 Posts

Posted - 08/02/2012 :  13:51:48  Show Profile  Reply with Quote
Lamprey, what version of SQL are you using? I'm getting syntax errors when I try to execute your code. Specifically, the table values from your first post, and the OUTER APPLY from the second post.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/02/2012 :  13:56:06  Show Profile  Reply with Quote
I tried it on several versions of SQL: 2008, 2008 R2 and 2012. Is your Server perhaps running in a backwards compatable mode for an older version of SQL? I don't have a running version of 2005, but my first query should work on that too.
Go to Top of Page

smithm55
Starting Member

9 Posts

Posted - 08/02/2012 :  14:46:17  Show Profile  Reply with Quote
Strange. No weird back-compatible modes that I'm aware of. I'll go back to the drawing board for just a bit. Thanks for staying with this!
Go to Top of Page

smithm55
Starting Member

9 Posts

Posted - 08/02/2012 :  15:06:40  Show Profile  Reply with Quote
Very puzzling. I've tried on my 2005 and 2008 R2 servers.

This statement:
DECLARE @Password_list TABLE (Password VARCHAR(50))
INSERT @Password_list (Password)
VALUES
('Orange')

returns this:
(1 row(s) affected)


But as soon as I add a second record:

DECLARE @Password_list TABLE (Password VARCHAR(50))
INSERT @Password_list (Password)
VALUES
('Orange'),
('Puppet')


I get this:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ','.
Go to Top of Page

smithm55
Starting Member

9 Posts

Posted - 08/02/2012 :  15:48:16  Show Profile  Reply with Quote
The version is indeed the culprit. I was working on different SQL Servers, but still connecting back to my database that's (gulp) SQL 2000 SP4. I guess I'd have to upgrade this server in order to get this code to work.
Go to Top of Page

smithm55
Starting Member

9 Posts

Posted - 06/17/2013 :  11:07:26  Show Profile  Reply with Quote
Lamprey, it looks like you're still around, so I wanted to resurrect this topic, if I may. I've upgraded my old server above to 2008. Let me ask a new question. If I copy and paste your code above, beginning with the DECLARE @Student TABLE..., everything works just as you described. I get a new word and number for every account, as I'd hoped.

However, as soon as I convert these into physical tables, things break down for me again, and I get the same word on every line.

For example, I'm curious if you would be willing to execute this code, and see if you get similar results:

==========

CREATE TABLE Student (Name VARCHAR(50))
INSERT Student
VALUES
('ABIGAIL'),
('BRANDON'),
('CAMERON'),
('CAPONE'),
('CHEYENNE'),
('CHLOE'),
('DAMARION'),
('DANIELLE'),
('DONTAVIAN')

CREATE TABLE PasswordList (Password VARCHAR(50))
INSERT PasswordList
VALUES
('Orange'),
('Rabbit'),
('Quacks'),
('Fudges'),
('Happy')

SELECT
Name,
(select top 1 password from passwordlist order by newid())
+ right('0' + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 99 + 1 AS VARCHAR(2)), 2) AS Password
FROM
student

===========

Results:

Name Password
ABIGAIL Happy33
BRANDON Happy36
CAMERON Happy20
CAPONE Happy91
CHEYENNE Happy72
CHLOE Happy43
DAMARION Happy76
DANIELLE Happy16
DONTAVIAN Happy41

Thank you for any advice!!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/17/2013 :  11:23:25  Show Profile  Reply with Quote
You might want to take a look at this for ideas.

Generate Password Procedure
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78859



CODO ERGO SUM
Go to Top of Page

smithm55
Starting Member

9 Posts

Posted - 06/17/2013 :  12:05:51  Show Profile  Reply with Quote
quote:
You might want to take a look at this for ideas.

Generate Password Procedure
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78859
Thanks Michael -- some good tips here, not nothing I could find to help me accomplish what I'm trying to do.

Edited by - smithm55 on 06/17/2013 12:08:56
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/17/2013 :  13:28:13  Show Profile  Reply with Quote
You might try something like an OUTTER APPLY. Although you need to corrilate it in some way, even falsely, in order to get the resutls you want. Here is one way to do it:
SELECT
	Name,
	A.Password
FROM
	student AS s
OUTER APPLY
(
	select top 1 
		password + right('0' + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 99 + 1 AS VARCHAR(2)), 2) AS Password
	from 
		passwordlist 
	WHERE
		s.Name <> '' -- Something that wil never be true
	order by newid()
	
) AS A
Go to Top of Page

smithm55
Starting Member

9 Posts

Posted - 06/17/2013 :  15:58:58  Show Profile  Reply with Quote
I just did a little preliminary testing, but I *think* this is going to work. THANKS Lamprey!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000