Syntax Error in Adapter Query

Products

• CA Business Service Insight

Issue/Introduction

Query in Adapter setting results in 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') 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') b 
where a.S_No = b.S_No2 
order by ticket_no, start_time, end_time 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.x and 9.x

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

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

5. Click on "Advanced" button section

6. Uncheck the "Is AutoComplete" check box.

7. Save the Adapter and run the query

Note: BSI will not use the key field on the query if the "Is AutoComplete" check box is unchecked.