topic Re: Temp table in stored procedure causes crash in QlikView

Temp table in stored procedure causes crash

Tue, 04 Sep 2012 21:31:21 GMT

Hi,

I am trying to use a sql server stored procedure in qlikview 10.

My procedure is something like this:

create proc sp_test

select a,b,c into #tmp from xxxx

select x,y,z,b from table join #tmp on #tmp.a=table.y

And I call stored procedure as

SQL Exec sp_test;

Every time trying to reload this script causes crash. There is no error, no message, just crash.

If i remove the temp table part of stored procedure as this:

create proc sp_test

select x,y,z from table

Then everything works fine.

Is there limitation for qlikview that i cannot use temp tables in my procedures? I tried almost everything and i couldn't fine a solution except not using temp tables.

Can you help me about this problem?

Thank you...

Re: Temp table in stored procedure causes crash

fosuzuki — Tue, 04 Sep 2012 21:49:17 GMT

The credential you are using to connect to the SQL has rights to create a temp table in the database?

Re: Temp table in stored procedure causes crash

Wed, 05 Sep 2012 05:02:17 GMT

Yes, my user has system admin rights.

Re: Temp table in stored procedure causes crash

fosuzuki — Wed, 05 Sep 2012 19:20:01 GMT

You're connecting to the SQL Server via ODBC or OLEDB?

Re: Temp table in stored procedure causes crash

Thu, 06 Sep 2012 05:34:33 GMT

OLEDB

Re: Temp table in stored procedure causes crash

alexpanjhc — Thu, 06 Sep 2012 18:20:45 GMT

Can you revise your store procedure and make it a SQL?

load *

sql select

a,b,c,

x,y,z,b from table xxx join table on xxx.a=table.y

then you can create your own table or qvd?

Re: Temp table in stored procedure causes crash

Thu, 06 Sep 2012 18:34:30 GMT

It was just an example. My sp is more complicated than that. It first fill sales into to a temp table and then it uses a lot of subqueries from this table.

Re: Temp table in stored procedure causes crash

fosuzuki — Tue, 11 Sep 2012 16:10:37 GMT

Hi yalco,

maybe if you change the way you create the temp table. Instead, use a table variable:

DECLARE @TibetanYaks TABLE (

YakID int,

YakName char(30) )

INSERT INTO @TibetanYaks (YakID, YakName)

SELECT YakID, YakName

FROM dbo.Yaks

WHERE YakType = 'Tibetan'

SELECT *

FROM @TibetanYaks

I took the sample above from http://www.sqlteam.com/article/temporary-tables

It says that:

If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.
If you need to create indexes on it then you must use a temporary table.
When using temporary tables always create them and create any indexes and then use them. This will help reduce recompilations. The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.

Hope this helps

Fernando