| Author |
 Topic  |
|
|
smithm55
Starting Member
9 Posts |
 Posted - 08/01/2012 : 16:09:35
|
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
3856 Posts |
|
|
smithm55
Starting Member
9 Posts |
Posted - 08/02/2012 : 12:03:37
|
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 |
 |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3856 Posts |
Posted - 08/02/2012 : 12:46:01
|
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
@studentResultsName Password
ABIGAIL Fudges46
BRANDON Quacks35
CAMERON Fudges50
CAPONE Rabbit67
CHEYENNE Quacks33
CHLOE Happy84
DAMARION Happy65
DANIELLE Happy34
DONTAVIAN Orange37 |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3856 Posts |
Posted - 08/02/2012 : 12:47:46
|
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 |
|
|
|
smithm55
Starting Member
9 Posts |
Posted - 08/02/2012 : 13:51:48
|
| 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. |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3856 Posts |
Posted - 08/02/2012 : 13:56:06
|
| 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. |
|
|
|
smithm55
Starting Member
9 Posts |
Posted - 08/02/2012 : 14:46:17
|
| 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! |
|
|
|
smithm55
Starting Member
9 Posts |
Posted - 08/02/2012 : 15:06:40
|
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 ','. |
|
|
|
smithm55
Starting Member
9 Posts |
Posted - 08/02/2012 : 15:48:16
|
| 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. |
|
|
|
smithm55
Starting Member
9 Posts |
Posted - 06/17/2013 : 11:07:26
|
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!! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7007 Posts |
|
|
smithm55
Starting Member
9 Posts |
Posted - 06/17/2013 : 12:05:51
|
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 |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3856 Posts |
Posted - 06/17/2013 : 13:28:13
|
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
|
|
|
|
smithm55
Starting Member
9 Posts |
Posted - 06/17/2013 : 15:58:58
|
| I just did a little preliminary testing, but I *think* this is going to work. THANKS Lamprey!! |
|
|
| |
Topic |
|
Brain teaser -- query to construct password field
