I am trying to pull data from a stored procedure SQL Server 2016. Everything works great until I try to pull from a stored procedure that uses temp tables. I initially thought that it was a permissions issue with the service account connecting to SQL Server but apparently there are no special permissions required to create or drop temp tables.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c903a88b-1a5b-4ba7-ab3d-81156e0dec72/permis...
Additionally, I connected to the database in SSMS using the service account and was able to run the stored procedure with no issues. I suspect I could rewrite the code to use table variables on CTEs on the SQL side but this would be a huge pain since I have to pull from dozens of existing store procs with temp tables. Any help would be greatly appreciated!
LIB CONNECT TO 'DB0123_ABC';
MyTest:
LOAD
*;
SQL EXEC Apps_Custom_DHP.dbo.usp_MySimpleSPTest
