Microsoft KB Archive/115907

= INF: ODBC Stored Procedures odbc#userid######## on SQL Server =

Article ID: 115907

Article Last Modified on 8/27/1999

-

APPLIES TO


 * Microsoft Open Database Connectivity 1.0

-



This article was previously published under Q115907



SUMMARY
When an ODBC enabled application uses prepared execution to execute an SQL query on SQL Server and the user has permission to create stored procedures, the ODBC SQL Server driver creates a stored procedure with a name like "odbc#userid########" (for example, odbc#sa2147024896) on SQL Server and executes that stored procedure.

The stored procedure is created in response to a SQLPrepare ODBC call and is executed when SQLExecute is called. Normally, when the connection is closed, the driver drops these stored procedures. Also, if the user does not have Create Proc permissions, the stored procedures are not created and SQL statements are executed directly.

However, under certain situations when a connection might be terminated without closing the connection, the driver is not able to delete the stored procedures. This can happen if the client process terminates abnormally and is a more common occurrence in a development environment.

This article describes how to remove these stored procedures from the SQL Server.



MORE INFORMATION
To remove these stored procedures, follow these steps. Make sure no ODBC client is connected to the server when the following steps are carried out.

  Run the following script, with actual database name in the USE command, using ISQL.EXE or any other SQL Server client tool and save the output so generated in a file called DROP.SQL: USE  go     SELECT CHAR(13) + CHAR(10)+ "DROP PROCEDURE " + NAME + CHAR(13) + CHAR(10) + "go" + CHAR(13) + CHAR(10) FROM sysobjects WHERE name LIKE "ODBC%" go                         Modify the DROP.SQL script so generated to include a USE  followed by a go at the top of the file. Remove any "---" lines generated by the tool to separate the header and the results. Delete any "(X row(s) affected)" line if generated in the output by the tool. Then, the DROP.SQL script file should look something like: USE  go

drop procedure odbc#sa1855970 go

drop procedure odbc#sa1232424 go

...                         Run the DROP.SQL as the input script using a SQL client tool. For example, using ISQL.EXE: isql -S -Usa -P -idrop.sql and verify that no errors were reported.

NOTE: If permission to create stored procedures is revoked from the users, the SQL Server driver will not create these stored procedures. This can be used as a workaround if it is desired that no stored procedures be created by the driver. 

Additional query words: 1.01.2807 VB Visual Basic Access Excel Fox MSVC

Keywords: KB115907

-

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

© Microsoft Corporation. All rights reserved.