PRB: 6.0 Transfer Manager With Dependencies Causes Error 145

• Microsoft SQL Server, version 6.0

SYMPTOMS

An attempt to use the SQL Transfer Manager to transfer a version 6.0 database with the "Include Dependencies" checkbox selected will fail. Examination of the transfer log shows the following error messages:

   Server message 145, severity 1, state 15:  Order-by
      items must appear in the select-list if SELECT
      DISTINCT is specified.
   Server message 207, severity 2, state 16:  Invalid
      column name 'tid'.
   Server message 208, severity 1, state 16:  Invalid
      object name #tempudt'.
   Server message 208, severity 1, state 16:  Invalid
      object name '#t1'.
   Server message 208, severity 1, state 16:  Invalid
      object name '#t1'.
   Server error 10007, severity 5:  General SQL Server
      error: Check messages from the SQL Server.

CAUSE

If your source server is Microsoft SQL Server version 6.0, the OBJECT60.SQL script must have been run on the server if you are including dependencies. Otherwise, SQL Transfer Manager tries to create the 4.21 dependency- evaluator stored procedure, which fails due to parser changes in version 6.0.

This is documented in the Microsoft SQL Server "Administrator's Companion," Chapter 11, under "Transferring Objects and Data Using SQL Transfer Manager".

WORKAROUND

Run the OBJECT60.SQL script located in the \SQL60\INSTALL directory. See Appendix A of the "Administrator's Companion," "Using SQL Administrator and SQL Object Manager" for details on installing this script.

Alternatively, you can use the 204 trace flag as documented in the "Transact-SQL Reference" manual under "Trace Flags."