ACC: Unexpected Stored Procedures Found in SysObjects Table

Last reviewed: May 20, 1997
Article ID: Q96898

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

Unexpected stored procedures with the general format ODBC#<login ID>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 "ODBC%"

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 "with no wait" 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 "Joe." To delete them:

Create a file called DROP_GEN.SQL:

      use <dbname>
      go
      select char(13) + char(10) + "go" +
         char(13) + char(10)+ "drop procedure " + name +
         char(13) + char(10) + "go" +
         char(13) + char(10)
      from sysobjects
      where name like "ODBC%"
      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<server> -Usa -P<password> -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

Finally, run the output file generated in step 3 through ISQL to drop the procedures:

ISQL -S<server> -Usa -P<password> -iDROPPROC.SQL

Keywords : kbusage OdbcOthr
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 20, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.