Testing if CLR is enabled from within a function

• cmartel 20772

  Ten Centuries

  Points: 1233

  More actions

  March 5, 2014 at 1:59 pm

  #396378

  I just wrote my first CLR function. It is a C# rewrite of a T-SQL function that crawls the nodes of large diagram in fraction of a second instead of nearly one minute.

  Since I want my application to be independent from the decisions of DBAs, I am testing the 'clr enabled' value in order to call either the CLR or the T-SQL function from within a wrapper T-SQL function with code similar to:

  IF EXISTS(SELECT NULL FROM sys.configurations WHERE name = 'clr enabled' AND value = 1)

  INSERT INTO @tempTable SELECT * FROM clrFastFunction

  ELSE

  INSERT INTO @tempTable SELECT * FROM tsqlSlowFunction

  Problem is that SQL server gives the error "Execution of user code in the .NET Framework is disabled" as soon as clr is disabled, like if clrFastFunction was called anyway.

  Is there a way to achieve what I want to do?

• Solomon Rutzky

  SSCoach

  Points: 16430

  More actions

  March 5, 2014 at 2:13 pm

  #1694492

  Just encapsulate the SELECT for the INSERT in an EXEC so that it is not a parse-time issue. For example:

  DECLARE @Table TABLE (IntVal INT NOT NULL)

  IF (1 = 0)

  BEGIN

  INSERT INTO @Table (IntVal)

  EXEC('

  SELECT IntVal

  FROM SQL#.Util_GenerateInts(1, 4, 1)

  ')

  END

  What is inside of the EXEC is not parsed unless the EXEC itself is run. I get around many parse-time issues in this manner :-).

  Take care,

  Solomon..

  SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
  Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
  Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
  Info sites — Collations     •     Module Signing     •     SQLCLR

• cmartel 20772

  Ten Centuries

  Points: 1233

  More actions

  March 5, 2014 at 2:28 pm

  #1694496

  Using EXEC works well in a standard script but calling it from within a function gives the error "Invalid use of a side-effecting operator 'INSERT EXEC' within a function".

• Solomon Rutzky

  SSCoach

  Points: 16430

  More actions

  March 5, 2014 at 2:48 pm

  #1694503

  cmartel 20772 (3/5/2014)

  ---

  Using EXEC works well in a standard script but calling it from within a function gives the error "Invalid use of a side-effecting operator 'INSERT EXEC' within a function".

  Yeah, I missed that part the first time I read it :(. Does it need to be in a function or can it be in a Stored Proc? I just tried encapsulating the CLR function call in an Inline TVF but got the parse-time error.

  SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
  Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
  Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
  Info sites — Collations     •     Module Signing     •     SQLCLR

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  March 5, 2014 at 4:53 pm

  #1694519

  It's fugly, but have you tried wrapping your clr function in a t-sql function?

  That will prevent the parser error.

  I have tested it on a "hello world" function to make sure it works.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]