SQL CLR Assembly for Syncfusion XlsIO

[TM]

Hi,

I want to deploy XlsIO assemblies as SQL CLR assemblies but getting many errors. As per project's requirement , we want to create an stored procedure which can call .net code ( implementation of XlsIO as CLR assembly) for writing excel files.

Is there any way I can achieve this from back-end  using XlsIO ? 

Thanks

[Ishwarya Narayanan]

Hi TriMarine Team, 

Thank you for using Syncfusion products.

we are trying to achieve your requirements. we will get back to you with further details on 13^th April,2015.

Please let us know if you need any clarification.

Regards,

Ishwarya N

[TM]

Hi,

Any update on this issue ? We really like XlsIO and want to use it for generating excel report in our project. If we could find a way to use it from SQL then it will be really helpful.

Thanks.

[Ishwarya Narayanan]

Hi TriMarine,

 

Sorry for the delay.

 

We  are able to  achieve your requirement at our end. It would be grateful if you share what type of error you are facing while deploying XlsIO assemblies as SQL CLR. Kindly share a simplified issue reproducing sample to proceed further.

 

Please let us know if you need any clarification.

 

Regards,

Ishwarya N

[TM]

Hi,

Thank you for replying on issue. I am trying to deploy Syncfusion.XlsIO.Base.dll as SQL CRL but it is giving me errors as below: 

Warning: The Microsoft .NET Framework assembly 'system.windows.forms, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Msg 10301, Level 16, State 1, Line 1

Assembly 'Syncfusion.XlsIO.Base' references assembly 'system.drawing, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

The command I am using for deploying it as SQL CLR is :

CREATE ASSEMBLY [Sample.Syncfusion.Utility]

AUTHORIZATION [dbo]

FROM 'C:\Syncfusion.XlsIO.Base.dll'

WITH PERMISSION_SET = SAFE

Hope this can give idea about the challenge I am facing.

Thanks.

[Ishwarya Narayanan]

Hi TriMarine,

 

Thank you for updating us.

 

The error details at your end indicates invalid reference. Please follow the below steps to deploy XlsIO assemblies as SQL CLR.

 

Steps:

1.       Please ensure to use the MS SQL Server with 2008 version.

2.       Create a new database and name the Database

 

 

3.       Expand the created data base and browse to the SyncfusionXlsIODll->Programmability->Assemblies-> path contains only default assembly.i.e., Microsoft.SqlServer.Types.

 

 

4.       Copy the Syncfusion.Compression.Base, Syncfusion.Core and Syncfusion.XlsIO.Base dll and paste in to the following location. “C:\Windows\Microsoft.NET\Framework\v2.0.50727”.

5.       Access the created Database using the below query.

 

USE SyncfusionXlsIODll

GO

 

 

6.       and deploy the Syncfusion.XlsIO.Base assembly in Sql server using the below query.

 

CREATE ASSEMBLY [Syncfusion.XlsIO]

FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Syncfusion.XlsIO.Base.dll'

WITH PERMISSION_SET = UNSAFE

7.       During  deployment,  if the following error occurs,

 

Error

Assembly 'Syncfusion.XlsIO.Base' references assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: version, culture or public key mismatch). Please load the referenced assembly into the current database and retry your request.

Sql Server Error Msg

Msg 10300

 

You could use the below query to resolve this error. Please ensure “System.Web dll” is referred from this location. 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727

 

ALTER DATABASE SyncfusionXlsIODll SET TRUSTWORTHY ON

 

 

CREATE ASSEMBLY[System.Web]

from 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll' with permission_set = UNSAFE

GO

 

 

8.       After executing the above query do the step 6. Now the Syncfusion.XlsIO.Base assembly will be added to the Assembly Node.

 

 

Let us know if you encounter any other issue, we will be glad to look into it and help you further.

 

Regards,

Ishwarya Narayanan

[Ishwarya Narayanan]

Hi TriMarine,

Thank you for updating us.

The error details at your end indicates invalid reference. Please find the attachment in the below link to deploy XlsIO assemblies as SQL CLR.

 

Attachment Link: http://www.syncfusion.com/downloads/support/forum/118777/How_to_deploy_the_XlsIO_assembly_as_SQL_CLR-772775788.zip

 

Regards,

Ishwarya Narayanan

[TM]

Hi Ishwarya,

Thank you for helping us with example. I forgot to mention earlier that we are using Win Forms controls ( latest version). I tried to follow same steps as you mentioned then instead of error of System.Web , I got error of System.Windos.Forms. When I tried to deploy System.Windows.Forms ( referring from 'C:\Windows\Microsoft.NET\Framework64\v2.0.5072' )  the error is :

Warning: The Microsoft .NET Framework assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Msg 6586, Level 16, State 1, Line 1

Assembly 'system.windows.form' could not be installed because existing policy would keep it from being used.

I have done ALTER DATABASE 'dbName' SET TRUSTWORTHY ON step already. Please help us.

Thanks.

[TM]

Hi Ishwarya,

Below are the details which may help you in looking into the issue:

1. Our current database: SQL 2014

2. Our current Syncfusion installation ( For Win Forms) :13.1.0.21

3. .Net version in our project : 4.0

Thanks.

[Ishwarya Narayanan]

Hi TriMarine,

 

The mentioned invalid assembly reference can be solved by setting the PERMISSION_SET to UNSAFE mode instead of accessing with SAFE mode. When creating assembly into a SQL Database server we can use any one of the security permission.

 

Security Mode Type	Usage
SAFE	1.       T_SQL Assemblies that are considered to be safe are only allowed to access resources within the SQL Server instance. They may not access the file system, System registry, variables and network resources. 2.       This SAFE assemblies would typically be used to pass values into procedure to do some sort of string manipulation or advanced math returning value back to the DB.
UNSAFE	1.       Assemblies that are created with the UNSAFE permission can perform actions that could comprise the robustness of the SQL Server instance. 2.       UNSAFE permission assembly can reference any native or third party. Dot Net assembly that allows them to have effectively unfettered access to the Windows operating System.

 

You could change the Permission mode to UNSAFE as per in the below code snippet to resolve the issue.

 

 

CREATE ASSEMBLY [Syncfusion.XlsIO]

FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Syncfusion.XlsIO.Base.dll'

WITH PERMISSION_SET = UNSAFE

 

Please let us know if you need any clarification.

 

Regards,

Ishwarya N

 

[TM]

Hi Ishwarya,

Can you please try to test our scenario at your end ? Here are details:

1. Use SQL Server 2014

2. Syncfusion Studio For Windows Form ( Version : 13.1.0.21)

•     We are taking Syncfusion.XlsIO.Base dll from this suite which are targeted for .Net framework 4.0.

What we are assuming that Syncfusion XlsIO for Windows Form dlls refer System.Windows.Forms ( not System.web) and this is why we are getting error :

Warning: The Microsoft .NET Framework assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Msg 6586, Level 16, State 1, Line 1

Assembly 'system.windows.form' could not be installed because existing policy would keep it from being used.

Please let us know if you need any other details.

Thanks

[Ishwarya Narayanan]

Hi TriMarine Team,

 

We have analyzed with your mentioned configuration and found that the library compiles without any errors. Please find the attachment in the below link and follow the steps to resolve the issue at your end.

 

Attachment Link:

http://www.syncfusion.com/downloads/support/directtrac/133021/SqlClrXlsIO-2126581887.zip

 

Kindly try with these steps and let us know if the issue is resolved.

 

Thanks,
Ishwarya N

[TM]

Hi ,

Thanks for the reply. 

We followed the steps as specified by you in previous reply.But Still at our end we are facing the same issue for assembly dependency again and again every time we execute the above steps to create assembly.

I think may be there are some steps missing at your end or may be at our end. As it is quite surprising that we always get the dependency issue which is never reproduced at your end.

Please if possible can you share some vedio or something with proper steps. Also please try at your end with a fresh DB without registering any other .dll .Also in C Drive just keep 4 .dlls as mentioned below:

  a) Syncfusion.Compression.Base.dll

  b) Syncfusion.Core.dll

  c) Syncfusion.XlsIO.Base.dll

  d) System.Windows.Forms.dll

Also, after exceuting create assembly statement we get below error:

"

Assembly 'Syncfusion.XlsIO.Base' references assembly 'system.drawing, version=4.0.0.0, 

culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. 

SQL Server attempted to locate and automatically load the referenced assembly from the same location 

where referring assembly came from, but that operation has failed (reason: version, culture or public

key mismatch). Please load the referenced assembly into the current database and retry your request.

"

Please let us know how to resolve this dependency issue.

Thanks,

TriMarine Team

[Ishwarya Narayanan]

Hi TriMarine Team,

 

We are not able to reproduce the assembly dependency error at our end. So we have prepared the video as per your requirement in the below link. If the issue exists at your end we would therefore like to set up a web meeting with you to look into it and provide resolution. Kindly share us with your mail id to conduct the web meeting for further follow up.

 

Video Link:

http://www.syncfusion.com/downloads/support/directtrac/137895/ScreenCapture_5-5-2015_10.51.48_AM1066208787.zip

 

Note: To resolve the dependency error while executing the assembly we have copied all the necessary dlls to a folder in c drive from c:\windows\Microsoft.NET\Framework64\.

 

Please let us know if you need any clarification.

 

Regards,

Ishwarya N

[TM]

 Hi Ishwarya,

Thanks for the reply.

We need the list of dependent .Net dlls used and the location from where you access it. Also the folder of dlls as refered in vedio.As there are some dependent .Net dlls that you are referring which are missing at our end as seen in vedio.

Please share the required dependent .Net dlls.

Thanks,

TriMarine Team

[Ishwarya Narayanan]

Hi TriMarine Team,

 

Thanks for your update.

 

The following list of .Net dependent assemblies are needed to deploy the XlsIO assemblies as SQL CLR and which can be accessed from the location “C:\windows\Microsoft.NET\Framework64\v4.0.30319 “. If this framework folder is not available you can make use of the dlls in 32bit folder. All the dlls other than Syncfusion are default .Net prerequisites and will be available by default.

 

Dependent Assemblies:

 

S.No	Dependent Dlls
1	Accessibility.dll
2	Microsoft.Build.Framework.dll
3	Microsoft.Build.Tasks.v4.0.dll
4	Microsoft.Build.Utilities.v4.0.dll
5	SMDiagnostics.dll
6	System.ComponentModel.DataAnnotations.dll
7	System.Configuration.Install.dll
8	System.Design.dll
9	System.DirectoryServices.dll
10	System.DirectoryServices.Protocols.dll
11	System.Drawing.Design.dll
12	System.Drawing.dll
13	System.EnterpriseServices.dll
14	System.Runtime.Caching.dll
15	System.Runtime.Remoting.dll
16	System.Runtime.Serialization.dll
17	System.Runtime.Serialization.Formatters.Soap.dll
18	System.ServiceModel.Internals.dll
19	System.ServiceProcess.dll
20	System.Web.ApplicationServices.dll
21	System.Web.dll
22	System.Web.RegularExpressions.dll
23	System.Windows.Forms.dll
24	System.Xaml.dll
25	Syncfusion.XlsIO.Base.dll
26	Syncfusion.Compression.Base.dll
27	Syncfusion.Core.dll

 

Please let me know if you need any clarification.

 

Regards,

Ishwarya N

[TM]

Hi Ishwarya,

Thanks for the reply :)

With help of these dependent dlls list we are successfully able to fix our issue. Thanks for the help.

In case we need any furthur help will let you know.

Thanks,

TriMarine Team

[Ishwarya Narayanan]

Hi TriMarine,

 

Good to know that your issue is sorted out now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

 

Regards,

Ishwarya N