Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

T-SQL #table in Qlikview Load Script

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.

7 Replies
hirishv7
Honored Contributor

Re: T-SQL #table in Qlikview Load Script

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
Valued Contributor

Re: T-SQL #table in Qlikview Load Script

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!

Re: T-SQL #table in Qlikview Load Script

#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

Not applicable

Re: T-SQL #table in Qlikview Load Script

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?

Highlighted

Re: T-SQL #table in Qlikview Load Script

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?

Not applicable

Re: T-SQL #table in Qlikview Load Script

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!!

Not applicable

Re: T-SQL #table in Qlikview Load Script

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.

;

Community Browser