Thanks forconfirming my thoughts. I didn't mention the reference date in the table being used is in local time(+10 hours).

Therefore the UTC, UTC+10 hours comparisons were always going to be...

• This reply was modified 1 year, 2 months ago by  andyewx.

Thanks Johan.

I have since traced the issue to the user context executing the change in another database. That's where the trigger is breaking.

The logic is fine. The user executing the...

I have just found an actual error from profiler that matches what I'm seeing in a Profiler Trace.

'The server principal "sa" is not able to access the...

• This reply was modified 1 year, 5 months ago by  andyewx.

Ken,

It seems I spoke too soon.

I thought I'd gotten this right but I'm now looking at the server module signing as the prime culprit.

Very basic testing using the links above...

Thanks again Ken.

It appears your good advice put me on the right track. Ironically, it's quite possible my foray into Service Brokers to address my core issue, trying to update...

Thanks Ken.

I'm having trouble capturing the error. If I have a simple error and the operation is able to fall into the CATCH block I get an error I can...

• This reply was modified 1 year, 6 months ago by  andyewx.

Hello Ken.

Thanks for the input.

I took your advice about Service Brokers and have run into another issue.

I've opened another post here if you're interested in helping out.

https://www.sqlservercentral.com/forums/topic/mssql-service-broker-dsql-operation-on-another-database-causing-broker-failure

• This reply was modified 1 year, 6 months ago by  andyewx.

Thanks Frederico. Your code is good but it appears the error was later in my code. Apologies for not posting the whole code set but it was long and convoluted....

Thanks Grant.

I did some reading and others and I see where Extended Events and Event Notifications might help.

The examples I looked at were wrapped around analysis of process but I...