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!
