Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2016
    Posts
    12
    Provided Answers: 1

    Unanswered: Extracting results from a Stored Proceedure

    I have a Stored procedure that Dynamically Creates a Crosstab from a view.
    It is Dynamic in the sense that it allows for additions in the underlying data that will create more columns in the crosstab output.
    That is working fine.

    What I need assistance with is getting the results from the SP into a Table or View the Database and then being able to Drop and CREATE that table going forward. I am thinking that I will need to trigger the first SP and then a second which will contain the UPDATE unless of course they can be combined.

    So what I need is the SP code to produce a table based on first SP output then because the first SP output is Dynamic DROP and CREATE the table again.

    I have looked at many online articles and have not been able to get any to work. My SP output is Dynamic so I have ended up using the following: -

    SELECT * INTO #TestTableT FROM OPENROWSET('SQLNCLI', 'Server=172-25-109-101\SQLEXPRESS;Trusted_Connection=yes;',
    'EXEC Butchers.dbo.WarehouseActivityCrosstab')
    -- Select Table
    SELECT *
    FROM #TestTableT;

    Now this runs better than any other attempt I have made but still errors as follows: -

    Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 0]
    The metadata could not be determined because statement 'EXEC (@SQLStatement)' in procedure 'WarehouseActivityCrosstab' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set
    Last edited by wscwt01; 04-16-17 at 09:38.

  2. #2
    Join Date
    Feb 2006
    Posts
    175
    Provided Answers: 1

  3. #3
    Join Date
    Jul 2016
    Posts
    12
    Provided Answers: 1
    Hi there,
    I have looked at many of these online articles and have not been able to get any to work. My SP output is Dynamic so I have ended up using the following: -

    SELECT * INTO #TestTableT FROM OPENROWSET('SQLNCLI', 'Server=172-25-109-101\SQLEXPRESS;Trusted_Connection=yes;',
    'EXEC Butchers.dbo.WarehouseActivityCrosstab')
    -- Select Table
    SELECT *
    FROM #TestTableT;

    Now this runs better than any other attempt I have made but still errors as follows: -

    Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 0]
    The metadata could not be determined because statement 'EXEC (@SQLStatement)' in procedure 'WarehouseActivityCrosstab' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

    Any idea on what the syntax might be for this?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,833
    Provided Answers: 15

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •