Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can automate report from sqlserver to be sent on email everyday. Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 8:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 4:55 AM
Points: 7, Visits: 18
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.
Post #1465744
Posted Thursday, June 20, 2013 8:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 14,408, Visits: 37,689
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!
Post #1465752
Posted Thursday, June 20, 2013 9:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 4:55 AM
Points: 7, Visits: 18
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.
Post #1465759
Posted Thursday, June 20, 2013 9:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 14,408, Visits: 37,689
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!
Post #1465770
Posted Thursday, June 20, 2013 9:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 14,408, Visits: 37,689
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!
Post #1465799
Posted Thursday, June 20, 2013 11:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 14,408, Visits: 37,689
tnnelzo (6/20/2013)
It is always from the first day of the current month....to the current day

and 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!
Post #1465867
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse