Check whether your ODBC connection test is ok or not,
- open QlikView
- go to File - Edit Script
- for database choose OLE DB
- check Force 32 Bit (i have PS on 64bit win)
- Check Relative Paths
- click button Connect
- In Data Link Properties, on Provider window, choose "Microsoft OLE DB Provider for SQL Server"
- In Data Link Properties, on Connection window, choose server name
- use Win NT integrated security or specific user name and password
- Click button: Test Connection
- Then on Edit script section, choose Select button, and choose appropriate Database and Owner to select from tables in database.
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.