Microsoft KB Archive/906508

= BUG: You receive a &quot;System.Data.SqlClient.SqlException&quot; error message when you run the Data Extraction Program tool (Rpdataextraction.exe) =

Article ID: 906508

Article Last Modified on 2/2/2007

-

APPLIES TO


 * Microsoft Office SharePoint Portal Server 2003
 * Microsoft SQL Server 2000 Reporting Services

-



Bug #: 403546 (SQLBUDT)



SYMPTOMS
Consider the following scenario. You install and configure the Microsoft SQL Server Report Pack for Microsoft Office SharePoint Portal Server 2003 on a SharePoint Portal Web server. You run the Data Extraction Program (DEP) tool (Rpdataextraction.exe) to extract the Microsoft Windows SharePoint Services (WSS) and Microsoft Internet Information Services (IIS) log files. In this scenario, you receive the following error message:

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 issue occurs because the field size of temporary tables is truncated when the tables are created. Then, rows are inserted that exceed the size of the field. Specifically, this issue is caused by the following stored procedures that are inside the dbSPSReporting database:  The usp_Insert_FactFileStorage stored procedure creates a temporary table that is named tblTempFileStorage_toFactStorage. In this table, the FileType field is incorrectly defined as nVarChar(25). Instead, the FileType field must match the DocType field that is in the dbSPSReporting.dbo.tblDocs table. The usp_Insert_FactWSS stored procedure creates a temporary table that is named tblTempWSS_ToFactLoad. In this table, the following fields are incorrectly defined:  WSSDate WSSUser WSSDoc

These fields must match the corresponding fields that are in the dbSPSReportingStaging.dbo.tblWSSLogData table.</ul>

<div class="resolution_section">

RESOLUTION
To resolve this issue, use one of the following methods.

Method 1: Modify the stored procedures
Modify the following stored procedures that are inside the dbSPSReporting database:
 * usp_Insert_FactFileStorage

In line 48, column 20, set the field size definition of the FileType filed to 255.
 * usp_Insert_FactWSS
 * In line 45, column 11, set the field type definition of the WSSDate field to smalldatetime.
 * In line 47, column 20, set the field size definition of the WSSUser field to 255.
 * In line 48, column 20, set the field size definition of the WSSDoc field to 255.

Method 2: Use SQL Query Analyzer
To resolve this issue programmatically, you must run two code samples. To do this, follow these steps: <ol>  Paste the following code sample into SQL Query Analyzer. Then, run the code by pressing F5. USE dbSPSReporting go

ALTER   PROCEDURE dbo.usp_Insert_FactWSS AS declare @StartDate datetime

set @StartDate = getdate

Begin Transaction if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTempWSS_ToFactLoad]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblTempWSS_toFactLoad] if @@error <>0 Begin --Insert logging table message. Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactWSS - Dropping Table tblTempWSS_toFactLoad', @StartDate, getdate, 'Errors Occurred' RAISERROR ( 'Errors Were encountered dropping tblTempWSS_toFactLoad', 16, 1 ) Return -1 End else Commit Transaction

Begin Transaction Create table dbo.tblTempWSS_ToFactLoad (        WSSFactID bigint identity not null Primary Key,        SiteGUID uniqueidentifier,        WebGUID uniqueidentifier,        WSSDate smalldatetime,        WSSTime nvarchar(8),        WSSUser nvarchar(255),        WSSDoc  nvarchar(255),        WSSList uniqueidentifier,        WSSReferrer nvarchar(255),        WSSRelativeURL nvarchar(255),        SiteID bigint,        WebID  bigint,        DateID bigint,        TimeID bigint,        UserID bigint,        FileID smallint,        ListID bigint,        ReferrerID bigint,        RelativeURLID bigint)

if @@error <>0 Begin --Insert logging table message. Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactWSS - Failed to Create Table tblTempWSS_toFactLoad', @StartDate, getdate, 'Errors Occurred' RAISERROR ( 'Failed to Create Table tblTempWSS_toFactLoad', 16, 1 ) Return -1 End else Commit Transaction

