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:
- 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
MORE INFORMATION
Additional resources can be found on the following links:
- Deployment Guide for SQL Server Report Pack for SharePoint
<Link available next week>
