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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy