Error while referencing external assembly in SQL Server 2005.

15 pts.
Database programming
SQL Server 2005
SQL Server development
Visual Studio 2008
Sir, I created a dll (SqlWcfServiceLibrary1.dll) using .net frammework 2008. My code has a reference of another assemble (WcfServiceLibrary1.dll) also. I want to create assemble in sql and use that assemble in a procedure. I am getting error that "Error while referencing external assembly". I am pasting all code that I used.... .cs page code - namespace SqlWcfServiceLibrary1 { public class SqlService1 { void SendToSql(string message) { SqlMetaData[] table = new SqlMetaData[1]; table[0] = new SqlMetaData("Message", SqlDbType.VarChar, 1000); SqlDataRecord row = new SqlDataRecord(table); row.SetValue(0, message); SqlPipe pipe = SqlContext.Pipe; pipe.SendResultsStart(row); pipe.SendResultsRow(row); pipe.SendResultsEnd(); } public static void GetMessage(SqlString name) { Assembly assembly = LoadAssembly("D:\OutCast\WcfServiceLibrary1\WcfServiceLibrary1\bin\Debug\WcfServiceLibrary1.dll", true); Type type = LoadType(assembly, "WcfServiceLibrary1.Service1", true); String message = ""; using (ServiceHost host = new ServiceHost(type)) { host.Open(); IService1 service1 = InitChannel(); message = service1.GetMessage(name.ToString()); host.Close(); } } static IService1 InitChannel() { Uri address = new Uri("net.tcp://localhost:8731/Design_Time_Addresses/WcfServiceLibrary1/Service1/"); ChannelFactory<IService1> factory = new ChannelFactory<IService1>(new NetTcpBinding(), new EndpointAddress(address)); return factory.CreateChannel(); } static Assembly LoadAssembly(String assemblyName, bool throwOnError) { Assembly assembly = null; try { assembly = Assembly.LoadFrom(assemblyName); } catch { } if (assembly == null && throwOnError) throw new Exception("Hello Sujeet, Error while referencing external assembly.rn rnAssembly '" + assemblyName + "' doesn't exist."); return assembly; } static Type LoadType(Assembly assembly, String typeName, bool throwOnError) { Type type = null; try { type = assembly.GetType(typeName, false, true); } catch { } if (type == null && throwOnError) throw new Exception("Error while loading type.rn rnType '" + typeName + "' doesn't exist."); return type; } } [ServiceContract] public interface IService1 { [OperationContract] string GetMessage(string name); } } using above code I created a dll and using it in sql. SQL Code - -- create assembly CREATE ASSEMBLY SqlWcfServiceLibrary1 FROM 'D:OutCastSqlWcfServiceLibrary1SqlWcfServiceLibrary1binDebugSqlWcfServiceLibrary1.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS GO --create procedure -- external name is composed by

Answer Wiki

Thanks. We'll let you know when a new response is added.

Does the assembly exist at the path specified on the SQL Server? You’d be better off placing the assembly in the GAC then calling it from there.

What is the end goal of this assembly?

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Batsa
    Thanks for your reply. My assemble exist in the path that I specified on the Sql server and code. Error is giving about that assemble that I used in code. Assembly assembly = LoadAssembly("D:\OutCast\WcfServiceLibrary1\WcfServiceLibrary1\bin\Debug\WcfServiceLibrary1.dll", true); After executing proc the above assemble is not found. If you give me your email id, I will mail you my project after that you can check. This assemble is returning a simple message. I am just wanting to use external assemble in class library. I tried this after adding my assemble in GAC but it is giving same error. If you have any other solution please tell me. Sujeet
    15 pointsBadges:
  • Denny Cherry
    My looking at the CLR code isn't going to help. I don't know enought about CLR to help on that path. My recommendation would be to load the other assembly into the SQL Server and create a function or procedure pointing to it directly as it doesn't appear that your .NET code is doing anything but calling the other assembly. Perhaps if you remove the explicit path to the DLL the SQL Server could then find it in the GAC?
    67,435 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: