Activity Notification Question

[clicker666]

Here's a new one. Similar to my last thread, but different. I've made a trigger to tell me when the system is full. Most of the time it works, but when the system is full and a user attempts to login they get an error message on saving to one of the stored procedures related to activity. (Didn't note the name sorry, and I won't put the trigger back in until it's clean)

Also, I've looked around for the syntax for the trigger, and I just can't seem to make it run without the very last line. Anyone know what I am doing wrong in terms of syntax?

CREATE TRIGGER SystemFull ON [DYNAMICS].[dbo].[ACTIVITY] 
FOR INSERT, UPDATE, DELETE
AS

DECLARE @RECORDS AS INT
DECLARE @r AS INT

SET @RECORDS = (SELECT COUNT (*) FROM [DYNAMICS].[DBO].[ACTIVITY])

IF @RECORDS = 10 
            EXEC @r = master.dbo.xp_smtp_sendmail
                   @FROM = N'administrator@mycompany.com',
                   @TO = N'me@mycompany.com',
                   @subject = N'Great Plains System Alert - Almost Full',
                   @message = N'There are currently 10 users in the system.',
                   @type = N'text/html',
                   @server = N'127.0.0.1',
                   @dumpmsg = N'C:\MyEmailLog1.log'
ELSE
            IF @RECORDS = 11
            EXEC @r = master.dbo.xp_smtp_sendmail
                   @FROM = N'administrator@mycompany.com',
                   @TO = N'me@mycompany.com',
                   @subject = N'Great Plains System Alert - Full',
                   @message = N'There are currently 11 users in the system.',
                   @type = N'text/html',
                   @server = N'127.0.0.1',
                   @dumpmsg = N'C:\MyEmailLog1.log'
ELSE
            set @r = 0

 

[Borvik]

Hey clicker666!

First I can't see anything wrong with syntax, as you only need the BEGIN..END statements if your using multiple commands within the IF.

Second - you don't need to set @r = 0 if you don't use it. As I see it you're using @r to store the return code for xp_smtp_sendmail - only if your checking later if sending failed would you need to do that.

I would drop the "set @r = 0" and that last ELSE statement. That way you just have a statement that looks like this:

IF <condition>
   Do this
ELSE
   IF <condition>
      Do this

Any other condition should just exit the trigger.

 

[clicker666]

Ahhhh.... I was trying to leave the ELSE at the end, that's why I added the @r=0, seemed to work when I did that LOL!

This is the error I'm getting when the 11th user tries to log in....

"A save operation on SY_CURRENT_ACTIVITY failed accessing SQL Data"

I can't even find the SY_CURRENT_ACTIVITY table or stored procedure!

I noticed with my other trigger that I get a similar error. Is there something I am missing here?

 

[Borvik]

The Great Plains naming system is so messed up. The error messages the program gives specify either the Display Name or the Technical Name. Here it is giving you the Technical Name. SY_CURRENT_ACTIVITY "translates" to DYNAMICS.ACTIVITY so its the table you have the trigger on.

Are you receiving the Alert - Full email message?

 

[clicker666]

Nope, just the error and no login. It's not consistent, sometimes it works, sometimes it doesn't.

 

[Borvik]

Hmm...

Well not being consistent makes it harder to analyze, but apparently this works:

SET @RECORDS = (SELECT COUNT (*) FROM [DYNAMICS].[DBO].[ACTIVITY])

Just to see what it does, what happens when you change the above to:

SELECT @RECORDS=COUNT(*) FROM [DYNAMICS].[ACTIVITY]

 

[clicker666]

This actually seems similar to a problem I'm having with my SOP notification. Works great in the test company, but works only sometimes in the live company. Same SQL server. I can't quite put my finger on why a trigger fires in one, but not the other, and why it fires consistently in the test company but not the live one.

 

[Borvik]

Like you said in the other thread... it could be a timing issue. I've have a timing error on my e-mail trigger as well. But then again, my trigger is much larger too, and once I gutted it to bare minimum it got rid of my error. Now to see how far I can push it.

Timing on your server, might be a different issue than length, as your triggers are fairly short compared to the one I have. Is your mail server 127.0.0.1 when referenced from the trigger? If so, processing could be taken up there on both sides (trigger sending, smtp receiving), thus causing a timing error.

I'm not 100% sure on that, but it's something to play with.

 

[clicker666]

Changing to:

SELECT @RECORDS=COUNT(*) FROM [DYNAMICS].[ACTIVITY]

Killed it pretty quick. Couldn't find the table.

The trigger is on a server that has SMTP. I'll try sending it to the Exchange server (another box) and see if that resolves it.