Prev: HELP! "could not complete cursor operation because the set options have changed since the cursor was declared"
Next: Dateformat
From: C on 14 Jun 2006 11:41
Hi,

I have created a CLR Strored Procedure.

When I call the Stored Proc I get below error.

Msg 6522, Level 16, State 1, Procedure GetPhaseStatus, Line 0
A .NET Framework error occurred during execution of user defined routine or
aggregate 'GetPhaseStatus':
System.InvalidOperationException: The context connection is already in use.
System.InvalidOperationException:
at System.Data.SqlClient.SqlInternalConnectionSmi.Activate()
at
System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnectionString options, Object providerInfo, DbConnection owningConnection)
at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()


Below is the C# code for my Stored Proc.

[SqlProcedure(Name = "proc_phase_get_status")]
public static SqlInt32 GetPhaseStatus(int phase_id)
{
bool recordExists = false;

using (SqlConnection conn = new SqlConnection("context
connection=true;"))
using (SqlCommand cmd = conn.CreateCommand())
{
try
{

cmd.CommandText = "proc_phase_get";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter param1 = cmd.Parameters.Add("@phase_id",
System.Data.SqlDbType.Int, 4);
param1.Direction = param1.Direction =
System.Data.ParameterDirection.Input;
param1.Value = phase_id;

conn.Open();

//SqlContext.Pipe.Send(cmd.ExecuteReader());



using (SqlDataReader sqlDataReader = cmd.ExecuteReader())
{
while (sqlDataReader.Read())
{
recordExists = true;
}
}

if (recordExists == true)
{
//SqlContext.Pipe.Send("1");
return GetSiteStatus(phase_id);
} &


 | 
Pages: 1
Prev: HELP! "could not complete cursor operation because the set options have changed since the cursor was declared"
Next: Dateformat