SQL Server Reporting Packs for SharePoint Error

How many of you have run into this when setting up the SQL Server Reporting Pack for SharePoint?

Steve found this post today when he ran into the same problem setting this up. Not a whole lot of documentation on the issue so I figured I would point to this post as well to pass the word.

SYMPTOMS
After you have installed and configured the SQL Report Pack for SharePoint on a SharePoint Portal webserver and started extracting the WSS and IIS logfiles, you encounter the following error when running the DEP tool RPDataExtraction.exe.

Copying data from the staging database to the reporting database… Data extraction failed at 99/99/9999 99:99:99 AM

Details:
System.Data.SqlClient.SqlException
String or binary data would be truncated.
The statement has been terminated.

CAUSE
This problem is caused by some field size truncation of temporary tables when they are initially created and rows are inserted that exceeded the size of the field. Specifically, it can be traced to two stored procedures inside the dbSPSReporting database:

The usp_Insert_FactFileStorage stored procedure creates a temporary table called tblTempFileStorage_toFactStorage where the field FileType is wrongly defined as nVarChar(25). It needs to match the DocType field from the dbSPSReporting.dbo.tblDocs table.

The usp_Insert_FactWSS stored procedure creates a temporary table called tblTempWSS_ToFactLoad which have 3 fields that are defined incorrectly. The WSSDate, WSSUser, and WSSDoc fields need to match the corresponding fields from dbSPSReportingStaging.dbo.tblWSSLogData table.

RESOLUTION
To resolve the issue, edit the stored procedures that are causing the error inside the dbSPSReporting database

Modify usp_Insert_FactFileStorage
In line 48, column 20, modify the field size definition of FileType to be 255
Modify usp_Insert_FactWSS
In line 45, column 11, modify the field type definition of WSSDate to be smalldatetime
In Line 47, column 20, modify the field size definition of WSSUser to be 255.
In line 48, column 20, modify the field size definition of WSSDoc to be 255

This entry was posted in Microsoft Stuff. Bookmark the permalink.

Comments are closed.