--Create indexes. Create Index idx_nc_TmpWSS_1 on dbo.tblTempWSS_ToFactLoad(SiteGUID) Create Index idx_nc_TmpWSS_2 on dbo.tblTempWSS_ToFactLoad(WSSDoc) Create Index idx_nc_TmpWSS_3 on dbo.tblTempWSS_ToFactLoad(WSSList) Create Index idx_nc_TmpWSS_4 on dbo.tblTempWSS_ToFactLoad(WebGUID)

Begin Transaction insert into tblTempWSS_ToFactLoad(SiteGUID, WebGUID, WSSDate, WSSTime, WSSUser, WSSDoc, WSSList, WSSReferrer, WSSRelativeURL) select SITEGUID, WebGUID, wsslogdate, convert(nvarchar,cast(wsslogtime as datetime),108), wssuser, right(WSSDoc, len(WSSDoc) - charindex('.', WSSDoc)), ListGuid, ReferringURL, RelativeURL from dbSPSReportingStaging.dbo.tblWSSLogData update tblTempWSS_ToFactLoad set SiteID = DimSite.SiteSurKey from DimSite where tblTempWSS_ToFactLoad.SiteGUID = DimSite.SiteGUID

update tblTempWSS_ToFactLoad set DateID = DimDate.DateSurKey from DimDate where WSSDate = DimDate.DateFull

update tblTempWSS_ToFactLoad set TimeID = DimTime.TimeSurKey from DimTime where WSSTime = DimTime.TimeFull

update tblTempWSS_ToFactLoad set WebID = DimWeb.WebSurKey from DimWeb where tblTempWSS_ToFactLoad.WebGUID = DimWeb.WebGUID

update tblTempWSS_ToFactLoad set UserID = DimUser.UserSurKey from DimUser where WSSUser = DimUser.UserName

update tblTempWSS_ToFactLoad set FileID = DimFile.FileSurKey from DimFile where WSSDoc = DimFile.FileType

update tblTempWSS_ToFactLoad set ListID = DimList.ListSurKey from DimList where WSSList = DimList.ListGUID

update tblTempWSS_ToFactLoad set ReferrerID = DimReferrer.ReferrerSurKey from DimReferrer where WSSReferrer = DimReferrer.ReferrerURL

update tblTempWSS_ToFactLoad set RelativeURLID = DimFileName.FileNameSurKey from DimFileName where WSSRelativeURL = DimFileName.RelativeURL

--Handle nulls. update tblTempWSS_ToFactLoad set ListID = DimList.ListSurKey from DimList where DimList.ListTitle = 'No Title' and ListID is null

update tblTempWSS_ToFactLoad set WebID = DimWeb.WebSurKey from DimWeb where DimWeb.WebName = 'No WebName' and WebID is null

update tblTempWSS_ToFactLoad set FileID = DimFile.FileSurKey from DimFile where DimFile.FileType = 'No File' and FileID is null

update tblTempWSS_ToFactLoad set ReferrerID = DimReferrer.ReferrerSurKey from DimReferrer where tblTempWSS_ToFactLoad.ReferrerID is null and DimReferrer.ReferrerURL = 'No Referrer'

update tblTempWSS_ToFactLoad set RelativeURLID = DimFileName.FileNameSurKey from DimFileName where tblTempWSS_ToFactLoad.RelativeURLID is null and DimFileName.RelativeURL = 'No FileName'

update tblTempWSS_ToFactLoad set SiteID = DimSite.SiteSurKey from DimSite where SiteID is null and DimSite.SiteOwner = 'No Owner'

if 1<= (select count(*) from tblTempWSS_ToFactLoad        where (DateID is null or UserID is null or SiteID is null or ListID is Null or             FileID is null or WebID is null or TimeID is null or ReferrerID is null or            RelativeURLID is null)) Begin Rollback Transaction --Insert logging table message. Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactWSS - Update tblTempWSS_ToFactLoad', @StartDate, getdate, 'Null Values' RAISERROR ( 'Null Values Exist in tblTempWSS_ToFactLoad. Data not loaded', 16, 1 ) Return -1 end else Begin Commit Transaction --Insert into logging table. Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactWSS - Update tblTempWSS_ToFactLoad', @StartDate, getdate, 'Update Successful' End

