Hitting my head against a wall here, but can't seem to use #table in a script to load data from SQl 2008R2. The query just seems to return no data, and produces no errors.
Check whether your ODBC connection test is ok or not,
#table will only be visible to the account that created the connection and while the session lasts. Where did you create this temporary table? During a QlikView SQL session?
Yes. So something like this:
SQL SELECT something INTO #temptable
SELECT * FROM #temptable;
But for some reason no data comes back. My question pertains more to the ability to use #tables in Qlikview SQL Script. Is it possible or is it known not to work?
That should work allright if you use a sequence like that in a stored procedure.
But I'm not sure about this working in a QlikView session as the DBMS may consider this to be two sessions. Control returns to QlikView between the two SELECTs. Why don't you use a resident table as temporary storage? Do you expect the temporary table to be exceptionally large?
The main issue is that I have read-only access to the server so I don't have the luxury of creating SP's or views at all. For now I've exported the results to CSV and read them into a QVD (with some ETL). I suspect that QV may be using two different sessions, since the #table ALWAYS contains nothing. I've used the same query in SSMS and got results so it's not a syntax issues.
A bit late but you could do the same thing using a CTE.
Using your example:
with temptable as (
from something )
//for more tables