SOP email notification? 1

[clicker666]

I can't imagine I'm the only person who ever needed this. I've tried to create a business alert that would notify a user when an order is saved. Unfortunately the best I could come up with was one that every minute sent all the open orders. Does anyone have the definition for this business alert lying around?

TIA

 

[clicker666]

Bizarre.

Using xp_smtp_sendmail (which by the way only takes a second to install and has no impact on the other sendmail), the code works correctly. If you take the mail portion out, and substitute it for the old xp_sendmail, no good.

In any event, another nice piece of work.

I'm actually hoping this will get rid of the problem I was having when trying to go live. I will change and retest.

 

[clicker666]

Update - I still get the save operation on my SOP notification when using it with the live database. I'm assuming it's something with our custom process locks system (VBA) that's interfering. I'll talk with the developer and see if they have some insight.

Works great in the test company though.

 

[jazgeek]

I do not have any custom programming on our installation, it still gives the error listed above.




-----------
and they wonder why they call it Great Pains!

jaz

 

[Borvik]

hmmm....

Interesting - I have it working just fine on our machine. I'm not sure what to make of it.

Maybe someone else out there has some ideas? Anyone?

 

[clicker666]

Like I said above, it's really weird. It does not work with the built in SQL mail. But with the add on one listed it works fine. I'm at a loss to see why, Profiler provided no insight at all. I would guess, and it's only a guess, that the table (or record) might be locked during the whole process, and as a result the normal manipulation is being interfered with.

I tried an AFTER UPDATE as opposed to UPDATE, that didn't resolve it. I will look at it again with Profiler, but I don't see anything else being affected that could make a good trigger spot. Did you happen to notice that if you select Remove All holds that you don't get an error, OR an email? Hmmm....

I have to say, this is turning into a nice thread LOL!

 

[Borvik]

Hmmm....

Well, the xp_sendmail uses Outlook email profiles. Do you have that setup? And if so, have you tested xp_sendmail by itself, without all the conditions? When I first set tried setting this up, I tried xp_sendmail and it took a bit to get it going. I'll take a look at some of my documentation when I get back in the office to see if there is something your missing in the setup for xp_sendmail.

 

[jazgeek]

we use xp_sendmail with no difficulty on scheduled jobs.

-----------
and they wonder why they call it Great Pains!

jaz

 

[Borvik]

Well, there is only one last thing I can think of Jazgeek.

When using xp_sendmail try removing the @DBUSE line.

I'm not sure what will happen, but if I'm right it should default to the current db that the trigger is running from.

Other than that, I'm not sure what could be causing the error.

 

[stef315]

When I did my xp_sendmail I had to give execute privledge to the DYNGRP role.

 

[clicker666]

Now I'm baffled. The trigger works great in the test company, on the same SQL server, but will not work in the live company. I keep getting save operation errors. We're putting it down to timing issues, possibly. The code has been modified a bit to account for only reporting on volumes over $5000.00. We went as far as to take out some custom code we had in the system, just to rule out interactions between the two.

CREATE TRIGGER NEW_SOP
ON SOP10100
AFTER UPDATE

AS

SET NOCOUNT ON

DECLARE

@r AS int,
@SOPNUMBE varchar(21),
@SUBJECTTEXT as varchar(50),
@MESSAGETEXT as varchar(100),
@ATTACHMENTNAME as varchar(50),
@VOLUME as numeric (19,5)

SET @SOPNUMBE = (SELECT  SOPNUMBE FROM INSERTED)
SET @VOLUME = (SELECT SUBTOTAL FROM INSERTED)
SET @SUBJECTTEXT = 'Order Notification: SOP ' + rtrim(@SOPNUMBE)
SET @ATTACHMENTNAME = rtrim('SOP' + @SOPNUMBE) + '.CSV'
SET @MESSAGETEXT= 'SOP Number: ' + rtrim(@SOPNUMBE) + ' was just created'

IF @VOLUME > 5000
	BEGIN
		EXEC @r = master.dbo.xp_smtp_sendmail
       		@FROM = N'administrator@mycompany.com',
	       	@TO = N'me@mycompany.com',
       		@subject = @SUBJECTTEXT,
	      	@message = @MESSAGETEXT,
       		@type = N'text/html',
	       	@server = N'127.0.0.1',
       		@dumpmsg = N'C:\MyEmailLog1.log'
	END

 

[stef315]

I did get save operation errors until I granted EXEC privledge. The save couldn't happen because the trigger was not allowed to fire.

 

[clicker666]

Silly question here, where do I find the EXEC privelage on a trigger? EXEC isn't an option under the table itself, although it is under the stored procedure section. (But the trigger isn't there either, that I can find.)

 

[stef315]

The xp_sendmail is in the master database under Extended Stored Procedures. There is a Permissions button there. I think I gave the Public role EXEC priviledge.

 

[clicker666]

Ahhh...

In any event I temporarily got around it using a rather convoluted method.

1. Small trigger to update another table I created, firing from SOP10100
2. Every 5 minutes poll the second table, via an exec'ing SP.
3. Send an email for each record.
4. Delete each record from the second table once sent.
5. Continue the cycle.

Since the record insertion is so fast it doesn't appear to have any effect on SOP10100. Real-time would be nicer, but really isn't necessary, just so long as it is at a reasonable time interval. I think 5 minutes is sufficient.

I appreciate everyone's help on this one. I'm going to try the right's thing, but as it stands this works OK. I *have* learned a lot in this thread, and am going to keep working on triggers.

 

[jazgeek]

OK!! I got it to work properly

I had to add an and statement excluding 2 users as it turned out that any time ANY hold was removed it would fire off the trigger. Also had to add a "where" statement for the ID that was being removed. I suppose I could have moved it down into the IF Statement, but this works too.

Now, I'm lost on the next part.

the email now sends off saying that a certain job has gone to production (also made a reverse one that fires off if the hold is put back on)... what I'd like to do now is to add the details of:

Salesperson ID
Currency ID and Document amount
requested ship date

as specified on the SOP10100 table.


I don't know what the syntax would be on my select statement, can anyone help?

----------------------------------------------------

CREATE TRIGGER PURCH_REL
ON SOP10104
FOR UPDATE
AS

DECLARE @r as int
DECLARE @JOB varchar(21)
DECLARE @VALUE tinyint
DECLARE @SOPNUMBE varchar(21)
DECLARE @SUBJECTTEXT as varchar(50)
DECLARE @MESSAGETEXT as varchar(100)
DECLARE @ATTACHMENTNAME as varchar(50)

SELECT @SOPNUMBE=SOPNUMBE FROM Inserted
SET @MESSAGETEXT= 'SOP Number: ' + rtrim(@SOPNUMBE) + ' was released to production.'
SET @SUBJECTTEXT = 'Order Notification: SOP ' + rtrim(@SOPNUMBE) + '.'
SET @ATTACHMENTNAME = rtrim('SOP' + @SOPNUMBE) + '.CSV'

SELECT @VALUE = DELETE1 from Inserted where PRCHLDID = 'PURCHASING' and (USERID = 'sa' or USERID = 'production')

IF @VALUE = 1
BEGIN
EXEC master.dbo.xp_sendmail
@recipients = 'customerservice',
@subject = @SUBJECTTEXT,
@message = @MESSAGETEXT,
@width = 600

END









-----------
and they wonder why they call it Great Pains!

jaz