Microsoft KB Archive/300192

= BUG: DTS Import/Export Wizard or TransferObjectsTask Fails to Maintain Filegroup Settings for Transferred Objects =

Article ID: 300192

Article Last Modified on 10/29/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q300192



BUG #: 354301 (SHILOH_BUGS)

BUG #: 101735 (SQLBUG_70)



SYMPTOMS
If you use the Data Transformation Services (DTS) Import Wizard or the DTS Export Wizard to transfer objects from one database to another, the Filegroup settings for the object are not maintained even if the destination database does have the same filegroups.

Programs scripted using the TransferObjectsTask object from the DTS Package Object Library also fail to maintain the Filegroup settings for objects.



CAUSE
The wizards use the DTS Package Object Library to transfer objects between databases. The methods used are:
 * DTS.TransferObjectsTask2

-and-


 * DTS.TransferObjectsTask.

Both of these methods, with or without the ScriptOptionEx property set to DTSTransfer_ScriptEx_NoFG, place the object on the primary Filegroup.



WORKAROUND
To work around this behavior, script out the objects on the source database and apply the scripts to the destination database. The Filegroup settings for the objects are now preserved. Use DTS to copy the data over from the source to the destination.

The Copy Database Wizard does maintain the Filegroup settings for all the objects in the database. However, using the Copy Database Wizard may not be convenient for the following reasons:


 * The Copy Database Wizard requires you to transfer the whole database to the destination server.


 * You cannot copy the database to the same server.

Using the SQL Server 2000 Enterprise Manager you can change the Filegroup settings for tables. Changing the Filegroup settings involves creating a new table with the correct Filegroup settings and then moving the data back into the table. Note that this may be too resource intensive a workaround for large tables.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce Behavior
  Create a database named MYDB1 with Filegroups named [one] and [two]: CREATE DATABASE [mydb1] ON (NAME = N'mydb1',     FILENAME = N'c:\mydb1.MDF', SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'mydb1_Log', FILENAME = N'c:\mydb1_Log.LDF',   SIZE = 1, FILEGROWTH = 10%) GO

ALTER DATABASE [mydb1] ADD FILEGROUP [one] GO ALTER DATABASE [mydb1] ADD FILE(NAME = N'mydb1_fgone',    FILENAME = N'c:\mydb1_fgone_Data.NDF', SIZE = 1,     FILEGROWTH = 10%) TO FILEGROUP [one] GO

ALTER DATABASE [mydb1] ADD FILEGROUP [two] GO ALTER DATABASE [mydb1] ADD FILE(NAME = N'mydb1_fgtwo',    FILENAME = N'c:\mydb1_fgtwo_Data.NDF', SIZE = 1,     FILEGROWTH = 10%) TO FILEGROUP [two] GO   Create another database with the same structure: CREATE DATABASE [mydb2] ON (NAME = N'mydb2',     FILENAME = N'c:\mydb2.MDF', SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'mydb2_Log', FILENAME = N'c:\mydb2_Log.LDF',   SIZE = 1, FILEGROWTH = 10%) GO

ALTER DATABASE [mydb2] ADD FILEGROUP [one] GO ALTER DATABASE [mydb2] ADD FILE(NAME = N'mydb2_fgone',    FILENAME = N'c:\mydb2_fgone_Data.NDF', SIZE = 1,     FILEGROWTH = 10%) TO FILEGROUP [one] GO

ALTER DATABASE [mydb2] ADD FILEGROUP [two] GO ALTER DATABASE [mydb2] ADD FILE(NAME = N'mydb2_fgtwo',    FILENAME = N'c:\mydb2_fgtwo_Data.NDF', SIZE = 1,     FILEGROWTH = 10%) TO FILEGROUP [two] GO   Create the source TABLE to be copied by DTS on Filegroup [one].

USE mydb1 GO

CREATE TABLE [dbo].[Table1] (   [x] [char] (10) NOT NULL,    [y] [char] (10) NULL ) ON [one] GO   You do not have to complete this step, but here is some test data: INSERT INTO Table1 Values('a','x') INSERT INTO Table1 Values('b','w') INSERT INTO Table1 Values('c','v')   Run the following Microsoft Visual Basic script from Visual Basic and then proceed to step 12. The Visual Basic code uses Trusted Connections so make sure this is not a problem.

NOTE: For instructions about how to use the Import/Export Wizard to copy Table1 from mydb1 to mydb2 proceed to step 6.

Private Sub RunTransfer(ByVal objPackage) Dim objStep        As DTS.Step Dim objTask        As DTS.Task Dim strServerName  As String Dim objXferObj

'Create step and task Set objStep = objPackage.Steps.New Set objTask = objPackage.Tasks.New(&quot;DTSTransferObjectsTask&quot;) Set objXferObj = objTask.CustomTask

strServerName = InputBox(&quot;Name of the source server ex. myserv1\instance1:&quot;) 'Configure transfer objects task With objXferObj .Name = &quot;XferObjTask&quot; .SourceServer = strServerName .SourceUseTrustedConnection = True .SourceDatabase = &quot;mydb1&quot; .DestinationServer = strServerName .DestinationUseTrustedConnection = True .DestinationDatabase = &quot;mydb2&quot; .ScriptFileDirectory = &quot;c:\winnt\temp&quot; .CopyAllObjects = False .IncludeDependencies = False .IncludeLogins = False .IncludeUsers = False .DropDestinationObjectsFirst = True .CopySchema = True .CopyData = DTSTransfer_ReplaceData If MsgBox(&quot;Use the No Filegroup for replication option?&quot;, vbYesNo) = vbYes Then .ScriptOptionEx = DTSTransfer_ScriptEx_NoFG End If   .AddObjectForTransfer &quot;Table1&quot;, &quot;dbo&quot;, DTSSQLObj_UserTable End With

'Link step to task objStep.TaskName = objXferObj.Name objStep.Name = &quot;XferObjStep&quot; objPackage.Steps.Add objStep objPackage.Tasks.Add objTask End Sub

Private Sub Form_Load Dim x

If MsgBox(&quot;Select Yes for TransferObjectsTask2 or No for TransferObjectsTask.&quot; & _   vbCrLf & &quot;Note: Choosing Yes requires the 8.0 DTS Package Object Library&quot;, _    vbYesNo) = vbYes Then Set x = New DTS.Package2 Else Set x = New DTS.Package End If

x.Name = &quot;testpkgforfg&quot; Call RunTransfer(x) x.Execute End Sub </li> Open the DTS Import/Export Wizard and connect to the server that has mydb1 and mydb2, which were created previously.</li> Select mydb1 as the source and select mydb2 as the destination. Click Next.</li> Select the Copy table(s) and view(s) from the source database option, and then click Next.</li> Select Table1 from mydb1 in the Source column. The default should be Table1 on mydb2. Click Next</li> Click Next. The default should have Run Immediately selected. Click Next.</li> Click Finish. The package should execute and transfer Table1 to mydb2. Click Done.</li> Select the properties for Table1. On the source and the destination the Filegroup setting is not preserved.</li></ol>

Keywords: kbbug kbpending KB300192

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.