T-SQL #table in Qlikview Load Script

[#1]

Hi All

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.

[HirisH_V7]

Hi,

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.

Video: Load Data from ODBC

HTH,

Hirish

[jonas_rezende]

Hi, jarppiduplessis.

This query run in data base own of the MS-SQL Server? Maybe the problem is in the query clause.

Hope this helps!

[Peter_Cammaert]

#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?

Peter

[#5]

HI Peter,

Yes.  So something like this:

TableName:

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?

[Peter_Cammaert]

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?

[#7]

Hi Peter

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.

Thanks though!!

[#8]

A bit late but you could do the same thing using a CTE.

Using your example:

TableName:

SQL

with temptable as (

select something

from something )

//for more tables

,temptable2 as(

select something

from something)

SELECT *

FROM temptable

join temptable2

     etc.

;