Hi Guys
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
as
select * from test
exec reporting.sp_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:-
http://stackoverflow.com/questions/4727121/ssrs-conditional-summing?answertab=oldest#tab-top
It tried replicating the same in sql 2008 and it works perfectly.
Feel free to leave any comments/feedback.
