Archive for the ‘SQL Jobs’ Category

I set this ETL SQL job to run as a service account which has read and write access to the relational database and warehouse. But it failed to run with the above error. This error is very misleading because the log file does exist. The name and the location of the log file are valid. How on earth it became invalid?

So I thought probably the issue with the permission to this file. Adding the service account to have read and write access to the whole ETL folder resolves this issue.


Read Full Post »

I set up my ETL process to run every hour daily and when it fails, I get an email notification. However, when the ETL failed, I did not get any emails at all. So I went in there and check what went wrong this time. SQL Server Agent log returns with “An attempt was made to send an email when no email session has been established”.  This is a bit strange as I still get emails notification when ETL process has warnings/errors. So I am completely certain that the email works.

My database mail has been configured properly  because the test email from the database mail has been successful.  Google returned with a few blogs that say there is a need to set up an “operator” for the SQL Server Agent. So I went in there and created a new operator, gave it my name and my email address. Next, right click on the SQL Server Agent, then select “Alert System”. Tick “Enable mail profile. Choose the appropriate Mail System (I chose Database Mail because SQL Mail will become obsolete in the future) and Mail profile (i.e. this is the profile name that I created in the Database Mail). I have also ticked “enable fail-safe operator” to myself and notify using Email.

The last step that I had to then was to check my SQL job setting. Under the “Notifications” section, I ticked the “Email” option so that when the SQL job fails, email to myself. This time I could select my name (i.e. operator)  from the drop down list.

OK..all set. Ran my SQL job for the ETL process that I know would fail. I still didn’t get any emails… hmmm..

Did a bit of research on Google and the clue for this is I have to restart the SQL Server Agent! So I restarted the SQL Server Agent and ran the ETL process again. I finally got the email notification when the SQL job/ETL process failed. Yay 🙂

Read Full Post »

%d bloggers like this: