Solved

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

Posted on 2006-11-01
11
1,495 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
Comment Utility
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
Comment Utility
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
Comment Utility
I guess i should say i added

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

and later opened that connection
0
 
LVL 29

Accepted Solution

by:
Nightman earned 300 total points
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 15

Author Comment

by:dave4dl
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now