October 20, 2008 at 6:11 am
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
October 20, 2008 at 7:43 am
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
Consultant - Straight Path Solutions
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
October 20, 2008 at 9:27 am
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
October 20, 2008 at 9:40 am
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
Consultant - Straight Path Solutions
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
October 20, 2008 at 10:50 am
Fair warning: Installing Outlook on a Server is no small feat. It takes some work.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 21, 2008 at 2:07 am
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?
October 21, 2008 at 2:49 am
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
October 21, 2008 at 7:48 am
Thanks to everyone for their help, all working now.
😀
October 21, 2008 at 8:22 am
Glad it worked out for you.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 21, 2008 at 10:36 am
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!!!!!
October 21, 2008 at 10:58 am
Log in to the SQL Server's Outlook client & mailbox yourself on the Server and try to set your defaults that way.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 22, 2008 at 3:15 am
You can send emails without outlook just with cdosys. Take a look at How to send e-mail without using SQL Mail in SQL Server http://support.microsoft.com/kb/312839/en-us
Regards Ramon
October 22, 2008 at 11:10 am
Yeah I think i will stick to the xp_sendmail function for now as it seems to be a bit quicker and easier to use. Will defo read up on it tho.
Cheers!!
With the code below
EXEC master.dbo.xp_sendmail
@recipients = N'',
@query = N'EXEC RPT_3_Daily ''21-oct-2008''',
@subject = N'UK1428',
@message = N'Please find new information attached.',
@attach_results = 'TRUE',
@width = 250,
@separator = ',',
@no_header = 'TRUE',
@attachments = 'New.csv'
the attachment I receive doesn't sit in the columns properly, I have opened the file up using notepad++ which displays a lot of "NULL" values when the stored procedure to be run contains characters as well as numerics in the results, however if I use the same code as above and use a stored procedure that will only produce numbers then the csv displays fine and even opens in excel, is there something else that I have missed off to allow the file produced display and organise the alphanumeric characters correctly within excel?
October 22, 2008 at 12:20 pm
This is probably caused by commas in your string-based columns. You will need to put quotation marks around the columns that might have commas embedded in their text. Alternatively, use a different separator character (like TAB) that you are sure will not be in any of the columns.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 10, 2008 at 2:00 am
Hi,
I want to send mail's from sql ,
eg: Consider table as Table1 , with 1 column as modifiedon, When the modifiedon date is greated than 2 days of last modified date than mail has to be automatically sent to users.
In google i read this can be done in SQLMAil,
Can you please give me details steps to do this.
Thanks in Advance,
Archana
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply