Minor fixes for 2 pages related to "clr enabled" server config option#2999
Merged
rothja merged 2 commits intoMicrosoftDocs:livefrom Sep 26, 2019
Merged
Minor fixes for 2 pages related to "clr enabled" server config option#2999rothja merged 2 commits intoMicrosoftDocs:livefrom
rothja merged 2 commits intoMicrosoftDocs:livefrom
Conversation
It was "hierarchy", which is not the actual datatype name. I also added the `FORMAT` function to the list to give a better indication of the scope of what will not work when "lightweight pooling" is enabled. Test: ```sql EXEC sp_configure 'clr enabled', 0; RECONFIGURE; EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'lightweight pooling', 1; RECONFIGURE; -- requires service/instance restart -- once instance has been restarted: DECLARE @nope hierarchyid; -- this, by itself, succeeds. SET @nope = '/1/'; -- this, not so much. /* Msg 5846, Level 16, State 2, Line XXXXX Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two options: "clr enabled" or "lightweight pooling". */ ```
The "clr enabled" server config option is _not_ an advanced options. Having the `sp_configure 'show advanced options', 1;` line in the example code has been confusing many people for years. Also: 1. Added `EXEC` to the stored procedure line because while it works without it, it only works when it is a single statement. If anyone attempts to execute two `sp_configure` statements in the same batch (or any other stored procedure without preceding it with `EXEC`) they will get an "incorrect syntax" error. Best to always prefix stored procedures with `EXEC`. 2. Removed the `GO` between `EXEC sp_configure...` and `RECONFIGURE` as it is entirely unnecessary. 3. Added clarification that only user-defined SQLCLR objects are affected. Built-in functionality that relies upon the CLR will continue working as expected, and only stops working if "lightweight pooling" is enabled. Test: ```sql -- Turn "show advanced options" OFF to show that "clr enabled" still works: EXEC sp_configure 'show advanced options', 0; RECONFIGURE; EXEC sp_configure 'show advanced options'; -- verify EXEC sp_configure 'clr enabled', 1; RECONFIGURE; EXEC sp_configure 'clr enabled'; -- verify ```
Contributor
|
@srutzky : Thanks for your contribution! The author(s) have been notified to review your proposed change. |
Collaborator
|
@srutzky Thank you for the detailed explanation and your test results. Appreciate the thoroughness. #sign-off |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Fix
hierarchyiddatatype name in "clr enabled" config optionIt was "hierarchy", which is not the actual datatype name.
I also added the
FORMATfunction to the list to give a better indication of the scope of what will not work when "lightweight pooling" is enabled.Test:
Removed unnecessary setting "show advanced options" for "clr enabled"
The "clr enabled" server config option is not an advanced option. Having the
sp_configure 'show advanced options', 1;line in the example code has been confusing many people for years.Also:
EXECto the stored procedure line because while it works without it, it only works when it's a single statement. If anyone attempts to execute twosp_configurestatements in the same batch (or any other stored procedure without preceding it withEXEC) they will get an "incorrect syntax" error. Best to always prefix stored procedures withEXEC.GObetweenEXEC sp_configure...andRECONFIGUREas it is entirely unnecessary.Test: