Sql clr

I am trying to load Aspose.Pdf in the SQL CLR along (with dependencies) with a custom DLL I made that will extract text from a PDF’s binary in a database. I have to load the following DLL’s in order to make this work:

CREATE ASSEMBLY [System.Drawing]
FROM ‘C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Drawing.dll’
WITH PERMISSION_SET = UNSAFE
GO

CREATE ASSEMBLY [System.Web]
FROM ‘C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.web.dll’
WITH PERMISSION_SET = UNSAFE
GO

CREATE ASSEMBLY [PresentationCore]
FROM ‘C:\Windows\Microsoft.NET\Framework64\v4.0.30319\WPF\PresentationCore.dll’
WITH PERMISSION_SET = UNSAFE
GO

CREATE ASSEMBLY [Aspose.Pdf]
FROM ‘D:\Src\CISNET\Utilities\CisDocumentTextExtractor\Aspose.Pdf.dll’
WITH PERMISSION_SET = UNSAFE
GO

CREATE ASSEMBLY TextExtractor
from ‘D:\Src\CISNET\Utilities\CisDocumentTextExtractor\bin\Debug\CisDocumentTextExtractor.dll’
WITH PERMISSION_SET = unSAFE
go

There is a scalar valued function that uses TextExtractor:

CREATE function ExtractText (@id varbinary(max))
RETURNS NVARCHAR(max) WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
AS
EXTERNAL NAME TextExtractor.Narrative.ExtractText

This is where Aspose is used to extract the text from a PDF.

If I run the code in CisDocumentTextExtractor.dll in Visual Studio, everything works fine. However, when I use things in SQL, it does not work. I only get an error of:

Error:Could not load file or assembly ‘zx_26ad550982d54ff2ad2356552e708440, PublicKeyToken=716fcc553a201e56’ or one of its dependencies. The system cannot find the file specified.

That assembly is Aspos.Pdf 19.6. It looks like Apsose.Pdf cannot load due to one of the dependencies it needs but I am not sure.

The PDF stored in the DB are in their binary format so I simply pass the binary column to the function that uses the DLL.

This is occurring on multiple SQL versions so far, 2014 and 2017. I can try to create a solution if you cannot reproduce this.

@MarkBernardi

Thank you for contacting support.

Would you please create a sample solution as this will help us efficiently address your concerns.

Download supporting files here: https://1drv.ms/u/s!AtBWzbYOgK_HgSRgFBLkBV8LvZg2?e=bbEhJD

Setup:

Run the setup scripts with the SA account.

Open the solution CisDocumentTextExtractor. In the project CisDocumentTextExtractor, change the path to the Aspose.Pdf license in Narrative.cs as needed and build the project.

On a MS SQL 2014 instance, open Setup1.sql, change the paths for the MDF and LDF files as needed and execute the script.

On the same MS SQL 2014 server where the DB was setup above, open Setup2.sql and change the path for the Aspose.Pdf and TextExtractor assembly setup to the respective paths in the built project previously… The paths for System.Web and PresentationCore should be fine as is. Execute the script. System.Web and PresentationCore are dependencies of Aspose.Pdf.

On the same MS SQL 2014 server, open Setup3.sql and execute it.

On the same MS SQL 2014 server, open Setup4.sql and execute it.

On the same MS SQL 2014 server, open Run.sql and execute it to see the resulting error being reported.

Open the solution CisDocumentTextExtractor and in the Tester project in program.cs, change the connection string server, username and password to what is required for your server. Set a breakpoint on line 19 and debug the project. You will see that for the same document in the database that failed previously, the text is extracted as expected on the same code that was imported in the SQL CLR.

You will notice code for Aspose.Words that is commented out. Aspose.Words works perfectly in the CLR. This was just a test that I left in there for review.

Please let me know if this setup does not work, I tested everything as instructed above and all worked fine for me.

One more note, some further testing showed that the Aspose.Pdf is failing to load in the CLR period. I believe it is related to a dependency. When using Aspose.Words in the CLR in the same manner, all worked fine. The only dependency that Aspose.Words has is System.Drawing. Aspose.Pdf has dependencies on PresentationCore and System.Web which in turn has many other dependencies. I do not know which dependency is the issue though.

You can see the dependencies in the assemblies node in SSMS under programability after all setup scripts are run. I was not able to load Aspose.Pdf in the CLR without PresentationCore and System.Web.

@MarkBernardi

Thank you for sharing the data and elaborating all the details well.

We have logged an investigation ticket with ID PDFNET-46626 in our issue management system for detailed investigations. We will let you know as soon as any update will be available in this regard.

Any updates on this?

@MarkBernardi

We are afraid any update is not available yet and it may take few moths to resolve owing to previously logged and critical tickets. We will let you know as soon as some significant update will be available in this regard.

A post was split to a new topic: Problem loading API in SQL CLR