How To Write File From A Clr Stored Procedures

Hi
I wrote a clr storred procedure that use fileopen, fileput an fileclose to write a report from a sql 2005 database but I don't know how to give it permissions to do its job.

The Code is

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic
Imports System.Security.Permissions

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure> Public Shared Sub PriceSum( ByVal intFecha as sqlInt32 )

dim strFecha as string
strFecha = cstr(intFecha)

Dim f As New FileIOPermission(PermissionState.none)
f.AllLocalFiles = FileIOPermissionAccess.write
f.Demand()

FileOpen(1, "G:AuVeJAPParchivo.txt", OpenMode.Random, OpenAccess.ReadWrite, OpenShare.LockReadWrite, 10)
Using connection As New SqlConnection("context connection=true")
Connection.Open()
dim strConsulta as string
strConsulta = "declare cur_unidades cursor for select id_tda_vdp from tda_venta_departamental_vdp where date_operacion_vdp = " & strFecha & " group by id_tda_vdp order by id_tda_vdp"

Dim command As New SqlCommand(strConsulta, connection)
Dim reader As SqlDataReader
reader = command.ExecuteReader()
using reader
Dim intKon as integer
intKon = 0

While reader.Read()
fileput(1, reader.GetSqlstring(0) & chr(0), intKon)
intKon = intKon + 1
End While

End Using
End Using
fileClose(1)
End Sub
End Class

As you could see, the report needs a chr(0) at the end of each line, thats why I spent a lot of time learning clr/sql, but when I finally success in crate the assembly an procedure in sql 2005 it gives me this frustrating error:


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

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

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)

at System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs, PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)

at StoredProcedures.PriceSum(SqlInt32 intFecha)

I use


Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)

Oct 14 2005 00:33:37

Copyright (c) 1988-2005 Microsoft Corporation

Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

I really appreciate your help, it could be a different way to do this or a simple step by step "how to grant permission"

thank you very much

Adolfo Ponce