Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Testing if CLR is enabled from within a function Expand / Collapse
Author
Message
Posted Wednesday, March 5, 2014 1:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 6:39 AM
Points: 9, Visits: 53
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?
Post #1548011
Posted Wednesday, March 5, 2014 2:13 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 6:35 PM
Points: 368, Visits: 1,942
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# - http://www.SQLsharp.com/
Post #1548019
Posted Wednesday, March 5, 2014 2:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 6:39 AM
Points: 9, Visits: 53
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".
Post #1548025
Posted Wednesday, March 5, 2014 2:48 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 6:35 PM
Points: 368, Visits: 1,942
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# - http://www.SQLsharp.com/
Post #1548032
Posted Wednesday, March 5, 2014 4:53 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:47 PM
Points: 1,778, Visits: 5,730
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


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1548049
    Posted Wednesday, March 5, 2014 7:05 PM This worked for the OP Answer marked as solution


    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: 2 days ago @ 6:35 PM
    Points: 368, Visits: 1,942
    Yes, a Multistatement TVF (as opposed to an Inline one like I had tried earlier, or even a View) seems to work.

    So first this:
    CREATE FUNCTION dbo.TestWrapper()
    RETURNS @Temp TABLE (IntVal INT)
    BEGIN

    INSERT INTO @Temp (IntVal)
    SELECT IntVal
    FROM SQL#.Util_GenerateInts(1, 4, 1)

    RETURN

    END

    and then this should work:
    DECLARE @Table TABLE (IntVal INT NOT NULL)

    IF (1 = 0)
    BEGIN

    INSERT INTO @Table (IntVal)
    SELECT IntVal
    FROM dbo.TestWrapper()

    END

    However, if this is called repeatedly or if the CLR function returns a lot of rows, doing a Multistatement TVF could be inefficient. In that case you would be left with the Stored Procedure option.

    Hope that helps.

    Take care,
    Solomon...





    SQL# - http://www.SQLsharp.com/
    Post #1548065
    Posted Thursday, March 6, 2014 9:08 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Tuesday, August 5, 2014 6:39 AM
    Points: 9, Visits: 53
    Tricking the T-SQL compiler is a clever solution. Better, performance penalty is insignificant. Thanks a lot!
    Post #1548339
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse