help with a stored procedure

here is my sp

create proc [login]

(

@.email as varchar(200)

@.password as varchar(200)

)

as

begin

select customerid from customerlogin

where email = @.email

and password = @.password

End

print 'Welcome'

print (@.email)

End

This works fine, but if the email is wrong, what do I need to add to this to print that the email is wrong and not print 'welcome email'

Help, please.....

Hello,

I think what you want is:

create proc [login]

(

@.email as varchar(200)

@.password as varchar(200)

)

as

IF EXISTS (select customerid from customerlogin where email = @.email and password = @.password)

BEGIN

print 'Welcome'

print (@.email)

END

ELSE

BEGIN

print 'Bad login!'

print (@.email)

END

GO

As a side note, best practice is to NOT pass the users' password, but rather store a hash of the password and then when the user attempts to login, you simply create a hash of the entered password and compare it with the hashed value stored in the db.

Cheers

Rob

|||

create procedure sp_login

@.email as varchar(200)

@.password as varchar(200)

AS

Select CASE WHEN ((Select COUNT(*) from customerlogin where email = @.email and password = @.password) = 0)

THEN Print 'Invalid Login'

ELSE Print 'Welcome ' + @.email

END AS [EmailTest]

Adamus

Help to encrypt a flat file destination

I am using SSIS to create a weekly data extraction that will be emailed to an external agency. I can extract the data, create the file and email it without any problems but I want to compress and encrypt it before I send to give some measure of protection to sensitive data it would contain.

If I did this manually, I would simply use Winzip to compress and encrypt the file to be sent. How can I achieve a similar result programtically?

Set a step to run a command, and use the command line functionality of Winzip.

Help sending email

My code below works fine when run from my pc (changed all the values for
obvious reasons). The code is placed inside a DTS task via VBS scripting.
But when I try to run directly from the server where sqlserver is installed,
the script fails.

I have SMTP running, but there is no outlook installed.

Can someone please advise what I am missing.
Thanks
Bob

Set objEmail = CreateObject("CDO.Message")

objEmail.From = "send@.test.com"
objEmail.To = "receive@.test.com"
objEmail.Subject = "TEST SUBJECT"
objEmail.AddAttachment "\\server\test.csv"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendusing") = 2
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpserver") = "SERVER_NAME"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpauthenticate") = 1
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendusername") = "username"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/sendpassword") = "userpwd"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
ration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send

set objEmail = nothingHi B

One thing you might try is change

> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = 2
to
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = "2"

quotes around the 2.

I spent several hours a week ago trying the very same thing and that made
all the difference.

Also is \\server\test.csv accessible from the server you are running this
on?

Here is the full text of the DTS Package I wrote. Note that I don't think
all the fields you included are necessary.

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
email_alert "dchristo@.yahoo.com", "George_Bush@.whitehouse.gov","Test
Subject", "Test Body"
Main = DTSTaskExecResult_Success
End Function

Sub email_alert(strTo, strFrom, strSubject, strBody)
Dim iConf 'As CDO.Configuration
Dim imsg 'As CDO.Message
Dim flds

Set imsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

Set flds = iConf.Fields

'The http://schemas.microsoft.com/cdo/configuration/ namespace defines
the majority of fields used to set configurations for various CDO objects.
We set and update the following three fields (SendUsing, SMTP_SERVER, and
TimeOut) of the Configuration object:

With flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") =
"2"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
"smtp-server.mn.rr.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")
= 10
.Update
End With

Set imsg.Configuration = iConf
imsg.To = strTo
imsg.From = strFrom
imsg.Subject = strSubject
imsg.TextBody = strBody
imsg.AddAttachment "c:\log\myfile.txt"
imsg.Send
End Sub
--
-Dick Christoph

