August 4, 2008 at 3:56 pm
I know enough about SQL to be dangerous. Here is my goal, I want to be notified by email whenever a customer in our Great Plains Accounting system has an invoice over 90 days. I have the below script which returns in query analyzer exactly what I want to see in in the email.
select RM00101.CUSTNAME,DOCNUMBR,docamnt,curtrxam,SOP30200.DOCDATE,RM00101.SLPRSNID
from rm20101,rm00101,SOP30200 where rm00101.CUSTNMBR=rm20101.CUSTNMBR AND RM20101.DOCNUMBR=SOP30200.SOPNUMBE
and DATEADD(day, DATEDIFF(day, 0, getdate()), 0)- RM20101.DOCDATE>=90 and curtrxam<>0
I have installed Outlook and configured the email to work directly on the SQL Server 2000 box. In SQL Server Agent I have created a job, with the above script as a step. I have scheduled it to run at the appropriate time and I have enabled the email notification and with a few headaches I have been able to get this to work. The problem is that the below is the email I receive.
JOB RUN:'Over 90 day notification' was run on 8/4/2008 at 3:24:54 PM
DURATION:0 hours, 0 minutes, 1 seconds
STATUS: Succeeded
MESSAGES:The job succeeded. The Job was invoked by User DOMAIN\Administrator. The last step to run was step 1 (Main).
I do not get any of the results from the SQL script.
Am I taking the right general steps to accomplish what I am after? I have read some about using xp_sendmail and other command line scripts, but I don't know if I need to pursue that route. Your assistance is appreciated in advance.
Thanks,
Austin
August 5, 2008 at 8:53 am
This is what I do within a SQL Agent Job Step, works every time.
DECLARE @query varchar(1000)
BEGIN
EXEC master.dbo.xp_sendmail @recipients='myname@mycompany.com',
@message='The attached text file contains a list of active employees.',
@query= 'select CompanyId as Company, CONVERT(VARCHAR,CurrentRunDate,101) as [Current Date], CurrentDayTotal as [Current Total],CONVERT(VARCHAR,LastRunDate,101) as [Last Date], LastDayTotal as [Last Total], Diff, PctDiff as [Percent Change] from DBMaint..ActiveEmployeeCountDiffs where PctDiff > 10 order by PctDiff desc',
@subject='Active Employees',
@attach_results ='True',
@attachments='ActiveEmployees.txt',
@width = 400,
@echo_error ='True'
END
-- You can't be late until you show up.
August 5, 2008 at 10:44 am
Terry, Thank you very much! I think I am on my way to having this solved, but still am having some issues. I changed your script to the below:
DECLARE @query varchar(1000)
BEGIN
EXEC master.dbo.xp_sendmail @recipients='myemail@mycompany.com',
@message='The attached text file contains invoices that are over 90 days old.',
@query= 'select RM00101.CUSTNAME,DOCNUMBR,docamnt,curtrxam,SOP30200.DOCDATE,RM00101.SLPRSNID
from rm20101,rm00101,SOP30200 where rm00101.CUSTNMBR=rm20101.CUSTNMBR AND RM20101.DOCNUMBR=SOP30200.SOPNUMBE
and DATEADD(day, DATEDIFF(day, 0, getdate()), 0)- RM20101.DOCDATE>=90 and curtrxam<>0',
@subject='Over 90 day notice',
@attach_results ='True',
@attachments='Over 90 days notice.txt',
@width = 400,
@echo_error ='True'
END
However, I am now getting the error:
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80004005
I did a search on this and found quite a few examples of it. I see many people who say to not use Outlook. Some say to use Outlook 2002 rather than 2003. Not sure who to believe or what to try. I am using Outlook 2003 and have it running at the time I launch the script. Your help again is appreciated.
Austin
August 5, 2008 at 11:04 am
Glad I could offer some assistance. Just a quick side note, my servers are all Windows 2003 and Outlook 2003.
edit - Also, at my last job we used SMTPMail. It takes Outlook out of the mix and is easy to use. Check it out here.
http://www.sqlteam.com/article/sending-smtp-mail-using-a-stored-procedure
-- You can't be late until you show up.
August 5, 2008 at 11:32 am
Outlook shouldn't be running on the server. It needs to be installed, but the SQL Agent account will use the Outlook DLLs to connect to your Exchange server and send the mail.
Go through the SQL Mail setup and testing (search for the kb) and once that is working, the script should work.
August 5, 2008 at 11:54 am
I forget some key information. We are using Pop3, not Exchange. Does that make a difference?
August 5, 2008 at 12:00 pm
At my last job we used XP_SMTP_SENDMAIL (http://sqldev.net/xp/xpsmtp.htm) on all our SQL 7 and 2000 servers so we could get away from SQL Mail except for alerts and failed job notifications and it worked flawlessly. Very similar to what Terry had linked to except it is built an extended stored procedure.
If you have Reporting Services installed you could also create a report with an email subscription.
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
August 5, 2008 at 12:07 pm
Jack Corbett (8/5/2008)
At my last job we used XP_SMTP_SENDMAIL (http://sqldev.net/xp/xpsmtp.htm) on all our SQL 7 and 2000 servers so we could get away from SQL Mail except for alerts and failed job notifications and it worked flawlessly. Very similar to what Terry had linked to except it is built an extended stored procedure.If you have Reporting Services installed you could also create a report with an email subscription.
Jack, that's exactly the one we used as well, originally on SQL 7 and then 2000 too. The link I provided was the first viable link when I Googled SMTPMail, so I took the easy way out. Thanks for the reminder.
And, yes, it worked flawlessly. When I started at my present job, Outlook was already installed so I now use SQL Mail, again, taking the easy way out...;)
-- You can't be late until you show up.
August 5, 2008 at 12:15 pm
tosscrosby (8/5/2008)
...Outlook was already installed so I now use SQL Mail, again, taking the easy way out...;)
Even with Outlook installed I wouldn't call using SQLMail the easy way out. I found XP_SMTP_SENDMAIL to be easier to use and more reliable. Unfortunately I had to use SQLAgent Mail to get Alerts and Job Notifications.
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
August 5, 2008 at 12:34 pm
Yes but with serveral hundreds jobs already in place, all using Outlook, I can't justify the time spent, although, reading what I just wrote, we could save a license fee for Outlook if we removed from the server, correct?
-- You can't be late until you show up.
August 5, 2008 at 12:46 pm
I suppose you could save that little $ if needed. I just would install XP_SMTP_SENDMAIL and use it for new email needs.
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
August 5, 2008 at 1:02 pm
Still trying with this thing. Not sure if I am taking the right direction. I have attempted to use the XP_Smtp_sendmail command. I pulled up an example online and changed only the email info to see if I could get that to work.
declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'sqlserver@mycompany.com',
@TO = N'austins@mycompany.com'
select RC = @rc
go
I get the error:
Server: Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'master.dbo.xp_smtp_sendmail'.
Isn't this stored proc part of SQL Server without needing to create it? I am on SQL 2000 (8.00.2050 SP4)
One other thing to mention, when I open Outlook it says something has the outlook.pst file in use. The program is the SQL agent that has it in use. Shutting down the service allows it to open. Is this normal or could this be causing some of my problems?
August 5, 2008 at 1:06 pm
You have to go out and get xp_smtp_sendmail and install it on the sql server, it is not part of sql server. The link I provided earlier includes the download link and installation instructions.
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
August 5, 2008 at 1:06 pm
If I remember correctly, you download it and create before you can use it. I'm also on S2KSP4 and I don't see it.
-- edit - Beaten by 2 seconds. 😛
-- You can't be late until you show up.
August 5, 2008 at 1:53 pm
This is killing me! Sorry for being so helpless.
I downloaded xp_smtp_sendmail and got it registered.
I ran this in query analyzer.
declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'sqlserver@mycompany.com',
@TO = N'austins@mycompany.com'
select RC = @rc
go
No errors, but no emails. It did display a 1 in the results.
I get emails to go through when I run a test in the SQL Agent properties & in the job I have scheduled. This would make me think all of my email configuration is ok. Any idea why this new xp_smtp_sendmail would not be working?
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply