05 February 2008 05:23 tonyrogerson

Little note to self; CLR - Passing more than 4000 characters to a CLR stored procedure/udf (varchar(max))

An update to my SMTP Email VB.NET CLR stored procedure, realised it only accepts 4000 bytes max, to fix this use SqlFacet eg...

<SqlFacet(MaxSize:=-1)> ByVal Body As SqlString 

Example usage....

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Imports System.Runtime.InteropServices

Imports System.Net.Mail

 

 

Partial Public Class StoredProcedures

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Function SMTPFullEmail(ByVal SMTPServer As SqlString, _

                                         ByVal SenderEmail As SqlString, _

                                         ByVal FromEmail As SqlString, _

                                         ByVal FromName As SqlString, _

                                         ByVal ToEmail As SqlString, _

                                         ByVal ReplyTo As SqlString, _

                                         ByVal Subject As SqlString, _

                                         <SqlFacet(MaxSize:=-1)> ByVal Body As SqlString, _

                                         ByVal AttachmentFileName As SqlString, _

                                         <Out()> ByRef SendStatus As SqlString) As SqlInt32

 

        Dim Message As New MailMessage

 

        '   Set up the message

        Message.Sender = New MailAddress(SenderEmail.ToString)

        Message.From = New MailAddress(FromEmail.ToString, FromName.ToString)

 

        Message.To.Add(ToEmail.ToString)

        Message.Subject = Subject.ToString

        Message.Body = Body.ToString

        Message.IsBodyHtml = True

        If AttachmentFileName.ToString <> "" Then Message.Attachments.Add(New Attachment(AttachmentFileName.ToString))

 

        Dim smtp As New SmtpClient

 

        '   Send it

        smtp.Host = SMTPServer.ToString

 

        Try

            smtp.Send(Message)

 

            SendStatus = CType("OK", SqlString)

            SMTPFullEmail = 0

 

        Catch ex As Exception

            SendStatus = CType(ex.Message, SqlString)

            SMTPFullEmail = 1

 

        End Try

 

    End Function

 

End Class

 

Filed under:

Comments

# re: Little note to self; CLR - Passing more than 4000 characters to a CLR stored procedure/udf (varchar(max))

05 February 2008 06:15 by Uri Dimant

Hi Tony.

We are going to implement something similar at work , so can  you please show me ,how do you register/call this CLR from T-SQL respectively? Thanks

# re: Little note to self; CLR - Passing more than 4000 characters to a CLR stored procedure/udf (varchar(max))

05 February 2008 07:38 by tonyrogerson

Just deploy it using Visual Studio and then call...

   EXEC @RC = smtpfullemail

@SMTPServer = 'smtp.server.name',

@SenderEmail = 'realemail@blah.com',

@FromEmail = 'realemail@blah.com',

@FromName = 'Pretty Email',

@ToEmail = @Email,

@ReplyTo = 'someone@blah.com',

       @subject = @subject,

       @body = @msg,

@AttachmentFileName = '',

@SendStatus = ''

# re: Little note to self; CLR - Passing more than 4000 characters to a CLR stored procedure/udf (varchar(max))

05 February 2008 10:58 by Uri Dimant

I got it thanks, will be waiting for another great articles :-)

# re: Little note to self; CLR - Passing more than 4000 characters to a CLR stored procedure/udf (varchar(max))

06 February 2008 18:33 by AdamMachanic

Hi Tony,

I think you might be happier using SqlChars rather than SqlString for passing larger data. SqlChars automatically maps to VARCHAR(MAX) -- no SqlFacetAttribute required -- plus the data is streamed in, so it ends up being quite a bit more efficient.

# re: Little note to self; CLR - Passing more than 4000 characters to a CLR stored procedure/udf (varchar(max))

11 February 2008 13:06 by simonsabin

The other subtle thing to note is that teh limit is 4000 bytes and not 8000 bytes. That is because CLR only accepts unicode strings.

So if you are passsing in a non unicode string it will be converted. I haven't tested the implications of this conversion compared to working in unicode (nvarchar(max)) in the TSQL code.