SQL and Email Problems

Question

I am trying to get sql to download email for me. and put the body into sql. Here is where i got the code from. http://www.limilabs.com/blog/download-emails-with-sqlserver#comment-472876
Basically this is what happens. I create the dll then run the query to create assembly the assembly that creates the stored procedure. the stored procedure is not created. See bellow. I think my dll code is screwed up.

Here is what is happening
I am getting this error 

Msg 6505, Level 16, State 2, Procedure GetEmails, Line 2
Could not find Type 'StoredProcedures' in assembly 'SqlEmailDownloader'.

When I run this query in management studio.

Code:

USE HelloWorld
GO
EXEC sp_configure 'clr enabled' , '1'

GO

RECONFIGURE

GO
Alter Database HelloWorld Set TrustWorthy On

GO
CREATE ASSEMBLY [System.Windows.Forms] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll'
With Permission_Set=Unsafe

GO

CREATE ASSEMBLY SqlEmailDownloaderAssembly FROM 'E:\mail\SqlEmailDownloader.dll'

With Permission_Set=Unsafe

GO

CREATE PROCEDURE GetEmails

AS EXTERNAL NAME SqlEmailDownloaderAssembly.StoredProcedures.GetEmails

GO

My DLL looks like this

Code:

Imports System.Data
Imports Microsoft.SqlServer.Server
Imports Limilabs.Client.IMAP
Imports Limilabs.Mail
Public Class Class1
    Public Class StoredProcedures
        <Microsoft.SqlServer.Server.SqlProcedure()> _
        Public Shared Sub GetEmails()
            Dim record As New SqlDataRecord({ _
                New SqlMetaData("UID", SqlDbType.BigInt), _
                New SqlMetaData("Subject", SqlDbType.NVarChar, 128)})

            Using client As New Imap()
                client.Connect("Imap.gmail.com")
                client.Login("username", "password")
                client.SelectInbox()

                SqlContext.Pipe.SendResultsStart(record)
                For Each uid As Long In client.Search(Flag.Unseen)
                    Dim eml As String = client.PeekHeadersByUID(uid)
                    Dim email As IMail = New MailBuilder() _
                    .CreateFromEml(eml)

                    record.SetSqlInt64(0, uid)
                    record.SetSqlString(1, email.Subject)
                    SqlContext.Pipe.SendResultsRow(record)
                Next
                client.Close()
                SqlContext.Pipe.SendResultsEnd()
            End Using
        End Sub
    End Class

Answer 1

take a look at this article

http://gallery.technet.microsoft.com/Steps-And-Script-For-1f40e832

---

Mark this post as answer if this resolves your issue.

---

Everything about SQL Server | Experience inside SQL Server -Mohammad Nizamuddin

Answer 2

Does your code compile successfully?  Sorry, I'm horrible at VB, but try taking out the line "Public Class Class1".

Answer 3

First of all, I guess there is a typo in your code snippet, as you have Public Class Class 1 just after the last Imports, followed by Public Class StoredProcedures. If that indeed is a typo, then the most likely reason why you get the error is that VB.NET has injected a namespace in your code (VB.NET has a tendency of doing that), and you are not referencing the namespace in your CREATE PROCEDURE statement:

CREATE PROCEDURE GetEmails AS EXTERNAL NAME SqlEmailDownloaderAssembly.[namespacename.StoredProcedures].GetEmails

The square brackets around namespace.classname has to be there if you do have a namespace, otherwise SQL doesn't know how to parse the statement.

HTH

Niels

---

http://www.nielsberglund.com | @nielsberglund