Need alernative to cursor

Hey guys and gals - here's my current code

DECLARE @.uID varchar(12)

DECLARE MyCursor CURSOR FOR
SELECT unique_identifier
FROM gvAbsence

OPEN MyCursor

FETCH NEXT FROM MyCursor
INTO @.uID

WHILE @.@.FETCH_STATUS = 0 BEGIN
UPDATE gvAbsence
SET holiday_brought_forward = CASE WHEN years_of_service < 5 THEN Floor(Rand() * 6) ELSE Floor(Rand() * 5) END
WHERE unique_identifier = @.uID

FETCH NEXT FROM MyCursor
INTO @.uID
END

CLOSE MyCursor
DEALLOCATE MyCursor

SELECT *
FROM gvAbsence

Which as you can see, sucks.
It cursors through nearly 4K records assigning a new random number (0-5) to an employees holiday_brought_forward. Naturally, this is an absolute ballache... I just can't think of a better solution at current so any advice you can give would be greatly appreciated :cool:

To put it simply, I want to assign a random number between 0 and 5 (inclusive) to each record in the table gvAbsence

-GeorgeWhich bit you stuck with? Version of SS?
ss5k method of getting a random number between 0 and 5. Not sure if it is the best way of course. With a suitable bit of SQLery I think you can get a random number per person.

select *
from
(select number
, rn = ROW_NUMBER() OVER (ORDER BY newid())
from dbo.numbers
where number between 1 and 5) AS der_t
where rn = 1

Did you try googling?|||SS 2K.
And the above code works how I want it to, but I was wondering if there was a better alternative to using a cursor?

--Return random number between 0 and 5 inc
SELECT Floor(Rand() * 6)

(Can Rand() return 1?)

I've just progressed on with my problem with the cursor and all is well, apart from it taking a full minute to cursor through and apply other updates etc.|||Define random for me:
Does the code need to generate a different number for a particular member of staf each time it is run? Does it need to generate an even distribution for the 4k records? Basically - do you need a truly random number?|||I am creating a test environment and to replicate the scenario I needed to assign all sorts of different values to certain fields (in this case holiday_bought_forward) to that I could test a bunch of different permeatations of the data.

It does not need to do it every time the code is run, except when I need to reset the data (and during testing the data will need reset a good number of times).

No, does not need to have an even distribution across the 4K records, truly random will do just fine ;)

It is not a high priority issue as it's only for the test environment but speeding things up a bit would be nice :p

I don't like cursors all that much (you guys have always told me not to use them!) so I was looking into what alternatives exist

Thanks Poots|||Does gvAbsence have a primary key? If so, extract them into a 2 column table, and when you extract them use the RAND function to generate your random value.

Since the RAND function returns a random float value between 0 and one, you will have to multiply by 5 and take the integer value (lookup FLOOR in BOL)

Hopefully this will get you started.

EDIT: Once you have this table, you can update gvAbsence with the random value from the table for a quick reset that will restore identical values each and every time you need a reset.|||My understanding was that rand won't work in a set based query.|||If I was to keep my cursor, I could use something like a counter instead of a random number for consistancy.
Something like

DECLARE @.Counter int
SET @.Counter = 0

UPDATE gvAbsence
SET holiday_brought_forward = @.Counter % 5
WHERE unique_identifier = @.uID

SET @. Counter = Counter + 1

I dunno, I still don't like cursoring...|||Aha! So nothing like random at all!

This is much easier now - we are deteriministic. Your people got a surrogate key (I know you like em ;))? Why not perform mod on that instead of an increment? Easy & set based.|||One issue I do have now is that the holiday_bought_forward has some constraints; after 5 years of service you're entitled to an extra days holiday -this means you can only bring over maximum of 4 days. (basically a total of 30).

I've actually finished my script now, but I'm simply not happy with the cursor. Like I said - this is only test data that I'm creating to replicate live stuff - so it doesn't have to be the same every time - it simply has to contain permeatations of every possibility (which it does).

If anyone's interested in seeing the redicuous script I had to write for this, just let me know :p|||Let's create some test data

select 'unique_identifier'=id, 'years_of_service'=id, 'holiday_brought_forward'=0
into #gvAbsence from sysobjects where id<20

Instead of using the the rand() function that does not work as expected use a numbers table e.g.

select top 6 seqno=identity(int) into #numbers from syscolumns

And instead of hard coding data into your code lets use a holiday_constraints table.
Now if the conditions chance you don't have to update code, just the data in your table.

create table #holiday_constraints (maxdays int, yrlow int, yrhigh int)
insert into #holiday_constraints select
6,0,5 union all select
5,6,999999999

Then the update.
Note: An unnecessary where clause in the sub select to force execution for every row.

update #gvAbsence
SET holiday_brought_forward =
(select top 1 seqno from #numbers c where seqno<=b.maxdays and a.unique_identifier=a.unique_identifier order by newid())
from #gvAbsence a, #holiday_constraints b
where a.years_of_service between b.yrlow and b.yrhigh

select * from #gvAbsence|||Unfortunately I'm using SQL Server 2000; TOP won't work.|||Also, there are more rules involved and the added problem of generating a random number between -5 and a conditional top value
I'm thinking that in this case a cursor might be my only solution.

I apologise, I don't think I made my problem/question very clear from the beginning but thanks everyone for your help! My final mark up of the cursor is below - the really clever line is the SET ;) Lovely formula right thar!

DECLARE MyCursor CURSOR FOR
SELECT unique_identifier
,holiday_entitlement
,holiday_brought_forward
FROM gvAbsence

OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @.uID, @.Ent, @.Bf

SET @.Min = -5
WHILE @.@.FETCH_STATUS = 0 BEGIN
SET @.Max = CASE @.Ent + @.Bf
WHEN 30 THEN 0
WHEN 29 THEN 1
WHEN 28 THEN 2
WHEN 27 THEN 3
WHEN 26 THEN 4
WHEN 25 THEN 5 END
UPDATE gvAbsence
SET holiday_bought = Floor(Rand() * (@.Max - @.Min + 1)) + @.Min
WHERE unique_identifier = @.uID

FETCH NEXT FROM MyCursor
INTO @.uID, @.Ent, @.Bf
END
CLOSE MyCursor
DEALLOCATE MyCursor|||Unfortunately I'm using SQL Server 2000; TOP won't work.Howdya figure that?|||I didn't think TOP was implemented in 2000?
Any time I try run a TOP statement in QA I get

SELECT TOP 5 surname
FROM employee
ORDER BY surname ASC
--
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '5'.

Errors!|||From SQL 2K BoL:
Limiting Result Sets Using TOP and PERCENT

The TOP clause limits the number of rows returned in the result set.
TOP n [PERCENT]
n specifies how many rows are returned. If PERCENT is not specified, n is the number of rows to return. If PERCENT is specified, n is the percentage of the result set rows to return:
TOP 120 /*Return the top 120 rows of the result set. */TOP 15 PERCENT /* Return the top 15% of the result set. */.If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set. The entire result set is built in the specified order and the top n rows in the ordered result set are returned.

The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP in these ways:

The SET ROWCOUNT limit applies to building the rows in the result set after an ORDER BY is evaluated. When ORDER BY is specified, the SELECT statement is terminated when n rows have been selected from a set of values that has been sorted according to specified ORDER BY classification.
The TOP clause applies to the single SELECT statement in which it is specified. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off.The top keyword changed in 2005 only in so far as you can use a variable.|||Then how come I can't execute SELECT TOP statements? :confused:|||Have you checked the compatability level of the database?|||Works for me

USE Northwind
GO

SELECT TOP 5 LastName
FROM employees
ORDER BY LastName ASC
GO|||Ummm...assuming your "algorithym" is correct, why couldn't you just do

USE Northwind
GO

CREATE TABLE myTable99(unique_identifier int, years_of_service int, holiday_brought_forward int)
GO

INSERT INTO myTable99(unique_identifier, years_of_service)
SELECT 1, 1 UNION ALL
SELECT 2, 10 UNION ALL
SELECT 3, 20 UNION ALL
SELECT 4, 30
GO

UPDATE g
SET holiday_brought_forward =
CASE WHEN years_of_service < 5 THEN Floor(Rand() * 6)
ELSE Floor(Rand() * 5) END
FROM myTable99 g
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||... because rand() will return the same value for all rows.:)|||Not necessarily...

select case floor (rand()*5)
when 1 then 1
when 2 then 2
when 3 then 3
when 4 then 4
else 5 end
from [table of your choice]

This came up in a very nice SQL puzzle posted here some time ago. For those of you who are interested...

select case floor (rand()*5)
when 1 then '1'
when 2 then '2'
when 3 then '3'
when 4 then '4'
when 5 then '5'
else 'OMG WTF LOL!!!111eleven!!' end
from [table of your choice]

