topic 0 lines fetched in QlikView

0 lines fetched

Thu, 26 Nov 2015 18:00:55 GMT

I'm new to QV and I'm trying to connect tables from MySQL. I connect through the OLE DB option in Edit Scripts and I am able to connect to all of the tables and attributes. The problem is that my row data doesn't pull through. When I reload, it says "0 lines fetched" for each table. Thanks in advance for your suggestions!

Re: 0 lines fetched

oknotsen — Thu, 26 Nov 2015 19:00:57 GMT

Check if the account you are contacting the database with has the right permissions.

Re: 0 lines fetched

Anonymous — Thu, 26 Nov 2015 19:02:43 GMT

If permissions check out ok, could you share your load script.

Re: 0 lines fetched

Thu, 26 Nov 2015 19:06:34 GMT

Could you elaborate on this? I'm sorry to be so ignorant, but this is new territory for me!

Re: 0 lines fetched

Thu, 26 Nov 2015 19:07:05 GMT

Could you please explain how to do this? (Totally inexperienced!!)

Re: 0 lines fetched

Clever_Anjos — Thu, 26 Nov 2015 19:10:10 GMT

Please check

Correct permissions
Correct Database
Does your SQL runs outside QV, using WorkBench?

Re: 0 lines fetched

oknotsen — Thu, 26 Nov 2015 19:12:59 GMT

I am not a DBA, so I fear I can not help you with that.

Alternative explanation is that you have some weird "where" clause in your load script, but for that I need to see that script.

Re: 0 lines fetched

Thu, 26 Nov 2015 19:39:40 GMT

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=C_FLAT;Data Source=PROTOCHIPS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=PROTOCHIPS;Use Encryption for Data=False;Tag with column collation when possible=False];
//-------- Start Multiple Select Statements ------
SQL SELECT *
FROM "C_FLAT".dbo."ACCOUNT_DIM";

SQL SELECT *
FROM "C_FLAT".dbo."OPPORTUNITY_DIM";

SQL SELECT *
FROM "C_FLAT".dbo."PARTS_DIM";

SQL SELECT *
FROM "C_FLAT".dbo."YIELD_FACT";

SQL SELECT *
FROM "C_FLAT".dbo."YIELD_TIME_DIM";
//-------- End Multiple Select Statements ------

Re: 0 lines fetched

Thu, 26 Nov 2015 19:42:56 GMT

Yes yes and yes

Re: 0 lines fetched

Thu, 26 Nov 2015 22:01:41 GMT

Just an update... When I use a dumpfile to execute the table creation in MySQL from my teammate (who is integrating in Talend), it gives me this problem. If I create the tables myself manually, then load the insert statements from the dumpfile (after opening in Notepad), I don't have this problem...! At least I figured out a workaround, but does this ring any bells for anyone?

Re: 0 lines fetched

Clever_Anjos — Fri, 27 Nov 2015 09:43:03 GMT

you said you´re retrieving data from a MySQL database but your script seems to be to a MS SQL Server database script, am I getting crazy?

Your connection string seems to be a MSSQL Server database string
Your tables have *.dbo.* schema that seems to be SQL Server tables

Re: 0 lines fetched

Peter_Cammaert — Fri, 27 Nov 2015 10:46:08 GMT

When you create/load the table&data with your teammate account, do you then grant the necessary permissions to your own account?

In every RDBMS, you have to specify who is allowed to access which tables and what they can do with them (SELECT,INSERT,UPDATE etc.)

Peter

Re: 0 lines fetched

Peter_Cammaert — Fri, 27 Nov 2015 10:53:55 GMT

Clever,

I had the same idea in another discussion where the CONNECT string and various other properties made me think a poster was accessing a SQL Server DB, while he kept denying he used anything else than a MySQL db.

I still haven't figured out what was the cause of this confusion... Maybe the MySQL driver developers decided to use the exact same names and connection properties as those used for a SQL Server DB?

Peter

Re: 0 lines fetched

Clever_Anjos — Fri, 27 Nov 2015 11:03:37 GMT

I´m not familiar with MySQL databases.

Maybe stefanie.boros‌ you should try Qlik ODBD driver Connectivity - New SFDC and ODBC Connectors

Re: 0 lines fetched

Fri, 27 Nov 2015 17:01:45 GMT

I'll look into this. I apologize for being confusing - I'm still new to this and I guess I was using the wrong terminology! I am using Microsoft SQL Server Management Studio.

Thank you all for your input!

Re: 0 lines fetched

Fri, 27 Nov 2015 17:05:31 GMT

So sorry - yes, I am using Microsoft SQL Server Management Studio. I am new to this so I am not sure on the terminology! I did try connecting through the ODBC but was unsuccessful. As I said in another reply, I am able to make it work if I create the tables myself (instead of using my teammate's dump file) and then insert the data. Looking into if the dump file needs permissions... Thank you all for your input!