Dundas Dashboard – My observations

Dundas Data Visualization Inc, is well-known to SSRS developers. Microsoft had earlier acquired their SSRS controls (Charts, Gauges & Map) and integrated them into SSRS 2008 & R2. These controls adds rich visualization to SSRS reports.

However one of the feature that SSRS miss is the real “Dashboard”. Dundas seized this opportunity and have released a Dashboard. It is Silverlight based ASP.NET application. It has a lot of features like Mashup, ability create dashboard on OLTP & OLAP data and many more. This link can give you more info on these feature.

I am using this tool for couple of months now and thought of writing down my observations

(1) Easy to learn…. Every thing is controlled by configuring properties. Most of the time your effort will be spent in understanding each properties and its impact on the visualization.

(2) Support is good. Got immediate responses.

(3) Since this tools is pretty new (2 to 3 years in the market) and the user base is less (compared to SSRS),  apart from Dundas articles you would not find much material in the NET.

(4) Like SSRS subscription, Dundas Dashboard has a scheduler. However it does not support any other format other than image

(5) You cannot change the data source dynamically. The environment I work is a distributed one with hundreds of database server. SSRS provides the ability to change the connection strings dynamically . However this is not feasible in Dundas by default. You may have to go for a custom data provider to achieve this.

(6) Troubleshooting is bit difficult. When error occurs, it throws the whole .NET error stack. Most of the time, it does not pinpoint which section or object error out.

Posted in Uncategorized | 4 Comments

SQL SERVER connectivity error: SQL Server does not exist or access denied

Recently I was troubleshooting a connectivity issue in a development environment from an application. The application was using the connection string as

“Provider=sqloledb;Data Source=myserver\instance1,1433;Network=DBMSSOCN;Initial Catalog=mydb;Integrated Security=SSPI;”

I was able to connect to the SQL SERVER from SSMS, however when connecting from an application it was throwing the error

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

This is very generic one and can happen due to many reasons like

(1) Account may not have access or password might be wrong
(2) Connection might be blocked by a firewall
(3) Since it is a named instance, the port might be different. In some cases it might be dynamic also, for which the SQL Server Browser service should be up and running.

Looking at the connection string, everything looked good except for the “Network”. I had no idea of what it is. Googling led me to this KB article from Microsoft which clearly explains what it is.

Basically when an application is trying to connect to SQL SERVER, if one protocol fails then it tries to connect with another protocol. Example, assuming TCP/IP is enabled as primary and Named Pipes as secondary. When an application tries to connect to SQL SERVER, it first tries with TCP/IP. If that did not succeed, it tries with Named Pipes. If that also fails, then only it throws the error.

However, we can force the application to use only one protocol and that is done by the “Network” keyword in the connection string. This article defines all the options available for “Network”.

In my case the option “DBMSSOCN” corresponds to TCP/IP and it was disabled in the development machine. Enabling it, using this article, fixed the issue.

Posted in SQL SERVER | 5 Comments

Review of my blog posts in 2011

The WordPress.com stats helper monkeys prepared a 2011 annual report for this blog.

Here’s an excerpt:

The concert hall at the Syndey Opera House holds 2,700 people. This blog was viewed about 26,000 times in 2011. If it were a concert at Sydney Opera House, it would take about 10 sold-out performances for that many people to see it.

Click here to see the complete report.

Posted in Uncategorized | Leave a comment

Data Driven Subscription failed with timeout error

One fine day, one of our data driven subscription which was running fine, failed with timeout error.

library!WindowsService_118!eb4!12/20/2011-07:30:01:: i INFO: Handling data-driven subscription 1b850434-fe69-47e5-a227-219d1b2e040c to report /External Reports/Report1, owner: domain\user, delivery extension: Report Server Email.
library!WindowsService_118!1288!12/20/2011-07:30:01:: i INFO: Initializing EnableExecutionLogging to ‘True’ as specified in Server system properties.
library!WindowsService_118!1288!12/20/2011-07:30:01:: i INFO: Initializing EnableExecutionLogging to ‘True’ as specified in Server system properties.
library!WindowsService_118!eb4!12/20/2011-07:30:37:: e ERROR: Error processing data driven subscription 1b850434-fe69-47e5-a227-219d1b2e040c: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

