Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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 @ 6:44 AM
Points: 12,905, Visits: 32,158
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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 @ 6:44 AM
Points: 12,905, Visits: 32,158
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1465770
Posted Thursday, June 20, 2013 9:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 12,905, Visits: 32,158
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1465799
Posted Thursday, June 20, 2013 11:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 12,905, Visits: 32,158
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1465867
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse