How to generate report image with CLR Sproc, cannot access System.Drawing on any non-sql project assemblies.

Question

Post reply

How to generate report image with CLR Sproc, cannot access System.Drawing on any non-sql project assemblies.

omhoge

SSCertifiable

Points: 5416
More actions
January 14, 2008 at 8:16 am

#182681

Hello all,

I'm trying to generate a custom graphic for a reporting services report using a SQL CLR stored procedure. (A RS CRI approach has become stuck at the VS integration phase so am trying to do it in SQL Server instead).

I've found I cannot access System.Drawing and most of the .net libraries and add references to them in a SQL Project in Visual Studio. It also does not permit a reference to another project that does hold them because it is not a SQL Project

"SQL Server projects can reference only other SQL Server projects.".

Does anyone know how to get to the full .net framework (which I thought was sort of the point for having CLR sprocs in the first place)?

Thanks gang for your attention!

much appreciated.

Skål - jh

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ericnewton76 SSC Rookie Points: 28 More actions · Answer 1

ericnewton76

SSC Rookie

Points: 28

February 12, 2008 at 3:06 pm

#777595

Basically, you cannot do that from inside SQL Server's hosted CLR.

Do it in your process by creating your own EXE and saving the resulting image to SQL.

This is a prime example of things NOT to do in SQL-CLR.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Completely agreed with above. SQLCLR does NOT give you FULL access to the framework. It's a specific subset.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Rumtata SSC-Addicted Points: 443 More actions · Answer 3

Just for my understanding:

Isn't it possible to register unsupported libraries and use them in e.g. CLR SP?

http://technet.microsoft.com/en-us/library/ms403279.aspx :

Unsupported libraries can still be called from your managed stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates. The unsupported library must first be registered in the SQL Server database, using the CREATE ASSEMBLY statement, before it can be used in your code.

And if this is correct - wouldn't it be possible to use the Syste.Drawing library?

???

Rumtata

(Besides I agree with ericnewton76 that this is something that shouldn't be done within SQL Server)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Well - yes. However when you do that - you then need to go into manually doing pretty much all of the rest of the project (meaning you will have to manually build the files, register the assembly with SQL Server by hand, and associate the SQLCLR stored procedure again by hand).

I'm pretty sure you even have to do that within a "generic" VS project, and not a Database project, and rebuild all of your references.

In other words - you're out of what the interface will help you with, and back to more or less where you were with building custom XP's in SQL 2000.... Off into that awful "no net and you're on your own if it blows up" land.

Not the place to be.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Lowell SSC Guru Points: 323482 More actions · Answer 5

I was trying a similar project...wanted to make a CLR that simply dumped RTF into an RTF object and returned it's .Text property, and found it was not allowed in CLR.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gaston_e_nusimovich SSC Journeyman Points: 76 More actions · Answer 6

When it comes to CLR integration in SQL Server, let's keep in mind the main objective of having .NET on SQL Server: to have an alternative language paradigm that is best on processor intensive data routines from a performance stand-point, in comparison to T-SQL, that is best on set-oriented processing of data.

Another thing to bear in mind is what parts of an application or solution should be processed at the data layer (I mean the DB), and what parts of the solution should left for another software layer to deal with.

IMHO, the actual drawing of a graph or any other visual representation of data does not seem to me to be something that should be dealt with by the data layer.

Best regards, Gastón