2

I got a nested stored procedure to get some report from third party application to my local application. The stored procedure is working as expected and returns the result, when I am trying to get it temp table showing the below error.

The metadata could not be determined because statement 'INSERT INTO [#TblSrc] EXEC [prcGetReportList] @Src' in procedure 'sysGetBalanceBill' uses a temp table.

This procedure exists in a third-party application.

My stored procedure looks like this:

ALTER PROCEDURE [dbo].[usrGetBalanceBystore]
    @Customer VARCHAR(100),
    @Store VARCHAR(100)
AS
BEGIN
    IF NOT EXISTS (SELECT * FROM tblRpBase 
                   WHERE idrpTable = '2B1A7150-FD1B-4D7B-A9AC-8AB7C83CC4AC')
    BEGIN
        INSERT INTO tblRpBase 
            SELECT * 
            FROM tblRpBaseDummy 
            WHERE idrpTable = '2B1A7150-FD1B-4D7B-A9AC-8AB7C83CC4AC'
    END

    EXECUTE sysGetBalanceBill '1/1/2018 0:0:0.0', '3/26/2018 23:59:59.998',
                              @Customer, @Store
END

and I'm trying to insert into the temp table like this:

DECLARE @TempTable1 TABLE 
        (       
            [InvNum] [INT],   
            [InvDate] [DATETIME] ,   
            [Customer] [UNIQUEIDENTIFIER] ,   
            [CustName] [VARCHAR](255),   
            [CustLName] [VARCHAR](255),   
            [InvTotal] [FLOAT]
      )

INSERT INTO @TempTable1
    SELECT * 
    FROM Openrowset('SQLNCLI','Server=Name;Database=DbName;Uid=sa;Pwd=Password',
  'EXECUTE [DbName1].[dbo].[usrGetBalanceBystore] "00000000-0000-0000-0000-000000000000",
 "00000000-0000-0000-0000-000000000000"')

SELECT * 
FROM @TempTable1 
WHERE CustName = 'ABC'

this line of code return empty table 

 SELECT * 
        FROM Openrowset('SQLNCLI','Server=Name;Database=DbName;Uid=sa;Pwd=Password',
      'EXECUTE [DbName1].[dbo].[usrGetBalanceBystore] "00000000-0000-0000-0000-000000000000",
     "00000000-0000-0000-0000-000000000000"')

if i run only this procedure i can find result

EXECUTE [DbName1].[dbo].[usrGetBalanceBystore] "00000000-0000-0000-0000-000000000000",
         "00000000-0000-0000-0000-000000000000"

How can I achieve the above task?

NAJEEB
  • 251
  • 2
  • 13
  • On a matter of symantics `@TempTable1` isn't a temporary table, it's a table Variable. The 2 are quite different. – Thom A Mar 30 '18 at 10:02
  • i tried with temp table also, while i was searching i got this soluton i just tried this way. also find same. Also i tried real table but i end with the same error. – NAJEEB Mar 30 '18 at 10:04
  • On a more related note, I can't see references to `#TblSrc` or`prcGetReportList`. What is the SQL for the procedure? What is it you're trying to achieve here, and why are you putting the results into a temporary table? I *assume* you are creating the object `#TblSrc` before running the statement? – Thom A Mar 30 '18 at 10:06
  • @Larnu Yes, #TblSrc and prcGetReportList from Third party application. It will check like(Store,Customer,etc) thats the source for the report. – NAJEEB Mar 30 '18 at 10:11
  • 2
    Maybe [with result sets](https://www.databasejournal.com/features/mssql/usage-and-benefits-of-using-with-result-sets-in-sql-server-2012.html) helps. At least the error sounds like that's what it is missing – James Z Mar 30 '18 at 10:25
  • 1
    See https://stackoverflow.com/questions/18346484/ssis-package-not-wanting-to-fetch-metadata-of-temporary-table for a very similar issue. – RnP Mar 30 '18 at 14:29
  • using result sets , not showing any error executes good. but not data returns. it is empty table. – NAJEEB Mar 31 '18 at 09:45

0 Answers0