Solved

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

Posted on 2006-11-01
11
1,505 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 29

Accepted Solution

by:
Nightman earned 300 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 200 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server - TSQL - Removing duplicates/How to get max records per id 13 47
T-SQL 10 35
RESTORE MASTER DATABASE -- NOW 2 20
SQL Availablity Groups List 2 8
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

830 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