Syntax Error in Adapter Query

Products

• CA Business Service Insight

Issue/Introduction

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

<Please see attached file for image>

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

<Please see attached file for image>

5. Click on "Advanced" button section

<Please see attached file for image>

6. Uncheck the "Is AutoComplete" check box.

<Please see attached file for 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.