Multi Instance Job - DsJobInvocationId

[synsog]

Hi All,

We have a scenario where we update the same audit table and column across multiple processes for different values in where clause, once when a Sequence job starts and finally as the last step when the Seq job ends.

The only change in the update query across different seq jobs is the value in the where clause changes based on the process

e.g.
Update TableName Set ColName1 = sysdate where Colname2 = 'XYZ'

Update TableName Set ColName1 = sysdate where Colname2 = 'ABC'

We are trying to pass the value 'XYZ' as the InvocationID and use it this way in the SQL statement

Update TableName Set ColName1 = sysdate where Colname2 = #DsJobInvocationId#

But this does not work..Can we or Can we not use the InvocationID this way in the where clause
(I believe it works if we use the InvocationId for the tablename in this query)

[chulett]

"Does not work" meaning what? It should work just fine as far as I know but you might want to try this instead of what you posted:

Code: Select all

Update TableName Set ColName1 = sysdate where Colname2 = '#DsJobInvocationId#'

But then again it depends on what "does not work" means.

[synsog]

By Not working I mean it does not update the record when we pass the Invocationid to be used as part of the Where Clause (We verified it is a valid value and should update 1 record)

I already tried using the single quotes around it, as you suggested, but then it treats that as a string value and does not evaluate the InvocationId. So does not update the record in this case as well

Any other way this can be achieved

[ray.wurlod]

In the past I've always doubled up - the Invocation ID governs the instance name, and I also pass the same value as an explicit job parameter. This approach works.

[chulett]

I already tried using the single quotes around it, as you suggested, but then it treats that as a string value and does not evaluate the InvocationId.

Then something is not right about how you are specifying the macro as I recall doing that exact same thing all the time back in the day and it will certainly evaluate the invocation id and then use it within the single quotes. Make sure you match case exactly with what the name of it is and if you are typing it in by hand use the built-in helper instead. I don't have any docs with me during the day, perhaps it is "DSJobInvocationId"... case matters. Double-check.