stuff

Well, I often need to create a comma-separated list out of values in a table.

here I found this pretty ellegant sollution:

STUFF(

(

SELECT ', ' + Supplier_ID

FROM SomeTable

join SomeOtherTable

on Something

WHERE SomeCondition

FOR XML PATH('')), 1, 1, '') as AllSuppliers

Execution order differs with rendering mode

Today our tester found a big saying "Incorrect numbering of the tables in the PDF export".
The issue is that we have a custom code function incrementing a number and than returning the resulting number. It starts from 0 and it'll return 1 first time it is called, 2 afterwards, 3 and so on. It works fine when my report renders in HTML, but not when it renders as PDF. The first table is numbered with 5 and the rest are numbered in order 1 to 4

The only difference between the first table and the rest is that the first table displays the table number in a row, while for the rest I had to add a text box above the table.

Apparently, the execution order of custom code is different when report is rendered in browser vs when it renders as PDF. I did a simple test by creating a test report, with just a matrix, a table and a text box and the textbox is indeed rendered first (it executes the custom code first). Only after, the tablixes are rendered

SSRS 2008 incorrect number of TablixCornerCell

I got a "nice" error today , while working on a report converted from SSRS 2005 to SSRS 2008.

Error 1 [rsWrongNumberOfTablixCornerCells] The tablix ‘matrix4’ has an incorrect number of TablixCornerCell. The number of TablixCornerCell within TablixCornerRow at index: 0 must equal the number of levels in the TablixRowHierarchy containing TablixHeaders.

Update , 2012-02-10: I fixed the problem by simply reading carefully the error message. All I did was adding an empty <TablixCornerCell></TablixCornerCell> into the <TablixCornerRow>

Sometimes VS 2008 cannot access DataSource file

Today, for the second time since we started using SSRS 2008 I had the following stupid error:
Running a dataset works from VS designed, but when I try to run the report (again, from the VS2008 designer) I get errors (DS couldn't be run)

After little investigation I realize that the problem is in fact that the report cannot be built. I had VS2008 access to RDS file denied. this is not a read-only file, is checked-out, so VS should be able to access the file and change it.
However, VS keeps telling that access to {MyReportsFolder}\bin\Debug\{MyDataSource.rds} is denied
The only way I found to fix this was to delete the folder {MyReportsFolder}\bin and rebuild.

Weird...

Error in SSIS VS2005

Yesterday I had to put some code into SSIS packages.
Being a lazy person, what I usually do is copying one of the existing task ("Execute SQL Task") and changing the name and the code itself. Apparently after installing VS2005 SP1 this was not working anymore.

I had to create every "Execute SQL Task", set the connection, etc....which is not a lot but is slower than what I was used to.

The error message I was receiving while trying to copy was "An error occurred while objects were being copied. SSIS Designer could not serialize the SSIS runtime objects". A fast google search revealed this page and the easy fix by jaegd:

Register the xml parser dlls with the below commands.

regsvr32.exe msxml3.dll
regsvr32.exe msxml6.dll

It really worked and fixed my problem, but lets me wondering why Microsoft didn't fix it and release a SP2 with all this fixes.

Find the Last Apparition of a Value in String

Well, indeed, I spent few minutes today trying to find the last apparition of a value in string. All this in a  simple T-SQL query in SQLServer 2005. The reason I wanted this is that I need the last part of a string separated by a known delimiter.

I was having a string like this:

declare @a as nvarchar(50)
set @a = 'ssss$kkkk$ooooo$abcde'

I searched the Internet a bit, but with no success. But after few minutes I remembered a function that I seldom use, the reverse. From here, all was as simple as:

select charindex ('$', reverse('ssss$kkkk$ooooo$abcde') )

and further play with this to get the value I need

Update: Of course, using it to trim out the last part of the string is going to be something like this:

select Substring(@a, 1, Len(@a) - charindex ('$', reverse(@a)))

SSRS. Must declare scalar variable @Parameter1

Well, one of the errors in Reporting Services 2005 says:

An error occurred during local processing

An error has occurred during report processing

Query execution failed for data set 'Name of the data set'

Must declare the scalar variable @Parameter1

This can be pretty annoying especially when you know you have the report parameter and it used to work. What happens is that, due to some black magic that I cannot explain, the data source is loosing the list of parameters. So the fix is to either add the parameters one by one or just delete and recreate the data source and everything will work.

ROLLUP vs CUBE

Well, that's what happen when you don't carefully read the documentation. I have recently discovered the benefit of the WITH ROLLUP clause in SQLServer 2005, and I thought what it was doing was returning all possible combinations of grouping.
Wrong, that's the WITH CUBE.

Using the WITH ROLLUP I found out that the number of rows returned by my query was dependent on the order of the fields in the group by clause. Funny.

Well, to keep it short, I found this: http://msdn.microsoft.com/en-us/library/ms189305%28SQL.90%29.aspx
because "ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns" we get results depending on this hierarchy.

unlike it, what I needed was the WITH CUBE clause: CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.

Visual Studio vs ReportingService

Today I almost pulled my hair off again (well, what rests of it). I was having a local report that used to work until I added some fields in the stored procedure that was filling it. After wandering and thinking and checking the SP, I found this:

http://social.msdn.microsoft.com/Forums/en-US/vsreportcontrols/thread/64532304-a4e8-466e-b0ce-65ee723a84b7

Visual Studio Editor

While working on a web site recently, I used Visual Studio 2008 for a small wizard in ASP.NET.

It was cool at the beginning, but it become really painful when, after a while, I was trying to update the tables on the page. Seems like Visual Studio is creating a lot of stupid style-sheets when you drag the table's rows or columns. Hilarious things, like very height rows of the tables, happen. When you try to resize the row back to it's normal size, no way. The only way I found was to manually edit the ASPX and delete the generated styles. And I'm not the only one having problems with it, a friend whom I am working with reported the same problem.

While I like Visual Studio for it's ease of use and clean interface, I found the way it deals with the wizard to be very...let's say at least uninspired. So, what can be done ?
1) Integrate really often, so if the things get messed up you can come back to a good version.
2) Manually edit the ASPX for small changes. It's much faster than trying to convince VS to work the way you'd expect.