Report dataset not displaying data in some instances from SQL Stored Procedure

Report dataset not displaying data in some instances from SQL Stored Procedure

Posted by: dbassion on 2 December 2024, 5:05 pm EST

• 

  dbassion

  • Post Options:
  • Link
    Copy

  Posted 2 December 2024, 5:05 pm EST

  I have an Active Report (version 17) with a Dataset that has a SQL stored procedure set up to populate some fields. If I have my SQL stored procedure pulling data a certain way, the Active Report won’t show the resulting data even though it’s the same in both cases. I’ve verified directly through SSMS that the query is pulling the same data in both cases

  This results in no data being displayed on the Active Report
  
  SELECT name, amount, date_added, record_id FROM table1 WHERE record_id = (SELECT TOP 1 record_id FROM #tempTable1)

  If I instead pass a record_id into the stored procedure and change the query to the below, it works with no issue
  
  SELECT name, amount, date_added, record_id FROM table1 WHERE record_id = @record_id

  Am I not able to use temp tables when pulling data for a report? Is there something else going on that I’m missing?

• 

  anand.sinha

  

  • Post Options:
  • Link
    Copy

  Posted 3 December 2024, 6:08 am EST

  Hi David,

  It looks like you are using a Local Temporary Table represented by a single ‘#’. A local temporary table can only be accessed within the same connection/session, and ActiveReports, when running the reports, creates a new connection and is unable to access the Local Temporary Table.

  Instead, you can use a Global Temporary Table represented by a double ‘##’. A global temporary table can be accessed from other connections/sessions as well and exists till the current or any other session that accesses the global temporary table is closed.

  You can learn more about their difference here Local Temp Table and Global Temp Table in SQL Server.

  Please find attached a sample video implementing the Global Temporary Table in ActiveReports for reference.

  Regards,
  
  Anand

  TemporaryTables.zip

• 

  dbassion

  • Post Options:
  • Link
    Copy

  Posted 3 December 2024, 12:24 pm EST

  I switched to a Global Temp Table and still no luck. I’m not dropping the Global Temp Table at the end of the stored procedure either so SQL is handling whether the table still exists or not.

  I’m getting very weird and inconsistent results now. I changed the stored procedure so I don’t pass in any parameters and instead set them to hard-coded values within the stored procedure and it works with Local and Global Temp Tables. It’s also worked in the past with a second Local Temp Table in the stored procedure but having the first Local Temp Table replaced with a hard coded value (SELECT TOP 1 id FROM #tempTable gets replaced with the id value of ‘123’). So it seems like in some instances, Local and Global Temp Tables work, but in others they don’t. Can you shed any light on this?

• 

  anand.sinha

  

  • Post Options:
  • Link
    Copy

  Posted 4 December 2024, 5:38 am EST

  Hi David,

  Looks like an SQL Server-specific issue, we would suggest that you try connecting and populating a DataTable in a .NET Console application using SqlServerConnection/Adapter as it will help you in debugging the cause of the issue, once you can populate the DataTable this way you can then use the connection string used in your console application directly in ActiveReports and it’ll work as expected.

  Regards,
  
  Anand

• 

  dbassion

  • Post Options:
  • Link
    Copy

  Posted 5 December 2024, 9:43 am EST

  If it were a SQL Server specific issue, then how would I be getting data to populate into the report when I pass in hard coded values as parameters (ex., dbo.ProcedureName ‘parameter1’, 1) but not when I use the Active Report parameters (ex., dbo.ProcedureName @parameter1, @parameter2)? Note that I’ve added the parameters to display on the report and they show the data that I’m expecting. Also, when I call the stored procedure directly through SSMS it pulls the data as expected.

• 

  anand.sinha

  

  • Post Options:
  • Link
    Copy

  Posted 6 December 2024, 6:35 am EST

  Hi David,

  Apologies for any confusion, with SQL Server specific issue I mean that the issue is related to the connection being made to the SQL Server, as we tried replicating the same on our end which seems to work as expected (as shown in our previously attached video), to debug the cause of this issue we suggest creating a .NET Console app that runs the same query you want to run through ActiveReports to populate a DataTable.

  This process will make it easier to debug using breakpoints and resolve the issue you are facing.

  Hope it helps!

  Regards,
  
  Anand