Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Creates a temporary table in the Qlikview script itself:
sql
create table #table (.......
insert into #table ........
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;
Are you entering the connection string?
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)
Try this?
ODBC CONNECT32 TO DatabaseName;
SQL
SELECT TOP 10 fechaalta, fechabaja
INTO ##temp
FROM dbo.dw_suscripciones with(nolock)
select * from #temp;
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!
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.
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);
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);