In a nutshell, I have a multiple idbased connector pulling date from MSSQL.
All the data required is spread across multiple tables, and multiple JOINs were added to the query. On the last JOIN (Highlighted below), the substring had to be used to split the C.SYSTEMNAME record, which contains "DOMAIN\SYSTEMNAME" together. So for the JOIN to work, the substring was added to remove the "DOMAIN\" from C.SYSTEMNAME. The query works fine in MSSQL, but when running in the FlexConnector the Highlighted piece does not return anything. I thought the issue was the backslash and I added the escape '\\' and '\\\', but did not work. Then I removed the backslash and added the number 5 to the substring "on (A.SYSTEMNAME = substring(c.systemname, 5, len(c.systemname)) ) " and still did not work. There is no error message in the agent.log. I have been running it in debug mode. It seems the substring is ignored or maybe not supported. Any ideas?
SELECT F.ID, C.EVENTID AS EVENTID1, D.EVENTID_DESCRIPTION, D.EVENTTYPE_DESCRIPTION, \
C.EVENTTIMESTAMP, C.SYSTEMNAME, C.ADMINID, C.GROUPID, C.AFF1ID, C.AFF2ID, C.EVENTINFO, \
E.OBJECT_VALUE, F.EVENTSTATUS, F.USERID, F.TICKETNUMBER, F.TICKETTYPE, F.USERCOMMENT, \
F.ENTRYTIME, F.EVENTID AS EVENTID2, \
A.MACHINECATEGORY ,A.CONTACT_GROUP, A.PRIMARY_CONTACT, A.SECONDARY_CONTACT, A.CONTACT_GROUP_EMAIL \
FROM TEVENTS C WITH(NOLOCK) JOIN LKUP_DOMAINEVENTS D WITH(NOLOCK) \
on (D.EVENTID_ID = C.EVENTID) \
left outer JOIN LMS_OBJECT E ON (E.OBJECT_IDX = C.GROUPID) \
JOIN TEVENTS_JUSTIFICATION F ON (C.ID=F.EVENTID) \
JOIN SERVERX.InvTAS.dbo.LOISCombined A \
on (A.SYSTEMNAME = substring(C.SYSTEMNAME, CHARINDEX('\', C.SYSTEMNAME)+1, len(C.SYSTEMNAME)) ) \
where (C.JUSTIFIABLE = 1) \
AND F.ID >= ? order by F.ID
Are you trying to use the default ODBC client. Download the Java (sqljdbc.jar) and use that client. I had issues with query's not returning any results with the old ODBC client and this resolved the issue for me.
I've got some simliar issue with the MSSQL Flexconnector using ODBC. Does anyone know if there's also a possiblity to replace this driver?
ok. I found out - it doesn't matter if you put the sqljdbc.jar into current/user/lib or current/user/agent/lib, but the first would be formaly better IMHO.
My query is the following:
query = SELECT \
EntryID, UserID \
FROM sx.ActivityLog.Entry as entry1 \
join sx.ActivityLog.Action as action1 on entry1.ActionID = action1.ActionID \
join sx.ActivityLog.User as user1 on user1.UserID = entry1.UserID \
join sx.ActivityLog.Computer as computer1 on entry1.ComputerID = computer1.ComputerID \
WHERE EntryID >?
I found out, when I delete the 2nd JOIN the QUERY will run ok, but into the SQL Editor I'm able to query exact this QUERY and it will be OK. This seems very, very strange to me.
That is odd, I did notie issues with the odbc driver, but they subsided with the new driver. Is there maybe an issue with the query?
join sx.ActivityLog.User as user1 on user1.UserID = entry1.UserID \ Is the user1.UserID correct, would it not be entry1.UserID?
I may be wrong, but not sure about your schema.
this would mean:
join sx.ActivityLog.User as user1 on entry1.UserID = entry1.UserID ? doesn't make any sense to me. I've ActivityLog.Entry Tabel with the FK Column UserID and the ActivityLoger.User Table with FW Column UserID. so to join this would make sense:
join sx.ActivityLog.User as user1 on user1.UserID = entry1.UserID?