EDIT: Due to the nature of the "oddity", it may not be a truly random sampling. Sounds like it may be random enough for you, though.|||Well pummel my piles with a paddle. What is that about then? The case statement causes the rand to be re-evaluated for each row rather than once for the set? And what's the deal with 'WTF'? I mean - wtf? You got a link?|||Actually, rand() is invoked on each row of the output. This is why inline user defined functions can get you in to piles of problems that will pummel your performance paddle(s) past pertinent perturbation.

Sadly, I do not have a link to the original puzzle. It may have been lost in the troubles that DBForums was going through some time back when posts just up and disappeared. Besides, it will likely be a good exercise for you to figure out what is going on in there.

DOH! Should have checked first. I guess it is the case statement mucking around with things, after all.|||Oh noblet - I forgot about the 0 :rolleyes:

But anyway:

select case 1
when 1 then floor (rand()*5) end
from sys.databases

select case 1
when 2 then NULL else floor (rand()*5) end
fromsys.databases

select case floor (rand()*5)
when -1 then NULL else floor (rand()*5) end
from sys.databases

Coooooooooooooooo.|||DOH! Should have checked first. I guess it is the case statement mucking around with things, after all.Ah jolly good! I was wondering what subtle distinction you were making :)|||tsk tsk tsk. Perhaps I should have taken the 0 into consideration, as well...

select case floor (rand()*5)
when 0 then '0'
when 1 then '1'
when 2 then '2'
when 3 then '3'
when 4 then '4'
when 5 then '5'
else 'OMG WTF LOL!!!111eleven!!' end
from [table of your choice]

Extend as necessary to convince yourself this is not so easy as all that.|||neat trick! this seems to work as well without the need for laying out all the possibilities in the case:

select case when name is not null then rand() else NULL end from sys.objects

all you need is something to check in the when clause that will always be true, but that the optimizer won't optimize away. 1=1 won't work. That is, this is broken:

select case when 1=1 then rand() else NULL end from sys.objects|||UPDATE gvAbsence
SET holiday_brought_forward = CASE WHEN years_of_service < 5 THEN
CASE WHEN 1=1 THEN Floor(Rand() * 6) ELSE NULL END
ELSE
CASE WHEN 1=1 THEN Floor(Rand() * 5) ELSE NULL END
END

Does not give the desired result - (same problem as before)

UPDATE gvAbsence
SET holiday_brought_forward = CASE Floor(Rand()*5)
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
ELSE 5 END

Same goes for this *sigh*
I can understand the use of CASE statements to make sure every row is evaluated individualy - but that doesn't appear to be the case when running the above...

this might sound silly but in other languages you need to use

Randomize

Would that apply here? *shrugs*
thanks for the input so far :)|||Can you create a derived table and update from that rather than directly with the case statement?|||Do you mean something like this?

IF EXISTS (SELECT NULL FROM sysobjects WHERE type='U' AND name='gvVals') BEGIN
DROP TABLE gvVals
END

CREATE TABLE gvVals
(
bought_forward int
)

INSERT INTO gvVals(bought_forward) VALUES(1)
INSERT INTO gvVals(bought_forward) VALUES(2)
INSERT INTO gvVals(bought_forward) VALUES(3)
INSERT INTO gvVals(bought_forward) VALUES(4)
INSERT INTO gvVals(bought_forward) VALUES(5)
INSERT INTO gvVals(bought_forward) VALUES(6)

UPDATE gvAbsence
SET holiday_brought_forward = CASE Floor(Rand()*6)
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
ELSE 5 END
FROM gvVals

Holiday Brought Forward
--
5.0
5.0
5.0
5.0
5.0
etc|||Text file containing the cursored SQL uploaded for your amusement / berrating ;)
EDIT: and I guess comments, too|||I could not get different values for rand() using the case statement on SS2K
Generating a random value from newid() seems to work
UPDATE gvAbsence
SET holiday_brought_forward =
right(convert(char(2),(convert(int, (convert(varbinary(1),right(newid(),1))))) ),1)/10.*5+1|||pdreyer - wow, that appears to work (and quickly!)
But I'm afraid I have absolutely no idea what it is doing ;)
/10.*5+1 ?? I removed the point and it didn't work - what's that doing :)

Seems like an aweful lot of converts in there and I can't for the life of me understand the char convert!

Hehe, thanks for that - I'd love to implement it, but only if I can understand it first :D

EDIT: I have removed the +1 as I want to include zero values :) I love the fact it even does halves! I hadn't even considered that before :)
RE-EDIT: Not quite returning the values I need... I need 0-5 inclusive and 0.5's would be lovely (but not essential).
/10.*5 returns 0 - 4.5 inc
/10.*5+1 returns 1 - 5.5 inc|||Do you mean something like this?Nope :)

But it sounds like rand() is working differently in 2k anyway. I don't have any 2k boxes to play with and test so I'll drop out at this point.|||Awww but Pooooooooots!
Thanks for all your prior help bud - it is appreciated :)
and if that's not what you meant... what is! :p|||Did we not cover derived tables in your last thread (or thread but one)? Genuinly can't remember for sure...|||I think I'm lost in jargon ;)
Are derived tables (to put it simply) sub-selects?

And did you see what I did before?

so I'll drop out at this point

I don't know why but I'm finding it harder and harder to ask questions here - easy enough to answer (some) but when I ask them I go into "newb" mode and yeah - it sucks :(|||I think I'm lost in jargon ;)
Are derived tables (to put it simply) sub-selects?
No. But you might be able to do it as a sub select.

Derived table example:

UPDATE gvAbsence
SET holiday_brought_forward = derived_table.random_number
FROM dbo.gvVals
INNER JOIN
(SELECT MyPK
, CASE Floor(Rand()*6)
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
ELSE 5 END AS random_number
FROM dbo.gvVals) AS derived_table
ON derived_table.MyPK = gvVals.MyPk


And did you see what I did before?Dunno - what did you do?|||I have absolutely no idea what it is doing
it uses the ASCII code of a character from newid() as a random number (caveat: limited range but OK for your needs)
can't understand the char convertNot needed, should be removed.
what's the point doingIt ensure decimal arithmetic else the integer devide will result in zero
it even does halvesThat was not the intended result. I assumed the target field was an integer, So to get values 0-5 multiply by 6 and convert to int at the end. e.g.
select convert(int,right(ascii(newid()),1)/10.*6) from sysobjects

Need Advise on SQL Express with Advanced Services

Hi Guys,

Need some advice on the free SQL Express with Advanced Services provided.

I plan to develop a small departmental multi-user applications with 5 to 6 simultaneous users using the free SQL Express with Advanced Services with VB.net and stored procedures on a local area network.

Is it a good choice to use this free SQL Express with Advanced Services or is it more advisable to purchase a standard version ?

Appreciate if someone could help me to address this issue?

One more thing, can i install the free SQL Express with Advanced Services on a normal desktop with Windows XP SP 2 and utilize it as a server or do i need a proper server with Windows 2003?

Thanks and Regards,

Jansen

If you have a small app and you're sure you'll only have 5 or 6 people generating a small load, then you should be OK with the Express version. I'd still recommend that it go on a server OS, primarilly because of the way security and networking are handled.

You also have some other limitations on Express such as database size and so forth, but you certainly could start your app there and grow into a larger server. All you have to do is backup and restore the database to the Standard Edition, and change your connection strings in your app.

Buck

|||

You should be fine with that, but you need to consider the space limitations as well. You can always grow the app into a Standard Edition. I'd also recommend that you always use a Server OS.

Buck

Need Advise on SQL Express with Advanced Services

Hi Guys,

Need some advice on the free SQL Express with Advanced Services provided.

I plan to develop a small departmental multi-user applications with 5 to 6 simultaneous users using the free SQL Express with Advanced Services with VB.net and stored procedures on a local area network.

Is it a good choice to use this free SQL Express with Advanced Services or is it more advisable to purchase a standard version ?

Appreciate if someone could help me to address this issue?

One more thing, can i install the free SQL Express with Advanced Services on a normal desktop with Windows XP SP 2 and utilize it as a server or do i need a proper server with Windows 2003.

Thanks and Regards,

Jansen

Express is well suited to the task you are discussing and yes it should install on a std windows desktop.

need advice

Hi guys,
Can you suggest good books on relational algebra and database design(NF)?
Thanks a lot in advance
Alexhttp://www.datamodel.org/
AMB
"Alex" wrote:

> Hi guys,
> Can you suggest good books on relational algebra and database design(NF)?
> Thanks a lot in advance
> Alex
>
>|||>> Can you suggest good books on relational algebra and database design(NF)?
One widely recognized book on the scientific & mathematical aspects (
algebra, calculus & deductive ) of relational theory is the Foundations of
Databases by Abiteboul, Hull & Vianu. The book is relatively advanced and
the ones starting out might find the book tough to comprehend. On the topic
of relational algebra and the coverage of its practical utility, no other
popular book does more justice more than C J Date's Introduction to Database
systems. As far as datamodelling is concerned there are several good books
around. Books by C J Date, Toby Teorey, David Maier etc are excellent
primers dealing with conceptual modelling and logical design.
As in every other field with its basis on science & mathematics, sometimes
it makes good sense to avoid product based design books and 10-minute guides
to learn data modelling & design.
Anith

Need a So-Called SSN Encryption

Hello, perhaps you guys have heard this before in the past, but here is
what I'm looking for.

I have a SQL 2000 table with Social security numbers. We need to
create a Member ID using the Member's real SSN but since we are not
allowed to use the exact SSN, we need to add 1 to each number in the
SSN. That way, the new SSN would be the new Member ID.

For example:

if the real SSN is: 340-53-7098 the MemberID would be 451-64-8109.

Sounds simply enough, but I can't seem to get it straight.

I need this number to be created using a query, as this query is a
report's record source.

Again, any help would be appreciated it.ILCSP@.NETZERO.NET wrote:
> Hello, perhaps you guys have heard this before in the past, but here is
> what I'm looking for.
> I have a SQL 2000 table with Social security numbers. We need to
> create a Member ID using the Member's real SSN but since we are not
> allowed to use the exact SSN, we need to add 1 to each number in the
> SSN. That way, the new SSN would be the new Member ID.

That's almost totally ineffective if the idea is to protect the SSN
from disclosure. Why don't you use a hash of the number instead? A
secure one like SHA1 for example.

If you must, then something like this should do it:

REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE(@.ssn,'0','#')
,'9','0'),'8','9'),'7','8'),'6','7'),'5','6')
,'4','5'),'3','4'),'2','3'),'1','2'),'#','1')

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||I agree - it's pretty lame that this method allows you to "comply" with
the policy.

Then again, when my school switched from using SSNs to "Student IDs",
it was a nightmare--but only because no one could remember them.|||Hi David, I totally agree with you. However, this does the job. I
tested and the numbers do get increased by 1.

And to think I was trying to do Case..WHEN.. END statements. Gosh!|||One method is to create a table of single, pairs, triplets etc. like
this

CREATE TABLE TripletEncode
(in_triplet CHAR(3) NOT NULL PRIMARY KEY
CHECK(in_triplet LIKE '[0-9][0-9][0-9]',
out_triplet CHAR(3) NOT NULL);

Break the input into substrings, and replace the characters with the
out_triplet. It is not a great system. Given some sample data, you can
figure out the areas and other repeated parts of SSN, phone numbers,
etc.

There are some modifications, like putting the last digit in front, or
using the last digit to pick which encoding to use from a table with a
extra column that goes from 0 to 9. The real trick is to be sure that
the encodings are reversible.|||Hi

Having nothing better to do this afternoon I wrote a function called
CodeDecodeSSN which has the advantage that it can be used to go from SSN to
Encrypted SSN and then from Encrypted SSN back to the originalSSN.

It has the disadvantage that the Encrypted SSN may contain Hex characters A
through F).

It probably is also slow.

The mask is arbitrary, you can select any number of the form ###-##-####'
(these must be numeric digits 0 to 9) but it must remain the same through
the life of the application.

Modify this line to set your own mask.
>set @.mask = '123-45-6789'

Basically the function works by doing a bitwise XOR of each digit in the
Source SSN against the corresponding digit in the @.mask.

Using a hash code such as David suggested is probably a better idea, but
like i said, I had nothing better to do this afternoon. ( I wish someone
would offer me a job :) )

select dbo.CodeDecodeSSN('123-46-7890')
Produces 000-03-1F19

select dbo.CodeDecodeSSN('000-03-1F19')
Produces 123-46-7890

