11 Replies Latest reply: Oct 13, 2015 8:02 AM by Giovanni Chiozza RSS

    MsAccess working query do not work in QlikSense.... Stucked !

    Giovanni Chiozza

      Dear all,

      I got several working queries on ms access database but when I insert flat script on QS I got Always strange error

      saying

      SQL##f - SqlState: 07001, ErrorCode: 4294964286, ErrorMsg: [Microsoft][Driver ODBC Microsoft Access] Parametri insufficienti. Previsto 1.

       

      Insufficient parameters . Expected 1.    ????

       

      ON PREVIEW (within data selection area)  I SEE ALL DATA NORMALLY

       

      This is the very simple query

       

      REM LOAD "Tipo Doc",

          "N doc",

          Anno,

          codtab,

          tipo,

          Data,

          Codart,

          Descrizione,

          "Codcat_Ft",

          Quantità,

          Prezzo,

          "Cod iva",

          PrezzoScontato,

          Totriga,

          "Tipo_Omaggio",

          Omaggi,

          CostoUnit;

      SQL SELECT "Tipo Doc",

          "N doc",

          Anno,

          codtab,

          tipo,

          Data,

          Codart,

          Descrizione,

          "Codcat_Ft",

          Quantità,

          Prezzo,

          "Cod iva",

          PrezzoScontato,

          Totriga,

          "Tipo_Omaggio",

          Omaggi,

          CostoUnit

      FROM "D:\EOS\CONTROLLO_GESTIONE\MDB\EOSSRL"."CG_RIGHE_FT_01";

        • Re: MsAccess working query do not work in QlikSense.... Stucked !
          Fredrik Thomsen

          Have you tried to create a database connection to the ms access database in the script first? And then try to open the table? Sense need a lib to be able to access the database.

           

          when the connection are up, it should create a lib connection string.

           

          LIB CONNECT TO 'Customer Service (username)';

           

          Or you could alter the script to legacy mode from the settings.ini file and it should work without the lib.

            • Re: MsAccess working query do not work in QlikSense.... Stucked !
              Giovanni Chiozza

              Yes , of corse

              LIB CONNECT TO 'Dinamico_Eos_BI_Local';

               

              the very strange is that every kind of imported script about Ms Access Table&Queries has the same behavior and generate that error when you try to upload data (also without using LOAD ).

               

              On preview area within connection I see all data with no problems. Ms AccessTables and also Queries.

              Did you try to use it on last QS version  ??

              • Re: MsAccess working query do not work in QlikSense.... Stucked !
                Giovanni Chiozza

                This is the error complete report on pink window.

                Insufficient parameters .  Expected 1.

                 

                Connessione a Dinamico_Eos_BI_Local

                Connesso

                Si è verificato il seguente errore:

                SQL##f - SqlState: 07001, ErrorCode: 4294964286, ErrorMsg: [Microsoft][Driver ODBC Microsoft Access] Parametri insufficienti. Previsto 1.

                L'errore si è verificato qui:

                SELECT "Documenti st"."Tipo Doc", "Documenti st"."N doc", "Documenti st".Anno, "Tabella generica".codtab, "Tabella generica".tipo, "Documenti st".Data, "Documenti st sotto".Codart, "Documenti st sotto".Descrizione, Articoli.Codcat AS Codcat_Ft, "Documenti st sotto".Quantità, "Documenti st sotto".Prezzo, "Documenti st sotto"."Cod iva", "Documenti st sotto".Omaggi, "Documenti st sotto".CostoUnit FROM ("Documenti st" LEFT JOIN "Tabella generica" ON "Documenti st"."Tipo Doc" = "Tabella generica".codtab) LEFT JOIN (("Documenti st sotto" LEFT JOIN "Tabella iva" ON "Documenti st sotto"."Codiva" = "Tabella iva"."Cod iva") LEFT JOIN Articoli ON "Documenti st sotto".Codart = Articoli.Codart) ON "Documenti st"."Id doc" = "Documenti st sotto"."id doc" WHERE ((("Documenti st".Anno)=2015) AND (("Tabella generica".tipo)=93) AND (("Documenti st sotto".Codart)<>'-----' And ("Documenti st sotto".Codart) Is Not Null And ("Documenti st sotto".Codart)<>'999999992' And ("Documenti st sotto".Codart)<>'#SN3')) OR ((("Documenti st".Anno)=2015) AND (("Tabella generica".codtab)='C11') AND (("Tabella generica".tipo)=93) AND (("Documenti st sotto".Codart)<>'-----' And ("Documenti st sotto".Codart) Is Not Null And ("Documenti st sotto".Codart)<>'999999992' And ("Documenti st sotto".Codart)<>'#SN3')) ORDER BY "Documenti st".Data

                I dati sono stati caricati. Correggere l'errore e riprovare a caricare.

              • Re: MsAccess working query do not work in QlikSense.... Stucked !
                Andy Weir

                Probably down to a missing column in your data source or issue in your data itself.

                 

                Try this to flush it out.

                 

                1. Run your query in Access and export to excel

                2. Save excel file and using the Qlik Sense data loader bring in the excel sheet and let Qlik Sense build the load script.

                3. Run the data load and see if your data goes in without any issues.

                4. If works ok create your new connection to the access database and replace the Excel source with Access source.

                 

                Hope this helps

                 

                 

                Andy

                • Re: MsAccess working query do not work in QlikSense.... Stucked !
                  Fredrik Thomsen

                  My script runs through when i try to read from a MsAccess database. The only difference i see i comparison with yours, is that you are missing the .mdb extension. Don't know if that might have an impact or not?

                   

                  FROM "E:\Qlik2Go\Qlik2Go Content\_Data\Databases\CustomerService.mdb".CallCtrFact;

                   

                  I'm using the MS Access driver and Qlik Sense 2.1 Enterprise.

                  • Re: MsAccess working query do not work in QlikSense.... Stucked !
                    Fredrik Thomsen

                    You could also try to load all fields by using SQL Select * to see if the problem is related to missing fields as Andy mentioned earlier.

                      • Re: MsAccess working query do not work in QlikSense.... Stucked !
                        Giovanni Chiozza

                        Yes , I'm only using the select NOT the Load and the error arise the same.

                         

                        LIB CONNECT TO 'Dinamico_Eos_BI_Local';

                         

                        REM LOAD "Tipo Doc",

                            "N doc",

                            Anno,

                            codtab,

                            tipo,

                            Data,

                            Codart,

                            Descrizione,

                            "Codcat_Ft",

                            Quantità,

                            Prezzo,

                            "Cod iva",

                            PrezzoScontato,

                            Totriga,

                            "Tipo_Omaggio",

                            Omaggi,

                            CostoUnit;

                        SQL SELECT "Tipo Doc",

                            "N doc",

                            Anno,

                            codtab,

                            tipo,

                            Data,

                            Codart,

                            Descrizione,

                            "Codcat_Ft",

                            Quantità,

                            Prezzo,

                            "Cod iva",

                            PrezzoScontato,

                            Totriga,

                            "Tipo_Omaggio",

                            Omaggi,

                            CostoUnit

                        FROM "D:\EOS\CONTROLLO_GESTIONE\MDB\EOSSRL"."CG_RIGHE_FT_01";

                        • Re: MsAccess working query do not work in QlikSense.... Stucked !
                          Giovanni Chiozza

                          Fredick,

                          the problem was very hidden in another section and the error was done to that.

                          I created from scratch a new app with only that SELECT and now it's running well.

                          Sorry but it was not so easy to find it out. Only with clean environment I discovered it.

                          Thanks again for prompt support !!!

                        • Re: MsAccess working query do not work in QlikSense.... Stucked !
                          Giovanni Chiozza

                          Hi,

                          it was connected to   "Anno_Off" field yet used before than Year("Data_Off") As "Anno_Off".

                          Impossible to understand.

                          I founded casually on ms access trying to use the Year function.

                          It said ... yet used

                          Bye