Create a scheduled task to email from SQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Create a scheduled task to email from SQL

20 posts, Page 1 of 2

1 2 »»

Create a scheduled task to email from SQL

Rate Topic

Display Mode

Topic Options

Author

Message

Arif Hafeez-Imam

Posted Monday, October 20, 2008 6:10 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 01, 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

Jack Corbett

Posted Monday, October 20, 2008 7:43 AM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

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

Arif Hafeez-Imam

Posted Monday, October 20, 2008 9:27 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 01, 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

Jack Corbett

Posted Monday, October 20, 2008 9:40 AM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

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.

Post #588654

RBarryYoung

Posted Monday, October 20, 2008 10:50 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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

Arif Hafeez-Imam

Posted Tuesday, October 21, 2008 2:07 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 01, 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

rcavill

Posted Tuesday, October 21, 2008 2:49 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 01, 2013 11:51 PM
Points: 292, Visits: 794

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

Arif Hafeez-Imam

Posted Tuesday, October 21, 2008 7:48 AM

Grasshopper

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

Thanks to everyone for their help, all working now.

:D

Post #589134

RBarryYoung

Posted Tuesday, October 21, 2008 8:21 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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

Arif Hafeez-Imam

Posted Tuesday, October 21, 2008 10:36 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 01, 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 »

20 posts, Page 1 of 2

1 2 »»

Permissions