create Function CodeDecodeSSN(@.src varchar(11))
returns varchar(11)
begin
declare @.mask varchar(11)
declare @.rv varchar(11)
set @.mask = '123-45-6789'
declare @.i int
declare @.j int
declare @.c int
declare @.c1 char(1)
declare @.c3 char(3)
declare @.m int
set @.i = 1
set @.rv = ''
while @.i <= 11
begin
if @.i = 4 or @.i = 7
set @.rv = @.rv + '-'
else
begin
Set @.c3 = '%' + substring(@.src,@.i,1) + '%'
set @.c = PatIndex(@.c3,'0123456789ABCDEF') -1
Set @.m = substring(@.mask,@.i,1)
set @.c = @.c ^ @.m
if @.c > 9
begin
set @.c1 = char(ascii('A') + @.c - 10)
end
else
begin
set @.C1 = cast(@.c as char(1))
end
set @.rv = @.rv + @.c1
end
set @.i = @.i + 1
end
return @.RV
end
--
-Dick Christoph
<ILCSP@.NETZERO.NET> wrote in message
news:1143739733.710415.9180@.z34g2000cwc.googlegrou ps.com...
> Hello, perhaps you guys have heard this before in the past, but here is
> what I'm looking for.
> I have a SQL 2000 table with Social security numbers. We need to
> create a Member ID using the Member's real SSN but since we are not
> allowed to use the exact SSN, we need to add 1 to each number in the
> SSN. That way, the new SSN would be the new Member ID.
> For example:
> if the real SSN is: 340-53-7098 the MemberID would be 451-64-8109.
> Sounds simply enough, but I can't seem to get it straight.
> I need this number to be created using a query, as this query is a
> report's record source.
> Again, any help would be appreciated it.|||ILCSP@.NETZERO.NET wrote:
> Hi David, I totally agree with you. However, this does the job.

Actually no, it doesn't do the job. The point of the policy is that
your company will be in line for a major lawsuit if (when) your
database server is hacked and the SSNs are stolen and your customers
start falling victim to identity theft. Knowing this, someone wrote a
policy that you are not allowed to store SSNs. You have NOT complied
with that policy.

Someone else already offered you one very simple solution, you could
hash the SSNs and use the hash as an ID rather than the SSN. The only
thing I would add is that you should hold on to the last four digits of
the SSN and use them to resolve collisions. That you refuse to
implement this simple and effective solution actually makes me just a
little angry. I'm angry knowing that people like you, who don't care
to protect *my* personal information, are often in positions where you
have charge of my personal information.

Incidentally, the "perfect" solution to this problem can be found in
Bruce Schneier's book Applied Cryptography. I don't have it in front
of me right now but the gist of it is that you not only hash the SSNs
but you use the SSN as a key to encrypt the rest of the data. With
this system, when someone steals your user database, they wont get
anything - not even names and addresses.

Please look into this. It's the right thing to do.|||Hi

Oh one more thing, this method is not particularily secure. If a hacker knew
one SSN and the associated encrypted SSN he/she could XOR them together to
determine the Mask used and then use this value to decrypt all the encrypted
SSNs in the table.

-Dick Christoph

"DickChristoph" <dchristo99@.yahoo.com> wrote in message
news:dmhXf.37756$Eg2.8093@.tornado.rdc-kc.rr.com...
> Hi
> Having nothing better to do this afternoon I wrote a function called
> CodeDecodeSSN which has the advantage that it can be used to go from SSN
> to Encrypted SSN and then from Encrypted SSN back to the originalSSN.
> It has the disadvantage that the Encrypted SSN may contain Hex characters
> A through F).
> It probably is also slow.
> The mask is arbitrary, you can select any number of the form ###-##-####'
> (these must be numeric digits 0 to 9) but it must remain the same through
> the life of the application.
> Modify this line to set your own mask.
>>set @.mask = '123-45-6789'
> Basically the function works by doing a bitwise XOR of each digit in the
> Source SSN against the corresponding digit in the @.mask.
> Using a hash code such as David suggested is probably a better idea, but
> like i said, I had nothing better to do this afternoon. ( I wish someone
> would offer me a job :) )
> select dbo.CodeDecodeSSN('123-46-7890')
> Produces 000-03-1F19
> select dbo.CodeDecodeSSN('000-03-1F19')
> Produces 123-46-7890
> create Function CodeDecodeSSN(@.src varchar(11))
> returns varchar(11)
> begin
> declare @.mask varchar(11)
> declare @.rv varchar(11)
> set @.mask = '123-45-6789'
> declare @.i int
> declare @.j int
> declare @.c int
> declare @.c1 char(1)
> declare @.c3 char(3)
> declare @.m int
> set @.i = 1
> set @.rv = ''
> while @.i <= 11
> begin
> if @.i = 4 or @.i = 7
> set @.rv = @.rv + '-'
> else
> begin
> Set @.c3 = '%' + substring(@.src,@.i,1) + '%'
> set @.c = PatIndex(@.c3,'0123456789ABCDEF') -1
> Set @.m = substring(@.mask,@.i,1)
> set @.c = @.c ^ @.m
> if @.c > 9
> begin
> set @.c1 = char(ascii('A') + @.c - 10)
> end
> else
> begin
> set @.C1 = cast(@.c as char(1))
> end
> set @.rv = @.rv + @.c1
> end
> set @.i = @.i + 1
> end
> return @.RV
> end
> --
> -Dick Christoph
> <ILCSP@.NETZERO.NET> wrote in message
> news:1143739733.710415.9180@.z34g2000cwc.googlegrou ps.com...
>> Hello, perhaps you guys have heard this before in the past, but here is
>> what I'm looking for.
>>
>> I have a SQL 2000 table with Social security numbers. We need to
>> create a Member ID using the Member's real SSN but since we are not
>> allowed to use the exact SSN, we need to add 1 to each number in the
>> SSN. That way, the new SSN would be the new Member ID.
>>
>> For example:
>>
>> if the real SSN is: 340-53-7098 the MemberID would be 451-64-8109.
>>
>> Sounds simply enough, but I can't seem to get it straight.
>>
>> I need this number to be created using a query, as this query is a
>> report's record source.
>>
>> Again, any help would be appreciated it.
>>|||SHA hash function:
http://www.sqlservercentral.com/col...oolkitpart4.asp
Encryption functions:
http://www.sqlservercentral.com/col...oolkitpart1.asp

<ILCSP@.NETZERO.NET> wrote in message
news:1143739733.710415.9180@.z34g2000cwc.googlegrou ps.com...
> Hello, perhaps you guys have heard this before in the past, but here is
> what I'm looking for.
> I have a SQL 2000 table with Social security numbers. We need to
> create a Member ID using the Member's real SSN but since we are not
> allowed to use the exact SSN, we need to add 1 to each number in the
> SSN. That way, the new SSN would be the new Member ID.
> For example:
> if the real SSN is: 340-53-7098 the MemberID would be 451-64-8109.
> Sounds simply enough, but I can't seem to get it straight.
> I need this number to be created using a query, as this query is a
> report's record source.
> Again, any help would be appreciated it.|||I'm tacking this very issue as well.
1. SQL Sever 2005 has symmetric and asymmetric encyption. Even tho its
slower, I testing asymmetric encryption. This way only the people that
should have access to the sSN will be able to decrypt it. The DBA's
wont even know the key.
2. On SQL Server 2000 see http://www.activecrypt.com/
This seemed to do the trick as well, but I have upgraded to 2005.
HTH
Rob

need a small help on SQL Query.

guys i need a small help on SQL Query.

I want to write a SELECT query which gives me CASE SENSITIVE search result. for ex: if a users original password in db is 'TOM' and user while logging in enters his password as 'tom', he should not be able to login ..But if he enters the password as 'TOM' he is permitted to log in .. How do i check this using a SQL Select Query ?
Any idea on this would be greatly appreciated..

NimeshUsing the PUBS database as an example, I have SQL Server installed as case insensitive. I can write a query to return the Author 'Green' from the AUTHORS table as:

select *
from authors
where au_lname = 'green'

Which would return 1 record even if the lastname is 'Green' with a capital letter. However if I want to enforce case sensitive I would convert the values to VARBINARY. The following would not return a record:

select *
from authors
where convert(varbinary,au_lname) = convert(varbinary,'green')

However this would return 1 record:

select *
from authors
where convert(varbinary,au_lname) = convert(varbinary,'Green')

Need a connection string for sql server via vb6

Hey guys,
sorry to bother you with something so newbish, but I am trying to connect to a remote sql server db, using vb6.
I want to use ado to do so but have been having problem with some of the samples I have found online.

I will probably just test the connection out on an existing database like pubs or northwind.

Can anyone provide me a code snip please?

This forum has always been a great resource for me, thanks again guys!Driver={SQL Server};Server=localhost;Address=localhost,1433;Ne twork=DBMSSOCN;Database=northwind;Uid=sa;Pwd=;

Switch the appropriate data out

Rob|||You also might want to add www.connectionstrings.com to your favorites.