3

First and foremost, the data server this is happening on is running SQL Server 2005 SP4, and unfortunately it's not an option to upgrade. We've been pushing the client to upgrade for the three years we've managed this thing but it still hasn't happened.

This data server hosts a ton of SQL stored procedures, many of which call .NET assembly functions that query other data sources. My current task is to get one of these SPs ("sample2") to return a piece of information that another SP gathers ("sample1"), and instead of duplicating the code, I thought I'd just call the second SP from the first, but right now I'm getting this error:

Msg 6522, Level 16, State 1, Procedure stacked_proc_sample, Line 5 [Batch Start Line 36]
    A .NET Framework error occurred during execution of user-defined routine or aggregate "GetUserStatus": 
    System.InvalidOperationException: Data access is not allowed in this context.  Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

Here's my current code. I wrote these two dummy SPs to narrow down the problem. "GetUserStatus" is a function from an Assembly written in C# that pulls information from some other data system and returns a SQL Server table, and I do have the code for it, but am not an expert on SQL assemblies. The assembly is currently using the "unrestricted" permission set.

create procedure stacked_proc_sample1
AS
BEGIN
    select firstname,lastname from GetUserStatus([params])
END;


create procedure stacked_proc_sample2
AS
BEGIN
    declare @results table(
        firstname varchar(50),
        lastname varchar(50)
    )
    insert into @results --If I remove this "insert" line, this SP works
        exec stacked_proc_sample1

    select * from @results

END;

exec stacked_proc_sample1; --works
exec stacked_proc_sample2; --error

The first "sample" proc successfully calls the assembly function and returns data. The second "sample" that calls the first one throws the error. The issue seems to involve that "insert into" statement; if I remove it, it executes without error (but I'm then not capturing the data I need). If I copy the temporary table logic into the first SP, I get the same behavior; the first SP works but not when I nest it into the second one.

Is there another way I should be capturing the return from the first "sample"? I'd like to not overhaul how the inner SP returns its data, but I can freely change how it's called by the outer SP, or change properties on the assembly. And the real "sample1" has a big logic fork in it where it calls one of eight "GetUserStatus" alternatives, so I really don't want to copy/paste all that logic into the real "sample2".

7
  • Is the GetUserStatus function decorated with DataAccessKind.Read or SystemDataAccessKind.Read attributes? Aug 25, 2021 at 22:04
  • Will attempt to write out the data into a regular table in the same database fail?
    – Anton Grig
    Aug 25, 2021 at 22:26
  • @AlwaysLearning I'd suspect a bug here: the DataAccessKind should not be relevant because the CLR function is not doing the data access. It looks like the compiler is pushing through the INSERT logic all the way to the inner query plan Aug 25, 2021 at 22:35
  • Are you at least on the latest build of SQL Server 2005? I'd suggest let's get this migrated to Database Administrators, there are some people who should have a better idea on this Aug 25, 2021 at 22:36
  • I just tried adding DataAccessKind.Read (it wasn't explicitly set before) and that does not seem to have helped. I also tried inserting into a real table, same deal. I am on SQL version 9.00.5000 which looks like the base version of 2005 SP4. I can see if I can upgrade to a newer patch; they just don't want to budget all the regression testing necessary to go up a full version or more.
    – Edward
    Aug 26, 2021 at 17:34

1 Answer 1

0

I've come up with a suitable workaround for this using an OUTPUT parameter on the inner SP ("sample1") instead of using INSERT EXEC in "sample2".

I'm not sure if you can return a table in this manner, but the real "sample1" selects its results as XML, so I really only need to pass back one field anyway. And if it did select a table, it would be easy enough to turn it into XML just to stuff it into the return variable.

alter procedure stacked_proc_sample1
(
    @output xml=null OUTPUT
)
AS
BEGIN
    --Selecting as XML now to better-match my actual use case.
    --Previously supplied example could just use two varchar output variables.
    select @output=
        (
        select firstname from GetUserStatus[params]
        FOR XML PATH('PERSON'), ROOT('ROOT'), ELEMENTS XSINIL
        )
END;

alter procedure stacked_proc_sample2
AS
BEGIN
    declare @_output xml;
    stacked_proc_sample1 @_output OUTPUT; --OUTPUT param doesn't throw the error my "INSERT EXEC" version threw
    --From here I can select the "firstname" node in the XML
    select @_output;
END;
1
  • 1
    You want to add , TYPE to the FOR XML. Not clear why you need ROOT('ROOT') or ELEMENTS XSINIL Aug 26, 2021 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.