ACC: Unexpected Stored Procedures Found in SysObjects Table (96898)

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