December 2012

You are currently browsing the monthly archive for December 2012.

SqlTuesday T SQL Tuesday #37 – Invite to Join me in a Month of Joins

When I saw this month’s T-SQL Tuesday’s topic, hosted by Sebastian Meine, my initial thoughts were indeed:

What on earth can I still write about JOIN if Sebastian is going to write 31 posts on them?

So then I thought, let’s not write about the T-SQL JOIN, but about the Join() function in Reporting Services!

I hope you enjoy this little read as we delve into a couple of the SSRS functions!

Joining Some Stuff

You may or may not already know, but the functions in Reporting Services are actually using VB.NET functions.  These functions are located in the Microsoft.VisualBasic namespace.

And the Join() is actually a method of the Strings class.

So what does the definition of the Join() look like?

public static string Join(
    Object[] SourceArray,
    string Delimiter
)

(Yes, I prefer C# style.)

It takes two parameters: an array of objects which contains the strings to be joined and a string that will be used as delimiter for the output.

So where in SSRS do we have an object array?  Well, in the case of a multi-valued parameter!  I’ve actually already mentioned this briefly in my article on combining multiple-value parameters with stored procedures about three years ago.

As per the definition, the delimiter is a string, not just one character.  Which means the following would be an interesting expression to display the selected values on the report:

=Join(Parameters!SomeMultivalueParam.Value, ", ")

That’s right, we can use more than one character as delimiter.  Assuming the report has got a multi-value parameter called SomeMultivalueParam, this could result in the following:

Joining several values from a multi-value parameter together in a comma-separated string

Even better, because our parameter is an array of strings, we can also get a count out of it using the following expression:

=Parameters!SomeMultivalueParam.Count
    & " items selected: "
    & Join(Parameters!SomeMultivalueParam.Value, ", ")

That generates the following output:

Displaying number of items selected=

Unjoining Some Stuff?

Okay, that’s enough about Join, what if we need the opposite?  To just get a particular selected value out of a multi-valued parameter, you can simply use the following array syntax:

=Parameters!SomeMultivalueParam.Value(1)

Beware that the array indexes are zero-based, so the above expression gives us the second selected value from the object array.  In our example it would thus return “second val”.

But what if we’ve got a character-separated list of values coming out of the database?  In that case we don’t have an object array, right?  But that doesn’t stop us from creating one!

Which brings us to the opposite of the Join(): the Split()!

Here’s what the Split function looks like:

public static string[] Split(
    string Expression,
    string Delimiter,
    int Limit,
    CompareMethod Compare
)

Now let’s look at its usage by trying the following expression:

=Split("val1,val2,val3", ",").GetValue(1)

What do you think it returns?

That’s right: “val2”!

Would this also work for longer delimiters?  Well, the MSDN page contains some contradictory information in that perspective.  It mentions the following as description for the Delimiter parameter:

Type: System.String

Optional. Any single character used to identify substring limits. If Delimiter is omitted, the space character (” “) is assumed to be the delimiter.

Hang on, single character??  But some of the examples on that same page are using longer delimiters!

Hmm, let’s try that out!  What do you think the following returns?

=Split("val1,;val2,;val3", ",;").GetValue(1)

Yep, you guessed right, once more “val2”!

BTW: the following syntax works as well but the SSRS editor complains about it so better not use it:

=Split("val1,;val2,;val3", ",;")(1)

To be really sure that we’re actually looking at the right definition, let’s do one more test.

Here’s what I did.  I opened Visual Studio 2010 and created a new C# Windows Forms Application project.  Then I added a reference to the Microsoft.VisualBasic assembly.

Yes, you can do that!  Just right-click the References node in the Solution Explorer and select Add Reference…

Adding an assembly reference to the C# project

Switch to the .NET tab and select the Microsoft.VisualBasic component:

Adding the Microsoft.VisualBasic assembly to the C# project

To continue, I double-clicked the Windows form’s grey background to get to the code behind the form and added the following using:

using Microsoft.VisualBasic;

And then I typed the following code in the Form1_Load method:

MessageBox.Show(Strings.Split("val1,;val2,;val3", ",;").GetValue(1).ToString());

To conclude I hit F5!

What do you think that returned?

Guess…

Output of the Split function using a multi-character delimiter

So the Split function does actually support a multi-character delimiter!

Have fun!

Valentino.

Share

Tags: , , ,

Once again I’ve been wasting some time because of a silly bug.  This time it was due to the OLE DB Source component and the way it works with parameters.  If you are in a situation where you know your query is working fine and yet no records are going down the data flow, here’s a possible solution!

Disclaimer: this issue exists up until SQL Server 2008 R2.  Read on for details!

Update: after being advised to do so by several people, including Jamie Thomson, I’ve filed a bug at MS Connect: SSIS OLE DB Source incorrectly returns zero records in combination with parameter and comment

The Situation

I had a Data Flow with an OLE DB Source that uses one parameter, for instance:

select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = ?

I knew the query was working fine because when executed through SSMS and with the question mark replaced with ‘blue’, it would return 28 rows:

28 records in Management Studio

But when executed in BIDS, through either Execute Package or Execute Task, it would return zero records:

Zero records, zilch, nada, niente, none at all!

So I thought something must be going wrong with the package variable that gets passed into the source parameter, somehow.  I’m not going into details on what I tried out in my attempt to get this working, but I can tell you that I started to get really irritated.  My colleague Koen Verbeeck (b|t) can confirm this because I called him over to my desk to help me think! (thanks btw!) Smile

After some further tinkering with the data flow, we had our smart moment of the day and decided to launch SQL Server Profiler to see what BIDS was sending to the server!  I’m not sure if you’re aware of this but BIDS is doing some metadata-related stuff when preparing queries.  As far as I can tell, it also tries to determine the parameter type by running the following query:

 set fmtonly on select Color from  dbo.DimProduct
--some really smart comment goes here where 1=2 set fmtonly off

When creating this statement, it seems to use the whole FROM clause of the original query, including any trailing comments.  It combines that with a SELECT statement that contains the field that gets filtered and it appends " where 1=2 set fmtonly off".

But alas, apparently it’s not aware that lines can be commented out by using a double dash.  So part of its generated statement is commented out.  What it should have done is used some CRLFs, especially in front of the WHERE clause.  But it didn’t.

So, as a result of that, FMTONLY remains on while the SELECT statement gets executed, resulting in zero records!

For those unfamiliar with the FMTONLY setting:

Returns only metadata to the client. Can be used to test the format of the response without actually running the query.

And I can actually confirm what I’m stating here by changing the query to the following:

set fmtonly off;
select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = ?

28 records down the pipe!

We've got data!

But this hack is a little too dirty to put in production.  So what else can we do?  Well, use block-style comments instead and we won’t face the issue!

select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
/* some even smarter comment goes here */
where Color = ?

So, as I mentioned at the start of the post, this behavior can be reproduced using SSIS versions prior to 2012.  What about 2012 then?  Here’s the result of the Data Flow using the first query mentioned above:

SSIS 2012: we've got data, even with the "faulty" query!

Alright, that works better!  Now let’s use Profiler to check what’s going on here.  This is the first statement that gets executed:

exec [sys].sp_describe_undeclared_parameters N'select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = @P1'

Further down, I also see this one:

exec [sys].sp_describe_first_result_set N'select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = @P1',N'@P1 nvarchar(15)',1

It is using an entirely different approach, no longer using the FMTONLY setting!  Hang on, this rings a bell!  Look what the BOL page for SET FMTONLY (2012 version) specifies:

Do not use this feature. This feature has been replaced by sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL).

Cool stuff!

Conclusion

If you’re not on SQL Server 2012 yet, be careful with comments in OLE DB Sources in the SSIS Data Flow!  Ow, and get the SQL Server Profiler off its dusty shelf now and then!

Have fun!

Valentino.

Share

Tags: , , , ,

© 2008-2017 BI: Beer Intelligence? All Rights Reserved