Arjuna Aircraft Ident.: F-MBSD | Bon, finalement, à force de chercher, pas trouvé de moyen d'utiliser cette fonction sans passer par Outlook...
Allez, zou !
Code :
- CREATE PROCEDURE SendMail
- (
- @From varchar(100),
- @To varchar(100),
- @Subject varchar(100)=" ",
- @Body varchar(4000) =" "
- )
- /******************************************
-
-
- This stored procedure takes the parameters and sends
- an e-mail. All the mail configurations are hard-coded
- in the stored procedure. Comments are added to the
- stored procedure where necessary. References to the
- CDOSYS objects are at the following MSDN Web site:
- http://msdn.microsoft.com/library/default.asp
- ?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
-
-
- *******************************************/
- AS
- Declare @iMsg int
- Declare @hr int
- Declare @source varchar(255)
- Declare @description varchar(500)
- Declare @output varchar(1000)
- --***** Create the CDO.Message Object *****
- EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
- --*****Configuring the Message Object *****
- -- This is to configure a remote SMTP server.
- -- http://msdn.microsoft.com/library/default.asp
- -- ?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
- EXEC @hr = sp_OASetProperty @iMsg,
- 'Configuration.fields ("http://schemas.microsoft.com/cdo/configuration/sendusing" ).Value',
- '2'
- -- This is to configure the Server Name or IP address.
- -- Replace MailServerName by the name or IP of your SMTP Server.
- EXEC @hr = sp_OASetProperty @iMsg,
- 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver" ).Value',
- '127.0.0.1'
- -- Save the configurations to the message object.
- EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
- -- Set the e-mail parameters.
- EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
- EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
- EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
- -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
- EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
- EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
- -- Sample error handling.
- IF @hr <> 0
- SELECT @hr
- BEGIN
- EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
- IF @hr = 0
- BEGIN
- SELECT @output = ' Source: ' + @source
- PRINT @output
- SELECT @output = ' Description: ' + @description
- PRINT @output
- END
- ELSE
- BEGIN
- PRINT ' sp_OAGetErrorInfo failed.'
- RETURN
- END
- END
- -- Do some error handling after each step if you have to.
- -- Clean up the objects created.
- EXEC @hr = sp_OADestroy @iMsg
- GO
|
Pis j'aime bien le trompe-la-mort que je suis, à première vue, c'est pas la base qui envoie le mail et on croit que je génère un mot de passe débile 
Code :
- CREATE procedure RegisterUser
- (
- @firstname varchar(20),
- @lastname varchar(20),
- @email varchar(255),
- @login varchar(20),
- @idLanguage tinyint,
- @ret tinyint output
- )
- as
- begin
- select @ret = sum(chk)
- from
- (
- select count(login) chk from users where lower(login) = lower(@login)
- union all
- select count(email) * 2 chk from users where lower(email) = lower(@email)
- ) tmp
- if @ret = 0
- begin
- -- This will raise a trigger that generate an email
- -- Planet generation will be done when first login (to avoid polution in database)
- insert into users (firstname, lastname, email, login, password, idLanguage)
- values (@firstname, @lastname, @email, @login, 'password', @idLanguage)
- end
- end
- GO
|
Mais c'est pas vrai, au contraire, je force cet envoi et la génération d'un password aléatoire même lors de l'instertion à la main 
Code :
- create trigger trg_register
- on dbo.Users instead of insert
- as
- declare @firstname as varchar(20)
- declare @lastname as varchar(20)
- declare @email as varchar(255)
- declare @login as varchar(20)
- declare @password as varchar(20)
- declare @idLanguage as tinyint
- declare @title as varchar(50)
- declare @body as varchar(50)
- begin
- select @firstname = firstname from inserted
- select @lastname = lastname from inserted
- select @email = email from inserted
- select @login = login from inserted
- select @idLanguage = idLanguage from inserted
- exec GetLabel 'registerTitle', @idLanguage, @title output
- exec GetLabel 'registerBody', @idLanguage, @body output
- exec sendmail '*@*.*', @email, @title, @body
- insert into users (firstname, lastname, email, login, password, idlanguage, registerdate)
- values (@firstname, @lastname, @email, @login, left(newid(),8), @idLanguage, getdate())
- end
|
Gniark !
Message édité par Arjuna le 10-06-2005 à 00:26:30
|
