I have a stored procedure which is called by a third-party tool. I believe it to be using ODBC, but it is essentially a black box. The DBMS is SQL Server 2012.
When the stored procedure is a simple select, the third-party tool receives the result set, but when it accesses a temporary table first, it does not. For example, the following two stored procedures return the same result from SSMS, but only the first can be called successfully from the tool.
What I don't understand is how these two functions are different from an external perspective. They both take the same parameters, and the last query yields the same result.
Where is the stored procedure executed?
--This one works when called both ways CREATE PROC [dbo].[sp_GetUsers] AS Select Top 1000 userId from Users --This only works from SSMS. CREATE PROC [dbo].[sp_ComplicatedGetUsers] AS If OBJECT_ID('tempdb..#TMPUsers') IS NOT NULL BEGIN DROP TABLE #TMPUsers END Select Top 1000 userId INTO #TMPUsers from Users Select * from #TMPUsers