Friday, 1 April 2016
SOME BUGS IN SQL-SSRS 2005
I know sql 2005 is getting outdated and there would be no support from Microsoft from this month. But lets not go on that road, because client is ready to take the risk. So as a result we are still working on sql 2005. However by working with so called soon to be a outdated version gives you a chance to come across various bugs with the technology. I am listing few of them which I encountered last week or so:-
1. I have a stored proc like this :-
create proc reporting.sp_test
select * from test
As you can see this is a stored proc belonging to a schema 'reporting' and referring to a table with default schema 'dbo'. On executing it I get a error message which says that - test does not exist.
However when I tested this in sql 2008, it works fine.
It seems the behaviour changed in 2008. It looks like in 2005 the default schema is the schema of the caller (in this case the SP) and in 2008 it's default dbo (in 2014 also)
2. Lets assume you have an expression like the following :-
=Sum(IIF(Fields!TYPE.Value="All Products", Fields!COLUMN1.Value,0))
where Column1 is of decimal type. You are bound to get an error message in 2005. In order to make this expression working, you need to use the following expression:-
=Sum(IIF(Fields!TYPE.Value="All Products", Dbl(Fields!COLUMN1.Value),CDbl(0)))
I dont know as to why we have to use CDbl, you could possibly find the answer here:-
It tried replicating the same in sql 2008 and it works perfectly.
Feel free to leave any comments/feedback.
Seldom are the days when you run into complex issues but resolve them in the shortest interval of time. Thanks to Larry Page and my fello...
Sometimes the mistakes you make in the coding lead you to a better understanding of a particular concept. This happened recently with m...
Requirements : A valid azure account, Local Instance of SQL Server (>= 2012), SQL Server Data Tools, Excel 2007 or above Scenario : Du...
I got a massive satisfaction last week when I was able to bring down the execution time of a sql stored procedure from > 3 hours to me...