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

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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;

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

Anonymous — Wed, 08 Mar 2017 17:50:26 GMT

Creates a temporary table in the Qlikview script itself:

sql

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

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

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

Anonymous — Wed, 08 Mar 2017 18:01:42 GMT

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;

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

Anonymous — Wed, 08 Mar 2017 18:07:16 GMT

Are you entering the connection string?

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

Anonymous — Wed, 08 Mar 2017 18:28:28 GMT

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)

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

vishsaggi — Wed, 08 Mar 2017 18:32:34 GMT

Try this?

ODBC CONNECT32 TO DatabaseName;

SQL

SELECT TOP 10 fechaalta, fechabaja

INTO ##temp

FROM dbo.dw_suscripciones with(nolock)

select * from #temp;

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

Anonymous — Wed, 08 Mar 2017 18:39:13 GMT

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!

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

Anonymous — Wed, 08 Mar 2017 18:39:37 GMT

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.

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

Anonymous — Wed, 08 Mar 2017 18:43:10 GMT

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);

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

Anonymous — Wed, 08 Mar 2017 19:13:13 GMT

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);

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

vishsaggi — Wed, 08 Mar 2017 19:24:19 GMT

Strange. It worked for me. Can you send us your actual script you are trying to use?

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

Anonymous — Thu, 09 Mar 2017 14:21:14 GMT

Sure. Here it is

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

Anonymous — Thu, 09 Mar 2017 14:22:13 GMT

Sorry, same result. Weird!

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

vishsaggi — Thu, 09 Mar 2017 14:36:51 GMT

Try this exact code? Use your correct usrname and password though.

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=<id>;Initial Catalog=<catalog>;Data Source=<dc>;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=<wid>;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is <pwd>);

SQL

SELECT TOP 100 fechaalta, fechabaja

INTO ##temp

FROM dw_suscripciones with(nolock);

select * from ##temp;

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

sasiparupudi1 — Thu, 09 Mar 2017 14:49:22 GMT

Pl Try this

SQL

SELECT x,y

INTO ##temp

FROM tab;

sql select x,yfrom ##temp

Drop table ##temp;