"B" <no_spam@.no_spam.com> wrote in message
news:KsydnTnYtfFV2wbZnZ2dnUVZ_uqdnZ2d@.rcn.net...
> My code below works fine when run from my pc (changed all the values for
> obvious reasons). The code is placed inside a DTS task via VBS scripting.
> But when I try to run directly from the server where sqlserver is
> installed,
> the script fails.
> I have SMTP running, but there is no outlook installed.
> Can someone please advise what I am missing.
> Thanks
> Bob
>
> Set objEmail = CreateObject("CDO.Message")
> objEmail.From = "send@.test.com"
> objEmail.To = "receive@.test.com"
> objEmail.Subject = "TEST SUBJECT"
> objEmail.AddAttachment "\\server\test.csv"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = 2
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserver") = "SERVER_NAME"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpauthenticate") = 1
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusername") = "username"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendpassword") = "userpwd"
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserverport") = 25
> objEmail.Configuration.Fields.Update
> objEmail.Send
> set objEmail = nothing|||Follow-up to my original post below.

Is it possible for the "objEmail.To" to lookup the values from a sqlserver
table?

At the moment, I type the email address separated by a semi-colon.

TIA~

"B" <no_spam@.no_spam.com> wrote in message
news:KsydnTnYtfFV2wbZnZ2dnUVZ_uqdnZ2d@.rcn.net...
> My code below works fine when run from my pc (changed all the values for
> obvious reasons). The code is placed inside a DTS task via VBS scripting.
> But when I try to run directly from the server where sqlserver is
installed,
> the script fails.
> I have SMTP running, but there is no outlook installed.
> Can someone please advise what I am missing.
> Thanks
> Bob
>
> Set objEmail = CreateObject("CDO.Message")
> objEmail.From = "send@.test.com"
> objEmail.To = "receive@.test.com"
> objEmail.Subject = "TEST SUBJECT"
> objEmail.AddAttachment "\\server\test.csv"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusing") = 2
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserver") = "SERVER_NAME"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpauthenticate") = 1
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendusername") = "username"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/sendpassword") = "userpwd"
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
> ration/smtpserverport") = 25
> objEmail.Configuration.Fields.Update
> objEmail.Send
> set objEmail = nothing|||Hi B,

Well not directly but you could create an ADODB Command, Connection and
Recordset and use the command to return you a recordset from the Database
that would have 1 or many email addresses that you could concatenate
together and stick in the objEmail.To field.

--
-Dick Christoph
"B" <no_spam@.no_spam.com> wrote in message
news:c4mdnfssT45ImT7ZnZ2dnUVZ_r-dnZ2d@.rcn.net...
> Follow-up to my original post below.
> Is it possible for the "objEmail.To" to lookup the values from a sqlserver
> table?
> At the moment, I type the email address separated by a semi-colon.
> TIA~
>
> "B" <no_spam@.no_spam.com> wrote in message
> news:KsydnTnYtfFV2wbZnZ2dnUVZ_uqdnZ2d@.rcn.net...
>> My code below works fine when run from my pc (changed all the values for
>> obvious reasons). The code is placed inside a DTS task via VBS
>> scripting.
>> But when I try to run directly from the server where sqlserver is
> installed,
>> the script fails.
>>
>> I have SMTP running, but there is no outlook installed.
>>
>> Can someone please advise what I am missing.
>> Thanks
>> Bob
>>
>>
>> Set objEmail = CreateObject("CDO.Message")
>>
>> objEmail.From = "send@.test.com"
>> objEmail.To = "receive@.test.com"
>> objEmail.Subject = "TEST SUBJECT"
>> objEmail.AddAttachment "\\server\test.csv"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/sendusing") = 2
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/smtpserver") = "SERVER_NAME"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/smtpauthenticate") = 1
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/sendusername") = "username"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/sendpassword") = "userpwd"
>>
> objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configu
>> ration/smtpserverport") = 25
>> objEmail.Configuration.Fields.Update
>> objEmail.Send
>>
>> set objEmail = nothing
>>
>>