Post reply

My Assembly failed verification

  • October 30, 2010 at 2:06 pm

    #230489

    Hello, thanks for viewing this. I've been working on a sendmail feature based on this post http://www.mssqltips.com/tip.asp?tip=1795 but ran into some errors.

    I get this:

    Msg 6218, Level 16, State 3, Line 1

    CREATE ASSEMBLY for assembly 'sqlserversendmail' failed because assembly 'sqlserversendmail' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message

    The assembly .cs file I created looks like this

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

    using Microsoft.SqlServer.Server;

    using System.Net;

    using System.Net.Mail;

    public partial class StoredProcedures

    {

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void SQLServerSendMail(string msgBody, string msgSubject)

    {

    System.Net.Mail.MailMessage msg = new MailMessage();

    msg.Body = msgBody;

    msg.Subject = msgSubject;

    // Put your code here

    SmtpClient sc = new SmtpClient("smtp.xxx.xxx.xxx");

    sc.Credentials = new NetworkCredential("myemail@mail.com", "mypw");

    sc.Send(msg);

    }

    };

    I created the dll using this:

    csc /target:library /out:c:\sqlserversendmail.DLL c:\sqlserversendmail.cs

    I also tried using the MSDB database as the article suggested. Then I tried it in the User database.

    I enabled CLR.

    Notes:

    I'm using Sql Server 2008 Express.

    Using VS2010

    I created the dll with .net v4.0.30319.

    I also tried this...

    alter database MembersSQL set trustworthy on

    and this...

    sp_configure 'clr enabled', 1

    reconfigure

    Here is the code I ran in SQL Server 2008 management studio.

    CREATE ASSEMBLY SqlServerSendMail From 'c:\sqlserversendmail.dll'

    WITH PERMISSION_SET = UNSAFE

    GO

    CREATE PROCEDURE [dbo].[spSqlServerSendMail] @msgBody nvarchar(2000), @msgSubject nvarchar(50)

    WITH EXECUTE AS SELF

    AS

    EXTERNAL NAME [SqlServerSendMail].[SqlServerSendMail.StoredProcedure].[spSqlServerSendMail]

    Any ideas? Thanks.

  • November 3, 2010 at 12:56 pm

    #1246238

    I had the exact same problem you are facing. I went pass it by using vbc (or csc for you) in .net framework v2.

    When you get to the command prompt, cd over to your v2 framework folder before you csc.

    Hope this will move you on to your next step.

  • November 3, 2010 at 7:10 pm

    #1246353

    Thanks for the suggestion. I tried using .net 2 but no luck.

    I did

    C:\Windows\Microsoft.NET\Framework\v2.0.50727>csc /target:library /out:c:\sqlser

    versendmail.DLL c:\sqlserversendmail.cs

    Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.4016

    for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727

    Copyright (C) Microsoft Corporation 2001-2005. All rights reserved.

    It created the DLL as before but I got

    Did a little more research and found out to verify the assembly was created use

    select* fromsys.assemblies

    and so this returned something. The assembly name is 'SqlServerSendMail' and the clr_name is

    "sqlserversendmail, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil".

    so I tried this again:

    CREATE PROCEDURE [dbo].[spSqlServerSendMail] @msgBody nvarchar(2000), @msgSubject nvarchar(50)

    WITH EXECUTE AS SELF

    AS

    EXTERNAL NAME [SqlServerSendMail].[SqlServerSendMail.StoredProcedure].[sqlserversendmail]

    Msg 6505, Level 16, State 2, Procedure spSqlServerSendMail, Line 1

    Could not find Type 'SqlServerSendMail.StoredProcedure' in assembly 'sqlserversendmail'.

  • November 23, 2010 at 1:31 am

    #1254227

    Hi

    Did anyone solve the problem? i tried using .Net framework 2.0 but still its the same error and my assemblies are not created.

    My dll is very simple just contain mathematics add , subtract functions written in VC++.Net

    I am using SQL SERVer 2005

    Thanks

  • June 13, 2011 at 11:14 am

    #1337922

    You'll get "failed verification" with assemblies built against .net4 even with SQL Server 2008 R2.

  • June 16, 2011 at 5:55 pm

    #1340133

    he is right i suggest you do this

    Database Mail

    In the Express edition of SQL Server the Database Mail functionality does not exist. Therefor you need an alternative way to send emails with T-SQL.

    Xp_smtp_sendmail

    One solution is xp_smtp_sendmail. This solution has been around for ages and works very well.

    CLR

    Another solution is to use a CLR stored procedure. I found this solution in Greg Robidoux's tip. I have adjusted the code a bit to get it right for my requirements.

    You have to follow the following steps:

    Write the code. In this solution it is VB.Net.

    Compile the code into a DLL.

    Create an assembly and a stored procedure.

    Test the solution.

  • June 19, 2011 at 2:53 pm

    #1340902

    walterjone96 (6/16/2011)

    Another solution is to use a CLR stored procedure. I found this solution in Greg Robidoux's tip. I have adjusted the code a bit to get it right for my requirements.

    URL to Greg Robidoux's tip:

    http://blog.ambitionit.nl/archive/2011/01/14/sql-server-2008-r2-express-send-email-with-clr.aspx

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply