Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor III

SQL temporary tables (phisical, local and global) does not return data

Hi everyone.

Does anyone knows a workaround to make this query to return data to QV? It works when running on SQL, but returns no data from QV load script.

Hints

1) Creating SP is not a solution due SQL permission/restrictions

2) SQL and connection is fine. It works when query does not create temp tables (phisical, #local or ##global ones).

Thanks in advance

test:

LOAD *;

SQL

select top 5 *

into ##tmp

from <table> with(nolock)

select * from ##tmp;

14 Replies
Highlighted
Contributor III

Re: SQL temporary tables (phisical, local and global) does not return data

Creates a temporary table in the Qlikview script itself:

sql

create table #table (.......

insert into #table ........

Highlighted
New Contributor III

Re: SQL temporary tables (phisical, local and global) does not return data

Tanks for your reply Marcio, but still same situation: No data is obtained by QV using your syntax. Running in SQL is fine.

SQL

create table #temp (

  fechaalta datetime,

  fechabaja datetime)

insert into #temp (fechaalta, fechabaja)

select top 10 fechaalta, fechabaja

from dw_suscripciones with(nolock)

select * from #temp;

Contributor III

Re: SQL temporary tables (phisical, local and global) does not return data

Are you entering the connection string?

Highlighted
New Contributor III

Re: SQL temporary tables (phisical, local and global) does not return data

Yes, connection string is used and connection is successful.

All queries that does not create or use a temporary table works fine. This, for exaple, works OK

test:

LOAD *;

SQL

select top 5 *

from <table> with(nolock)

Highlighted
Esteemed Contributor III

Re: SQL temporary tables (phisical, local and global) does not return data

Try this?

ODBC CONNECT32 TO DatabaseName;

SQL

SELECT TOP 10 fechaalta, fechabaja

INTO ##temp

FROM dbo.dw_suscripciones with(nolock)

select * from #temp;

Highlighted
New Contributor III

Re: SQL temporary tables (phisical, local and global) does not return data

Using this CS: OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=<user>;Initial Catalog=<IC>;Data Source=<datasource>;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=<workstationid>;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is <xpassword>);

Change to  this CS: OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=<user>;Initial Catalog=<IC>;Data Source=<datasource>;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=<workstationid>;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is <xpassword>);

Same result: No data in QV.

Thanks for your feedback!

Highlighted
Contributor III

Re: SQL temporary tables (phisical, local and global) does not return data

I have a situation of this, but not in an application.

Temporary table I store on a qvd, and do qvd load in the application.

Highlighted
New Contributor III

Re: SQL temporary tables (phisical, local and global) does not return data

When adding last row for saving "test" into "test.qvd", an "Table not found" error jumps when I run the script.

test:

LOAD *;

SQL

use ubbiwarehouse

create table ##temp (

  fechaalta datetime,

  fechabaja datetime)

insert into ##temp (fechaalta, fechabaja)

select top 10 fechaalta, fechabaja

from dw_suscripciones with(nolock)

select * from ##temp;

STORE test into test.qvd(qvd);

Highlighted
Contributor III

Re: SQL temporary tables (phisical, local and global) does not return data

Try this:

This is the exact form I used.

I just do not use the TOP.

SQL

create table #temp (fechaalta datetime,fechabaja datetime)

insert into #temp select top 10 fechaalta, fechabaja from dbo.dw_suscripciones with(nolock)

test:

LOAD *;

select * from #temp;

STORE test into test.qvd(qvd);