Temp table variables doesn't seem to be working when SP is called from c#

I am calling a stored procedure from C# code. This SP creates temp tables to store some values. When I run this SP from Query Analyzer everything seems to be fine. But when I run it from c# code, temp tables are not getting created.

I am using @tableName and not #tableName.

The temp tables are being referred only in stored procedure. They are not needed outside the SP. enter image description here Is there anything I am missing out ?

Solution

That happens because the user credentials you are using in Query analyzer ('sa' maybe?) are probably not the same than the ones used by the C# application.

The user needs to have access to system database tempdb (read/write) if not he cannot create those temporal tables (within the procedure) when the stored procedure is called.

Give access to tempdb to the user that C# uses or test your C# app login in with the same user you use in Query Analyzer (or any other dbadmin user) just to test it works.

As per your comment asking about SQL Profiler by statement.

Step 1: open sql profiler and create a new trace. go to tab event selection. and unmark Security Audit and Sessions

enter image description here

Step 2: check the Show all events checkbox on the bottom right and you will get several events on the grid. Go to Stored Procedures and T-SQL and mark the ones in the screenshot below.

enter image description here

Step 3: run it.