Generate a XLS file using SQL CLR That does not have loads of incompatible Dependencies

Question

I want to do something simple, Create a SQL CLR that converts data to XLS.

I've tried Using OpenXML, but it keeps installing windowbase as a dependency, which uses system.XAML, which SQL server (2019) refuses to install as it's not "tested" whatever that means.

ClosedXML has a similar issue, System.Drawing refuses to be referenced.

I tried writing raw xml to xls and The io.Compression dependency file won't work with SQL server either!

I'm out of ideas, All I really want is a version of openXML that isn't using WPF Nonsense like system.xaml, but I can't seem to get that to work.

Any help is appreciated.

Retrieve the rows in a c# client, in a datagrid for example, and convert the datagrid ? I am sure there are lots of libraries that can help with that — GuidoG
– GuidoG, Commented Dec 17, 2024 at 14:21
Getting the data isn't the part that is stopping me, but the actual conversion to an XLS. OpenXML and CLosedXML are recommended but both have severe incompatibablities with SQL server assemblies — Sean
– Sean, Commented Dec 17, 2024 at 14:22
You can use SQL Server Integration Services (SSIS). It has Excel Destination Adapter. — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Dec 17, 2024 at 14:23
Why, out of interest, do you want an XLS, rather than an XLSX file? The former are for much older Excel files. — Thom A
– Thom A ♦, Commented Dec 17, 2024 at 14:26
Well a CSV in Powershell would be trivial. Just use DbaTools's Export-DbaDbTableData function. — Thom A
– Thom A ♦, Commented Dec 17, 2024 at 14:40

David Browne - Microsoft · Accepted Answer · 2024-12-17 16:54:12Z

3

Don't try do this in SQLCLR. Instead Build a .NET console application to do the work, and invoke it from an Agent job or even xp_cmdshell.

In modern .NET you can even combine your app and all the dependencies into a single .exe as a Single-File Application.

answered Dec 17, 2024 at 16:54

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sean Dec 18, 2024 at 11:01

The idea was to circumvent xp cmdshell due to security concerns. But I'm now starting to think that this entire process simply isn't fit for a sql clr and am going to look for alternatives like you suggested.

Collectives™ on Stack Overflow

Generate a XLS file using SQL CLR That does not have loads of incompatible Dependencies

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related