Error in Creating Stored Procedure from VS 2005

When I create a stored procedure in VS 2005 using C# and deploy it to the server I can't execute it there and here is the error message:

Msg 6522, Level 16, State 1, Procedure GetAll, Line 0

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

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.GetAll()

and the code for creating the stored procedure is:

SqlConnection connDB = new SqlConnection(@."Initial Catalog=MDB;Data Source=Server1;");

SqlCommand cmd = new SqlCommand();

cmd.Connection = connDB;

cmd.CommandText = "SELECT * FROM Modifier";

connDB.Open();

SqlDataReader rdr = cmd.ExecuteReader();

SqlContext.Pipe.Send(rdr);

rdr.Close();

connDB.Close();

your help is appreciated...

What is the permission_set that you assigned for the assembly? This is the one in the CREATE ASSEMBLY. You need to set it to EXTERNAL_ACCESS due to use of SqlConnection that is accessing remote resource. Additionally, you will have to enable TRUST_WORTHY bit (use with care) or do the recommended key based login creation & assign external access assembly permission to it and use that user as owner of the assembly. If you download the new version of SQL Server 2005 Books Online it should contain updated topics that show how to do this. If you need some examples, please post back and I will try to locate a sample for you.|||

I am getting the same error when trying to debug my stored procedure. How do I get around int?

Here's my code:

Try

Dim conn As SqlConnection = New SqlConnection

conn.ConnectionString = "Data Source=XXX-XXXX\SQLSERVER2005;Initial Catalog=MotorFleetConversion;User ID=xxxx;password=xxxx"

conn.Open()

command = New SqlCommand(sqlAction)

'command.Parameters.AddWithValue("@.rating", rating)

command.Connection = conn

' Execute the command and send the results directly to the client

'SqlContext.Pipe.ExecuteAndSend(command)

Dim drUnitCode As SqlDataReader = command.ExecuteReader()

While drUnitCode.Read

If drUnitCode.Item("CompanyCode").ToString <> prevCompanyCode Then

agencySysNo = 0

If drUnitCode.Item("CompanyCode").ToString <> "" Then

agencySysNo = InsertAgency(drUnitCode.Item("CompanyCode").ToString, drUnitCode.Item("UC_DEPARTMENT_DESC").ToString, _

Convert.ToBoolean(drUnitCode.Item("NCAS")), drUnitCode.Item("UC_BILLING_CODE").ToString)

End If

End If

If agencySysNo > 0 Then

InsertDivision(agencySysNo, drUnitCode.Item("UC_DIVISION_DESC").ToString, drUnitCode.Item("UC_SHORT_DEPT_DIV").ToString, _

drUnitCode.Item("UC_CODE_NUMBER").ToString)

End If

prevCompanyCode = drUnitCode.Item("CompanyCode").ToString

End While

Catch ex As Exception

End Try

Thanks!