Hej!

Jag har en ASP-lösning kopplad mot en SQL-server.

Jag skulle vilja köra en fråga en gång per dygn som kollar i en tabell och om vissa kriterier uppfylls ska ett mail skicka ut till de som berörs av ärendet. Dvs. mail-adresserna hämtas från db:n och är olika från gång till gång.

Så min fråga är hur ska själva mailproceduren skrivas och hur gör man för att den ska gå en gång per dygn?? Jag antar att allt sker i databasen. Skulle uppskatta alla tips eller hänvisningar till bra artiklar/tutorials

MVH

Fredrik

Exempel på en sp du kan skriva som jag brukar använda.

<code>
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@CC varchar(100) = null,
@BCC varchar(100) = null,
@Subject varchar(100)=' ',
@Body varchar(4000) =' '
/*********************************************************************

This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference 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
-- 1 means Send message using the local SMTP service pickup directory.
-- 2 means Send the message using the network (SMTP over the network).
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.
-- For example you can mention localhost
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mail.pellesoft.nu'

-- 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, 'CC', @CC
EXEC @hr = sp_OASetProperty @iMsg, 'Bcc', @BCC
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, 'TextBody', @Body
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.
-- If you pass wrong email ids then it will throw 2147220977 error
-- If you dont give the correct MAIL Server Name then it will throw -2147220973

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 need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg


GO
</code>

Stort tack! Den funkade perfekt

Hej!

Om jag nu vill konfigurera denna stored procedure så den ska logga in mot en annan smtp-server har jag då tänkt rätt enligt nedan?? Jag får det nämligen inte att funka...

<code>
ALTER PROCEDURE dbo.sp_send_cdosysmail
@From varchar(100) ,
@To varchar(100) ,
@CC varchar(100) = null,
@BCC varchar(100) = null,
@Subject varchar(100)=' ',
@Body varchar(4000) =' '

/*********************************************************************

This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference 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
-- 1 means Send message using the local SMTP service pickup directory.
-- 2 means Send the message using the network (SMTP over the network).

-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
-- For example you can mention localhost

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value', '2'
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mail.se'
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value', 25
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', 'anvnamn'
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', 'lösenord'


-- 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, 'CC', @CC
EXEC @hr = sp_OASetProperty @iMsg, 'Bcc', @BCC
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, 'TextBody', @Body
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.
-- If you pass wrong email ids then it will throw 2147220977 error
-- If you dont give the correct MAIL Server Name then it will throw -2147220973

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 need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg

</code>