Skip to main content

Posts

Showing posts from 2012

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 sub string a string value using a character value in SSRS

In a report you may want to get a part of a string value by considering a character or characters.
Such a situation we have to use Mid and InStr functions.

Assume that the Description filed value contains "/".
If we want to get the string before that "/", we can use the below expression in the report text box field.

1: Mid(Fields!Description.Value,1,InStr(Fields!Description.Value,"/")-1)
InStr function provides at which location that the "/' character exists.
You can get the last part after the "/" character by using the below expression

1: Mid(Fields!Description.Value,InStr(Fields!Description.Value,"/")+1,Len(Fields!Description.Value))

How to avoid data is moving to the next page in SSRS even though the space is available in the previous page...

Recently I faced an issue that in a SSRS table, data in a particular row is moving to the next page if the content with in that row is not fit enough to the available space.

We can overcome this issue by setting the KeepTogether property of the Row Cell to False.
Select the particular Row Cell and go to the Properties window. In that you can find the KeepTogether property. Default the value is set to True, change it to False.
That is it. Just run the report and you will see the content is moved to that free space and additional content is  moved to the next page.

Split first word from a field in SQL table

We can use SUBSTRING and CHARINDEX functions to split the first word from a value in SQL.
As an example, if there is a field as FullName in UserInfo table and if we want to get the first word of it, then we can use the below query to get it.

1:SELECT FullName , SUBSTRING( FullName ,0,CHARINDEX(' ',Name+' ',0)) AS FirstName 2:FROM dbo.UserInfo
In the CHARINDEX function I added ' ' to it, because otherwise it will return null if the FullName value only has a single word.

SSIS Row Count Component...

Recently I used the Row Count component in SSIS toolbox. I used it in a Data Flow to set the row count of a Lookup match output. When I tried to get that set value in a Conditional Split component in the same Data Flow, I found that the variable has the default value and the row count was not set for that variable.

But when I get the variable value using Script Task, I found that the variable has the row count.
Actually the issue is the row count value is available after the completion of the Data Flow task. We cannot access the variable inside the same data flow task which sets the row count value.

Due to that I have to use two Data Flow tasks. In the first data flow task I set the row count value to a variable using Row Count component and in the other data flow task, the variable value is checked and do the required processing.

Replace double quotes in SSIS...

Recently I had a requirement to load csv file data which is generate from FoxPro.
But when I checked the csv file, found that it has additional double quotation mark at the start and the end of the value. As an example if the actual branch value is Branch1, the csv file has that as "Branch1".

Therefore to get the actual value I had to remove those double quotation mark and able to do it using REPLACE function.

I added a Derived Column component to the package and in that replace the branch column value after removing double quotes. For that I used the below expression.
1: LTRIM(RTRIM(REPLACE([branch],"\"","")))
That is it, It will give you the actual branch value correctly as Branch1.

How to show the All Member hierarchy value in a SSRS Filter...

For some filters we may need to select all the values at once. As an example if the SSRS parameter is a single valued one and user should be able to filter all the values if needed, then we need to provide All member value in the filter as well. We can achieve this easily by retrieving the all member value to the data set of the filter.

For an example we can write the query for the Branch data set, which loads the all the branches available in the [Sales-Branch] hierarchy as

1:WITH MEMBER [Measures].[ParameterCaption] AS [Sales-Branch].CURRENTMEMBER.MEMBER_CAPTION 2: MEMBER [Measures].[ParameterValue] AS [Sales-Branch].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Sales-Branch].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , {[Sales-Branch].[All],[Sales-Branch].[Branch].MEMBERS} ONROWSFROM [Sales]
In that you can see the ParameterLevel for the top record as 0 and indicates …

Configuring SSRS Report Subscription using a Remote SMTP Server...

In one of my previous post, I mentioned how to Configure SSRS Report Subscription using Gmail as the SMTP Server.
But in some situations we may need to configure it using Remote SMTP Server. It is bit easier than the previous one.
Open the Reporting Services Configuration Manager and connect to the reporting service instance. Then go to the E-mail Settings. In that provide the appropriate Sender Address and SMTP Server values.
That is it.
If you check the rsreportserver.config file, you can see that the sender address and smtp server values are in the file. Also you can see that the SendUsing value is set to 2, indicating that the smtp server is a remote server.

Filtering Dimension Members having the same name in a MDX Query...

Sometimes different dimension members having different key values may have the same name. This kind of situations mainly occurs when we set a default value to those unknown dimension member values. As an example if we set the value as "Unknown" for the items in the sales records, which do not match our existing items, then the key value for those items in the cube will be different but the name value will be same.

But if we try to filter those "Unknown" member values using the query designer, it will only filter the selected members. Otherwise we have to select all the members having the name value as Unknown, to filter those. But if there are large number of members having the same name, then it will be difficult and not feasible to select all those.

We can filter those in the MDX query using INSTR function. The query will looks as below.

1:SELECT NON EMPTY { [Measures].[Sales] } ON COLUMNS, NON EMPTY { ( 2: [Items—Items_Vendor_Item].[Item Name].ALLMEMBERS ) …

SSRS table border double is not working...

I wanted to add double line for the bottom of the each row in a SSRS table. But when I set the Border Style for the Bottom of the Row as Double and preview the report, I was unable to see the double line at the bottom of any row.

Actually the reason was even though it was applied, it was not visible. To fix it just change the Border Style for the Top to None while keeping the Border Style for the Bottom as Double. Then increase the Border Width for the Bottom as 2pt. Then try to preview the report. I was able to get the double line by setting the border width as 2pt.
But if still you are not getting it, try to increase the border width for the bottom as 2.5pt or 3pt. Then you should be able to see the double line at the bottom of each row.

How to make SSRS Indicator as fix size...

If you just add the Indicator in SSRS 2008 R2 or 2010 to a cell in a table and try to preview the report, you will see that the Indicator will be stretched if the row has multiline records.

To fix this what you have to do is just add a Rectangle to the cell and after that add the Indicator.
Then try to preview the report and you will see that even though the row has more than single line, Indicator size is fit to the height of a single line and it is not stretched.

Use of UrlRoot property in SQL Report Server Config file...

In SQL Report Subscription, you may want to send the report with the link and you can do it by selecting Include Link value. But some times you will get the link include the computer name and not the domain name. Due to that the given is not working if you try to it from outside.


This happens since you have not set the value for the UrlRoot property in the rsreportserver.config file.
To fix it, open the rsreportserver.config file in a text editor and find the UrlRoot property.
Set the value for it as the report server URL with the domain name as below.
https://server.domain.com/Reports
That is it and you will get the link with the given domain name.

Allow E-mail address to be changed for the Users having Browser permission in SSRS Subscriptions...

Normally Users having Browser permission cannot changed the e-mail address (To: field) in the report subscription page. Default the user name is applied as the value for the To: field.
To allow this to be modifiable by users having Browser permission, you need to modify the rsreportserver.config file.

Open the rsreportserver.config in a text editor.
Then set the SendEmailToUserAlias to False and Save the rsreportserver.config file.

That is it.
Now just try to create a New Subscription and you will see there is no value in the To: field and it is editable.

Configuring SSRS Report Subscription using Gmail as the SMTP Server...

Recently I was trying to configure SSRS Report subscription. I checked some links in the MSDN and by following those steps I was unable to configure it. The reason was I used the SMTP server as smtp.gmail.com since we are not having a local smtp server. To use the smtp.gmail.com as the smtp server we need to follow some additional steps other than the steps mentioned here.

The first step is we need to configure a Virtual SMTP server.
First make sure that the SMTP Feature is installed on your server, if not installed it.
Then use IIS Manager to configure the virtual SMTP server. I am using Windows Server 2008 and it has both the IIS 6 and IIS 7.
To configure virtual SMTP server we have to use IIS 6.
Open the IIS 6.0 Manager and you will get a screen as below.


In that if the SMTP Virtual Server is not started, then start it. Then right click on it and select Properties.
You will get a screen as given below.


In the General tab select the IP Address value and unmarked the Limit Number of…

How to get the installed SQL Server details...

We can get the installed SQL Server details such as SQL Server Edition, Product Version and Product Level using the below query. Just open a query window using SQL Server Management Studio and run it.

1:SELECT SERVERPROPERTY ('edition') AS SQLEdition,SERVERPROPERTY ('productversion') AS ProductVersion, SERVERPROPERTY ('productlevel') AS ProductLevel

Passing Parameter value for a rdlc report in ASP.Net and Windows Forms......

In most of the times we need to filter report data. Recently I had a requirement to create a rdlc report and add it to an ASP.Net page. In that I need to filter data using the name.

In ASP.Net and Windows forms, creating data set and reports parts are same. The different is passing the parameter value from the code.

In ASP.Net, steps used are

Create a Data Set by right click on the project and select Add → New Item Then select Data → DataSet When the DataSet is added to the solution, right click on the DataSet Designer and select Add→TableAdapter. In that you need to provide the query to retrieve data and in this example I assume it as SELECT Name, Address, ContactNo FROM Users WHERE (Name LIKE @name + '%')@name is the parameter which we need to pass.Then add a new report and select the DataSet as the one which we created earlier.Then add a ReportViewer component and select the Report as the one created earlier. These steps are common for the Windows Forms as well. But next steps…

Get Week Numbers for Quarter and Month in SQL...

I needed to get the Week Number for the Month and the Quarter for a BI Application.
I was able to retrieve it using the below query.

SELECT DATEDIFF(WEEK, DATEADD(MONTH, DATEDIFF(MONTH, 0, '2012-03-17'), 0), '2012-03-17') +1 AS WeekNoMonth,DATEDIFF(WEEK, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, '2012-03-17'), 0), '2012-03-17') +1 AS WeekNoQuarter

The results are WeekNoMonth=3 and WeekNoQuarter=11

Mid function is not working in SSRS...

I tried to sub string SSRS data set field to have new calculated field. But when I see the output I did not get the desired one and original string value was there. Also I did not get any error message too.
The issue was with the Start Position. I provided the start position as 0. But the correct start position should be greater than 0. When I changed it to 1, I got the correct output. The correct formula is Mid("StringVal",1,6). It gives the first six characters of the provided string.

Error in PerformancePoint 2010...

While trying to create a new data source in PerformancePoint 2010 Dashboard Designer, I got an error message as This data source cannot be used because performancepoint services is not configured correctly. The error message is shown in the below figure. I got it after providing the server and while trying to expand the available databases.




This is because that the PerformancePoint is not configured correctly. While configuring PerformancePoint, we need to configure Unattended Service Account. The error occurred due to that account is not configured correctly.
To fix that issue we need to re-configure that unattended service account correctly.
To configure unattended service account, you need a Secure Store Service Key.

Steps to fix the above issue are
To generate a secure store service key, go to SharePoint 2010 Central Administration →Application Management → Manage Service Applications → Secure Store ServiceThen click on Generate New KeyOnce that is done go to the SharePoint 2010 C…

An error occurred during local report processing...

I modified an existing report by deleting some dataset  fields and adding new ones. Then in the report I removed the deleted fields from each reference and added the required new ones. But when I tried to preview the report, I got the report processing error as
The Hidden expression for the grouping "Group1"  refers to the field "Field1". Report item expressions can only refer to the fields within the current data scope or, if inside an aggregate, the specified dataset scope...

I checked the report and already the reference for Filed1 is deleted from all the locations. But finally I was able find the reason. Error was in the Static Hidden Groups.
In some of the those hidden static groups, Field1 is used. To view those groups, select the Tablix and click on the Down arrow in the bottom right hand corner as shown in the below figure and select Advanced Mode.



Then you will those Static groups as in the above image. In that check the relevant static groups for the grou…

[Connection manager "Excel Connection Manager"] Error: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered...

While trying to execute a package in 64 bit SSIS, I got the below error
[Connection manager "Excel Connection Manager"] Error: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

To fix it just right click on the SSIS project in the solution explorer and click Properties. Then click on the Debugging of the Configuration Properties. You will find Run64BitRuntime under Debug Options. Set it to False and execute the package. You will not get the above error anymore.

SSIS Toolbox in SQL 2012...

When we open a Integration Service Project created using SQL 2008, all the required components are available in the Toolbox tab. I tried to upgrade this SSIS package to use the SQL 2012 environment. Upgrading process was successfully completed and I was able to open the package. But the Toolbox tab is empty and could not find any component as in the BIDS 2008. Those components are in the SSIS Toolbox tab. Click SSIS -> SSIS Toolbox to view it.

Unspecified Error in Microsoft Access Data Engine...

While trying to access Excel 2007 file using SSIS Excel Source component, I got an error in the Microsoft Access Data Engine as Unspecified Error. I was using SQL Server Business Development Studio 2008. I got this error trying to get the Sheets in the excel file after creating the connection.

I tried to find the reason and the solution for this issue and able to find a helpful blog.
http://ddkonline.blogspot.com/2010/08/ssis-2008-unspecified-error-and-could.html

According to that post the this is related to the driver issue. After following that post I was able to fix is by re-installing the driver in the below link.
http://www.microsoft.com/downloads/details.aspx?FamilyID=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

After the installation completes, the issue will be fixed. If you already opened the Business Development Studio project, just close it and re-open it.

How to show column headers on all pages in SSRS Report

If a report contains several pages it will be useful to have column headers on each page. In tablix properties we have options as Repeat header columns on each page as shown in below figure.

But even though we put a tick on that property, sometimes it is not applied and and column header are not repeated in other pages. This is really confusing and I found useful link in the internet.
https://connect.microsoft.com/SQLServer/feedback/details/337720/katmai-reporting-services-2008-tablix-control-repeat-column-headers-does-not-work
It mentions the work around as well the reasons that may cause for it.

Work around is Select the Tablix that you want to repeat column headers for by clicking on it.At the bottom of the screen, find the "Row Groups" and "Column Groups" section.Click the small drop-down-arrow on the right side of that section. In the "Row Groups" section, locate the top-outermost "static" row and click on it. In the properties pane, you need …

Extracting QuickBooks data using SSIS Package...

To extract data from QuickBooks, I used QODBC driver.
You can download a trial version of it using below linkhttp://qodbc.com/default.htm
When we installed the QODBC driver, if ODBC entry is not available for it, we can add it as System Data Sources. For that go to the Control Panel --> Administrative Tools -->Data Sources(ODBC)
Then you will get the ODBC Data Source Administrator window. In that click on the System DSN and you will get a screen as below
In that click on the add button and select the QODBC Driver for QuickBooks as shown in the below figure.
Then you have to configure the ODBC connection. Initial set up window is as given below.
If you want to set a different company profile, click on the Browse button and select the qbw file as shown in the below figure.
Then you can use the created ODBC connection in SSIS package.
Create a new Integration Service Project and add a Data source. In the New Data Source window, select the Provider asOdbc Data Provider as in the below figu…

Passing two Multi-Value report parameters to a MDX Query...

When we write a MDX Query to filter data, we may want to filter the data set using two multi value report parameters. As an example we may want to get the Sales value for multiple Branches and for multiple Sales Persons.
In such kind of a situation the MDX Query will be as below.
SELECT NON EMPTY { [Measures].[Sales]} ON COLUMNS, NON EMPTY { ([DimBranch].[Branch].ALLMEMBERS * [DimEmployee].[SalesPerson].ALLMEMBERS) } ON ROWS FROM (SELECT ( STRTOSET ( '{" & Join(Parameters!Branch.Value,",") & "}', CONSTRAINED) ) ON COLUMNS FROM (SELECT ( STRTOSET ( '{" & Join(Parameters!SalesPerson.Value,",") & "}', CONSTRAINED) ) ON COLUMNS FROM [Sales]))
Branch and SalesPerson are the Multi-Valued report parameters and Sales is the Cube.
Also when you set the Value field for those parameters, set it as the field taken from considering UNIQUENAME. Otherwise if you have the parameter value as a string which has spaces, then it will gi…

Record Count in SSAS Fact Table

We may need to take the record count for a fact table values in SSAS cube. As an example we may need to take the item count for a category. For that we do not need to write any MDX queries or create any Calculated Members.
If you just added the fact table by using New Measure Group, you can see a measure as FactTableNameCount, which was not in the data warehouse. We can use this measure to take the item count for the above example.
But sometimes when we create the fact table, initially we may not add that measure and later we may want it. Then we can add it to the relevant fact table by right clicking the fact table and selecting New Measure.In the New Measure window default the Usage value is Sum. Click the down arrow and select Count of rows as shown in below figure.

Then click Ok and FactTableNameCount measure will be added. Use that measure to get the record count.

FROM clause have the same exposed names...

When I tried to get the matching item records from two databases, I got the below error.
The objects "Database1.dbo.Sales" and "Databse2.dbo.Sales" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
The query used was SELECT DISTINCT Database1.dbo.Sales.ItemName FROM Database1.dbo.Sales INNER JOIN Databse2.dbo.Sales ON Databse1.dbo.Sales.Id=Databse2.dbo.Sales.Id

It is because that the table name is same in those two databases. To avoid it we have use aliases.
The query is shown below
SELECT DISTINCT Table1.ItemName FROM Databse1.dbo.Sales AS Table1 INNER JOIN Databse2.dbo.Sales ON Table1.Id=Databse2.dbo.Sales.Id
Databse1.dbo.Sales AS Table1 will fix the error.

Cube File is Corrupted...

While trying to view SSRS reports, which load the SSAS cube data I got the below error message.
File system error: The following file is corrupted: Physical file:\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Sales.0.db\Customer.0.dim\5.English Education.(All).sstore. Logical file . Errors in the metadata manager. An error occurred when loading the Customer dimension, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Sales.0.db\Customer.5.dim.xml'. Errors in the metadata manager. An error occurred when loading the ASales cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Sales.0.db\Adventure Works DW.9.cub.xml'.
The reason for this error message is the the cube file in the file system is corrupted. You can't this issue by just redeploying the cube. When you try to deploy the cube, you will get the same error message and deployment process will get failed.
You can fix the above issue by following bel…

How to Change the Start Up xaml file in a Silverlight Application

To change the Start Up xaml file for a Silverlight Application, open the App.xaml.cs file.In that you will find the below code
private void Application_Startup(object sender, StartupEventArgs e) { this.RootVisual = new MainPage(); }
Change the "MainPage" to your new xaml file.
Run the test page and you will see the content of your new page...