This blog will no longer be updated.

New content is available on my new blog

December 2009 - Posts - Piotr Rodak

December 2009 - Posts

Happy New Year

Well, this is my last post this year - I know, there weren't too many of them anyway, but I plan to improve my frequency, promiseSmile .

I have a few days off and all of a sudden I got a flu or cold, felt pretty rubbish two days ago. I spent all day in bed yesterday, sleeping, playing with Rubik's Cube and reading a book - The Storage Engine Big Smile. The effect of day in bed was that I couldn't sleep at night and my brain was plotting grand plans how to become rich and retire before end of 2010. Well, I didn't come up with anything you wouldn't know already so regarding this it was a wasted night. However, I came across idea of using regular expressions to parse some SQL queries to pick table names or columns in select statemets. I downloaded very cool regex designer and was playing with the idea for the whole day today. Call me a geek, but I think regular expressions are cool. It's a pity they are not natively implemented in SQL Server, but on the other hand it would encourage developers to do really heavy lifting on character data in the database - and DBAs would have yet another trouble to look after.

Anyway, happy New Year folks, let it be even better than the old one.

 

Default value or value provided for the report parameter .. is not a valid value

I've been working on a report for last few days. It worked fine on my visual Studio and when it was ready, I decided to deploy it to our application server which runs Report Server 2005. To my disappointment, the report didn't want to work after deployment. It threw an error on me as on the below picture:

 

Default value or value provided for the report parameter 'RunDate' is not a valid value

I looked for solution far and near. One of the first articles I stumbled upon was the fix for apparently the same error in Microsoft KB base: http://support.microsoft.com/kb/970058

I installed service pack 3 on the Report Server, bounced the application server box, then downloaded and installed CU4 which is supposed to contain fix for my error. Then I restarted the box once more and launched the reports manager. Unfortunately, the error didn't go away. It was the moment I begun to think rather than look for ready solutions. My report has several parameters passed to it from the feeding query. The values of these parameters are displayed on the header of each page of the report. It dawned on me, that the Report Manager doesn't complain about string parameters, it complains about this particular, DateTime type parameter. This parameter is read from the query and a formatting string is applied to it so it is displayed nicely in the header.

 

Ok, so this might be something related to the representation of the date on the app server. I checked regional settings and indeed, there was difference - my local machine had different location set - Ireland instead of United States. Yet all other values for date formats were the same.

I modified the data type of the parameter to be String and removed the formatting. I have got default date representation as specified in regional settings in Control Panel. This was not what I wanted and I wanted to have the thing done. I ended up with modifying query to convert all three datetime columns for parameters to strings and formatted them using the most important SQL Server function - CONVERT:

 convert(varchar(20), a.RunDate, 13) RunDate

The report works now nicely, producing expected output and I don't have to count on regional settings or whatever to properly format my date parameters.It looks like for Reporting Services it is better sometimes to format data for them than to rely on them to do it because they tend to behave differently on different machines, depending on the external environment settings.