Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Temp table in stored procedure causes crash

Hi,

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

My procedure is something like this:

create proc sp_test

as

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

as

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...

7 Replies
fosuzuki
Partner - Specialist III
Partner - Specialist III

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

Not applicable
Author

Yes, my user has system admin rights.

fosuzuki
Partner - Specialist III
Partner - Specialist III

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

Not applicable
Author

OLEDB

alexpanjhc
Specialist
Specialist

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?

Not applicable
Author

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.

fosuzuki
Partner - Specialist III
Partner - Specialist III

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