SOS!! Proc written in C# throwing errors !!

Hot Topic

How to include SSIS into install file?
Query cost
CLR Stored Procedures & VS2005 ASP.NET designer support?
Report Model Deployment Error
prorating the date fields with cost
Scheduled Job Not Working
Why can't query analyzer be made to work for SQL 2005?
AMO documentation
Stored Procedure Version Control
printing reports without previewing in a .NET app

SQL Server

The connection manager 'OLEDB' is not properly installed on this computer
beginner question
Device error or device off-line ???
How to find out whether a report is being generated ON-DEMAND or ON-SCHEDULE?
ntext & nvarchar(max) storage methods
Implicit Keep Together For Export?
Problems with restoring and deleting old keys- reporting services 2005
How to run DTS package/upgrade in SQL 2005.
Debugging SP On server from workstation using Query Analyzer
File Enumerator does not show after install of SP1

SOS!! Proc written in C# throwing errors !!

I have a stored proc which is being called by reporting services. It is written in C# and is a wrapper for executing other procs dynamically.It generates the following error time and again. Any help would be hugely appreciated as this is a production level issue.

Thanks

Shai

Error Message:

System.InvalidOperationException: The context connection is already in use. System.InvalidOperationException: 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() at StoredProcedures.USP_GenerateReport(String procname, String parameters) .



The Code



public static void USP_GenerateReport(string procname, string parameters)

{

string[] parameterarray;

string parametername = "", parametervalue = "", separator = ",";

//Create a connection in context of the calling app

SqlConnection objConn = new SqlConnection("Context connection= true");

objConn.Open();

try

{

//Create command object

SqlCommand objCmd = new SqlCommand(procname, objConn);

objCmd.CommandType = CommandType.StoredProcedure;

//Find the parameters for the target proc and add values

parameterarray = parameters.Split(separator.ToCharArray());

 

for (int i = 0; i <= parameterarray.Length - 1; i++)

{

parametername = parameterarrayIdea.Substring(0, parameterarrayIdea.IndexOf("="));

parametervalue = parameterarrayIdea.Substring(parameterarrayIdea.IndexOf("=") + 1, parameterarrayIdea.Length - parameterarrayIdea.IndexOf("=") - 1);

objCmd.Parameters.AddWithValue(parametername, parametervalue);

}

 

 

SqlContext.Pipe.ExecuteAndSend(objCmd);

}

catch(Exception e)

{

objConn.Close();

}

finally{

objConn.Close();

}

Answer this question

SOS!! Proc written in C# throwing errors !!

  • hatem elshenawy

    hey! i also ran into the same problem. In my case the function works fine as long as i let it complete its execution. Once it is interrupted in between, subsequent invocations give 'context connection in use' error.

  • Dave Rollins

    Are you calling this code from T-SQL as a stored-proc or from another CLR function

    You can have only one context connection open per stored-proc invocation, so that might be getting in the way.



  • SOS!! Proc written in C# throwing errors !!
Answer this question