\nI heard that when SQL Server 2005 ships, SQLCLR (the ability to execute .NET code in SQL Server) will be disabled by default. This is part of an "off-by-default" philosophy that goes "because there are so many revolutionary new features in SQL Server 2005, and DBAs and programmers may not have had time to digest the ramifications of using all of them, you'll have to enable them, one at a time, when you understand them. They'll be off until you make a conscious decision to turn them on". IIS 6.0 works this way with its programming extensions; for example, you must enable using ASP.NET or ASP classic.\n<\/p>\n
\nThought I'd better look up how to turn it on and off:\n<\/p>\n
\nEXEC sp_configure 'show advanced options', 1
\nGO
\nRECONFIGURE
\nGO
\n— turn it on
\nEXEC sp_configure 'clr enabled', 1
\nGO
\nRECONFIGURE
\nGO\n<\/p>\n
\n— or turn it off
\nEXEC sp_configure 'clr enabled', 0
\nGO
\nRECONFIGURE
\nGO\n<\/p>\n
\nThat was easy. I turned it off and restarted SQL Server wanting to see what error message it would produce. Some folks I've spoken to claim to want it off even though they understand it, because SQLCLR loads the .NET runtime. This takes about 10 meg of memory, from SQL Server's "normal" memory pool (it does not use the MEM-to-leave pool as in-SQL Server COM components do). Turning SQLCLR off did not cause the CLR to be unloaded (or at least it didn't produce a log message to that effect). So I wanted to see if I could get the .NET runtime to load if SQLCLR is disabled. Started with:\n<\/p>\n
\nCREATE ASSEMBLY foo FROM 'c:\\foo.dll'
\nGO\n<\/p>\n
\nEven though foo.dll doesn't exist on my machine, this statement normally causes to .NET runtime to load. Even though it errors out with the message:\n<\/p>\n
\nMsg 6501, Level 16, State 7, Line 1
\nCREATE ASSEMBLY failed because it could not open the physical file 'c:\\foo.dll': 2(The system cannot find the file specified.).\n<\/p>\n
\nThe reason that the .NET runtime is loaded in this case in that SQLCLR internally calls Assembly.Load("c:\\foo.dll") (or some variation) to load the assembly and validate it using the reflection APIs. Both Assembly.Load and reflection are, of course, managed code, requiring the runtime.I expected a different error this time, because SQLCLR is disabled. Same error. And the log revealed that the .NET runtime had been loaded. Even though SQLCLR is disbaled. Hmm….didn't expect that. Just to see how far I could go, I got out a real assembly and ran:\n<\/p>\n
\nCREATE ASSEMBLY MetricConverter
\n FROM 'c:\\types\\metricconverter.dll'\n<\/p>\n
\nThis succeeded, I'd cataloged my assembly. Hmm… How about:\n<\/p>\n
\nCREATE FUNCTION convertme(@a FLOAT)
\nRETURNS FLOAT
\nAS EXTERNAL NAME MetricConverter.[DM.EssentialYukon.MetricConverter].KilometersToMiles\n<\/p>\n
\nThis succeeded too. At this point I started to doubt that I'd actually turned SQLCLR off. Then I ran:\n<\/p>\n
\nDECLARE @f FLOAT
\nSET @f = dbo.convertme(42)
\nPRINT @f\n<\/p>\n
\nThis failed as expected:\n<\/p>\n
\nMsg 6263, Level 16, State 1, Line 2
\nExecution of user code in the .NET Framework is disabled.\n<\/p>\n
\nSo the 'clr enabled' option disables *running* user CLR code in SQL Server 2005. It doesn't diable loading the runtime. Or cataloging database objects, like assemblies and UDFs, that use SQLCLR. It's a convenience to allow DBAs (usually the only ones with this permission) to catalog these objects before allowing the actual user CLR code to be executed. If you're really concerned about the 10 meg, don't use the DDL. Note to self: test *everything* before making assumptions. I guess that goes along with “off-by-default”.<\/p>\n","protected":false},"excerpt":{"rendered":"
I heard that when SQL Server 2005 ships, SQLCLR (the ability to execute .NET code in SQL Server) will be disabled by default. This is part of an "off-by-default" philosophy that goes "because there are so many revolutionary new features in SQL Server 2005, and DBAs and programmers may not have had time to digest […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,38],"tags":[],"class_list":["post-1018","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005","category-sqlclr"],"yoast_head":"\n