Reporting Services

You are currently browsing articles tagged Reporting Services.

Is it 32 or 64?

May 1, 2009 in Reporting Services, SQL Server | 1 comment

That’s a question that you should always keep in mind when working on a 64-bit machine.  In case you’re wondering why I’ll tell you a little story.

On a server not so long ago the operating system was Windows Server 2008 64-bit.  The database system was SQL Server 2008, 64-bit as well.

That server needed to be able to connect to a DB2 database on another server.  For that purpose someone installed the IBM ODBC 9.5 driver, 32-bit.  As usual when someone asks for connectivity to that DB2 server, the batch script containing some typical DB2 commands was executed.  What this script does is create a couple of ODBC System DSNs.  The person that installed the driver showed that all was working fine because using some command-line tool he was able to connect to the databases.

However, when I opened the ODBC Data Source Administrator through Administrative Tools > Data Sources (ODBC), it didn’t show me any System DSNs and when I clicked the Add… button it didn’t show me any DB2 drivers either.  So it seemed that something went wrong.  Or not?

What I needed to be able to do was to connect from Reporting Services to the DB2 database.  So as next test I decided to create a small report directly on the server (BIDS was installed).  And all went fine, I was able to create a data source connecting to DB2 and dataset querying data from the DB2 database.

Then I decided to deploy the report and run it through the Report Manager.  Deployment went fine but when I ran the report it could not connect to DB2.  Hmm, is it working now or not?

I was fairly convinced that it had something to do with the fact that the ODBC driver was 32-bit.  The Business Intelligence Development Studio is 32-bit, that’s why it worked fine using the 32-bit ODBC driver.  But the Report Server is 64-bit, that’s why it couldn’t connect to DB2 using the 32-bit driver.

But why wasn’t the ODBC DSN showing up?  After all, it must exist because the BIDS showed me the list of DSNs that were created.

And then I found the answer, as usual, on the internet.  And it was fairly logical but sometimes you just don’t think of it.  The ODBC Administrator that you launch from the Start > Administrative Tools menu on a 64-bit server is the 64-bit version of the tool.  And that 64-bit version only shows DSNs using 64-bit ODBC drivers.  To get the 32-bit version of the ODBC Administrator you need to launch it manually through Explorer.

And now you may be wondering where on earth disk you’re supposed to find that 32-bit version.  Here’s the answer:

  • The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
  • The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

And indeed, that version of the tool showed me the DSNs and the DB2 driver.

(Why they didn’t rename the 64-bit version to odbcad64.exe – after all, that is what the 32 in the filename means – remains a mystery to me.)

Sidenote: there appears to be an issue with this tool where both the 32 and 64-bit version of the tool show both the 32 and 64-bit DSNs.  My server didn’t have this issue but it may be interesting to keep it in mind.  More details about that issue in this Microsoft Support article.

Share

Tags: , , , , ,

SSIS 2005 from SSMS 2008? No.

February 8, 2009 in Integration Services, Management Studio, Reporting Services | 1 comment

Having trouble connecting to your 2005 Integration Services using Management Studio 2008?  Even when you only have SQL Server Integration Services running, and not version 10.0?

Integration Services

To make it even better, have you noticed that SQL Server Integration Services 10.0 will start up when you connect to it from SSMS 2008?

Well, that’s by design.  SSMS 2008 can only connect to SSIS 2008.  And SSMS 2005 can only connect to SSIS 2005.  So don’t go uninstalling the 2005 Management Studio to save some space if you use Integration Services 2005!

There’s some (limited) info on Microsoft Connect and on SQL Server Central.

Update: when trying to use SSMS 2008 to connect to SSRS 2005, an error coming from the Report Server WMI provider will show up complaining about a couple of invalid namespaces (Microsoft.SqlServer.Management.UI.RSClient and System.Management).  It has been reported as a bug on Microsoft Connect.  Microsoft’s feedback is that the issue is caused by a breaking change in the WMI namespace and that it won’t be fixed.

So there you go, neither Reporting Services nor Integration Services 2005 can be connected to using Management Studio 2008.

Share

Tags: , , , , , , , , ,

DIY: Alternative Reporting Services client

August 3, 2008 in .NET, Reporting Services | 2 comments

The other day I was looking for an alternative Reporting Services client, as an extra client besides the web-based Report Manager. As I couldn’t really find one I thought “how difficult could it be to write one myself?”.

And indeed, with the ReportViewer control in Visual Studio 2005 it’s really no big deal. All you need to do is drag the control onto a form, tell it that you want to load a remote report, configure the ReportServer so that it knows where to look for reports and the ReportPath so that it knows what report to load. Then call the RefreshReport() method to load the report.

When configuring the ReportPath, pay attention to the mandatory leading slash.

   1: reportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Remote;
   2: reportViewer1.ServerReport.ReportServerUrl = new Uri("http://localhost/reportserver");
   3: reportViewer1.ServerReport.ReportPath = "/My Reports/ReportName";
   4: // load report
   5: reportViewer1.RefreshReport();

The C# code snippet above is loading a report called ReportName, located on the local report server in a folder called My Reports.

Update: how to browse the reports on the server

Microsoft has provided an interesting sample called RSExplorer as part of the SQL Server samples, located at CodePlex.  Once you’ve installed the samples you can find RSExplorer in C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Application Samples\RSExplorer Sample\, if you chose to install to the default folder.

The sample shows how to use the Reporting Services web service to populate a WinForms ListView with a list of folders and reports, and even how to do some basic management tasks like creating a folder or editing a report description.

When you double-click a report it will open in a popup window.  What I do find funny in this sample is that the popup window, called ReportViewer, does not use the ReportViewer control.  It uses a WebBrowser control  instead.

Share

Tags: , , , ,

SQL Server 2005 Reporting Services: the black background issue

July 27, 2008 in Reporting Services | No comments

Recently I came across a bug that was introduced in the Reporting Services part of SQL Server 2005 SP2. When previewing the “Print Layout” of the report it would show like this:

SSRS_preview_printlayout_bug

The by default Transparent background would show as black instead of white. Obviously this is not very convenient, especially when the text is also black.

Luckily Microsoft was already aware of this issue, its KB is located here.

I managed to solve the problem by installing Cumulative Update 1 (build 3161), which can be requested for download through the following Microsoft page: http://support.microsoft.com/kb/935356/en-us.

If you don’t like installing CUs, you could wait until SP3 ships in Q3 this year (this info comes from several sources, just do a search on “sql server 2005 SP3″).

Share

Tags: , , , , ,

Newer entries »

© 2008-2012 A Developer's Blog All Rights Reserved