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”
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
Hi Bob,
Wanna know something else you can’t do in SQLCLR? Get the name of the currently executing proc. i.e. the equivalent of OBJECT_NAME(@@PROCID) in T-SQL.
I’ve submitted a connect posting about this (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=189211) where status is ‘Fixed’ but no details have been provided.
Regards
Jamie
[jamieDOTthomsonATconchangoDOTcom]
Comments are closed.