Two things you can’t do in SQLCLR

Since SQL Server 2005 was introduced with .NET programming support, folks have been trying to push the boundaries of what can be used in SQLCLR or at least trying to determine where those boundaries are. Here's two things that, as far as I know, can't be done in SQLCLR.

1. Use dynamically generated code. The canonical example of this is dynamic serialization assemblies generated when you use "Add Web Reference" in the generated web service proxy code. The way around this is to use the sgen utility. But lately, it's been brought up that dynamic programming languages such as Iron Python always generate dynamic code. SQLCLR forbids using this, even in UNSAFE assemblies. No dynamically generated languages.

2. Use the SMO libraries. A combination of SMO not supporting partially trusted callers and using a special type of connection result in SMO being unusable even in UNSAFE assemblies. The obvious workaround is to use SQL DDL, but SMO encompasses more than DDL, for example, configuring service settings via WMI. If you really want to use SMO, it would be possible to call out to a web service or better yet, a Service Broker-based service that uses external activiation, does the SMO calls and returns the script and/or results.

2 thoughts on “Two things you can’t do in SQLCLR

  1. Yes, but I have a library for scripting. For example you can script all tables like this:

    SELECT SqlScript=[dbo].[CreateAs] (‘databasename does not work now’,SCHEMA_NAME(schema_id),object_name(object_id))
    FROM Sys.Tables where name not in (‘SalesOrderHeader’,’Product’)

    It is very alpa :)))))

    This is the example code:
    https://www.netacademia.net/tudastar/default.aspx?upid=15859

    Download the last CreateAs.zip file

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.