topic Table Not Found - t-sql/stored procedure in QlikView

Table Not Found - t-sql/stored procedure

Tue, 10 Feb 2015 17:23:02 GMT

Needing some help on my issue with a query that runs fine outside of Qlikview but returns the 'Table not found' message when running in the load script.

I've read a lot of different posts on the discussion forums about stored procedures and the answers, when there is one, and I have not been able to get those solutions to work. I'm most likely missing something or misunderstanding something.

I've condensed the query for simplicity below, if my understanding is correct Qlikview is not retrieving the results.

I've included the top portion of the query so you can see what is going on with @Dateoffset. The query hits a MS SQL Server 2008 environment.

I receive 'Table not found' when I get to: STORE Data INTO ../0_QVD_Generators/Data.qvd (QVD);

I've tried updating the second to last line to look like this, EXECUTE sp_executesql @sql output
SELECT @sql as result; based on a post on the forums, but my results where the same.

Any help and guidance is greatly appreciated.

Data:
SQL
DECLARE @DateOffset AS INT
DECLARE @sql AS NVARCHAR(MAX)
SET @DateOffset = 0;
SET @sql = N''
WHILE (@DateOffset < 3)
BEGIN
IF @DateOffSet > 0 SET @sql = CONVERT(NVARCHAR(MAX), @sql) + N' Union ';
SET @sql = CONVERT(NVARCHAR(MAX), @sql) +
N'
SELECT
Z.MARKETING_NAME,
Z.EFF_DATE,
Z.END_DATE,
CASE
WHEN Z.END_DATE > DATEADD(YEAR, ' + CAST(@DateOffset AS NVARCHAR) + ', Z.EFF_DATE) AND YEAR(DATEADD(YEAR, ' + CAST(@DateOffset AS NVARCHAR) + ', Z.EFF_DATE)) <= YEAR(GETDATE()) THEN 1
ELSE 0
END AS ACTIVE,
CASE
WHEN Z.END_DATE BETWEEN DATEADD(YEAR,' + CAST(@DateOffset AS NVARCHAR) + ', Z.EFF_DATE) AND DATEADD(YEAR, (' + CAST(@DateOffset AS NVARCHAR) + ' + 1), Z.EFF_DATE) THEN 1
ELSE 0
END AS TERMINATED
FROM
( SUB SELECT HERE
)
LEFT JOIN HERE'
SET @DateOffset +=1
END

EXECUTE sp_executesql @sql;

STORE Data INTO ../0_QVD_Generators/Data.qvd (QVD);

Re: Table Not Found - t-sql/stored procedure

Anonymous — Fri, 27 Feb 2015 05:44:34 GMT

Hi,
Since you are using sp_executesql, Use ODBC connection in QliK View script and open the conneciton in Write mode
ex.:ODBC CONNECT TO [DB](Mode is write)

Re: Table Not Found - t-sql/stored procedure

Mon, 02 Mar 2015 23:11:09 GMT

Purushotham m -

Thank you for taking the time to look over my issue and provide feedback. I had previously enabled the option in the settings tab, however I did not add the code to the connection string. (Mode is write) fixed my problem.

Thank you.

Re: Table Not Found - t-sql/stored procedure

Anonymous — Tue, 03 Mar 2015 05:35:05 GMT

Tim.

Good to hear, it worked...

Re: Table Not Found - t-sql/stored procedure

Anonymous — Tue, 29 Sep 2015 06:43:45 GMT

Switching to ODBC will get your around the problem, however the cause of the problem is most likely a result of the audit messages TSQL creates while executing the procedure.

As SQL server Updates, Inserts, Deletes etc it gives feedback in the form of small text snippets to the calling process. e.g. when you run the proc in SSMS you will see something like this in the "messages" window:

(30 row(s) affected)

(0 row(s) affected)

(36 row(s) affected)

...

Command(s) completed successfully.

You will need to turn these off in order for QV to properly read the data output from the proc using OLEDB. To do this, alter the procedure by adding a "SET NOCOUNT ON" statement before your code runs anything.