Skip to main content

Posts

Showing posts from September, 2015

Email Alerts are not received...

In one of application, Email alerts are sent using SQL sp_send_dbmail stored procedure. Email alerts were working properly, but suddenly noticed email alerts are not received.

To troubleshoot the issue, first I checked the msdb.dbo.sysmail_event_log table using the below query.

SELECT log_id, event_type, log_date, description, process_id, mailitem_id, account_id, last_mod_date, last_mod_user
FROM
msdb.dbo.sysmail_event_log 
ORDER BY
log_date DESC

If there is any error, you should see records with the event_type value as error.
In my case there are records with event_type value as error, and the description value for those records is same as below

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2018-02-01T10:41:27). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: Authentication required).  )

As per the error descript…

How to prevent first time cube access performance issue, after a cube refresh with Dynamic Security...

Recently we faced an issue that, after a cube refresh there is a performance issue while accessing the cube. This happens since the dynamic security was implemented.
This performance issue can be prevented by building cache for each user.

SSIS package was implemented to build the cache. In the package the approach used was to execute the create cache mdx command using each user id.
You can connect to the SSAS cube with different users by using the EffectiveUserName property in the Connection String.

The structure of the package is as shown below



Steps:

Create four variables as shown below. 




OLAP_ConnString is the value used as the connection string for SSAS cube. It includes the user name by using the EffectiveUserName property.UserNames variable is used to get the list of users. Each user is assigned to UserName variable in the Foreach Loop Container.



Execute SQL Task us used with in the Foreach Loop Container use to create the cache using the MDX command. Since Effective User Name pro…