Direct discovery not working in SR7?

[prabhuappu]

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

[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

[avinashelite]

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.