ODBC Connection Issue

[pooja_sn]

I am using and ODBC connection to connect to Sybase and my qlikView code is similar to below:

ODBC CONNECT TO DataSourceName (XUserId is UserName, XPassword is Password);

LOAD *;
SQL  
exec StoredProcedureName;

The connection is successful but, it always returns few number of records which is an incorrect output. When I run the same script with OLEDB connection, it works perfectly fine. Any idea what might be causing this issue..

[Gysbert_Wassenaar]

Perhaps the ODBC driver you're using doesn't support something that's return by the stored procedure. Is there a reason you think you should use ODBC instead of OLEDB?

edit: Perhaps that's not even necessary. Have you seen this video:

[buzzy996]

are u created ur ODBC connection through Control Panel-Administrative tools--Data source --on system DSN tab?

Good to use OLEDB instead of ODBC.

[pooja_sn]

Using ODBC connection was mandatory just because we are facing issues with OLEDB on server.

I have configured ODBC dataSource in System DSNs. The script returns correct result when executed for the first time and it seems that the result is cached and returned by the query every time I reload .  I tried using 'DISCONNECT' in script which does not work.

[pooja_sn]

Yes. I have configured ODBC in System DSNs and it works perfectly fine for other scripts.

[Gysbert_Wassenaar]

Check on the database server if the query that Qlik Sense sends to the database is executed and returns the results you expect.

[maksim_senin]

Hi,

This would be helpful if you provide ODBC error message. Without this I can assume the following:

1. If this is possible - try debuging your stored procedure in order to return top 1, 2, 3 ... n records from it.

So you'll make sure whether it happens due to some specific record(s) or due to value of data.

2. Another way is to reproduce a query from the procedure on QV side. Of course this is only possible in case the procedure contains nothing except a select-query.

3. Not sure, but if you procedure makes something except select's (insert's or update's), maybe it makes sense to tick this setting in Script Editor:

It's needed when QV makes insert's or delete's from its side, though. So this is more likely a shamanism since it works over OLE DB.

Best regards,

Maxim

[pooja_sn]

Yes. If I execute the same query in Sybase, it returns correct result.

[Gysbert_Wassenaar]

If OLEDB works and ODBC doesn't then the ODBC driver is the problem. Ask the vendor that supplied to ODBC driver for help. Or just use OLEDB which will perform better anyway.

[pooja_sn]

No error message is returned by the query instead it returns few incorrect records.

If I tick the setting 'Open database in Read and Write mode', the script is failing to reload.

The stored procedure mentioned is making few insert/update/delete using temporary tables in database.