February 8, 2010 at 1:26 pm
I have been searching high and low on the internet to see if it's possible to send Query results in the body of an e-mail message. Most people say it can't be done. I need to send an HTML formatted e-mail out every month that includes the results from a very simply select statement. i.e
SELECT * FROM table WHERE ID = 1
I know how to format the e-mail in SQL using tables and what not. I do not have the option of installing Outlook on my 2003 server running SQL 2000. I am just looking for the easiest way without having to install programs or upgrading servers. There must be a way when using 'sp_send_cdosysmail'. Any help would be so appreciated. By the way are there other free MAPI programs that can be used in conjunction with configuring SQL mail to allow me maybe to use XP_SENDMAIL My example. I really need some help as soon as possible. I have posted something that asks this question but the answer I received did not work . Please help!!:-)
declare @body varchar(8000)
set @body = SELECT * FROM TABLE
exec sp_send_cdosysmail 'from@email.com','to@email.com','This is my Subject', @BODY
February 10, 2010 at 2:25 pm
What error are you getting trying your method?
I do not know about using the sp you talked about, but there are some alternatives you can try. Here is a Microsoft Support KB talking about how to use CDO for Windows 2000:
How to send e-mail without using SQL Mail in SQL Server
Alternatively, you can use T-SQL in conjunction with VBScript to send the e-mail using CDO. Here is a very helpful article showing how to send mail with VBScript:
VBScript To Send Email Using CDO
Finally, if you install PowerShell 2.0 you could try running a script with PowerShell and assigning the results to a variable that you can send in an e-mail using the send-mailmessage cmdlet:
Joie Andrew
"Since 1982"
February 11, 2010 at 12:00 pm
Just a thought. Do you have access to SQL Reporting Services? If so, you can create a report and have it distributed on a scheduled basis.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply