SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How can automate report from sqlserver to be sent on email everyday.


How can automate report from sqlserver to be sent on email everyday.

Author
Message
tnnelzo
tnnelzo
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 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.
Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29020 Visits: 39984
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!

tnnelzo
tnnelzo
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 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.
Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29020 Visits: 39984
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!

Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29020 Visits: 39984
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!

Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29020 Visits: 39984
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!

welcome.to.route66
welcome.to.route66
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 3
try vsql-email, you can find it on google, it is exactly what you want and saved me a lot of time.
JustMarie
JustMarie
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1033 Visits: 1301
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.
Luis Cazares
Luis Cazares
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17074 Visits: 19122
I wonder why an SSRS subscription wasn't considered. Too late for that, I suppose.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29020 Visits: 39984
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search