Error in Creating Stored Procedure from VS 2005

Hot Topic

URGENT: PARAMETER ISSUES WITH SQL TASK
Lookup error
Interactively showing reports detailing results/stats of executing a package?
somebody helps,How to dynamic determine the position of a table in a report.
Insert into Table with no values using the identity column
SQL Server 2005 Service Pack 1 Setup errors
Compatibility of MSDE with .NEt
Report Builder - Editing existing report
system.outofmemoryexception
UPDATETEXT

SQL Server

How to drop users programmatically through a sp
SQL Replication
Executing an external process() in SQLCLR Project
Simulate ROW_NUMBER () in SQL 2000
DBSTATUS_UNAVAILABLE with OLEDB Command
SQL SERVER & Coldfusion connection problem - server newbie
Time Intelligence Wizard script shortcomings
Variables Disabled in IDE?
sql_variant in Data Flow Task
AS2005 September CTP Memory Error

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...

Answer this question

Error in Creating Stored Procedure from VS 2005

  • CamCam

    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!



  • prashant52197

    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.

  • Error in Creating Stored Procedure from VS 2005
Answer this question