SQL Server: error could not find type in assembly during dll trigger creation

Question

In Microsoft SQL Server, I want to call a dll assembly as a trigger

This is my C# code :

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WriteTimeInfile
{
    public class Program
    {
        public static void Main(string[] args)
        {
            File.AppendAllText(@"C:\Users\Vivien\Desktop\date.txt",
                   DateTime.Now.ToString() + Environment.NewLine);
        }
    }
}

This is my properties panel:

Then I create the assembly in SQL Server, it seems to work and create an assembly:

CREATE ASSEMBLY triggertest
FROM 'C:\Users\Vivien\Documents\visual studio 2015\Projects\WriteTimeInfile\WriteTimeInfile\bin\Release\WriteTimeInfile.dll'
WITH PERMISSION_SET = SAFE

But when I try to create the trigger, i try to do like it is explained in this link:

CREATE TRIGGER tri_Publishes_clr
ON STATION
FOR INSERT
AS EXTERNAL NAME triggertest.[WriteTimeInfile.WriteTimeInfile.Program].Main

I get an error

Could not find Type 'WriteTimeInfile.WriteTimeInfile.Program' in assembly 'WriteTimeInfile'.

Nothing works

Could you help me please?

Check your project settings codepal.co.uk/show/Could_not_find_type_in_assembly — Serg
– Serg, Commented Jan 2, 2017 at 18:02
PERMISSION_SET = SAFE and writing to a file won't work either. — Martin Smith
– Martin Smith, Commented Jan 2, 2017 at 22:07
you are right I was just realising that problem , just as my code canot work because it has no System.Security.Permissions.FileIOPermission — Pipo
– Pipo, Commented Jan 2, 2017 at 22:10
@MartinSmith and Vivien: Correct, SAFE won't work here. Please see the two notes I just added to the my answer (under the line). — Solomon Rutzky
– Solomon Rutzky, Commented Jan 2, 2017 at 22:10

Solomon Rutzky · Accepted Answer · 2017-01-02 22:10:05Z

You did not create a SQLCLR Trigger in your .NET code. This signature:

public static void Main(string[] args)

is for a Console App. You need to use the proper declaration for a SQLCLR object, which include using the SqlTrigger attribute.

[SqlTrigger(Name = "trigger_name", Target = "[schema].[table]",
            Event = "FOR INSERT, UPDATE, DELETE")]  
public static void TriggerName()
{
}

For more info on SQLCLR Triggers, please see the MSDN page for CLR Triggers.

For more info on working with SQLCLR in general, please see the series I am writing on SQL Server Central (free registration is required to read content on that site): Stairway to SQLCLR.

PLEASE NOTE: IF the only reason for doing a SQLCLR Trigger is to use File.AppendAllText, then you might be better off creating a SQLCLR UDF / Scalar Function to do the same thing and then using that Function in a regular T-SQL Trigger.

ALSO, and this is very important, since you are doing file system related functionality, your Assembly will need to be set to EXTERNAL_ACCESS. Please do not accomplish this by setting the Database to TRUSTWORTHY ON. Please sign the Assembly (protect it with a password), then create an Asymmetric Key from that Assembly in master, then create a Login based on that Asymmetric Key, and then grant that Key-based Login EXTERNAL ACCESS ASSEMBLY. You will see this approach in the various articles I wrote in the above mentioned "Stairway to SQLCLR" series.

Collectives™ on Stack Overflow

SQL Server: error could not find type in assembly during dll trigger creation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related