none
The metadata could not be determined because the statement RRS feed

  • Question

  •   

    We recently upgraded our SQL server from 2008R2 to 2017 - the below piece of code is not working, any alternative solution?

    We have 100 IF condition in our business proc with dynamic result set, hence I guess I cannot use WITH RESULT SETS clause easily?

     /*
     Create or alter procedure t1_test @t1 int
     as
     begin
     set fmtonly off
     if @t1=1
     select getdate() dt, @@SERVERNAME s
     if @t1=2
     select GETDATE() d2  
     end
     */
      
    -- Insert into Temp Table
    SELECT * INTO #TempTable
    FROM OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;',
    'set fmtonly off; EXEC mydb1.dbo.t1_test 2')
    drop table #TempTable
    GO
     
     


    Error:

    Msg 11512, Level 16, State 1, Procedure sys.sp_describe_first_result_set, Line 1 [Batch Start Line 0]
    The metadata could not be determined because the statement 'select getdate() dt, @@SERVERNAME s' in procedure 't1_test' is not compatible with the statement 'select GETDATE() d2' in procedure 't1_test'.


    Mahesh

    Tuesday, May 14, 2019 8:28 PM

All replies

  • Starting Sql 2012, OPENQUERY and OPENROWSET needs to provide the structure of the result set returned; as it has started using sys.sp_describe_first_result_set

    Read below link, this will help you -

    http://www.sommarskog.se/share_data.html#OPENQUERY

    • Proposed as answer by sanyouss Thursday, May 16, 2019 8:20 AM
    Tuesday, May 14, 2019 8:41 PM
  • Hi Mahesh Dasari,

     

    When you run a query with OPENQUERY, SQL Server needs to know at compile time which columns OPENQUERY returns. There is an similar issue with yours ,and please refer to it. How to use "WITH RESULT SETS" clause in SQL 2012 for dynamic column names.

     

    In above article , as Erland Sommarskog said that 'It seems that you are doing some dynamic pivot, and that's all right, but why do you try to run this through OPENQUERY? That is not the right way to do it.', so I think you'll inevitably  use WITH RESULT SETS.

     

    Or please try following script.

     

    /*
     Create or alter procedure t1_test @t1 int
     as
     begin
     set fmtonly off
     if @t1=1
     select getdate() dt, @@SERVERNAME s
     if @t1=2
     select GETDATE() dt, NULL s  
     end
     */
      
    -- Insert into Temp Table
    SELECT * INTO #TempTable
    FROM OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;',
    'set fmtonly off; 
    EXEC mydb1.dbo.t1_test 2 
    WITH RESULT SETS ((dt datetime,
                    s nvarchar(50)))')
    drop table #TempTable
    GO
    


     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by sanyouss Thursday, May 16, 2019 8:20 AM
    Wednesday, May 15, 2019 3:03 AM
  • I cannot utilize WITH RESULT SETS as I have to make that code for almost 100 output conditions. 

    Basically, we have a SP (having 100 different Selects  which is run based on different conditions)  and its output is sent to other parties via email as attachments.

    Thanks

    Mahesh


    Mahesh

    Wednesday, May 15, 2019 5:52 PM
  • Hi Manesh,

    Just as an idea, you can generate output of the stored procedure as XHTML.

    It will allow you to send it as e-mail attachment as a 'blob' regardless of its structure.

    Wednesday, May 15, 2019 6:08 PM
  • Hi Manesh,

    Just as an idea, you can generate output of the stored procedure as XHTML.

    It will allow you to send it as e-mail attachment as a 'blob' regardless of its structure.

    I have limited control there, in our case the output is sent in different format csv, txt, etc. and with different delimiters.


    Mahesh

    Wednesday, May 15, 2019 7:31 PM
  • Hi Manesh,

    In such case the stored procedure can return XML, and the middle tier will convert it into "...csv, txt, etc..." formats.

    Wednesday, May 15, 2019 7:37 PM
  • Hi Manesh,

    In such case the stored procedure can return XML, and the middle tier will convert it into "...csv, txt, etc..." formats.

    Do you have any example? Thanks in advance.

    Mahesh

    Thursday, May 16, 2019 10:53 AM