SQL Server Error 8179: Could not find prepared statement with handle x

S

Sherif ElMetainy

Hello

I have an application using ADO.NET (.NET version 1.1 (SP1) on Windows 2000
Server SP4) and SQL Server 2000 Standard Edition (SP3 on another Windows
2000 Server SP4). The application is a windows service, that has some
commands that are executed thousands of times so I use the Prepare method to
improve performance. The application worked fine for 3 months, then I
started getting SqlExceptions 2 days ago with error 8179 (Could not find
prepared statement with handle x) where x is a number that changes for each
exception. I restarted SQL server, same problem. When I commented the lines
calling Prepare (please see below) everything worked fine again, but I want
to know why?. The code below is an example, the exception is thrown with
many statements at different parts of the program. Can anyone tell me why
this error started occuring? What is the effect of not using prepare ?


// Preparing the command
SqlConnection dataConnection = GetConnection();
SqlCommand deleteAccountCommand = dataConnection.CreateCommand();
deleteAccountCommand.CommandText = "DELETE FROM [Accounts] WHERE [AccountId]
= @id";

SqlParameter idParam = deleteAccountCommand.CreateParameter();
idParam.ParameterName = "@id";
idParam.SqlDbType = SqlDbType.Int;
idParam.Size = 4;
deleteAccountCommand.Parameters.Add(idParam);
deleteAccountCommand.Prepare();

//Executing the command (in another place) this code is called many times
deleteAccountCommand.Parameters[0].Value = id;
deleteAccountCommand.ExecuteNonQuery(); // SqlException thrown here


Best regards,
Sherif
 
E

Elton W

Hi Sherif,

I think you might fix your problem by change the size of
the parameter.

Before you call Prepare, specify the data type of each
parameter in the statement to be prepared. For each
parameter that has a variable length data type, you must
set the Size property to the maximum size needed. Prepare
returns an error if these conditions are not met.

If you call an Execute method after calling Prepare, any
parameter value that is larger than the value specified by
the Size property is automatically truncated to the
original specified size of the parameter, and no
truncation errors are returned.
 
S

Sherif ElMetainy

Hi Elton

Thanks for the reply. I know about the size, and I make sure that I set the
size correctly, for example in my original post, I set the size of an int
parameter to 4. And as I said the application worked for 3 months without a
problem, then suddenly I started getting these exception, and it was solved
when i commented out the prepare method call.

Best regards,
Sherif

Elton W said:
Hi Sherif,

I think you might fix your problem by change the size of
the parameter.

Before you call Prepare, specify the data type of each
parameter in the statement to be prepared. For each
parameter that has a variable length data type, you must
set the Size property to the maximum size needed. Prepare
returns an error if these conditions are not met.

If you call an Execute method after calling Prepare, any
parameter value that is larger than the value specified by
the Size property is automatically truncated to the
original specified size of the parameter, and no
truncation errors are returned.

-----Original Message-----
Hello

I have an application using ADO.NET (.NET version 1.1 (SP1) on Windows 2000
Server SP4) and SQL Server 2000 Standard Edition (SP3 on another Windows
2000 Server SP4). The application is a windows service, that has some
commands that are executed thousands of times so I use the Prepare method to
improve performance. The application worked fine for 3 months, then I
started getting SqlExceptions 2 days ago with error 8179 (Could not find
prepared statement with handle x) where x is a number that changes for each
exception. I restarted SQL server, same problem. When I commented the lines
calling Prepare (please see below) everything worked fine again, but I want
to know why?. The code below is an example, the exception is thrown with
many statements at different parts of the program. Can anyone tell me why
this error started occuring? What is the effect of not using prepare ?


// Preparing the command
SqlConnection dataConnection = GetConnection();
SqlCommand deleteAccountCommand = dataConnection.CreateCommand();
deleteAccountCommand.CommandText = "DELETE FROM [Accounts] WHERE [AccountId]
= @id";

SqlParameter idParam = deleteAccountCommand.CreateParameter();
idParam.ParameterName = "@id";
idParam.SqlDbType = SqlDbType.Int;
idParam.Size = 4;
deleteAccountCommand.Parameters.Add(idParam);
deleteAccountCommand.Prepare();

//Executing the command (in another place) this code is called many times
deleteAccountCommand.Parameters[0].Value = id;
deleteAccountCommand.ExecuteNonQuery(); // SqlException thrown here


Best regards,
Sherif


.
Click to expand...
Click to expand...
 
Top