Microsoft KB Archive/112865

{|
 * width="100%"|

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

 * }

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 : kbprogramming

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbSQLServPTK420