SSRS export to Excel converts DateTime Column to Text Column

Recently we observed a weird issue with SSRS export to Excel. One of the datetime column in the report was formatted using the FORMAT function as “MM-dd-yyyy HH:mm:ss” (24 hrs format). However when this report was exported to Excel, this column was converted as Text column instead of DateTime.

In the below screenshots, the first one depicts the report and the second one is the Excel output of the same.

Note the highlighted in red, the DateTime column is treated as General (Text) not as DateTime, this removes the ability to pivot or apply some formula on that column.

After couple of hours I came across an article (unfortunately forgot the URL 😦 ) which said that using FORMAT function removes the actual data type of the column while exporting. Instead it suggested to use FORMAT property (as in below screenshot)

That trick worked. Below screenshot is from the Excel export of the report where the formatting was done using the Property and it behaved as expected.

 

 

 

Mirrored Database as SSRS Data Source

Recently one of our report started to fail with error like Unable to Connect to Database error. Then only we came to know that the said database is in a mirror setup. In the mirrored environment, we have to always connect to Principal, Mirrored database will be inaccessible to the applications.  When the failover happens, Principal becomes Mirror and vice versa. And this flip can happen automatically also.

So our reports should be intelligent enough to identify which one is Principal. If it tries to access the Mirror, then it will error out. However SSRS spares us from this headache. The only thing we need to tell SSRS is which servers form Principal/Mirror pair. And this information has to be mentioned in the connection string itself like below

Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=DB1

Thats it. When failover happens SSRS will do a retry, if required, and connect to the appropriate Principal Server.

SSRS: FORMAT option for milliseconds

Some of our reports have hyperlink enabled on few metrics which points to an internal website. Recently there was a change in the internal website which required us to send the a particular Date as a parameter. The page was expecting the milliseconds also.

This looked simple as we thought of using FORMAT function, but we could not find the option to include milliseconds in FORMAT function. We spent a couple of hours going thru different blogs but most of them stopped with seconds.

Finally in one of the forum, in an unrelated post, we came across the format for milliseconds.  And it is nothing but “f” 🙂

=format(now(),”MMM/dd/yyyy hh:mm:ss:fff”)

returns

SQL SERVER Replication – Troubleshooting Steps for Beginners

Few days back there was a replication issue in one of the QA servers. Since the DBA was out of office that day, I was called in to look into the issue. Though I am not an expert in replication, I was able to identify the issue. Just wanted to share the troubleshooting steps that I performed. This is for beginners only. If you are an experienced DBA with Replication in your arsenal, please review and correct, if anything is interpreted wrongly.

Replication has 3 components, Publisher , Subscriber and Distributor. In simple term Publisher is the source of data, Subscriber is the target (one which receives the data) and Distributor is the facilitator. You can read about the Replication terminology here.

My QA was able to give me the publisher server name. So  I had to

(a) find out the Distributor and Subscriber server.

(b) find out what are all replicated

(c) find out where the error occurred, in Publisher or Distributor or Subscriber

I logged into the Publisher to figure out the servers of Distributor and Subscriber. In the SSMS you can  find a separate folder for Replication

Right clicking on the Replication folder brought up the below property screen, which tells where the Distributor server is located. Note that Distributor may be on the Publisher itself or in another SQL SERVER.

Clicking on the “Publication Databases” displays what are all databases published from this server. Also if you note carefully, it gives you the type of Replication also. It is not necessary that all the tables or rows or columns are published in a single publication.  We can set up  multiple replication. We have to identify in which replication (publication) our objects are published. You can get this by expanding the “Local Publication” under Replication folder in SSMS.

The “Articles” lets you know the objects that are published under the specific publication.

The other property that got my attention was whether the subscription is push/pull subscription and also whether any schema changes are also published.

It took me a while to figure out the Subscriber, however in the process I noticed one more tool, “Replication Monitor”. You can launch the Replication Monitor from SSMS by right clicking on “Replication” folder.

In the Replication Monitor you could see your publisher listed. Just click on the Publisher and in the right side window, you will see all the Subscriber listed

In the above screenshot, the  Subscriber is in error status. Just double-click on it to the subscription history.

Note that in the above screen, you can find 3 tabs, Publisher to Distributor, Distributor to Subscriber and Undistributed Commands. In a single screen you can find, not just the status but the reason of failure, of all the publication from the current publisher.

Converting SQL SERVER DateTime? Test it thoroughly….

Today one of my user complained that a report was missing one particular ticket from the output. The report in question is very simple, it accepts start date & end date and filters on the ticket creation date time column to get the list of tickets.

The report query was something like

Select * from table1 where convert(datetime,CONVERT (int, CreatedDate)) between ‘2012-01-01’ AND ‘2012-01-31’

Cursory look at the query looked fine. However I was proved wrong when I ran the query in SSMS. It was missing a ticket whose CreatedDate  was ‘2012-01-31 18:57’

After some troubleshooting , I figured out that the issue was due to the conversion of CreatedDate to INT. In the query, the date was first converted to INT to remove the time part and then again converted to date time for comparison.

When the time part is in PM, the conversion to INT, converts it to next day. The below screenshot will explain it more precisely

Handling DateTime in SQL SERVER is very tricky especially when you are some processing like conversion, finding datediff etc. The only option we have is to test it out thoroughly.

Passing BIGINT data to SSRS Sub Report

Few days back one of my team mate complained to me that one particular sub report is failing randomly with

Error: Subreport could not be shown

The particular sub report was called from another report’s tablix control column. It accepts  a parameter whose base data type in SQL SERVER database was BIGINT. Since SSRS does not have BIGINT, the parameter type was set to FLOAT.

As mentioned by my colleague, the sub-report was failing for some particular rows. I thought this might be an issue due to mis-handled scenario in the sub-report. I went thru the code and everything worked fine. I just ran the sub-report as stand-alone and that too worked fine. Spent more that 4 hrs and tried many things but neither could find the issue nor get the report working.

At last I was able to identify the issue. When we pass a BIGINT value as a parameter to SSRS sub-report, even though the parameter type is float seems it does not accept the value.

Here is what I did. I created a main report (Report2), with one parameter. I am passing this parameter to a dataset which in-turn inserts it into a temp table. This dataset is binded to a tablix, which calls a sub-report (Report3) and passes this value.

The sub-report just prints the value.

When I supplied a value , say 2127762734, it worked fine.

However when I supplied a value , say 2327762734, it failed even though the sub report parameter was set to FLOAT.

This seems strange and I could not find the root cause. Another strange thing I observed is that when the value is hard-coded (instead of inserting into temp table) it worked fine like in the below screenshot.

 

SSRS 2008 Error – The report definition is not valid

Recently I downloaded a file from one of our production server, did some changes and reviewed it in BIDS and everything looked good. However when I tried to upload the file in the Report Manager got a strange error

The report definition is not valid. Details: ‘.’, hexadecimal value 0x00, is an invalid character. Line 1570, position 10. (rsInvalidReportDefinition)

Since the error message was talking about Report Definition is not valid, checked whether I have corrected the report in appropriate version (we have SSRS 2005, 2008 & R2).

I opened the code behind of the RDL by clicking the “View Code” option in BIDS and looked at the line mentioned in the error message. I was surprised to see few blank spaces after the end of the Report tag and BIDS had highlighted it as an error.

I just simply deleted it, saved and built. Then it worked fine. However I still could not figure out how it was added 😦

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.

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.

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.