PRB: DTS Does not Copy Identity, Indexes, Primary Key or Other Constraints

• Microsoft SQL Server version 7.0

SYMPTOMS

When you run the Data Transformation Services (DTS) Wizard from SQL Enterprise Manager and copy tables from a SQL Server 6.5 source to a SQL Server 7.0 destination, DTS does not copy identity attributes, indexes, primary keys, or other constraints.

CAUSE

This is by design, as documented in SQL Server 7.0 Books Online:

Transferring Database Objects

When using heterogeneous data sources, the built-in facilities of DTS only move table definitions and data. To transfer other objects such as indexes, constraints, and views you must use methods such as specifying tasks that execute the SQL statements needed to create these objects on the destination data source. However, if both the source and destination are SQL Server 7.0 data sources, you can define a Transfer SQL Server Objects task to transfer indexes, views, logins, stored procedures, triggers, rules, defaults, constraints, and user-defined data types in addition to transferring the data.

WORKAROUND

Here are two ways to work around this behavior:

• When both the source and the destination are SQL Server 7.0, you can choose to transfer objects (the third option in the DTS wizard), check the options to transfer primary keys and choose to include all dependant objects.
  
  "Transfer objects" (the third option in the DTS wizard) is not available for a SQL 6.5 data source. You can still select the Transform button and modify the create table script to add the primary key and identity information.



• Another workaround is to use the \Mssql7\Upgrade\Scptxfr.exe to script 6.x databases to generate the schema. You can then create the object on SQL 7.0 and use DTS to move the data. For example, the following command generates scripts for all the objects in pubs database and saves those scripts in a single file:

  
  C:\MSSQL7\UPGRADE>scptxfr.exe /s <SQLServer> /d Pubs /P <Sa's pssword> /f <filename> 

MORE INFORMATION

For details on using other options with this tool, refer to the parameters by using 'scptxfr.exe -?'.

Here is a list of the parameters and a description for each parameter:

SCPTXFR	/s <server> /d <database> {[/I] | [/P <password>]} 

	{[/F <script files directory>] | [/f <single script file>]}

	/q /r /O /T /A /E /C <CodePage> /N /X /H /G /?




/s  -  Indicates the source server to connect to.


/d  -  Indicates the source database to script.


/I  -  Use integrated security.


/P  -  Password to use for 'sa'. Note that login ID is always 
       'sa'. 


       If /P is not used or if a password does not follow the 
       flag, a null password is used. Not compatible with /I.


/F  -  The directory into which the script files should be 
       generated. This means one file is generated for each 
       category of objects.


/f  -  The single file into which all scripts are to be saved.
       Not compatible with /F.


/q  -  Use quoted identifiers in the generated scripts.


/r  -  Include drop statements for the objects in the script.


/O  -  Generate Original Equipment Manufacturer (OEM)script files.
       Cannot be used with /A or /T. This is the default behavior.


/T  -  Generate UNICODE script files. Cannot be used with /A or 
       /O.


/A  -  Generate ANSI script files. Cannot be used with /T or /O. 
 

/?  -  Command line help.


/E  -  Stop scripting when error occurs.
       Default behavior is to log the error and continue.


/C  -  Indicate the CodePage that overrides the server CodePage.


/N  -  Generate ANSI PADDING.


/X  -  Script stored procedures (SPs) and extended stored 
       procedures (XPs) to separate files.


/H  -  Generate script files without header (the default is: with 
       header).


/G  -  Use the specified server name as the prefix for the 
       generated output files (to handle dashes in server name).

 

REFERENCES

SQL Server Books Online; topic: "How to generate a script (Enterprise Manager)"

Additional query words:

Keywords : kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbprb