Skip to content

Conversation

@srutzky
Copy link
Contributor

@srutzky srutzky commented Sep 17, 2019

Fix hierarchyid datatype name in "clr enabled" config option

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:

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".
*/

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:

  1. Added EXEC to the stored procedure line because while it works without it, it only works when it's 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:

-- 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

-----------------------------

EXEC sp_configure 'clr enabled', 0; RECONFIGURE;
EXEC sp_configure 'clr enabled'; -- verify

DECLARE @Good hierarchyid;
SET @Good = '/1/';

SELECT FORMAT(GETDATE(), N'D');

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
```
@PRMerger14
Copy link
Contributor

@srutzky : Thanks for your contribution! The author(s) have been notified to review your proposed change.

@rothja
Copy link
Collaborator

rothja commented Sep 26, 2019

@srutzky Thank you for the detailed explanation and your test results. Appreciate the thoroughness. #sign-off

@rothja rothja merged commit 964d8de into MicrosoftDocs:live Sep 26, 2019
@srutzky srutzky deleted the patch-2 branch November 15, 2019 19:35
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Projects

None yet

Development

Successfully merging this pull request may close these issues.

6 participants