Syntax Error in Adapter Query

Document ID : KB000110036
Last Modified Date : 08/08/2018
Show Technical Document Details
Issue:
Query in Adapter prompts a syntax error message

Error in Fill Data of "NewQuery" query. Caught COM ERROR in operation QUERY (select ticket_no, start_time, end_time,ticket_category from 
(select ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS S_No, ref_num ticket_no, dateadd(ss,time_stamp,'1970-01-01 05:30:00') as start_time, status, 
(select substring(sym,0,charindex('.',sym)) from prob_ctg where persid=c.category) as service, 
(select sym from prob_ctg where c.category = prob_ctg.persid )as ticket_category 
from [mdb].[dbo].call_req c , [mdb].[dbo].act_log a, [mdb].[dbo].prob_ctg 
where c.persid = a.call_req_id and c.category = prob_ctg.persid and a.type in ('INIT','SLARESUME') and status='CL'and prob_ctg.sym = 'RSI.DARPAN RSI.New.ClientApp.Deployment' 
) a, 
(select ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS S_No2, ref_num r2,dateadd(ss,time_stamp,'1970-01-01 05:30:00') as end_time, 
(select substring(sym,0,charindex('.',sym)) from prob_ctg where persid=c.category) as service2, 
(select sym from prob_ctg where c.category = prob_ctg.persid )as ticket_category2 from [mdb].[dbo].call_req c , [mdb].[dbo].act_log a, [mdb].[dbo].prob_ctg 
where c.persid = a.call_req_id and c.category = prob_ctg.persid and a.type in ('SLADELAY','CLREQ') and status='CL' and prob_ctg.sym = 'RSI.DARPAN RSI.New.ClientApp.Deployment' ) b 
where a.S_No = b.S_No2 
order by ticket_no, start_time, end_time and ( (ticket_no>'TKT0000000') or (ticket_no = 'TKT0000000' and start_time>CONVERT(datetime,'07/07/1970 00:00:00',104)) ) order by ticket_no ,start_time ) [[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'and'. (HR: 0x80040e14) (IDispatch error #3092) (Source = Microsoft OLE DB Provider for ODBC Drivers)][(Error #80040e14) (Source = Microsoft OLE DB Provider for ODBC Drivers) (Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'and'.) (SQLState = 37000) (NativeError: 9c)]
Environment:
CA Business Service Insight 8.3.5
Cause:
This is caused by the Data Source Interface default setting
Resolution:
Please follow below steps to resolve this issue

1. Login to Business Service Insight (BSI) as Administrator
2. Click on Design drop down - Data Acquisition - Adapters
3. Open the Adapter that causing the error message and Click Edit

User-added image

4. Got to "Data Source Interface" step of the adapter

User-added image

5. Click on "Advanced" button section

User-added image

6. Uncheck the "Is AutoComplete" check box.

User-added image

7. Save the adapter and run the query

Note: BSI won't use the key field on the query if the "Is AutoComplete" check box is unchecked.