You are currently offline, waiting for your internet to reconnect

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

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

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.
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:
  1. Paste the following code sample into SQL Query Analyzer. Then, run the code by pressing F5.
    USE dbSPSReportinggoALTER    PROCEDURE dbo.usp_Insert_FactWSS ASdeclare @StartDate datetimeset @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 TransactionBegin 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 -1endelseBegin	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  Endelse  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 0go
  2. Paste the following code sample into SQL Query Analyzer. Then, run the code by pressing F5.
    USE dbSPSReportinggoALTER   PROCEDURE dbo.usp_Insert_FactFileStorage ASdeclare @StartDate datetime, @ProcessDate datetimeset @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 TransactionBegin Transactioncreate 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 -1endelseBegin	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  Endelse  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 0go
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.
STATUS
Microsoft has confirmed that this is a bug in the Microsoft SQL Server Report Pack for Microsoft Office SharePoint Portal Server 2003.
REFERENCES
For more information, visit the following Microsoft Web sites:
Properties

Article ID: 906508 - Last Review: 02/02/2007 03:19:00 - Revision: 3.4

  • Microsoft Office SharePoint Portal Server 2003
  • Microsoft SQL Server 2000 Reporting Services
  • kbtshoot kbpending kbbug KB906508
Feedback