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

This article was previously published under Q220163
This article has been archived. It is offered "as is" and will no longer be updated.
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

For additional information about generating scripts refer to the following:

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

Article ID: 220163 - Last Review: 01/16/2015 19:28:44 - Revision: 4.3

  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • kbnosurvey kbarchive kbpending kbprb KB220163
Feedback