hi i have a project on .net which includes a trigger, written with c#, now after the steps to work with CLR like:

- generates serialization assembly -> on

- Allow unsafe code

- TRUSTWORTHY ON

form the article : http://www.codeproject.com/KB/database/SQLCLR.aspx

i couldn't succeed at last step  which is to register my Serialized Assembly DLL to my database. here is what i tried:

 CREATE ASSEMBLY [PCTrigger.XmlSerializers] FROM 'C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\HedeWebService\PCTrigger\bin\Debug\PCTrigger.XmlSerializers.dll'
WITH PERMISSION_SET=EXTERNAL_ACCESS

result:

Msg 6501, Level 16, State 7, Line 6
CREATE ASSEMBLY failed because it could not open the physical file 'C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\HedeWebService\PCTrigger\bin\Debug\PCTrigger.XmlSerializers.dll': 5(error not found).

i'm sure that the path of the  PCTrigger.XmlSerializers.dll is right. I already read some comments about this error but couln't figure out. also i have another PCTrigger.XmlSerializers.dll file under [samepath]\obj\Debug\PCTrigger.XmlSerializers.dll , but i think i must use the one under \bin. i tried both of them and same result.

need help,thanks.

 any idea,solution,reference would be great,i'm in a little hurry!

Hi piratejackus,

Please refer to the following description:

When attempting to access the assembly specified in <client_assembly_specifier>, SQL Server impersonates the security context of the current Windows login. If <client_assembly_specifier> specifies a network location (UNC path), the impersonation of the current login is not carried forward to the network location because of delegation limitations. In this case, access is made using the security context of the SQL Server service account.

If PERMISSION_SET = EXTERNAL_ACCESS is specified, the SQL Server login must have EXTERNAL ACCESS ASSEMBLY permission on the server. If PERMISSION_SET = UNSAFE is specified, membership in the sysadmin fixed server role is required.
User must be the owner of any assemblies that are referenced by the assembly that are to be uploaded if the assemblies already exist in the database. To upload an assembly by using a file path, the current user must be a Windows authenticated login or a member of the sysadmin fixed server role. The Windows login of the user that executes CREATE ASSEMBLY must have read permission on the share and the files being loaded in the statement.

Are the assembly file and SQL Server on the same machine? If so, please right-click the assembly file, select Properties and check if the current Windows login has the "Read & execute" permissions in Security tab.

If they are not on the same machine, I think we need to share the folder on your network and grant permissions to the SQL Server service account.

Related links:

CREATE ASSEMBLY (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms189524.aspx

Setting Up Windows Service Accounts
http://msdn.microsoft.com/en-us/library/ms143504.aspx

 Hi Jian Kang,

 Thank you very much for your reply and suggestion. Sql server and assembly file are on the same machine, after the problem that i've posted i copied my XmlSerializers to another location (like C:\dir\PCTrigger.XmlSerializers.dll) and then i could create assembly,no error like :

CREATE ASSEMBLY failed because it could not open the physical file ...

 than i thought something wrong with the path,but it didn't look logical.

i'm going to tell what i want to do step by step and maybe it will be easier to find the error and the solution,here i go;

i created a trigger in visual studio for my project, code is :

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;


public partial class Triggers
{
    // Enter existing table or view for the target and uncomment the attribute line
    [Microsoft.SqlServer.Server.SqlTrigger (Name="AdayOyEklendi", Target="Aday", Event="FOR UPDATE")]
    public static void AdayOyEklendi()
    {
        SqlTriggerContext sct = SqlContext.TriggerContext;
        if (sct.IsUpdatedColumn(5))
        {
            String adayId;

            SqlConnection conn = new SqlConnection("context connection=true");
            conn.Open();

            SqlCommand sqlCmd = conn.CreateCommand();
            sqlCmd.CommandText = "SELECT * FROM " + "inserted";
            SqlDataReader reader = sqlCmd.ExecuteReader();
            reader.Read();
            adayId = (String)reader[0];
            reader.Close();

            PCTrigger.PCAdayOy.PCAdayOyTopla adayOy = new PCTrigger.PCAdayOy.PCAdayOyTopla();
            adayOy.PCAdayOyGonder(adayId);

            conn.Close();
        }
    }
}

i couldn't check if that code is working or not yet because of this assembly problem.than i deployed this trigger in specified database (just right click and deploy) after that in trigger properties ( visual studio ) :

- generates serialization assembly -> on   //for the web service

- Allow unsafe code

then in sql server query editor:

- TRUSTWORTHY ON

no problem till here,now i have to register my PCTrigger.XmlSerializers.dll which is under C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\HedeWebService\PCTrigger\bin\Debug  but i couldn't succeed you know.

all i have got now is my trigger created in VS under assemblies.

i'm logged in as administrator ( windows server 2003 ) and i have the permission read&execute of the assembly file.

if you have any other suggestions i'm here,stuck with my problem.

thanks again.

 question: on sql server i'm executing query with the username and password which i use to login my sql server and also this user is the owner of my database,on the other hand i'm logged on OS as administrator, so these are diferent accounts by nature,can that be the reason?