BUG: OBJECT2.SQL May Fail to Execute the Last Statement

ID: Q112865

The information in this article applies to:

Microsoft SQL Server Programmer's Toolkit, version 4.2

BUG# 9906 (4.2)

SYMPTOMS

The OBJECT2.SQL script for SQL Object Manager may fail to execute the last statement if one of the stored procedures is not created

WORKAROUND

Identify the problem that causes the stored procedure creation to fail, correct the problem, and re-run OBJECT2.SQL if possible.

For example, the current SQL Transfer Manager requires OBJECT2.SQL to be installed on the source server if the user does not have CREATE PROCEDURE permission. However, since there are no reserved, used, rows columns in sysindexes on Sybase version 4.8 or later, one of the stored procedures sp_MStable_properties which refers to these columns will always fail.

Running OBJECT2.SQL on Sybase will not print the error message indicating that it has failed. To workaround this particular problem, grant the user Create Procedure permission or explicitly grant execute permission to the stored procedures that have been created successfully

STATUS

Microsoft has confirmed this to be a problem in SQL Object Manager version 4.2 for Windows NT and Windows. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The last statement in OBJECT2.SQL checks to see if all the stored procedures are created. If not, it will print out an error message and drop sp_MSOM_version; otherwise, it will grant the execution permissions on these stored procedures to public and print out a message indicating the script has been successfully installed.

The last statement is as follows:


   if exists (select * from #spmissing)
   begin
      print ""
      print ""
      print " =====================  ERRORS!  ===================="
      print "       The following objects were not created."
      print " Sql Object Manager will not run against this server."
      print ""
      select * from #spmissing
      drop procedure sp_MSOM_version
   end
   else
   begin
      print ""
      print "Granting execute permissions on procedures"
      grant execute on sp_MScheck_OM to public
      grant execute on sp_MSuser_info to public
      grant execute on sp_MStable_properties to public
      grant execute on sp_MScolumn_properties to public
      grant execute on sp_MSobject_list to public
      grant execute on sp_MSobjecttype_name to public
      grant execute on sp_MStable_permissions to public
      grant execute on sp_MSobject_dependencies to public
      grant execute on sp_MSOM_version to public
      print ""
      print ""
      print " Successful installation."
      exec sp_MSOM_version
   end
   go

However, when there is one stored procedure missing, the whole IF...ELSE statement will fail with a syntax error, thus neither of the parts get executed. The user will not receive any message indicating that the installation of the script has failed.


Keywords          : kbprg SSrvSQL_Admin kbbug4.20 
Version           : 4.2
Platform          : WINDOWS 
Issue type        :

Last Reviewed: March 19, 1999