Hello there,
I am having trouble with the following and appreciate any help.Thanks in advanced.
I have created an assembly to delete records in exchange (which works fine when testing it in debug mode). I then create the assembly in SQL server 2005(tried both unsafe and external access btw), and then i create a stored procedure calling the assembly.
Now my error occurs when i try to execute this stored procedure in SQL Server. I get the following...
Msg 6522, Level 16, State 1, Procedure sp_DeleteExchApp, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_DeleteExchApp':
System.Security.SecurityException: System.Security.Permissions.SecurityPermission
System.Security.SecurityException:
at App_Delete.eSay.AppointmentDelete.DeleteAppointment(Guid GUID)
.
(1 row(s) affected)
Now when i have looked around the web, i have come accross people saying that it is to do with the assembly trying to access something it does not have permission for. But i cant figure out what that is or why it is throwing an error.
The code for the assembly is as follows:
Public Shared Sub DeleteAppointment(ByVal GUID As System.Guid)
Dim CalendarURL As String
Dim ItemURL As String
Dim Rs As New ADODB.Recordset
Dim Rec As New ADODB.Record
Dim Conn As New ADODB.Connection
Dim iAppt As New Appointment
Dim Target As String
Dim strCRMGUID As String
Dim strGUID As String
Dim sSQL As String
Dim Check As String
strCRMGUID = GUID.ToString
'Using the Exchange OLE DB provider
CalendarURL = "file://./backofficestorage/esay-solutions.co.uk/public folders/Office Diary"
'Open a recordset for the items in the calendar folder
Rec.Open(CalendarURL)
Rs.ActiveConnection = Rec.ActiveConnection
sSQL = "SELECT ""DAV:href"", " & _
" ""urn:schemas:calendar:location"", " & _
" ""urn:schemas:calendar:dtstart"", " & _
" ""urn:schemas:calendar:dtend"", " & _
" ""urn:schemas:httpmail:textdescription"" " & _
"from scope('shallow traversal of """ & CalendarURL & """')"
Rs.Open(sSQL, Rec.ActiveConnection, CursorTypeEnum.adOpenUnspecified, LockTypeEnum.adLockOptimistic, 1)
'Enumerate the recordset, checking each item's location
If Rs.RecordCount > 0 Then
Rs.MoveFirst()
Do Until Rs.EOF
'get the location of each item
Check = Rs.Fields(CdoHTTPMail.cdoTextDescription).Value
strGUID = StripGUID(Check)
'test for desired location
If (strGUID = strCRMGUID) Then
Rs.Delete()
End If
Rs.MoveNext()
Loop
End If
End Sub
Private Shared Function StripGUID(ByVal i_strAppDescription As String) As String
'Function to strip the GUID from the message field
Dim r As Integer
Dim SearchChar As String
Dim testPos As Integer
Dim GUID As String
SearchChar = "**|"
testPos = InStr(i_strAppDescription, SearchChar)
testPos = testPos + 3
If testPos > 0 Then
GUID = Mid(i_strAppDescription, testPos)
End If
StripGUID = GUID
End Function
End Class
End Namespace
Dim CalendarURL As String
Dim ItemURL As String
Dim Rs As New ADODB.Recordset
Dim Rec As New ADODB.Record
Dim Conn As New ADODB.Connection
Dim iAppt As New Appointment
Dim Target As String
Dim strCRMGUID As String
Dim strGUID As String
Dim sSQL As String
Dim Check As String
strCRMGUID = GUID.ToString
'Using the Exchange OLE DB provider
CalendarURL = "file://./backofficestorage/esay-solutions.co.uk/public folders/Office Diary"
'Open a recordset for the items in the calendar folder
Rec.Open(CalendarURL)
Rs.ActiveConnection = Rec.ActiveConnection
sSQL = "SELECT ""DAV:href"", " & _
" ""urn:schemas:calendar:location"", " & _
" ""urn:schemas:calendar:dtstart"", " & _
" ""urn:schemas:calendar:dtend"", " & _
" ""urn:schemas:httpmail:textdescription"" " & _
"from scope('shallow traversal of """ & CalendarURL & """')"
Rs.Open(sSQL, Rec.ActiveConnection, CursorTypeEnum.adOpenUnspecified, LockTypeEnum.adLockOptimistic, 1)
'Enumerate the recordset, checking each item's location
If Rs.RecordCount > 0 Then
Rs.MoveFirst()
Do Until Rs.EOF
'get the location of each item
Check = Rs.Fields(CdoHTTPMail.cdoTextDescription).Value
strGUID = StripGUID(Check)
'test for desired location
If (strGUID = strCRMGUID) Then
Rs.Delete()
End If
Rs.MoveNext()
Loop
End If
End Sub
Private Shared Function StripGUID(ByVal i_strAppDescription As String) As String
'Function to strip the GUID from the message field
Dim r As Integer
Dim SearchChar As String
Dim testPos As Integer
Dim GUID As String
SearchChar = "**|"
testPos = InStr(i_strAppDescription, SearchChar)
testPos = testPos + 3
If testPos > 0 Then
GUID = Mid(i_strAppDescription, testPos)
End If
StripGUID = GUID
End Function
End Class
End Namespace
Thanks again for any help.