Help Needed: I have a dll (vb 2005) that I want to call in a SqlFunction that returns data from an Excel file

[JNBG]

Ok, if anyone knows anything about the subject please help. Here's the setup: I have written a dll using vs 2005 that reads data from an excel file. It works fine when I reference the dll in my windows app...but when I create the assembly/function combo in SQL I get an OleDB Unspecified error. The weird thing is that it was working on Monday, then when I came back in on Tuesday the server gave me weird errors and now the conn.Open throws an exception I cannot figure it out: (Thanks in advance for any help!) Here is the error when I try to call the Function in SQL Declare @a int set @a = (Select dbo.ExcelTester()) A .NET Framework error occurred during execution of user-defined routine or aggregate "ExcelTester": System.Data.OleDb.OleDbException: Unspecified error System.Data.OleDb.OleDbException: at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at ChargeCardCom.ChargeCardCom.ExcelTester() Here are the errors that happened on the server Tuesday: AppDomain 122 (KWCDB_WBU.dbo[runtime].121) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations. and AppDomain 130 (KWCDB_WBU.dbo[runtime].129) is marked for unload due to memory pressure. and AppDomain KWCDB_WBU.dbo[ddl].131 was unloaded by escalation policy to ensure the consistency of your application. Out of memory happened while accessing a critical resource.

[Abhishek Sur]

First let me know what ExcelTester does? Does it return something using SqlPipe object? I think there must be something wrong with the Function you created. You can read my articles if you wish to have clear idea on how to write them : Working with CLR Objects in SQL Server 2005 or Higher: Part I[^] Working with CLR Objects in SQL Server 2005 or Above Part 2[^] Let us see the code of your CLR Function, so that I could help you. :cool:

Abhishek Sur

---

My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB

**Don't forget to click "Good Answer" if you like to.

[JNBG]

Thank you for your reply Abhishek...I will post some code shortly (I have to log in to work as I'm at home now). Basically the ExcelTester (at this point) just creates an OleDB Connection to an excel file and attempts to open it. This is not my first CLR function integration project and I had the program working almost perfectly by Monday afternoon. Then I came in on Tuesday and started cleaning up some comments, re built the project and once again attempted to create the assemblies. It was at this time I began receiving the errors listed in my post and basically I spent all week trying to make it work again. It would probably be much easier to figure out if OleDB was not returning Unspecified Error when I catch the exception. The whole thing is weird and I'm sure it is something simple that I am just overlooking. Basically, the function just returns a string "Success" if it works and "Failure" if it doesn't (or an Integer 0 if it works and -1 if not..I've tried many variations). It is breaking on the conn.Open and I feel pretty certain the connection string is okay. If I remove the .Open call the function works perfectly and I'm able to create the assemblies and function and call it with no problem.

[JNBG]

Abhishek, Here is the class I am using (along with my AssemblyInfo file).   The only section at this point I am worrying about is the ExcelTester...I had the chgCardRead working by calling it in SQL on Monday, but again, something has gone wrong and I'm at a loss.   Also, I am creating the assemblies as Unsafe already.   Like I said before, it worked just fine and then just stopped and I have no idea why.   Thank you for your assistance. Option Strict Off Imports System.Data.OleDb Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports System.Reflection Imports System.Data Imports System.IO Imports System.Runtime.InteropServices Imports System Imports Microsoft.Win32 Imports System.Data.OleDb.OleDbPermission <ComClass(ChargeCardCom.ClassId, ChargeCardCom.InterfaceId, ChargeCardCom.EventsId)> _ Public Class ChargeCardCom #Region "COM GUIDs"       ' These   GUIDs provide the COM identity for this class       ' and its COM interfaces. If you change them, existing       ' clients will no longer be able to access the class.       Public Const ClassId As String = "2DF8ED1A-55D0-4EC1-AF8E-1611FF6C631E"       Public Const InterfaceId As String = "14CA7221-FBFD-4D6C-92B3-E60EAD6B48D7"       Public Const EventsId As String = "F994EEB1-4001-444C-BF01-09EA090B7CA6"       'Public Const ClassId As String = "d05374f7-f2e0-4c82-badc-2f5f93c6f1b1"       'Public Const InterfaceId As String = "b4d276c1-6548-4d70-9c05-e9e1e034c55a"       'Public Const EventsId As String = "0d3e3508-c31e-45df-b41a-b4bd0997fcb4" #End Region       ' A creatable COM class must have a Public Sub New()       ' with no parameters, otherwise, the class will not be       ' registered in the COM registry and cannot be created       ' via CreateObject.       Public Sub New()             MyBase.New()       End Sub       Public Shared Function ExcelTester() As String             Log("in tester")             Dim xlconn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\10098