Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SQL Server 2005 CLR Stored procedure - Select and then update.

Posted on 2006-11-01
Medium Priority
Last Modified: 2012-06-27
I am trying to write a stored procedure that selects from one table, reads through that table record by record, and uses info from each record to create an update statement which i then run.

My problem is that SQL server is not letting me open more than one SqlConnection at a time.

Here is the code i have:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
    public static void Concat()
        SqlConnection conn = new SqlConnection("context connection=true");        
        SqlCommand cmd = new SqlCommand();
        SqlCommand cmd2 = new SqlCommand();
        SqlDataReader dataReader;
        SqlString strPm = "";

        cmd.Connection = conn;
        cmd2.Connection = conn;
        cmd.CommandText = "SELECT BLAH FROM DB.MyTable WHERE Blah2 != 1";
        dataReader = cmd.ExecuteReader();

        while (dataReader.Read())
            if (!dataReader.IsDBNull(1))
                strPm = Convert.ToString(dataReader["BLAH"]);
            cmd2.CommandText = "update MyTable2 set BLAH = '" + strPm + "'";

            cmd2.ExecuteNonQuery();  // <-- Fails here



when i run this procedure in SQL Server Management Studio it says:

Msg 6549, Level 16, State 1, Procedure Concat, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'Concat':
System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.
   at System.Data.SqlClient.SqlInternalConnectionSmi.ValidateConnectionForExecute(SqlCommand command)
   at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
   at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at StoredProcedures.Concat()
. User transaction, if any, will be rolled back.

Does anyone know how to do this?

Question by:dave4dl
  • 6
  • 3
  • 2
LVL 29

Expert Comment

ID: 17852671
Quite simply you cannot have more than one active datareader on a single connection. Rather create a new connection object (you aren't - you are setting cmd2.Connection to the same connection object) and use that for the update.
LVL 15

Author Comment

ID: 17852881
Thanks for the post Nightman,

I gave that a shot when this problem cropped up and i got the error message:

Msg 6522, Level 16, State 1, Procedure Concat, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'Concat':
System.InvalidOperationException: The context connection is already in use.
   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()
   at StoredProcedures.Concat()
Does the same not happen for you?
LVL 15

Author Comment

ID: 17852889
I guess i should say i added

SqlConnection conn2 = new SqlConnection("context connection=true");

and later opened that connection
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

LVL 29

Accepted Solution

Nightman earned 1200 total points
ID: 17852996
Ahh - missed that - context connection.

You can only have one open context connection at a time within a clr procedure ("context connection=true").  

have a look at http://codebetter.com/blogs/sahil.malik/archive/2005/07/26/129824.aspx for a good explanation (and see the comments from Niels Berglund at the bottom)
LVL 15

Author Comment

ID: 17853273
I tried putting all my update statements into an array so i could execute them later but the stored proc failed saying it ran out of memory (there are over a million rows in this table).

I could save them to a file and execute that file later but i really dont want to have my stored procs storing stuff temporarily in holding files (because it feels like the wrong thing to do and because i forsee a lot of problems like concurrent runs, folder existence/permissions, disk space, etc).  This seems like it should be such a simple thing to do.
LVL 15

Author Comment

ID: 17853363
The codebetter link seems to just say that what i am trying to do (an update that runs for each record within a datareader "Read" loop) is not possible.
LVL 30

Expert Comment

ID: 17853893
You may be able to do this directly (and far more efficiently) using T-SQL.

The general syntax for updating one table from another table is:

SET Col1 = TableB.Col2
WHERE TableA.Col6 = TableB.Col7

It may be that you are doing something more complicated than that, or maybe you want to learn CLR programmign in SQL. Not sure. If this looks like an option for you let me know and I can expand on it.
LVL 15

Author Comment

ID: 17854087
thanks for the post nmcdermaid.

Your right that i should just use sql if what i did in my example code for the post was all i needed to do.
Unfortunately i do need to use a clr stored proc because i am actually doing more than i posted.  In order to simplify my post i took out the more complex logic i was using that combined values from multiple rows.  
LVL 30

Assisted Solution

nmcdermaid earned 800 total points
ID: 17855303
You can probably still do it.... depends what you're doing. I can expand more if you give me an idea of your business rules.

If you are concatenating strings from multiple rows into one row it may eb a little trickier but you can use a T-SQL cursor.

It will still be quicker and you won't run into out of memory issues.

I don't mind helping if you want to expand on the logic. In fact I would be very interested to see if there is a T-SQL solution to your issue.

LVL 29

Expert Comment

ID: 17857535
I agree with nmcdermaid - looking at the logic there is probably a set-based T-SQL method to do this (might not be, but even a T-SQL cursor would be more efficient). Post some of the logic here and I'm sure that we can help.

LVL 15

Author Comment

ID: 17903078
Thanks for your posts guys.

Actually i already had a solution that uses sql and cursors but it is really slow.  I needed to combining parts of varchars from multiple rows and the performance was really slow.  I have noticed that sql server string functions are less than speedy so i thought i could get a performance gain by coding it in a clr stored procedure.  

What i ended up doing was breaking the tasks into smaller chunks and using a clr function with a clr aggregate to get what i was looking for (and the performance gain was there but not very significant).

Thanks again for your help!

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question