Skip to main content


Showing posts from May, 2010

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
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…

Load Data using Change Data Capture (CDC)...

What is Change Data Capture (CDC)
Change Data Capture is a feature of the Database Engine which is available only in SQL Server 2008 Enterprise, Developer, and Evaluation Editions. CDC feature captures insert, update and delete operations applied to SQL Server tables and provides the details of the changes available in an easily consumed, relational format. CDC uses it's own tables to store data related to tracking tables with the same columns as in the tracking table and some additional columns to store important meta data.
CDC with SQL Server Integration Services (SSIS)
There may be a situation where we will need to store the data loaded from ETL Package to another table. That means in a situation where we maintain two tables to keep the same data. In such a situation we can use CDC. Using CDC, we can load data to one table where CDC is enabled and execute a Stored Procedure to load data to the other table by capturing changed data using CDC.
Enabling CDC
Before enabling CDC for a tab…

How to Combine Two Fact Tables in SSAS...

In SQL Server Analysis Services Cube, there may be a situation where we need to combine two related fact tables. This kind of a situation may occur due to the same fact having different dimensions. For an example if we consider two dimensions as Branch and Region, in some areas Sales may happen just only in Regions and sometimes it may go to the level of Branch.
In such a scenario in the Data warehouse we need to have two different tables for those two type of sales like dbo.FactSalesRegion and dbo.FactSalesBranch. But for analysis purposes we should have all the sales as one. Due to that we need to create one Fact by combining those two fact tables in the data warehouse.
We can do it in the Cube by creating a new fact table using Named Query. Open the Data Source View by double clicking it in the Solution Explorer. Then right click on the design area and then select New Named Query. Then provide a name for the combined fact table like FactSales and enter the SQL Query to get the union o…

Some KPI Values and Indicators are not shown in a PerformancePoint Scorecard...

While creating some scorecards for BI application, I faced a strange problem. That is, in that scorecard values and the indicators are not shown for some KPIs and others are ok.
The approach I used to create the scorecard is to Import SQL Server Analysis KPIs and after that add some KPIs from PerformancePoint. But the values and the indicators for the KPIs which are created by the PerformancePoint were not shown as shown in the below figure. PPSKPI is the KPI created in PerformancePoint.

I checked the procedure to create that scorecard and KPIs, and found it is ok. Finally I was able to find the reason for that issue. It is a very simple reason.
The reason is that the column names of those two KPI types are different.

For the KPIs which were imported from Analysis Services, those column names are Value and Goal and Status. KPIs which were created from PerformancePoint, those column names are Actual and Target.
When those column names were changed to Value and Goal and Status, the problem w…

Setting Up BI Server for Application Developments

Before start development for a Business Intelligence (BI) application, we need to have a proper environment. For that we should need to have a server which has the Windows Server version. For this post I'm using 64-bit Windows Server 2008 R2 installed server.

Server should has
.Net Framework 3.5Internet Information Services (IIS)Microsoft SQL Server 2008Microsoft Office SharePoint Server 2007PerformancePoint Server 2007
In the server we should configure it to install .Net Framework 3.5 and Internet Information Services (IIS).
To install IIS, go to the Server Manger and then select Add Roles as shown in the below figure.

Then you will get the Add Role Wizard as shown below

Click Next and then you will get the window to select Server Roles.
Put tick in the check box of Web Server (IIS) to install IIS.
Then click Install to install the selected roles.

After that Install the Microsoft SQL Server 2008. Then install the SQL Server 2008 Service Pack 1.
Then Install the Microsoft Office SharePoint …

Saving Changes is not permitted for a SQL Table...

When you try to save a change to a table in the SQL Server Databse, sometimes you will get a error message saying "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created..." as shown in the below figure.Due to that you will not be able to change database tables. You can solve that issue with just a one click. You can find Tools -> Options from SQL Server Management Studio and you will get a Window as shown below. Select Design and under that you can find there is a option saying Prevent saving changes that require table re-creation is selected as shown in the below figures.Just remove the mark of it and try to save your changes. You should be able to save it unless there are no other issues....