The report was scheduled to run at 1:00 am GMT. The error log does not say which one timed out, whether the data driven subscription query or the actual report query or the report server itself. When we returned to office (around 4:00 am GMT), we ran the data driven subscription query and it completed within few seconds. We ran the report on-demand and it worked it fine.

Also noted that the Execution Log table did not even have an entry. Normally the execution log should have an entry whether the report completed  successfully or not. This made me to think that the report failed even before execution… probably at the initiation itself. But was not sure whether execution of data driven query is part of execution or initiation.

Went through the error log line by line but still could not get any clue. Finally I decided to involve my DBA to troubleshoot using profiler. And the culprit was data driven query, it was timing out during that specific time. We adjusted the run time little bit and it worked fine.

Posted in Uncategorized | 1 Comment

Silly issues with SSRS

Recently I was troubleshooting a report issue where in one of the column was empty in the report. I started by checking the report body to ensure that the corresponding column was called appropriately, then looked into the dataset fields to see whether there was any mapping issue. Both of them looked good.

I thought the dataset query might be the culprit, returning empty value for that column. But running the query in SSMS returned correct values in the column. After half-an-hour or so, I accidentally stumbled on the reason.

When I clicked on the column name in the dataset fields list I could see an extra space.

See the gap between the “d” and the cursor. That was the culprit.

In another instance, I had an issue with providing default value for the parameter whose data type was float. I gave 1234567890, but it had considered it as 1234568000.

BIDS converted this into float (as below)

When I printed this parameter, I got the value as 1234568000 not my original value. However when I changed the default value into expression, it worked fine. i.e. as below

Posted in SSRS | 3 Comments

Displaying blank sub report

Recently I had to convert a stand alone report into a sub-report. That report was very simple… it had two date parameters , a data set displayed in a tablix,  NoRowsMessage was enabled on the tablix to display a message when the data set returns empty row.

This report feature, NoRowsMessage worked fine when it was a stand-alone report. However when I added this report as a sub-report in another report, the dataset’s NoRowsMessage did not appear when the dataset was empty. I thought this might be due to caching and I tried different options, like building the report, deleting the .DATA file , closing the project, closing the BIDS itself. But nothing worked.

I removed the NoRowsMessage on the tablix,  added a text box and added an expression in the Visible property in both tablix and textbox to toggle the visibility based on the record count in dataset. Even with this approach, the Textbox was not visible when the dataset was empty. However when I ran it stand-alone, it worked as expected.

After spending more than couple of hours, I came across this post which says that if all the datasets in the sub-report returns zero rows then the sub-report will not be displayed. The solution to display no row message in this case was very simple 🙂

(1) Create a dummy data set in the sub report to return at least 1 row OR

(2) Set the NoRowsMessage at the sub-report level (in the main report)

Posted in SSRS | 4 Comments

This field is missing from the returned result set from the data source.

This warning

The dataset ‘dataset1’ contains a definition for the Field ‘column1’. This field is missing from the returned result set from the data source.

is very common error and reason behind it is really simple. The report object is referring to a field which is not available in the dataset. Most of the time the developer might be referring to a wrong field name due to spelling mistake.

So when one of my colleague told me that he is facing this error, I asked him to check whether the column name in the dataset and the report object is matching. He came back saying it does match. Then I asked him to check whether the dataset query is returning the correct column. “Yes” was the reply.

I could not understand how this was possible. So I asked him to send me the RDL. I ran the report in BIDS and I too faced the same issue. I checked the field name in the dataset columns, dataset query and the report object and everything was matching.

This dataset was populated by a mix of static T-SQL statements and dynamic T-SQL statements. Thought there might be some condition in which the field was not populated. So I copied the dataset statements and ran it in SSMS passing the same set of parameters used in BIDS. Everything looked perfect. It was fetching the referred column.

Then I traced the report run (from BIDS) thru Profiler. Took the command and ran it in SSMS. Still it brought the required field. But BIDS was throwing the same warning.

After spending more than couple of hours, I could figure out the cause. When tracing thru Profiler, I observed that the time taken to execute the query was considerably less compared to running it in SSMS. That gave me a hint that some part of the query might not be running.

I reviewed the dataset commands once again and found that above the block where the required field was populated, there was a single row comment detailing the purpose of the block. I just removed the single row comment (whole line) the warning went off.

My understanding is that for same reason the BIDS was considering the comment line and the below block as one single row and did not execute the block which resulted in the field being missed 🙂

Posted in SSRS | 3 Comments