Begin Transaction insert into FactWSS(DateSurKey, UserSurKey, SiteSurKey, ListSurKey, FileSurKey, WebSurKey, TimeSurKey,       ReferrerSurKey, FileNameSurKey) select DateID, UserID, SiteID, ListID, FileID, WebID, TimeID, ReferrerID, RelativeURLID from tblTempWSS_ToFactLoad

if @@error <> 0 Begin Rollback Transaction --Insert logging table message. Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactWSS - Insert Rows FactWSS', @StartDate, getdate, 'Errors Occurred' RAISERROR ( 'Errors occurred loading FactWSS. Data not loaded', 16, 1 ) Return -1 End else Begin Commit Transaction --Insert into logging table. Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactWSS - Insert Rows FactWSS', @StartDate, getdate, 'FactWSS Loaded Successfully' End Return 0 go </li>  Paste the following code sample into SQL Query Analyzer. Then, run the code by pressing F5. USE dbSPSReporting go

ALTER  PROCEDURE dbo.usp_Insert_FactFileStorage AS declare @StartDate datetime, @ProcessDate datetime

set @StartDate = getdate set @ProcessDate = getdate

Begin Transaction if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTempFileStorage_toFactStorage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblTempFileStorage_toFactStorage] if @@error <>0 Begin --Insert logging table message. Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactFileStorage - Dropping Table tblTempFileStorage_toFactStorage', @StartDate, getdate, 'Errors Occurred' RAISERROR ( 'Errors Were encountered dropping tblTempFileStorage_toFactStorage', 16, 1 ) Return -1 End else Commit Transaction

Begin Transaction create table dbo.tblTempFileStorage_toFactStorage (   RowID   int identity not null,    DocGUID uniqueidentifier null,    FileSize bigint not null,    FileDateTime datetime not null,    ListGUID uniqueIdentifier null,    FileType nvarchar(255) not null,    WebGUID uniqueIdentifier not null,    SiteGUID uniqueIdentifier not null,    RelativeUrl nvarchar(255) not null,    FileID int null,    DateID int null,    TimeID int null,    ListID int null,        WebID int null,    ArchID int null,    SiteID int null,    FileNameID bigint ) if @@error <>0 Begin --Insert logging table message. Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactFileStorage - Failed to Create Table tblTempFileStorage_toFactStorage', @StartDate, getdate, 'Errors Occurred' RAISERROR ( 'Failed to Create Table tblTempFileStorage_toFactStorage', 16, 1 ) Return -1 End else Commit Transaction

--Create indexes outside transaction. Create Index idx_nc_TmpFileStorage_1 on dbo.tblTempFileStorage_toFactStorage(ListGUID) Create Index idx_nc_TmpFileStorage_2 on dbo.tblTempFileStorage_toFactStorage(DocGUID) Create Index idx_nc_TmpFileStorage_3 on dbo.tblTempFileStorage_toFactStorage(SiteGUID)

Begin Transaction insert into tblTempFileStorage_toFactStorage (DocGUID, FileSize, FileDateTime, ListGUID, FileType, WebGUID, SiteGUID, RelativeUrl) select DocGUID, DocSize, @ProcessDate, ListGUID, DocType, tblDocs.WebGUID, SiteGUID, RelativeUrl from dbSPSReportingStaging.dbo.tblDocs tblDocs join dbSPSReportingStaging.dbo.tblWebs tblWebs on tblDocs.WebGUID = tblWebs.WebGUID update tblTempFileStorage_toFactStorage set FileID = DimFile.FileSurKey from DimFile, tblTempFileStorage_toFactStorage a   where a.FileType = DimFile.FileType update tblTempFileStorage_toFactStorage set DateID = DimDate.DateSurKey from DimDate where convert(varchar,FileDateTime,101) = convert(varchar,DateFull,101) update tblTempFileStorage_toFactStorage set TimeID = DimTime.TimeSurKey from DimTime where convert(varchar,FileDateTime,108) = convert(varchar,TimeFull,108) update tblTempFileStorage_toFactStorage set ListID = DimList.ListSurKey from tblTempFileStorage_toFactStorage a, DimList where a.ListGUID = DimList.ListGUID update tblTempFileStorage_toFactStorage set ListID = DimList.ListSurKey from DimList where ListID is null and DimList.ListTitle = 'No Title' update tblTempFileStorage_toFactStorage set WebID = DimWeb.WebSurKey from tblTempFileStorage_toFactStorage a, DimWeb where a.WebGUID = DimWeb.WebGUID update tblTempFileStorage_toFactStorage set ArchID = DimArch.ArchSurKey from tblTempFileStorage_toFactStorage a, (select ArchSurKey, SiteGUID   from DimArch, ( select distinct DatabaseName, VirtualServerName, IISServiceName, SiteGUID from dbSPSReportingStaging.dbo.tblDatabases DB join dbSPSReportingStaging.dbo.tblSites SITES on DB.DatabaseGUID = SITES.DatabaseGUID JOIN dbSPSReportingStaging.dbo.tblVirtualServers VS ON DB.VirtualServerGUID = VS.VirtualServerGUID ) tblArch   where DimArch.DatabaseName = tblArch.DatabaseName and           DimArch.IISServiceName = tblArch.IISServiceName and           DimArch.VirtualServerName = tblArch.VirtualServerName) DimArch where a.SiteGUID = DimArch.SiteGUID

update tblTempFileStorage_toFactStorage set ArchID = DimArch.ArchSurKey from DimArch where ArchID is null and DimArch.VirtualServerName = 'No VirtualServerName' update tblTempFileStorage_toFactStorage set SiteID = DimSite.SiteSurKey from tblTempFileStorage_toFactStorage a, DimSite where a.SiteGUID = DimSite.SiteGuid

update tblTempFileStorage_toFactStorage set FileNameID = DimFileName.FileNameSurKey from DimFileName, tblTempFileStorage_toFactStorage where tblTempFileStorage_toFactStorage.RelativeURL = DimFileName.RelativeURL

update tblTempFileStorage_toFactStorage set SiteID = DimSite.SiteSurKey from DimSite where SiteID is null and DimSite.SiteOwner = 'No Owner'

if 1<= (select count(*) from tblTempFileStorage_toFactStorage       where (DateID is null or SiteID is null or ListID is Null or             FileID is null or WebID is null or ArchID is null or            TimeID is null or FileNameID is null))

Begin Rollback Transaction --Insert logging table message. Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactFileStorage - Update tblTempFileStorage_toFactStorage', @StartDate, getdate, 'Null Values' RAISERROR ( 'Null Values Exist in tblTempFileStorage_toFactStorage. Data not loaded', 16, 1 ) Return -1 end else Begin Commit Transaction --Insert into logging table. Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactFileStorage - Update tblTempFileStorage_toFactStorage', @StartDate, getdate, 'Update Successful' End

Begin Transaction insert into FactFileStorage(FileSurKey, DateSurKey, TimeSurKey, ListSurKey, WebSurKey, ArchSurKey, SiteSurKey, FileNameSurKey, DiskSpaceUsed) select FileId, DateID, TimeID, ListID, WebID, ArchID, SiteID, FileNameID, FileSize from tblTempFileStorage_toFactStorage if @@error <> 0 Begin Rollback Transaction --Insert logging table message. Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactFileStorage - Insert Rows FactFileStorage', @StartDate, getdate, 'Errors Occurred' RAISERROR ( 'Errors occurred loading FactFileStorage. Data not loaded', 16, 1 ) Return -1 End else Begin Commit Transaction --Insert into logging table. Insert Into tblProcessAudit(StepName, StartDateTime, EndDateTime, StepCompletionStatus) select 'usp_Insert_FactFileStorage - Insert Rows FactFileStorage', @StartDate, getdate, 'FactFileStorage Loaded Successfully' End Return 0 go </li></ol>

Note The Microsoft SQL Server Report Pack for Microsoft Office SharePoint Portal Server 2003 and the Data Extraction Program (DEP) tool (Rpdataextraction.exe) are not supported by Microsoft Product Support Services. Microsoft gives no express warranties, guarantees or conditions regarding these tools. This software is licensed “as-is.” You bear the risk of using it.

<div class="status_section">

STATUS
Microsoft has confirmed that this is a bug in the Microsoft SQL Server Report Pack for Microsoft Office SharePoint Portal Server 2003.

<div class="references_section">