PRB: Transfer Objects Fails if SP Preceded by Multi-line Comment

• Microsoft SQL Server versions 7.0, 6.5

SYMPTOMS

Transfer of objects using the Transfer Manager in SQL 6.5 or Data Transformation Services (DTS) in SQL 7.0 is unable to transfer a stored procedure that is owned by a user other than 'dbo' when the stored procedure is preceded by a multi-line comment. The transfer will error out with the following message in the log file:

::Error::
In file <Server>.<DB>.PRC in statement on line xxxx
Transfer Status: Creating Stored Procedures on destination database
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near '*'.
[Microsoft][ODBC SQL Server Driver][SQL Server]'CREATE PROCEDURE' must be the first statement in a query batch.

CAUSE

This error occurs any time the stored procedure being transferred is not owned by 'dbo'.

For example the following procedure will generate an error during transfer:

/***
-- This won't work.
***/ 
CREATE PROCEDURE user1.sp_getversion AS
SELECT @@version 

WORKAROUND

To avoid this problem it is recommended that all comments for stored procedures be placed inline with the procedure statements, after the CREATE PROC command as follows:

  CREATE PROCEDURE dbo.sp_getversion AS
     /***
     -- This is the recommended location for the first line of 
  comments in an SP.
     ***/ 
     SELECT @@version 

Additional query words:

Keywords : SSrvTrans kbbug6.50 kbbug7.00 kbSQLServ650bug kbSQLServ700bug
Version : winnt:6.5,7.0
Platform : winnt
Issue type : kbprb