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


The information in this article 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.

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

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

SharePoint Portal Server
http://office.microsoft.com/en-us/FX010909721033.aspx

Modification Type:MajorLast Reviewed:12/29/2005
Keywords:kbtshoot kbpending kbbug KB906508 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.