August 5, 2008 at 2:11 pm
You nailed it. Thank you!
The turkey who published this gave the minimum parameters, but left off the server name: http://sqldev.net/xp/xpsmtp.htm#Installation
(I hope none of you were that turkey. If so I take it back)
I don't think I am out of the woods yet, but making progress.
August 5, 2008 at 2:18 pm
Actually it was under "more comprehensive example". Plus, you're supplied with the entire lsit of parms for the stored proc in the article. Sometimes the simplest solutions are the easiest missed. Making progress is always a good thing!
-- You can't be late until you show up.
August 5, 2008 at 2:36 pm
OK. Here is my current script. How do I not incorporate my query from the accounting db to get my 90 day invoices.
The email goes through until I attempted to incorporate the @query line. I got this syntax from xp_sendmail, not xp_smtop_sendmail. Am I close? It runs without error, but no email.
declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'sqlserver@mycomp.com',
@FROM_NAME = N'SQL Server',
@server = N'smtp.everestkc.net',
@TO = N'austins@mycomp.com',
@subject = N'90 Day Invoice Notification',
@message = N'One of the below emails is currently at 90 days',
@type = N'text/plain',
@query = N'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'
select RC = @rc
go
August 5, 2008 at 2:43 pm
I think (and it's been awhile since I've used it and I don't have any samples here) you need to run the query, sending the results to a text file and then "attaching" it to your email. I'm going to load the stored proc and fool around with it when I can but no gaurantees that it'll be soon!
-- You can't be late until you show up.
August 5, 2008 at 3:39 pm
You guys are almost done with me. I believe I have it working with one exception.
I created 2 steps
Step - 1 (create/output to text file)
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
Step - 2 (email text file)
declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'sqlserver@mycomp.com',
@FROM_NAME = N'SQL Server',
@server = N'smtp.everestkc.net',
@TO = N'austins@mycomp.com',
@subject = N'90 Day Invoice Notification',
@message = N'One of the attached invoices is currently at 90 days',
@type = N'text/plain',
@attachments= N'c:\temp\90day.txt'
select RC = @rc
go
-------------
I hope this is my final request. How can I send only if there are records selected? I will be setting this to only send on the day an invoice is at 90days. If there are no invoices at 90 days old then I don't want an email to go out. Can we do an if-then type of statement?
August 5, 2008 at 9:24 pm
You could do an @@ROWCOUNT at the end of the first step.
If @@ROWCOUNT = 0
BEGIN
Return 1
END
Else
BEGIN
REturn 0
END
Which I think will cause the step to end in failure and you can quit the job on failure and go to next step on success.
Sorry for jumping out of the thread I was out starting at 4 est.
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 6, 2008 at 7:50 am
I need to sort out somethat that I am probably taking too literally. Here is my step 1 with the addition of your script. Does your script go in exactly like you said? Is it seperated by a comma, semi-colon or other? Or do I need to insert my script in the middle of yours. If you can't tell I don't do this everyday.
Here is what I have been trying, but get a "A RETURN statement with a return value cannot be used in this context."
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
;
If @@ROWCOUNT = 0
BEGIN
Return 1
END
Else
BEGIN
REturn 0
END
August 6, 2008 at 8:47 am
That was just an idea I threw out there without having the opportunity to test it. Are you putting the select directly in a job step?
If so you CAN do this to force failure:
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('No Rows', 16,1)
END
It may be that the best solution is to wrap the entire thing in a stored procedure that you call from a job. Then you would do the select, check the rowcount and, if the rowcount > 0, call the email procedure else don't call the email procedure.
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 6, 2008 at 10:19 am
I am sure I can figure out how to do a stored procedure, but it is not something that comes naturally to me.
Yes, I put the entire script I posted above, (the select and the rowcount in the same first step of the job).
I can do simple sql scripts, but am having a hard time with the syntax on this. The below is the logic I think it needs to take place, but I don't know the syntax
IF @@ROWCOUNT of (my select script)=0
then end\failure (job quits on failure and no email is sent)
else (the row count is greater than 0)
run (my select script) (with the results output to the text file)
end\success (proceed to job step # 2 for processing of the email)
If you are saying a stored proc is the only or best way to handle I will pursue that route. I have too much invested in this project to give up now. Thanks again.
August 6, 2008 at 10:30 am
The following is code from one of our jobs that looks for a name change and send an email if appropriate. Look at the logic, and see if you can use it in your process.
DECLARE @days int
SET @days = -7
SET NOCOUNT ON
--NAME CHANGES
INSERT INTO ProductionSupport.dbo.NameChanges
EXECUTE DWSQL1.PEEPS.dbo.dp_PeopleNameChange_Parameter @days
if exists (select
*
from
ProductionSupport.dbo.NameChanges P
INNER JOIN PSOPRDEFN O
ON P.PSemplid = O.EMPLID
WHERE
O.OPRID NOT LIKE '1%' AND O.OPRID NOT LIKE '2%'
AND O.SYMBOLICID <> 'archive')
exec msdb..sp_send_dbmail
@recipients = 'user1@mycompany.com','user2@mycompany.com', -- took out the actual email addresses used here.
@subject = 'H88PRD - Name Changes',
@body = 'This is message is automatically generated, DO NOT reply to sender. The attached data is confidential, if you are not intended recepient, please delete.',
@attach_query_result_as_file = 1,
@execute_query_database = 'H90PRD',
@query = 'select ''NAMECHANGES'' as ChangeType, O.EMPLID, P.chgdt, P.oldname, P.newname, O.OPRID
from ProductionSupport.dbo.NameChanges P
INNER JOIN PSOPRDEFN O
ON P.PSemplid = O.EMPLID
WHERE O.OPRID NOT LIKE ''1%'' AND O.OPRID NOT LIKE ''2%''
AND O.SYMBOLICID <> ''archive'''
TRUNCATE TABLE ProductionSupport.dbo.NameChanges
😎
August 6, 2008 at 1:22 pm
I really, really appreciate everyones help on this. It is amazing how many of you and how much you are willing to help.
I am still stuck on this and really can't afford to spend anymore time on it. I would have to think this project is 90% or more complete. Are any of you for hire? Is that against the rules to ask? Call me offline if you are interested. 913-764-6229, ask for Austin.
Viewing 12 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply