After many hours with QlikView tech support, we concluded that QlikView would not read from a stored procedure over OLE, but was OK with ODBC. However, I just tried a sproc which uses a global temporary table, and this fails even when using an ODBC connection. Has anyone else had this problem? Found a workaround?
Here's the error message:
SQL##f - SqlState: S0002, ErrorCode: 3701, ErrorMsg: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the table 'Temp_PaidSearchReport', because it does not exist or you do not have permission.
SQL EXEC Routine.PaidSearchReport
The sproc runs just fine from SQL Management Studio or SQL Integration Services; the problem is limited to QlikView.