April 2009

You are currently browsing the monthly archive for April 2009.

This is not a post about animals,

this is not a post about trees.

This is a post I wrote especially for those who are wanting to call a stored procedure with parameters in a DB2 database from SQL Server Reporting Services 2008 using the IBM ODBC DB2 9.5 driver.

When defining your Dataset, the BIDS let’s you specify the query type.   The possible values are Text, Table and Stored Procedure.  Which one would you choose?  Indeed, Stored Procedure seems like a good choice.

Dataset using Stored Procedure

So I filled it all out as in the screenshot above.  When clicking the Refresh Fields button, the BIDS was able to read the required parameter from the stored procedure.

Refresh Fields Shows Parameters

I gave it a valid value and clicked OK.  And guess what?  Then it stopped working, showing me the following (quite common) unexpected token error (when connecting to DB2):

ERROR [42601] [IBM][CLI Driver][DB2/NT] SQL0104N  An unexpected token “END-OF-STATEMENT” was found following “MYSP”.  Expected tokens may include:  “JOIN <joined_table>”.  SQLSTATE=42601

The full screenshot:

Unexpected Token Error

I’m quite sure that this is something going wrong with the ODBC driver.  I also tried this scenario using the following OLE DB drivers:

  • Microsoft OLE DB Provider for DB2
  • IBM OLE DB Provider for DB2

The Microsoft OLE DB driver for DB2 is one of the Feature Pack downloads.

The best results were achieved using the IBM OLE DB driver.  I was able to successfully call the stored procedure.

I’d like to mention that the Connection Strings website is a very useful resource in the context here.  With the information available there I was able to quickly form the connection strings that I needed to provide to the drivers.

But for my project the requirements tell me to use ODBC.  So I found the following solution to my problem.

Instead of selecting Stored Procedure as query type for the dataset, I chose Text and typed the following statement:

call MYSCHEMA.MYSP(?)

Or to say it with an image:

Dataset calling a DB2 stored procedure using the Text query type

This worked quite well, I got results from the stored procedure as expected.

One additional tip that I’d like to mention here is that the BIDS is not always happy about using the Query Designer in combination with the stored procedure.  Not sure what exactly is going wrong but sometimes the Dataset Properties window stops responding when coming back from the Query Designer.  Maybe something to do with the population of the Fields list?

I did find that it would be less unstable if the fields were already defined.  So if possible and you’re having this issue, try to get a similar resultset as the stored procedure returns by using a SELECT statement.  When that is working fine, your fields collection is defined as the stored procedure expects it.  Then swap the SELECT statement with the CALL statement.

Have fun!

Share

Tags: , , ,

10.0.2531.0

Right.  10.0.2531.0.  That’s the version number that my SELECT @@VERSION statement is reporting when executed on my SQL Server 2008.  How come?  Well, I installed Service Pack 1.

In other words, SP1 for SQL Server 2008 is out and can be downloaded from the Microsoft Download Center!

Besides fixing several bugs there are some new features to the installation package itself:

  • Slipstream
    You can now integrate the base installation together with service packs or with hotfixes. Therefore, you can install in a single step.
  • Service pack removal
    You can now uninstall only the service pack without uninstalling the whole instance.
  • ClickOnce capability
    You can now deploy Report Builder 2.0 by using the ClickOnce deployment technology.

I especially like the slipstream feature.  This should wipe out a problem which I encountered with 2005.  Some time ago I got a machine that had SQL Server 2005 installed, including SP2.  However, the Management Studio was not installed.  Ever tried getting it installed in such a scenario?  The install DVD complains that there’s already a newer version on the machine and refuses to install SSMS.  So I ended up uninstalling and reinstalling everything.  This problem should now be avoidable, or at least, that’s what I hope.  I haven’t actually tried it out but that’s how I understand slipstream.

Together with SQL Server, the Express Edition of 2008 has also gotten an update to SP1.  And the same goes for the SQL Server 2008 Feature Pack which is now dated April 2009.

Other updated downloads include Report Builder 2.0, Data Mining add-ins for Office, Reporting Services add-in for SharePoint and the Upgrade Advisor.

Update: the BOL have gotten their update as well.  Now available for download: Microsoft SQL Server 2008 Books Online (May 2009).

Share

Tags: , , ,

Once upon a time in a Business Intelligence Development Studio 2005 Reporting Services solution there liveth a Textbox object which developers could use to put text on a report.  It was a fairly simple object, allowing only two ways of programming the text.

The first and most simple way of the two was to type plain and simple text directly into the box:

SSRS Textbox with simple constant text

And the second, more advanced way, alloweth developers to use an expression to create the text dynamically.  With this they could combine data from several sources into the same Textbox:

SSRS Edit Expression window

Then developers realized that something was still missing.  Text could be created dynamically but they also wanted to optimize the way the text looketh.  So they selected part of the expression and tried to change some font properties.  But alas, they couldn’t.  It was all or nothing:

Text rendered using some font styles in SSRS 2005

Then arriveth the day of the birth of the successor to BIDS 2005.  As the family’s tradition went, it was called the same as its parent.  The only difference was its birth year: BIDS 2008.

And developers started exploring BIDS 2008 and discovereth that the Textbox object had gotten an upgrade.  When editing the content of the textbox, the right-click menu has gotten a new item:

SSRS 2008 Create Placeholder menu item

That’s right, Create Placeholder.  And this was the moment in time when developers started to have fun again.  The placeholder can be given a value through an expression, just like the olden days:

Placeholder Properties - General tab

The Placeholder’s value can be given a format through the Number tab:

Placeholder Properties - Number tab

And a Placeholder’s font can be customized:

Placeholder Properties - Font tab

Now you may ask yourselves, wherein lies the difference with the olden days?

Well, it lies in the fact that several Placeholders and regular constant text can all be combined in the same Textbox!

This is what it looks like in Design time:

SSRS 2008 textbox with placeholders

And rendered in Preview:

SSRS 2008 Preview of textbox content

Powertip: to easily open up the properties of an existing Placeholder, single-click the Textbox to select it, again single-click to edit the content, then double-click a Placeholder to get its properties window.

When reports developed using BIDS 2005 are attached to a 2008 SSRS solution, any expressions in textboxes are acting as a placeholder as well:

Textbox with expression converted to BIDS 2008

And developers liveth happily ever after.

Although not entirely.  Try entering a chemical formula in the textbox.  Indeed, there’s no subscript option in the font settings.  And it doesn’t work through HTML either, as explained in this Microsoft Connect bug report.  It’s probably not as easy as it sounds, but if Word can do it I think so should SSRS.

Small update: a colleague recently had an interesting idea related to the subscript issue.  If subscript is not possible, try putting the numbers in a smaller font than the other characters.  So instead of H2O, it would become H2O.  The subscript version looks like this: H2O.  The workaround doesn’t look as good as with subscript but better than just in regular font size.  (Thanks Bart :-) )

Share

Tags: , , , , ,

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