Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-11-01
11
Medium Priority
?
1,522 Views
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
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void Concat()
    {
        SqlConnection conn = new SqlConnection("context connection=true");        
        SqlCommand cmd = new SqlCommand();
        SqlCommand cmd2 = new SqlCommand();
        SqlDataReader dataReader;
        SqlString strPm = "";

        conn.Open();
        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
        }

        dataReader.Close();
        conn.Close();

--------------------------------------------------------------------------------------------------------------

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.
System.InvalidOperationException:
   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?

0
Comment
Question by:dave4dl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
11 Comments
 
LVL 29

Expert Comment

by:Nightman
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.
0
 
LVL 15

Author Comment

by:dave4dl
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.
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()
   at StoredProcedures.Concat()
-------------------------------------------------------------------------------------------------------------------
Does the same not happen for you?
0
 
LVL 15

Author Comment

by:dave4dl
ID: 17852889
I guess i should say i added

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

and later opened that connection
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 29

Accepted Solution

by:
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)
0
 
LVL 15

Author Comment

by:dave4dl
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.
0
 
LVL 15

Author Comment

by:dave4dl
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.
0
 
LVL 30

Expert Comment

by:nmcdermaid
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:

UPDATE TableA
SET Col1 = TableB.Col2
FROM TableB
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.
0
 
LVL 15

Author Comment

by:dave4dl
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.  
0
 
LVL 30

Assisted Solution

by:nmcdermaid
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.

0
 
LVL 29

Expert Comment

by:Nightman
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.

Cheers
Night
0
 
LVL 15

Author Comment

by:dave4dl
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!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 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