Help with Multiple connections in a CLR stored procedure

Here's what I'm trying to accomplish:

1. Open a connection and retrieve a datareader, using the context connection.
2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection.

The problem I have is that I can't open the second connection. Here's some sample code:

public partial class StoredProcedures {

[Microsoft.SqlServer.Server.SqlProcedure]
public static void up_TestClr() {

//Use the current context connection
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT TOP 100 AccountId FROM Account";

conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {

SqlConnection conn2 = new SqlConnection();
conn2.ConnectionString = "Server=localhost;Database=TestDb;Integrated Security=SSPI";
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = conn;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "up_TestProc";
int serviceFilterId = Convert.ToInt32(reader["accountId"]);
SqlParameter parm = new SqlParameter("@.accountId", serviceFilterId);
cmd2.Parameters.Add(parm);
conn2.Open();
cmd2.ExecuteNonQuery();
cmd2.Dispose();
}

reader.Close();
conn.Close();

}

};

When I attempt to execute the procedure, I get the following:

Msg 6549, Level 16, State 1, Procedure up_TestClr, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'up_TestClr':

System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermission()

at System.Data.SqlClient.SqlConnection.PermissionDemand()

at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at StoredProcedures.up_TestClr(String& dbName)

. User transaction, if any, will be rolled back.
Any suggestions? I've tried unsuccessfully to create an Asymetric key to mark my assembly for External Access:

USE master

GO

CREATE ASYMMETRIC KEY SN FROM EXECUTABLE FILE = 'C:\Shc\SqlServerProject1.dll'

CREATE LOGIN TestLogin FROM ASYMMETRIC KEY SN

GRANT EXTERNAL ACCESS ASSEMBLY TO TestLogin

GO

Resulted in:

Msg 15208, Level 16, State 1, Line 2
The certificate, asymmetric key, or private key file does not exist or has invalid format.
Msg 15151, Level 16, State 1, Line 3
Cannot find the asymmetric key 'SN', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 4
Cannot find the login 'TestLogin', because it does not exist or you do not have permission.
ANY IDEAS/SUGGESTIONS?

<AKS@.discussions.microsoft.com> wrote in message news:68f11211-89fe-4a3f-972c-512cddfc8ec4@.discussions.microsoft.com... 1. Open a connection and retrieve a datareader, using the context connection.2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection. Unfortunately, you can only have a single context connection open at once. Have you considered using a DataSet instead of the DataReader? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457--|||Ugh. I didn't realize that I could use a DataSet inside a CLR-based SP. I guess somehow, all the examples I've seen used the SqlDataReader.

That should work fine. Thx.|||

Not sure how using a DataSet helps, but...

He is not opening a 2nd context connection. He is opening a 2nd connection that may happen to point to the same server/database as the context connection.

This may be needed for example to persist auditing information that needs to survive even if the context connection's transaction is later rolled back.

Are you saying this is not allowed?

My code attempts to create a 2nd connection like this, and I'm getting the same error when I attempt to .Open() it later:

<code>

public static SqlConnection NewConnection()

{

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

connection2.Open();

SqlCommand command = new SqlCommand("select @.@.servername, db_name()", connection2);

SqlDataReader sdr = command.ExecuteReader();

sdr.Read();

string serverName = sdr.GetString(0);

string dbName = sdr.GetString(1);

return new SqlConnection("Server=" + serverName + ";Database=" + dbName + ";Trusted_Connection=yes;Enlist=false");

}

</code>

|||I receive the same error when attempting to connect to another db instance. What was the solution for this problem?