March 30, 2009 at 2:50 pm
I have a trigger that is executing the stored procedure sp_SQLSMTPMail. The trigger work fine from within SQL server 2005, but when the VB.net 2005 front-end inserts a record which initiates the trigger; the trigger fails. What could be causing the trigger to fail?
Thanks
March 30, 2009 at 3:22 pm
Hi Leland
First please surround your error in VB with a try-catch block and copy this to the catch:
' ...
Catch ex As Exception
Dim msg As String = Nothing
Dim stackTrace As String = ex.StackTrace
While Not ex Is Nothing
If String.IsNullOrEmpty(msg) Then
msg = String.Format("Exception: {0}", ex.Message)
Else
msg = String.Format("{0}{1}------------{2}Inner Exception: {3}", msg, vbCrLf, ex.Message)
End If
ex = ex.InnerException
End While
msg = String.Format("{0}{1}STACK TRACE:{2}{3}", msg, vbCrLf, vbCrLf, stackTrace)
MsgBox(msg)
End Try
' ...
Press ctrl+c when you see the message box and copy the output into a post.
Greets
Flo
March 31, 2009 at 7:11 am
What is the user being used to connect using the VB app? Does the user have permissions to execute sp_SQLSMTPMail?
I typically recommend against sending email from a trigger, unless you are using sp_send_dbmail which is asynchronous. If you have to send mail using another method, I recommend creating a mail queue table and use a job to send the mail.
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
April 2, 2009 at 6:21 am
Thanks for the responses...
I was able to resolve the issue. I checked the event log and found out that the username I was under could not execute the stored procedure sp_OACreate. After I granted the permissions on the username, the trigger worked properly.
I ran the following script to grant the permissions:
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OACreate] TO [YOURDOMAIN\YourUser]
GO
April 2, 2009 at 6:29 am
If you are really using SQL Server 2005, I strongly suggest you move to using sp_send_dbmail. There are just way too many things that can go wrong when using the sp_OA% procedures to risk using them in a trigger. Remember the trigger happens WITHIN a transaction so if the trigger fails, the whole transaction fails. Is this result you really want, if the problem is that you can't contact the mail server or a dll has been changed or deleted?
I suggest that you read this article[/url] and the accompanying discussion.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply