The Remote Procedure Call Failed

I have a named instance of SQL Server where I had to do an edition upgrade from SQL Express to Standard edition. A while later I notice that the SQL Agent is not running. I go to SQL Server Configuration Manager to start the Agent. First I need to change the start mode to either manual or automatic. However, I get the error "The remote procedure call failed." I try changing the log on account to something else but I am still unable to start the Agent. I search and Google and finally find this little weird tip. Do this in the service mmc! Normally you do not want to make any changes to SQL Service related items there but for some strange reason that was the solution for me as well.

Setting up SQL Server to email you when a SQL job fails

In this post I will list all the steps needed to be completed in order to have SQL Server send email notifications when a job fails.

Step 1.
Enable Database Mail unless it has already been done. Make sure you have a profile set up. I will not go into details on this step as it's pretty straight-forward. Make sure your test email will go through though. In our case our STMP server only accepts email messages from specific servers so if I set up database mail on a new SQL Server then I will have to notify our server admin to add that server to the white list.

Step 2.
Create an Operator. This item is found under SQL Server Agent in SSMS.
This step is pretty straight-forward as well. You need to do is name the operator and specify an email address for it. At my current job we do not have a large group of database admins so I only have one operator with a distribution group email address that goes to exactly 2 people.

Step 3.
Enable mail profile for SQL Server Agent. This can be done in the Properties of SQL Server Agent, Alert System section. Check the Enable mail profile item, select Database Mail as the Mail system and select the Mail profile that was created in Step 1. In this section you may enable the a fail-safe operator as well by selecting the Operator you created in Step 2.

Step 4.
Restart SQL Server Agent! This bites me all the time. You do everything but this step and you wonder why it does not work. Because you forgot to restart the Agent!

Step 5.
Edit the properties of the SQL Job for which you want to set up email notifications. In the Notifications section check E-mail and specify the Operator you set up in Step 2. You obviously want the notification to be sent out when the job fails. However, in some unique scenarios you also want notifications when the succeeds or simply just completes. I usually also check the Write to the Windows Application event log as well. Emails can get deleted. You need to make of an effort to get rid of an event log. Save the SQL Job and you are done!

Mind-boggling Problem!

I created an SSIS package that reads a comma delimited (CVS) file from a network share, loads it into a SQL Server table and then moves the file to a folder called Completed. Simple, right? After fighting my battle with the "truncate" error that you could see in my previous post the package runs fine. So it's time to set it up as a SQL job.

I set up the job, run it and it says success but there is no data in the database and the file has not been moved to the Completed folder.

I added logging to the package so that everything will be logged onError, onInformation, and onWarning to the Windows Event log. (You can do that by going to the SSIS>Logging menu item.)

I see that I have a Warning in the event log that the job could not find any files. I check on the permissions and I see that everything is set up right. Both the share permissions and the Security tab shows Modify permissions for the group that the SQL Agent user is in.

I spend all morning with a co-worker of mine (thanks Dennis for your time!) trying all kinds of crazy workarounds and solutions. And what we found that worked is mind-boggling!

If we change the permissions on the share to give Modify rights to the SQL Agent user itself (not just the group it's in) everything works!!!!

Can someone please explain to me why?????

Failed to notify 'operator' via email.

I had to shuffle some SQL Servers around along with SQL Jobs. Our SMTP server has changed as well so I had to run through all SQL Jobs to make sure notifications are properly set. As I'm testing some notifications I realize that e-mails are not going through. What can be wrong?

• Database mail enabled.
• Test e-mail goes through.
• Operator set up correctly.
• Notification is set for job completion so it should go through no matter what.

However, in the History of the SQL Job, there is the following note: Failed to notify 'operator' via email.

I vaguely remember this error from the last time I was setting up a new SQL Server but I could not find my notes as to what the fix was. So I do my usual search and I find the solution.

Solution: Go to SQL Agent Properies>Alert System and enable the mail profile and restart SQL Agent.