Skip to main content

Posts

Showing posts from 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
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…

Programmatically Set Audience to a SharePoint 2010 WebPart

To provide permission to a sharepoint web part for specific SharePoint Group, we have to use AudienceManager as shown below
SPWeb currentSite = SPContext.Current.Site.RootWeb; SPSite spSite = currentSite.Site;
SPServiceContext sc = SPServiceContext.GetContext(spSite);
AudienceManager am = new AudienceManager(sc);
Microsoft.SharePoint.WebPartPages.SPLimitedWebPartManager mgr = null; mgr = currentSite.GetLimitedWebPartManager("SitePages" + "/" + page, System.Web.UI.WebControls.WebParts.PersonalizationScope.Shared); foreach (System.Web.UI.WebControls.WebParts.WebPart webPart in mgr.WebParts) { if (webPart.Title == "WebPartTitle") { //Get the Existing Audience for the Web Part string audWebPart = webPart.AuthorizationFilter; //If there is no Audience for the Web Part just add it if (audWebPar…

Retrieve SharePoint 2010 Permission Levels to a ASP.NET DropDownList

Below code shows how we can get the SharePoint permission levels and assign it to a ASP.NET DropDownList (drpPermissions)
drpPermissions.AppendDataBoundItems = true;
SPWeb currentSite = SPContext.Current.Site.RootWeb;

SPRoleDefinitionCollection permissionColl = currentSite.RoleDefinitions;
foreach (SPRoleDefinition permission in permissionColl) {
drpPermissions.Items.Add(permission.Name.ToString()); drpPermissions.DataBind(); }

Issue with SharePoint Designer 2010...

While working with dashboard pages, a problem was identified.First a dashboard page was published to a SharePoint 2010 site using PerformancePoint. That page includes a SQL Server Report and a PerformancePoint Filter was applied for it. After publishing it to the SharePoint Site, the SharePoint page was opened using SharePoint Designer 2010. Then some modifications for the layout of the page was done and saved it. But when I opened the page in the browser and tried to view the report, that PerformancePoint Filter was not applied for the report.
The issue is when we save the page using SharePoint Designer, the mapping between the report and the filter was lost. To apply the filter we have to manually do the mapping.

Hosting ASP.Net Site in IIS in Windows Server 2008 R2

I am posting this post since I had some problems while hosting a site in IIS in Windows Server 2008 R2.

To add a web site in IIS, right click on Sites in the IIS Manager and select Add Web Site as shown in the below figure.




Then you will get the Add Web Site window as shown below and enter the required details.




Physical Path should be the folder which contains the web site files and it should locate inside the C:\inetpub\wwwroot\wss\VirtualDirectories folder.

Then click Ok to complete it.
But if you try to browse the site, it will give you an error.

To avoid it and browse the web site you need to do two things.
First one is, add the IIS_IUSRS and allow full permission. To do it right click on the site and select Edit Permission as shown in the below figure.



Then go to the Security tab in the new window and add the IIS_IUSRS user and allow Full Control. Then click Ok.

Still you cannot view the page because of an authorization error.

To avoid it you need to do the second thing.
Select the site and…

Increase Virtual Hard Disk Size in Hyper-V

In some situations we may need to increase the virtual hard disk size.
We can increase the size of the disk by using Edit Disk under Actions in the Hyper-V Manager.

Then you will get a window as shown below.



Select Expand from it and click Next.




Then add the New Size as shown below and click Next.




Then you will get a Summary as shown below and click Finish to complete it.




But you cannot use that increased space straight away. When you start the Virtual Machine, previous disk size will be avilable. To add the increased capacity, go to the Disk Management window by right clicking Computer and by selecting Manage. Then right click on the partition and select Extend Volume as shown below.




Then you will get the Welcome screen as shown below and click Next.




Then you will get the Extend Volume Wizard. Give the amount to be increased as shown below and click Next.





After that you will get the final screen as shown below.



Click Finish to add that disk space.

Connect to IBM DB2 Database Using Toad

We can use Toad to explore the DB2 Database Structures. Before using Toad we need to configure IBM DB2. To configure it open the Configuration Assistant as shown in the below figure.

Then you will get a window as shown below. Select TCP/IP as the Protocol and click Next.

After that we have to provide the details of the Host and the Service.

Insert those information and click on Retrieve button to get the Port Number.

Then follow the instructions as shown in below figures.

Then launch the Toad for DB2 as shown in the below figure.

After that create a new connection right click on the Connection Manager and select Create as in the below figure.

Then you will get a window as shown below.

You can see the available databases in the Database alias.

Then provide login details and the category.

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

#Error in SQL Server Report...

When we run a SQL Server report, it gives an #Error for text field which computes the summation.
In that report we get the values from an SQL Server Analysis Service Cube and get the summation of those values considering the group.
In the Data warehouse we used the data type for the taken field as money.

Finally I was able to find the reason for the error occurs during the summation.
In the cube, we assign the value 0 for the field if it is null. Problem arises due to it.
When we change that to 0.00 instead of 0, it works.
I don't know any exact reason why it happens like that. It may be due to some problems while converting to the data type it may convert it to different data type like int and it gives errors while summation.
Anyway I think this will help you as well...

Add Row Number to a SQL Report contains Groups

We can add row number to a report which has groups by adding below expression to Row Group of the Row Number column

= RunningValue(Fields!CountColumn.Value, CountDistinct, "MyDatasetName")

To add Row Number to the Details Group, select the field of the Row Number in the Details Group and add the below expression.

= RowNumber(Nothing)


Create PerformancePoint Filter and Set a Default Value for the Filter

We can create a Filter for a PerformancePoint page easily. When you open the Dashboard page in PerformancePoint Dashboard Designer, you can see the Filters tab. Just click on it and you can see Filters there. Then click on New Filter as shown in the below figure.



Follow the wizard by providing a name and the data source, then you will get a screen to select members for the filter as shown in the below figure.



In the shown figure Filter members are already selected. But you can set the default value for the filter while selecting the filter members. To do it, just select the member value and right click on it. Then you will get a screen as shown below.



Select Set as Default Selection as shown in the above figure and after that you will get a screen as below showing the default selection.



Then just follow the other steps to add the filter...

Redirect to a Different page from a Web Part Zone in SharePoint

Recently I had a problem with redirect to an another SharePoint page.
The problem was when I try to click on a Report publish from the PerformancePoint, it opens the redirected page in the same zone, not in the same window.
To overcome that I used a javascript. The approach I used was, have .aspx page which contains the javascript to redirect to the requested url and call that .aspx by passing a parameter when the report is clicked. The aspx page is located in the Root of the PPSMonitoring site. You can find the path of the Root folder by right clicking on the PPSMonitoring site in IIS and select Explore as shown below.



Then add the redirect.aspx page (according to my sample) which contains the below code.





What this javascript do is it gets the parameter passed by the clicked page and set the redirected url. Then it set the parent page url as the redirecting page url. The reason to set the parent url is that when the request comes from a Zone, it consider the Window as the Zone. Therefore…

Warnings while Configuring PerformancePoint Server 2007 in Windows Server 2008 R2

While configuring PerformancePoint Server 2007 in Windows Server 2008 R2, I got two warnings even though those patches for the SQL Server were installed.
Those warning are
SQL Server Native Client 9.0 (SP2) is missingSQL ADOMD.NET 9.0 (SP2) missingThose patches were installed using the Feature Pack for Microsoft SQL Server 2005 - February 2007 release in the http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en link.

Intalled patches are
sqlncli_x64.msiSQLServer2005_ADOMD_x64.msiSQLServer2005_ASOLEDB9_x64.msiSQLServer2005_XMO_x64.msiI got warning for the sqlncli_x64.msi and SQLServer2005_ADOMD_x64.msi.

I was able to resolve that issue by installing the sqlncli_x64.msi and SQLServer2005_ADOMD_x64.msi from the Feature Pack for Microsoft SQL Server 2005 - December 2008 release in the http://www.microsoft.com/downloads/details.aspx?FamilyID=536fd7d5-013f-49bc-9fc7-77dede4bb075&displaylang=en link.

It is better to install others from…

Enabling Java scripts and Ajax in IE for Windows Server 2008 R2

I had a problem while run the PerformancePoint Server 2007 Dashboard Designer using Internet Explorer in Windows Server 2008 R2. That is the Run button and the Go button are not visible for the Dashboard Designer page.
Also I had some problems for the SharePoint Server 2007 in the same OS and with the same browser. In sharepoint, those javascripts were not working.

Those errors occurs due to the security settings provided by the OS.
To solve those issues go to the Server Manager.
Then as shown in the below figure, you can find Configure IE ESC under the Security Information.



Click on it and select Off form those selections as shown in the below figure.



Then click Ok and check your sites.
It will works...

Data Cleansing with SSIS

Data cleansing is the process of detecting and correcting (or removing) corrupt or inaccurate records from a record set, table or database. For an example correcting misspelled records from a given record set.

Below diagram shows when data cleansing is required.



To avoid those situations and to have consistent, relevant and accurate data, data cleansing is required.

Stages of the data cleansing process is as shown below.



As shown in the above figure, before data cleansing, Data Quality Testing need to be done.
After that data cleansing is done by parsing, data transformation, duplicate elimination and by doing some statistical analysis.
The final output of the data cleansing process will be accurate, consistent and relevant data.

SQL Server Integration Services(SSIS) is providing the facility to implement data cleansing processes.
There are some components which can be used to perform data cleansing operations provided by SSIS.
They are
LookupFuzzy LookupFuzzy Grouping
Below diagram shows how …

SQL Server 2008 Installation Failed....

While trying to install SQL Server 2008 in Windows XP, I got a error in "System Configuration Check"
The error is Rule "Performance counter registry hive consistency check" failed. The performance counter registry hive is corrupted.
The problem occurred since the installation trying to read the above register value and does a calculation on Last Counter and Last Help.
We can solve this issue by changing the those Last Counter and Last Help values

Type regedit in the Run and press Enter. It will open Registry Editor window.




Open the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009.
Then you will get a screen as shown in below figure.



Double click on Counter record to get a window as shown below.



Then scroll to the second last line and you will see a number like 3422 as shown in the below figure and remember it.


This is the Last Counter number that the system uses.

Then do the same thing for the Help record too and it is the Last Help number that the…