System.Security.HostProtectionException:The protected resources (only available with full trust) we All ....

Hello everybody..
I have started to "experiment" the CLR within SQL Server 2005.

I have to say that sometimes it's very handy ans sometimes very "complicated".

I got a problem, I have understood why and how to solve it.

I created a TV function that takes a CSV file so something on the data and retrieve a table as I need.

when I select the function I get this error:

here is the code:( a little bit long ...

' The record that holds each row returned by the TVF
Partial Public Class ImportRecord
Public DateTimeWeather As DateTime
Public EffectiveTemperature As Double
Sub New(ByVal dateTimeWeatherData As DateTime, ByVal temp As Double)
Me.DateTimeWeather = dateTimeWeatherData
Me.EffectiveTemperature = temp
End Sub
End Class

Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction(fillRowMethodName:="FillRow", TABLEDEFINITION:="DATETIMEWEATHERDATA DATETIME,TemperatureEffective FLOAT")> _

Public Shared Function MEImport(ByVal filename As String) As IEnumerable
Try
Dim sfl As SingleFileLoader = New SingleFileLoader(filename)
Dim ROWARRAY As ArrayList = New ArrayList()
Dim cippa()() As Object = sfl.GetDataIntoArray()
Return cippa
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Function

'The fill row method that returns the individual columns.
Private Shared Sub FillRow(ByVal obj As Object, <Out()> ByRef DateTimeWeather As DateTime, <Out()> ByRef TemperatureEffective As Double)
'Dim row As Object() = CType(obj, Object())
If obj IsNot Nothing Then
Dim row As Object() = CType(obj, Object())
DateTimeWeather = CType(row(0), DateTime)
TemperatureEffective = CType(row(1), Double)
End If
End Sub
End Class

Private Sub SingleFileLoaderHelper()
Dim OriginalContext As WindowsImpersonationContext = Nothing
Try
'Impersonate the current SQL Security context
Dim CallerIdentity As WindowsIdentity = SqlContext.WindowsIdentity
If Not CallerIdentity Is Nothing Then
OriginalContext = CallerIdentity.Impersonate()
fs = New FileStream(fn, FileMode.Open)
Else
fs = Nothing
End If
Catch ex As Exception
'If file does not exist or for any problems with opening the file,set filestream to null
fs = Nothing
Finally
'Revert the impersonation context; note that impersonation is needed only when opening the file. SQL Server will raise an exception if the impersonation is not undone before returning from the function.
If Not OriginalContext Is Nothing Then
OriginalContext.Undo()End If
End Try
End Sub
'
Public ReadOnly Property Current()
Get
Return (fs)
End Get
End Property

Public Function GetDataIntoArray() As Object()()
Dim dtValues As New DataTable
Dim JaggedArray()() As Object = New Object(30)() {}
Try
Dim myReader As New System.IO.StreamReader(fn)
'Read the entire text, and set it to a string
Using myReader
Dim line As String
Dim unit As String = ""
Dim lineNumber As Integer = 0
Dim ss As Integer = 0
' Read and display the lines from the file until the end of the file is reached.
Do
Dim WeatherData() As String = Nothing
Dim mixedTypes(2) As Object
line = myReader.ReadLine()
If line IsNot Nothing Then
If line.Length <> 0 Then
line = line.Replace("""", "")
WeatherData = line.Split(","c)
Dim gday As DateTime = Date.Parse(WeatherData(0).ToString).ToUniversalTime
Dim effectiveTemperature As Double = GetEffectiveTemperature(CType(WeatherData(1), Double), CType(WeatherData(2), Double))
mixedTypes(0) = gday
mixedTypes(1) = effectiveTemperature
JaggedArray(ss) = mixedTypes
ss = ss +
End If
End If
lineNumber = lineNumber + 1
End If
Loop Until line Is Nothing
myReader.Close()
End Using
Catch ex As Exception
MsgBox(ex.Message)
End Try
GetDataIntoArray = JaggedArray
End Function

Hi...

because the amount of code was starting be long I haven't specify that function that anyway is :

Private Function GetEffectiveTemperature(ByVal predictedTemperature As Double, ByVal WindSpeed As Double) As Double
Try
GetEffectiveTemperature = predictedTemperature - (WindSpeed / 1.5)
Catch ex As Exception
End Try
End Function

I haven't specify other functions like:

Public Sub Reset() ...
Public Function MoveNext()
Public ReadOnly Property Current()

Maybe I should restart and rewrite everything from scratch!

Thank you very much
Marina B.

Thank you everybody...

as 2 of you adviced me the Msgbox caused the exception!!

I'm more a Window developer and when I switch from the windows applications to the Sql server 2005 development I forget that is not completly the same!!

Now I would like to ask: what about the trapping of exceptions

I think that is fair enought that I have to put my code within a try/catch block and what if the best to way to throw the exception to the caller

Thankx everybody!

Marina B.