3 Replies Latest reply: Jun 5, 2015 9:24 AM by Herbert Schager RSS

    Load from MSSQL stored procedure with / without temporary table

      Hello Experts,

      I experienced some strange behaviour when I tried to load data from a stored procedure (MSSQL 2008 R2). My original task is a bit different, but after some analysis I was able to reduce the question to the following:
      In which way can the definition of a stored procedure influence the results in QlikView, while the SQL-results stay the same? What exactly does the temporary table change?
      How to reproduce the behaviour...
      Step 1: define the procedure
      CREATE PROCEDURE [dbo].[SimpleExample]
      as
      select 1 as ID
      Step 2: use the procedure in a qvw-load-script
      SQL exec [dbo].[SimpleExample] ;
      Step 3: check the result --> as expected
      Step 4: modify the procedure in the following way
      ALTER PROCEDURE [dbo].[SimpleExample]
      as
      declare @tmptab table(ID int)

      insert into @tmptab(ID)
      select 1 as ID
      select ID from @tmptab

      Step 5: reload the qvw

      Step 6: check the result --> no data was found

       

       

      The results for both versions are the same, when the procedure is called via SQL Management Studio.

       

      According to SQL Profiler both versions are called in the exact same way.

       

       

      Thank you very much in advance for explanations.