-1

I am trying to setup the SQL server to send automatically an email every time a new record is inserted in one table. I have setup the Database Mail and it is working perfectly when I run the app locally from Visual Studio. But at soon as I publish it to the company IIS server, it fails and worse any new record are not even inserted in the table.

What could make it work on the local IIS express and not on the company IIS server? I went to IIS Manager and I haven't found any settings regarding SMTP with a SQL server..

Here is the code for the trigger which again works but only locally. Thanks.

CREATE TRIGGER dbo.trigger1
ON  dbo.******
AFTER INSERT
AS 

BEGIN

DECLARE @bodyHtml AS VARCHAR(100)

SET @bodyHtml = 'http:***************'+ CAST (( SELECT MAX(Number) FROM *****) AS nvarchar(max))    

EXEC msdb.dbo.sp_send_dbmail  
        @profile_name = '**********',  
        @recipients = '**********',  
        @subject = '***********',
        @body = @bodyHtml,
        @body_format = 'HTML';
        
END
GO
Julien7377
  • 475
  • 4
  • 15
  • Have you gone through the Database Mail troubleshooting guide here: https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail-general-troubleshooting?view=sql-server-ver15#:~:text=General%20database%20mail%20troubleshooting%20steps%201%20Permissions.%20You,5%20Retry%20mail%20delivery.%20...%206%20See%20also ? And if the trigger is failing, the rows won't be inserted. But you should see an error in the application. – David Browne - Microsoft Nov 18 '21 at 18:23

1 Answers1

0

Thank you to David Brown for pointing me to the right direction. I opened the link and the first word I saw was "Permission" ..

I feel stupid... On local host I was connecting to the SQL server with my credentials.. The IIS server uses different credentials to connect to the SQL server, adding the server role sysadmin to the IIS server fixed the issue..

Julien7377
  • 475
  • 4
  • 15