topic Re: T-SQL #table in Qlikview Load Script in QlikView

T-SQL #table in Qlikview Load Script

Sat, 30 Jan 2016 08:55:16 GMT

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.

Re: T-SQL #table in Qlikview Load Script

HirisH_V7 — Sat, 30 Jan 2016 20:24:02 GMT

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

Re: T-SQL #table in Qlikview Load Script

jonas_rezende — Mon, 01 Feb 2016 10:32:29 GMT

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

Peter_Cammaert — Mon, 01 Feb 2016 10:37:27 GMT

#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

Re: T-SQL #table in Qlikview Load Script

Mon, 01 Feb 2016 10:41:50 GMT

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?

Re: T-SQL #table in Qlikview Load Script

Peter_Cammaert — Mon, 01 Feb 2016 11:07:40 GMT

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?

Re: T-SQL #table in Qlikview Load Script

Mon, 01 Feb 2016 11:43:50 GMT

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

Re: T-SQL #table in Qlikview Load Script

Fri, 04 Mar 2016 15:26:52 GMT

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.

;