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

[Anonymous]

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;

[Anonymous]

Creates a temporary table in the Qlikview script itself:

sql

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

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

[Anonymous]

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;

[Anonymous]

Are you entering the connection string?

[Anonymous]

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)

[vishsaggi]

Try this?

ODBC CONNECT32 TO DatabaseName;

SQL

SELECT TOP 10 fechaalta, fechabaja

INTO ##temp

FROM dbo.dw_suscripciones with(nolock)

select * from #temp;

[Anonymous]

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!

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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