Microsoft KB Archive/96898

= ACC: Unexpected Stored Procedures Found in SysObjects Table =

Article ID: 96898

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q96898



SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

Unexpected stored procedures with the general format ODBC#nnnnnnn are found when you query the SysObjects table in SQL Server. For example, if you issue the following query from SQL Administrator Facility (SAF)   SELECT * FROM SysObjects WHERE Name LIKE &quot;ODBC%&quot; you see stored procedures such as:   ODBC#sa24518 ODBC#sa2334 ODBC#sa17854 This article describes what these stored procedures are and how to delete them.



MORE INFORMATION
When Microsoft Access runs a parameter query, it calls the ODBC function SQLPrepare, which creates and compiles stored procedures. These stored procedures pass the proper parameter arguments to ensure proper execution of the query.

These stored procedures are normally deleted once the ODBC connection is closed. However, if any action, including the following, causes the ODBC connection to be dropped abnormally, the stored procedures may not be deleted:
 * The client (Microsoft Access) system is restarted.
 * SQL Server is shutdown using the &quot;with no wait&quot; command.
 * The network connection breaks.

You can use SAF to drop the stored procedures manually. The command syntax is   DROP PROCedure [owner.]procedure_name[, [owner.]procedure_name...] where procedure_name is the procedure you are removing. For example   DROP PROC ODBC#sa24518, ODBC#sa2334 removes two stored procedures, ODBC#sa24518 and ODBC#sa2334.

If there are a number of stored procedures to delete, you can automate the process somewhat by using SQL Server to create a script to issue the DROP PROC statements.

The example below assumes that you have a number of stored procedures created by a user logged in as &quot;Joe.&quot; To delete them:   Create a file called DROP_GEN.SQL:      use go     select char(13) + char(10) + &quot;go&quot; + char(13) + char(10)+ &quot;drop procedure &quot; + name + char(13) + char(10) + &quot;go&quot; + char(13) + char(10) from sysobjects where name like &quot;ODBC%&quot; go NOTE: By adding the GO statement before and after the drop statement, you eliminate the need to edit the output file of the column header information.  Run this script using ISQL:

ISQL -S -Usa -P -iDROP_GEN.SQL -oDROPPROC.SQL  This creates an output file containing:      1> 2> 1> 2> 3> 4> 5> 6> 7> go     drop procedure odbc#JOE61375 go

go     drop procedure odbc#JOE65224 go

go     drop procedure odbc#JOE67088 go

go     drop procedure odbc#JOE69907 go                   </li> Finally, run the output file generated in step 3 through ISQL to drop the procedures:

ISQL -S -Usa -P -iDROPPROC.SQL</li></ol>

Keywords: kbinfo kbusage KB96898

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.