CA Service Management

Have created SQL adapters to connect Service Desk Manager MSSQL MDB database in CA BSI. Such adapters are not working eventhough its SQL QUERY is working fine in SQL server and getting records in the same.

1) First SQL adapter created, whose status showing in 'Started' state however no records found under Event Management eventhough I can see records are coming in adapterstatistics file and no errors found in log file.

2) Second SQL adapter created, whose status showing in 'Error' state post multiple attempts. I can see below errors in log file. Its Query is working fine and getting records in SQL Server.

<msg l="E" s="0" u="5" code="ffffffff" msg="Adapter message" o="Oblicore Adapter critical" f="Query.cpp" ln="606" t="07/09/2016 09:42:03" info="Error in Fill Data of "NewQuery" query.  Caught COM ERROR in operation QUERY (Select DATEADD(ss, call_req.open_date,CONVERT(DATETIME,'01-01-1970 05:30:00', 121)) as OPEN_DATE, DATEADD(ss, call_req.resolve_date, CONVERT(DATETIME,'01-01-1970 05:30:00', 121)) as RESOLVE_DATE, datediff(n,(dateadd(ss,call_req.open_date,'01/01/1970 05:30:00 am')),dateadd(ss,call_req.resolve_date,'01/01/1970 05:30:00 am')) as [RESOLUTION_DURATION], call_req.ref_num, call_req.summary, call_req.status From call_req where call_req.resolve_date is not null and OPEN_DATE<CONVERT(datetime,'1/1/1970 12:00:00',104)  order by OPEN_DATE desc)  [[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type datetime. (HR: 0x80040e57) (IDispatch error #3159) (Source = Microsoft OLE DB Provider for ODBC Drivers)][(Error #80040e57) (Source = Microsoft OLE DB Provider for ODBC Drivers) (Description = [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type datetime.) (SQLState = 22003) (NativeError: 1fb3)]"/> <msg l="I" s="0" u="5" code="ffffffff" msg="Adapter message" o="Oblicore Adapter critical" f="SqlInterface.cpp" ln="97" t="07/09/2016 09:42:03" info="Disconnect from critical"/>

Anyone faced similar issue in CA BSI. Appreciate Quick help on this.

Regards,

Manisha

Hi,

Can you please provide the full configuration.xml of the adapters and the log files?

Hello Koen,

I am facing same issue and I am attaching adapter configuration and log files.can you pls check and revert to me ASAP.

Regards,

Sudheer.y

Hi Sudheer,

I do not see any files attached to you comment.

Please use the 'advanced editor' at the to right of your comment. You can then use the 'Attach' button at the bottom of the editor to include files.

best regards,

Koen

Hello Koen_Vervoort, i'm having the same problem. I'm attaching the information in order to know if you can help me.

I'll be waiting for your answer.

Hi Gustavo,

There is a query error in the log: [10.2.12-MariaDB]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where  ( (Ticket_Number>'0') or (Ticket_Number = '0' and Fecha>'1/1/2016 00:00:0' at line 22)

The auto complete is on (AutoCompleteQuery="yes"). This means that the adapter will update the query where-statement using the key fields.

Where-statement in original query:

Where ((ca.type = 'I' And ic.resource_name is not null and resolve_date is not null)
and (ic.resource_name like ('%herdez%')))

Where-statement in query when the adapter runs (taken from log file):

Where ((ca.type = 'I' And ic.resource_name is not null and resolve_date is not null)
and (ic.resource_name like ('%herdez%')))
); where ( (Ticket_Number>'0') or (Ticket_Number = '0' and Fecha>'1/1/2016 00:00:00') or (Ticket_Number = '0' and Fecha = '1/1/2016 00:00:00' and Status_Type>'') ) order by Ticket_Number ,Fecha ,Status_Type )

You need to update your query so it still works when the autocomplete is on.

Hello Manisha,

I am facing the same problem as i am also creating service desk adapter query is running fine. But still the adapter is under running state. But no records found in the event management table.

Anyone facing same problem and did resolution please provide the solution.

Regards

Sunil Chugh

Hi Sunil,

Without more information it is very difficult to determine the cause. Can you please provide the full configuration.xml of the adapters and the log files?

regards,

Koen

Dear Keon,

sorry for the late reply. thanks for the update. I tried to to upload the documents,due to some network issue I am unable to upload docs that time.

We have Ca support account and can I raise case with you for this issue.or else I will upload the adapter configuration files soon.

Pls reply me then I can take action with support of my boss.

Regards,

sudheer.y 

Hi Sudheer,

Then please raise a CA support case and the BSI support people will help you. They can always contact me to help with the case.

regards,

Koen

Hi  Keon,

I have raised case and that has been allocated to Iain lambert. I have requested them to allocate case to keon but they did not consider to do that.anyhow can you pls take the ownership of that case.I thought you will be helpful for us instantly.

Regards,

sudheer.y

Hi Sudheer,

Koen works in CA Services; he's not a Support Engineer that can take his own cases. However, once I've investigated I'll be reaching out to him as necessary.

Regards

Iain