April 11, 2010 at 10:19 am
Hello comunity
I need to create a StoredProcedure to send Emails to my different sails vendors on each friday automatically if on of them have sales.
I read some SP, like above, but i have many vendors and surely i will need to create a cursor to controle when the vendor changed, and if is changed before to continue my stored procedure i need to send the Email to the current vendor.
After that, the cursor must continue the nomal loop cycle until you meet nothing more vendors.
Also , how can do this task like i say "on each friday week automatically.".
I read this script:
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT Product AS 'td','',SUM(SalePrice) AS 'td'
FROM SalesHistory GROUP BY Product FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><H1>Sales Reports</H1><body bgcolor=yellow><table border = 2><tr><th>Product</th><th>SaleAmount</th></tr>' SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'chapman.tim@gmail.com',@body = @body,@body_format ='HTML',@subject ='Message Subject',@profile_name ='DatabaseMailProfile'
On my invoice header , i have the name of my vendor and it´s easy to build a TSQL union query to find their Email adress.
If is more simple i can use SMTP and not the Database Mail configuration of SQL Server.
Someone give me any help.
Many thanks
Luis Santos
April 11, 2010 at 1:19 pm
just set up a dbmail profile to be able to send emails, and then schedule a sqlagent job to launch your emails.
You can either put your sqlscript in the sqlagent job step to loop through your recipients and the execute a query provided with the particular recipient.
you can add the query results as an attachement.
Or create a stored procedure that performs all your coded stuff and just execute that in your sqlagent job.
Personally I favor having it all in the job itself because that allows me to query the sqlagent sysjobs... tables to figure out who does what.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 12, 2010 at 10:34 am
Hello
Thanks for your reply.
I will ask you if in the comunity, i could have an example on how to configure DBMAIL.
Also, have you a very simple example of:
"You can either put your sqlscript in the sqlagent job step to loop through your recipients and the execute a query provided with the particular recipient."
Many thanks for your reply.
Best regards
Luis Santos
April 13, 2010 at 1:02 am
Best is to just read about it in Books online and then perform a test using the Database mail configuration wizard of SSMS.
Here's a nice setup ref: http://www.sql-server-performance.com/da_email_functionality.asp
Here's a little sample on how to send a db-mail with the data as attachement:
declare @subject varchar(1000)
set @subject = @@servername + ': Rows of master sysfiles'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Just4DBAprofile',
@recipients = 'myemailaddress@somwhere.com',
@query = 'SELECT * FROM master.sys.sysfiles ',
@subject = @subject,
@body='Have a look at the data in the attachement !',
@query_result_width = 8000,
@query_result_separator = '',
@attach_query_result_as_file = 1 ,
@query_attachment_filename='deQueryResultFileName.csv',
@append_query_error = 1 ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 13, 2010 at 3:36 am
Hello again
Many thanks for your reply , i will test it and it´s seems the better way to make what i need.
Best regards
Luis Santos
August 4, 2014 at 1:00 am
Hi community,
I want to know whether we can create an automated html format mail for new users and it has to be immediate as soon as a new user will be there a welcome email will be sent to him .
Is it possible with sql server, because we can generate mails for job failures and all. I hope will get some positive reply asap.
Thanks and Regards
Vinod Kumar
August 4, 2014 at 1:04 am
vinodmmec916 (8/4/2014)
Hi community,I want to know whether we can create an automated html format mail for new users and it has to be immediate as soon as a new user will be there a welcome email will be sent to him .
Is it possible with sql server, because we can generate mails for job failures and all. I hope will get some positive reply asap.
Thanks and Regards
Vinod Kumar
Keep in mind this is an old thread ! (2010)
Please start a new one with your Q.
( to which the answer is YES ! )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply