| 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.
You can create a script to delete these stored procedures when all ODBC
clients have disconnected from the server:
USE <database_name>
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
As a workaround, you can revoke permissions to create stored procedures from
the users and the SQL Server driver will not create these stored procedures.
The drawback is that your queries may run significantly slower.
Prepared execution is an efficient way to execute a statement more than once.
It is commonly used by vertical and customer applications to repeatedly
execute the same, parameterized SQL statement e.g. the MS Jet Engine.
You should run something like ODBC SPY or just enable the trace facility in
the ODBC manager to get an idea of what SQL statements are being passed to the
Server and determine whether disabling "prepared execution" will affect
performance.
Hope this helps,
Dennis.
[Posted by WWW Notes gateway]
|