How can automate report from sqlserver to be sent on email everyday.
-
Hello!
Everyday i run a querry in sqlserver 2008 to get a report to sent to my boss, but i want to automate this, so that the email will be sent at 5:00 pm everday without having me to do it ....can anyone help me on this....thanks.
-
it depends on the "report"
Since you said it's a query, i assume you must be running it, copying it into an excel or something, and email it?
or formatting as HTML?
or just pasting it as plain text?
this post has some great recommendations for sending html formatted emails:, if you can adapt your process:
http://www.sqlservercentral.com/Forums/Topic1465444-279-1.aspx
once you have a query, you can add sp_sendsbmail to use it,a nd then a scheduled job to execute that whole codeblock whenever you want it to.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks lowell,
I have been running the querry and then copying the result into an excel sheet and send as an attachement ...so other than sending it as html is there any other way i can send ...like in excel? thanks.
-
tnnelzo (6/20/2013)
Thank you for your help...however i want to send this report in excel ..is there any way i can do that? thanks you so much.well, it depends.
have you set up Database mail? is it working?
That's a prerequisite to everything you are asking.
As Far as Excel you can create a CSV file, which opens by default in Excel very easily with sp_sendDBMail.
If the Excel needs custom formatting, headers, bold, etc, then no, not easily. it's much more involved. Automating Excel, and opening an existing template is required; lot easier to do html than anything else.
a basic example for a CSV File:
declare @body1 varchar(4000)
set @body1 = 'Sample Email from SQL2008 ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +
' '
EXEC msdb.dbo.sp_send_dbmail
@profile_name='MyGmailProfileName',
@recipients='lowell@somedomain.com',
@subject = 'SQl 2008 email test',
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT top 3 * from sysobjects where xtype=''U''',
@query_result_header = 1,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'qry.csv',
@query_result_no_padding = 1
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
tnnelzo (6/20/2013)
Hello lowell,Here is the querry i run everyday,
Select day(b.date_entered)Day_D, sum(a.despatched_qty), sum(a.val) from scheme.dgtable1 a, scheme.dntable3 b
where a.order_no = b.order_no
and a.product like 'Machines%'
and b.date_entered >=cast('2011-05-01 00:00:00.000' as datetime)
and b.date_entered < cast('2011-05-24 00:00:00.000'as datetime)
group by day(b.date_entered)
order by day(b.date_entered)
so i have been changing the date depending on the current date so it is pretty challenging because it has to run and give results of stats of the previous day......please help me do this....
it can still be done, but how are the dates to calculated?
think of it compared to today:
if today is 06/20/2013, and i want the first day of the month, i can use
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
is it from the first day of this month? the previous month?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
tnnelzo (6/20/2013)
It is always from the first day of the current month....to the current dayand b.date_entered >=cast('2013-06-01 00:00:00.000' as datetime)
and b.date_entered < cast('2013-06-20 00:00:00.000'as datetime)
and when the month ends it changes to
and b.date_entered >=cast('2013-06-01 00:00:00.000' as datetime)
and b.date_entered <=cast('2013-06-30 00:00:00.000'as datetime)
Thanks and Best Regards.
so you just need to get used to the DATEADD /DATEDIFF functions,a s they are very powerful;
this is what i think you are after; note i changed your join to be explicit, and aliased your columns :
SELECT
DAY(b.date_entered) AS Day_D,
SUM(a.despatched_qty) AS DesQty,
SUM(a.val) AS Val
FROM scheme.dgtable1 a
INNER JOIN scheme.dntable3 b
ON a.order_no = b.order_no
WHERE a.product LIKE 'Machines%'
AND b.date_entered >=DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0) --first day of THIS month
AND b.date_entered < DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 1) --midnight of tomorrow morning
GROUP BY DAY(b.date_entered)
ORDER BY DAY(b.date_entered)
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
try vsql-email, you can find it on google, it is exactly what you want and saved me a lot of time.
-
I'm late to the party but I do send scheduled emails with HTML formatting using SSIS.
It's a bit involved since I need to use XML to create the table and then wrap the HTML formatting around that then a script task to send it but it works great. Now.
I'm sending basic tables but if you want to plunk around to make fancy stuff I can post the steps that work for me.
-
I wonder why an SSRS subscription wasn't considered. Too late for that, I suppose.
-
Luis Cazares (11/15/2016)
I wonder why an SSRS subscription wasn't considered. Too late for that, I suppose.at the time i was answering, i had tunnel vision.
i swear, it looked like a nail!
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
welcome.to.route66 (11/4/2016)
try vsql-email, you can find it on google, it is exactly what you want and saved me a lot of time.Why? The OP was looking to send an Excel spreadsheet. This does not appear to do that.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Luis Cazares (11/15/2016)
I wonder why an SSRS subscription wasn't considered. Too late for that, I suppose.This would be the approach I would take. It is simple to set up Subscriptions in SSRS. Another option with Subscriptions is that you can also save the reports (say as Excel) to a network drive every day so that you have a history as well. Your boss could find them there if he is not a fan of email delivery.
----------------------------------------------------
-
tnnelzo (6/20/2013)
Hello!Everyday i run a querry in sqlserver 2008 to get a report to sent to my boss, but i want to automate this, so that the email will be sent at 5:00 pm everday without having me to do it ....can anyone help me on this....thanks.
Why not make an updateable spreadsheet that performs and external query so that your boss can refresh the data any time (s)he wants to?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (11/16/2016)
tnnelzo (6/20/2013)
Hello!Everyday i run a querry in sqlserver 2008 to get a report to sent to my boss, but i want to automate this, so that the email will be sent at 5:00 pm everday without having me to do it ....can anyone help me on this....thanks.
Why not make an updateable spreadsheet that performs and external query so that your boss can refresh the data any time (s)he wants to?
I think the boss may want to have historical information segmented out.
----------------------------------------------------
-
MMartin1 (11/17/2016)
Jeff Moden (11/16/2016)
tnnelzo (6/20/2013)
Hello!Everyday i run a querry in sqlserver 2008 to get a report to sent to my boss, but i want to automate this, so that the email will be sent at 5:00 pm everday without having me to do it ....can anyone help me on this....thanks.
Why not make an updateable spreadsheet that performs and external query so that your boss can refresh the data any time (s)he wants to?
I think the boss may want to have historical information segmented out.
Possibly. But the boss can save the spreadsheet everyday.
As a bit of a sidebar and,as I know you know, so just saying it for others... daily spreadsheets aren't the place to store historical data/information. That's kind of stuff needs to be accumulated in a table so that you can actually do some stats across different time-frames.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply