Configuring MSSQL Events

Frank ROGERS · ‎02-12-2015

I'm trying to configure a MSSQL event on rows added to a table. I selected every item in the MSSQL tab using the drop down. The calendar is 'every day of the year', no time window. I've tried other events as well, Table created, dropped, etc. The event never triggers.

I can schedule SQL jobs, so the connection, userid, etc, are all correct.

I'm using TES 6.0.3 and MSSQL 2008R2.

Any ideas?

Dinesh Sankaranarayanan · ‎02-12-2015

1. Does the runtime user have sufficient privileges(eg: sysadmin) on the database being watched, as mentioned in the MSSQL adapter document ?

2. On the client UI, under logs do you see messages which indicate that the 'user does not have sufficient privileges and some MSSQL events may not work' ?

3. Is the transaction log of the database that the event is watching, large ? that the adapter is taking time to parse ?

-Dinesh

Frank ROGERS · ‎02-12-2015

Thanks for replying Dinesh.

The runtime user does have the sysadmin role in SQL.

I do not see any permission messages in the log, but I do see a warning, "Startup parse of transaction logs. Performance may be negatively affected and database events will be delayed until complete."

I don't see a message the parse completed.

Dinesh Sankaranarayanan · ‎02-12-2015

The message is because the transaction log is probably large and the adapter tries to parse it in memory. In my case, it was a database used by another product and had no control over its transaction logs. Eventually, you should see the parse completion message in log.

Few things you can try

- Try setting the event to watch a database with less transactions(insert/update/delete), for testing purpose. I feel it will work

- If that works, talk to your DBA and try reducing transaction log size. Restart the MSSQL connection. It should work

- Cisco has recently update the MSSQL adapter code to optimize the transaction log handling process. Talk to them and see if you have the right hotfix in your env.

Also, please confirm the user that you have set on the event. Is it set to * ?

-Dinesh