January 16, 2011 at 4:23 pm
I created a trigger on a DB that fires when a new record is inserted.
The main goal is to send an email with information regarding data inserted.
The code i wrote works fine if i run it manually.
The same code when is run from the trigger generates on the aplication side the error "Operation is not allowed when the object is closed".
The aplication is not mine. I cant change anything in it...
Is there anything wrong with my code?
Thanks In Advance
-------------------------------------------------------------------------
CREATE TRIGGER NewOrder ON LinhasDoc AFTER INSERT AS
DECLARE @NomeCliente varchar(50)
DECLARE @CodCliente varchar(10)
DECLARE @TipoDoc VarChar (5)
Declare @IdCabecDoc VarChar (100)
Set @IdCabecDoc = (Select Top 1 IdCabecDoc From inserted)
SET @CodCliente = (SELECT Entidade FROM CabecDoc Where Id = @IdCabecDoc)
SET @NomeCliente = (SELECT Nome FROM CabecDoc Where Id = @IdCabecDoc)
Set @TipoDoc = (Select TipoDoc From CabecDoc Where Id = @IdCabecDoc)
IF @TipoDoc = 'ECL'
BEGIN
DECLARE @msg varchar(500)
Declare @sql VarChar(500)
Set @sql = 'Select Left(Artigo,15) as Artigo, Left(Descricao, 40) As Descricao,
Quantidade, PrecUnit From PRIDEMO.DBO.LinhasDoc With (NOLOCK) Where IdCabecDoc = "' + @idcabecDoc + '" Order By NumLinha'
Set @msg = 'Order Inserted for "' + @NomeCliente + '"' + CHAR(10)+char(13) + CHAR(10)+char(13)
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'xxxxxx@yyyyyy.com',
@body= @msg,
@subject = 'New Order Inserted',
@profile_name = 'EMailTest',
@query = @sql,
@attach_query_result_as_file = 0,
END
GO
January 16, 2011 at 4:34 pm
Just a hunch, try using SET NOCOUNT ON in the trigger.
January 16, 2011 at 4:55 pm
Ninja's_RGR'us (1/16/2011)
Just a hunch, try using SET NOCOUNT ON in the trigger.
Hello!
Already tried that... 🙁 Placed the SET NOCOUNT ON in the beginning of the code and SET NOCOUNT OFF just before the END statement.
Thanks
January 16, 2011 at 5:01 pm
Have you givien us the complete error message you get from the application or just a summary of what it says.
January 16, 2011 at 5:17 pm
Lynn Pettis (1/16/2011)
Have you givien us the complete error message you get from the application or just a summary of what it says.
Hi!
It's the full message. However I had to translate the original message because I'm from Portugal.
But I think the translation is acurate.
Thanks
January 16, 2011 at 5:37 pm
Hi Jota,
I don't see exactly what would cause that error, but I do have to ask why this trigger is on that table?
That table seems like it might hold order lines and could contain multiple lines per order - so you could be attempting to send multiple emails - depending on how the application inserts order lines.
It is possible that the sending of one or more emails is taking too long for the application and it is not handling that correctly...?
Have you tried using SQL profiler to analyse what is happening when the application tries to insert Order Lines with your trigger in place?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 16, 2011 at 7:09 pm
Well here's to plan B. Sending mails from triggers is a bad idea anyways.
Make a log table that saves the history of the processed orders.
Then create a job that scans for "unprocessed" orders. Send all the e-mails and mark processed as each mail is done.
Schedule the job to run every few minutes or horus depending on how live you need those e-mails. The app will go back to being fine.
January 17, 2011 at 12:25 am
Ninja's_RGR'us (1/16/2011)
Well here's to plan B. Sending mails from triggers is a bad idea anyways.Make a log table that saves the history of the processed orders.
Then create a job that scans for "unprocessed" orders. Send all the e-mails and mark processed as each mail is done.
Schedule the job to run every few minutes or horus depending on how live you need those e-mails. The app will go back to being fine.
Absolutely right - I should have said it myself....
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 17, 2011 at 3:08 am
mister.magoo (1/16/2011)
Hi Jota,I don't see exactly what would cause that error, but I do have to ask why this trigger is on that table?
That table seems like it might hold order lines and could contain multiple lines per order - so you could be attempting to send multiple emails - depending on how the application inserts order lines.
It is possible that the sending of one or more emails is taking too long for the application and it is not handling that correctly...?
Have you tried using SQL profiler to analyse what is happening when the application tries to insert Order Lines with your trigger in place?
Hello MM
On the mail I need data from 2 tables. The "lines" table and the "headers" table. First I tried to put the trigger no the headers table. The problem e that when the trigger is fired the data in the lines table is not yet saved, so the select returns allways empty... So I tried the other way arround.
Only one mail is beeing sent because after the trigger is fired the mail is sent based on data from the headers table...
I didnt use the SQL Profiler because I'm not familiar with it. I will look into it.
Thank you!
January 17, 2011 at 3:11 am
Ninja's_RGR'us (1/16/2011)
Well here's to plan B. Sending mails from triggers is a bad idea anyways.Make a log table that saves the history of the processed orders.
Then create a job that scans for "unprocessed" orders. Send all the e-mails and mark processed as each mail is done.
Schedule the job to run every few minutes or horus depending on how live you need those e-mails. The app will go back to being fine.
Hello Ninja!
That is also my plan B 🙂 I prefered the "plan A" because it seamed easier and requeired less control of the data...
Can you explain because why sendind mails from a trigger is a bad idea?
Thank you!
January 17, 2011 at 5:38 am
The transaction has to wait for something external to the application before completing. It's like waiting for the mailman to pick up the mail before taking the next breath.
Also with a queue system if something goes wrong, you can always start where you left off. If you cause a rollback of the transaction, you're pretty much screwed. Especially if you can't change the 3rd party app.
January 17, 2011 at 7:35 am
Ninja's_RGR'us (1/17/2011)
The transaction has to wait for something external to the application before completing. It's like waiting for the mailman to pick up the mail before taking the next breath.Also with a queue system if something goes wrong, you can always start where you left off. If you cause a rollback of the transaction, you're pretty much screwed. Especially if you can't change the 3rd party app.
So, what you are saying is that the SQL transaction waits until the mail is sent? I always thought that a new thread was created and the processes ran separately...
The rollback part I understand and agree completely.
By the way you were correct about the fact that SQL is trying to send several mails 🙁
I guess I'm going to go for the plan B 🙂
January 17, 2011 at 7:38 am
Yes I think part of it is done async. However you're still starting a process outside the transaction which is outside the control of sql server db engine... never a good thing.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply