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 12»»

Create a scheduled task to email from SQL Expand / Collapse
Author
Message
Posted Monday, October 20, 2008 6:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 1, 2010 5:45 AM
Points: 19, Visits: 73
Hi,

is it possible to get SQL to email information that it has collected from a query as an attachment in csv format?

I am able to email the information in question using an SQL query however I only know how to put it into the body of the email and not as a csv attachment. Please could someone help me?

Arif
Post #588490
Posted Monday, October 20, 2008 7:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:53 AM
Points: 11,238, Visits: 12,998
Lookup xp_sendmail in BOL. Set @query, @attach_results, and @separator parameters.

OR

If you are using 2000 and have Reporting Services you could setup a subscription in RS.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #588550
Posted Monday, October 20, 2008 9:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 1, 2010 5:45 AM
Points: 19, Visits: 73
Thanks for this,

I have used the query below:

EXEC master.dbo.xp_sendmail
@recipients = N'name@domain.com',
@query = N'EXEC ComSys..RPT_3_DailyEmail ''17-oct-2008''',
@subject = N'SQL Server Report',
@message = N'Test',
@attach_results = 'TRUE',
@width = 250 ;

and it throws back an error of:
Server: Msg 18030, Level 16, State 1, Line 0
xp_sendmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.

I have since installed outlook on the SQL server and set microsoft outlook to the default mail client but it still displays the error above.

What else have I missed?

Thanks again

Post #588649
Posted Monday, October 20, 2008 9:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:53 AM
Points: 11,238, Visits: 12,998
Outlook has to be installed using the SQL Server service account and have a profile for that account. So you need a domain account for SQL Server to send this email.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #588654
Posted Monday, October 20, 2008 10:50 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Fair warning: Installing Outlook on a Server is no small feat. It takes some work.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #588699
Posted Tuesday, October 21, 2008 2:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 1, 2010 5:45 AM
Points: 19, Visits: 73
Thanks for the help,

I have managed to install an outlook client on the server and restarted the services and the above code works however it attachs the query as a .txt file, is there a way for me to make it send as a csv?
Post #588994
Posted Tuesday, October 21, 2008 2:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 1, 2014 12:48 AM
Points: 292, Visits: 810
So you can control the name of the attachment, use the @attachments parameter for xp_sendmail.
I suggest you also review the online help for the @separator parameter since you mention you would be using CSV extension.

------
Robert



Post #589009
Posted Tuesday, October 21, 2008 7:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 1, 2010 5:45 AM
Points: 19, Visits: 73
Thanks to everyone for their help, all working now.

:D
Post #589134
Posted Tuesday, October 21, 2008 8:21 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Glad it worked out for you.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #589170
Posted Tuesday, October 21, 2008 10:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 1, 2010 5:45 AM
Points: 19, Visits: 73
And lastly.. i promise

is there a way to make the email being sent using the guidelines above in HTML format? and if so how?

Thanking you!!!!!
Post #589297
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse