The SharePoint Chronicles

Upcoming KB Article to address issue in the SQL Report Pack for SharePoint

Knowledge Base Article: ?????

TITLE

You received a Sytem.Data.SqlClient.SqlException error message when running the Data Extraction Program (DEP) RPDataExtraction.exe.

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:

1. 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.

2. 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

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

MORE INFORMATION

Additional resources can be found on the following links:

• Microsoft SQL Server Report Pack for Microsoft Office SharePoint Portal Server 2003
  <http://www.microsoft.com/downloads/details.aspx?FamilyId=49159368-544B-4B09-8EED-4844B4E33D3D&displaylang=en>

• Deployment Guide for SQL Server Report Pack for SharePoint
  <Link available next week>

• Office Online
  http://office.microsoft.com/en-us/FX010909721033.aspx