2 Replies Latest reply: Jan 5, 2015 12:59 AM by Avinash R RSS

    Direct discovery not working in SR7?

    prabhu appu

      Hi,

       

      I am using the below query to fetch data from MSACCESS through direct discovery.

      OLEDB CONNECT32 TO [Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=D:\359084\COE\DIRECT\Data.accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False];
      
      
      
      DIRECT QUERY 
      dimension PRODUCT
      measure PRICE
      FROM Sheet1;
      
      

       

       

      But i'm getting the below error. I could't identify the mistake.

       

      ErrorSource: Microsoft Access Database Engine, ErrorMsg: Syntax error in query. Incomplete query clause.

       

      DIRECT QUERY

      dimension PRODUCT

      measure PRICE

      FROM Sheet1

       

      I used the below query in SR4. i just worked fine in SR4 but the same didn't worked in SR7.

       

      DIRECT SELECT PRODUCT
          IMPLICIT PRICE
      FROM Sheet1;
      

       

      Please help...

       

      Regards,

      Prabhu Appu

        • Re: Direct discovery not working in SR7?
          Eric Tay

          Hi Prabhu,

           

          I had the same issue as you.  But after I added below scripts, the issue was solved:

           

          SET DirectStringQuoteChar = '"';
          SET DirectIdentifierQuoteChar = '[]';

          Hope it helps.

           

          Eric

          • Re: Direct discovery not working in SR7?
            Avinash R

            HI Prabhu,

             

            Direct discovery has two formats:

             

            1.1 Syntax

            Old Syntax                    New Syntax

            DIRECT SELECT          DIRECT QUERY

            EXPLICIT                      DIMENSION

            IMPLICIT                      MEASURE

            SQL(‘’)                          NATIVE(‘’)

             

            1.2 New and changed Set Statements

            New Syntax

            SET DirectCacheSeconds= 15;

            SET DirectStringQuoteChar=’”’;

            SET DirectIdentifierQuoteChar=’[]’;

            SET DirectConnectionMax= 4;

            SET DirectTableBoxListThreshold= 100000;

            SET DirectDistinctSupport=false;

            SET DirectIdentifierQuoteStyle=’ANSI’;

             

             

            Refer this document.