Ssms works on procedure, but php crashes #tmp

I have a strange issue. I have a very large memory driven stored procedure that does all the work in #tmp tables. Runs perfect from ssms, and .net query, but if I execute the procedure from php it never returns the correct results, it crashes during one of the inserts into #tmp??

I fixed by changing my #tmp table to a table variable, but this one has me confused. All options are set the same from what I can see. Any idea why there is a difference?

The same issue happens if you use a stored procedure with a temp table in SSIS. Switching it to a table variable works, though that is not ideal as table variables aren't great for performance.

Pretty sure Microsoft has to fix this bug.

1 Like

Well glad to know I wasn't the only one who experienced a headache with this:)

It's because of the session scope of a Temp Table. Not sure it's an actual bug although I'd really like to see it work the way any reasonable person would expect it to. Table Variables can be pretty bad for performance because they don't use statistics and the optimizer generally treats them as only having one row unless you use a statement level recompile after the Table Variable has been populated.

I think you are correct in it being session related, but if a stored procedure is executed from a php stAtement until it's completion, and then returns the results of that procedure how does that fall out of a session scope and be the intended way it works?

How is it a session scope problem if the stored procedure creates/uses the temp table only? There are no other sessions involved in that case.

Not 100% sure how PHP works but it appears to be a problem similar to using xp_CmdShell... if a Temp Table is involved, it won't work because it's a different scope.

But it's within a stored procedure.

Like this:

CREATE PROC someproc
AS
create table #t1 (col1 int)

insert into #t1
blah

select ...
from #t1

drop #t1
GO

This doesn't work in SSIS in certain circumstances. I believe it was in a data transformation, but I'd have to dig to find it as this was at my last job. If the temp table is all contained within one single stored proc, then it should be one scope. I shouldn't have to switch it to a table variable in this case.

Exactly. MS didn't do such a hot job when it comes to the scope of a Temp Table.