Tag: SSRS

Posted on

Troubleshooting an SSRS Error

SSRS logo

SQL Server Reporting Services is a great tool that comes with SQL Server, and it is the tool I prefer to use when a report is needed.

I have built and continue to maintain many such reports, and once the reports are built, they don’t often need updating, unless the database schema changes or the customer’s requirements change.  As a result, once they’re deployed, the reports don’t often require a lot of minding.

However, just as with any application, unforeseen events can cause the reports to fail.  In my case today, bad data was allowed to be inserted into the database that was the source for one of my reports.  The mechanism that inserted the data allowed the data in, but the code used to built the result set for the report threw an exception when it encountered the data.  For security reasons, that exception was caught and a generic error appeared in the Web browser when attempting to run the report:

  • An error has occurred during report processing.
    • Query execution failed for data set <>
      • For more information about this error navigate to the report server on the local server machine, or enable remote errors

To find out what the source of the error was, I opened the report in the BI Development Server from SQL Server 2005.  The data source was a stored procedure.  When executing the SP in Query Analyzer, I got this error:

“Msg 8115, Level 16, State 6, Line 49
Arithmetic overflow error converting float to data type numeric.”

My next step was to get the source for the SP, commenting out the CREATE statement, and declaring and setting the parameters as variables to change the SP source code into ad hoc executable statements.  By plugging the dates in question into these variables, I got the same error as above.

The line causing problem in the stored proc updated a table variable based on values from a static table.  Since I could not directly determine what values in the static table were causing the SP to throw the error, I read in all of the values that were in the column of the table variable that were normally joined to the static table into a cursor.

I then scrolled through the cursor, using SELECT statements that contained a modified version of the expression that had been in the UPDATE statement, until I found out which value caused the problem.  Also, I added two SELECT identical statements to the cursor, one before and one after the SELECT statement that had been modified from the original UPDATE statement.  These two SELECT statements simply read “SELECT <<cursor variable>>” to indicate which value was being used for each iteration through the cursor.

When the value causing the problem was reached, the error statement appeared in the Messages pane of the Query Analyzer between two lines, each of these lines showing the value that caused the error.  By then using that value in the WHERE clause of a SELECT statement against the static table, I was able to find out what bad data had been entered.

Posted on

Running SSRS Reports with Lotus Notes Databases as the Original Data Source

SSRS logo

I had a need to be able to report on data inside a set of Lotus Notes 7.0 databases, and I wanted to use SQL Server Reporting Services (SSRS) to do this. There is no direct interface between SSRS and Notes, but I found that IBM provided an ODBC connection from Notes using their NotesSQL drivers.

I have set up an interface between Lotus Notes and Microsoft SQL Server 2000 using IBM’s NotesSQL ODBC drivers. The Lotus Notes 7 Client, NotesSQL 8.5.1 drivers, and an ODBC DSN (using the NotesSQL client) for each Lotus Notes database are all installed on the SQL Server. These ODBC drivers are compatible with 32-bit SQL 2000 but not SQL 2008 R2 (64-bit). I have not had an opportunity to test on any other platform as yet, though it’s certainly safe to assume that other versions of Lotus Notes up to 8.5.1 should work, since that’s what the NotesSQL drivers were written for.

I created a Linked Server on the SQL Server for each ODBC DSN; that is, each connected Notes database. The views and tables in the Notes DBs can be accessed via OPENQUERY commands, which can be included in a SQL database on the SQL Server. Because the connection between the two using only the Linked Server seemed so slow, I built a SQL Server database called Noteslink.

To test, I connected Query Analyzer to the NotesLink database on the SQL Server. I created Stored Procedures with the convention sp_DBname_AllDocuments to pull the All Documents view from the Notes database whose DSN had the name DBname. (In each case where there is a “DBname” below, substitute the name of the ODBC DSN.)

USE [NotesLink]
GO

/****** Object:  StoredProcedure [dbo].[sp_DBname_AllDocuments]  ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_DBname_AllDocuments] AS
BEGIN
SELECT * FROM OPENQUERY (DBname,
'Select * from All_Documents')
END

GO

Run the command “EXEC sp_DBname_AllDocuments” in Query Analyzer and this will pull the All Documents view from the DBname database.

From here, I built various jobs that would insert the results into tables in the “NotesLink” SQL Server database. I connected the SSRS reports to the “NotesLink” database, and fun was had by all. This exact configuration will not work if you need real-time reports; you would have to use OPENQUERY statements in the SSRS queries to pull directly from the Notes databases.

Please let me know if you have tried this and what other ways this could be accomplished. As SQL Server 2000 is reaching end-of-life, a newer solution using SQL 2005/2008/2012 could become necessary.