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

Article translations Article translations
Article ID: 906508 - View products that this article applies to.
Bug #: 403546 (SQLBUDT)
Expand all | Collapse all

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://www.microsoft.com/PRODUCTS/info/product.aspx?view=22&pcid=2f7ba03d-1762-4ae9-b059-23aa198c08fb

Properties

Article ID: 906508 - Last Review: February 2, 2007 - Revision: 3.4
APPLIES TO
  • Microsoft Office SharePoint Portal Server 2003
  • Microsoft SQL Server 2000 Reporting Services
Keywords: 
kbtshoot kbpending kbbug KB906508

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com