Need to Bulk Update Email Addresses in 7.0

Hi,

Well it depends on what sets the email address in the first place....

• If the value is calculated using a template (value calculation) then the template would need to be changed and then recalculated for all objects
• If it is a static value that is set from some input (e.g. HR feed) the it needs to be changed in that feed and the feed re-applied
• If it is a static value that is set on Person some other way and just needs to be changed then you could use DataImporter to update the values

HTH, Barry.

In the previous version I was able to use vid_InsertForHandleObject_0.  So I was looking for something similar.

Thanks,

Blane

Hi Blane,

Well honestly DataImporter is way easier but if you want to do it from the SQL console then 'exec dbo.QBM_PJobCreate_HOUpdate ' is the V7/V8 equivalent.

HTH, Barry.

Could you provide an example use case for 'exec dbo.QBM_PJobCreate_HOUpdate' ?

This is an example of what I have tried:

exec QBM_PJobCreate_HOUpdate
'update', 'person',
'uid_person=''00000000-00000-0000-0000-0000000''',
'defaultemailaddress', 'test@test.com', @procid=12345

And this is the error:

Msg 206, Level 16, State 2, Procedure QBM_PJobCreate_HOUpdate, Line 0 [Batch Start Line 0]
Operand type clash: varchar is incompatible with QBM_YParameterList

Here you go:

Declare @procid varchar(38)

select @procid= newid()

Declare @objkeys dbo.QBM_YParameterList

-- Update

exec dbo.QBM_PJobCreate_HOUpdate 'ADSAccount','isnull(UID_Person,'''') <> '''' AND isnull(UID_TSBAccountDef,'''') <> '''' And isGroupAccount=0 ',@GenProcID=@procid,@ObjectKeysAffected=@objkeys, @p1='IsGroupAccount',@v1=1

Barry,

This is great!  Exactly what I needed.

Thank you

Barry,

when I submit this SQL it spawns a lot of jobs do you know wny?

Declare @procid varchar(38)
select @procid= newid()
Declare @objkeys dbo.QBM_YParameterList
-- Update
exec dbo.QBM_PJobCreate_HOUpdate
'ADSAccount','UID_Person= ''00000000-0000-0000-0000-000000000000''
AND UID_TSBAccountDef = ''00000000-0000-0000-0000-000000000000''
And isGroupAccount=0 '
,@GenProcID=@procid,@ObjectKeysAffected=@objkeys,
@p1='mail',@v1='test@test.com'

-- Update
exec dbo.QBM_PJobCreate_HOUpdate
'Person','UID_Person =''00000000-0000-0000-0000-000000000000''
'
,@GenProcID=@procid,@ObjectKeysAffected=@objkeys,
@p1='defaultemailaddress',@v1='test@test.com'

Thanks,

Blane

Hi Blane,

It should only create 2 jobs from what I can see.

What exactly do you mean by a lot of jobs?

B.

Hi Blane,

It should only create 2 jobs from what I can see.

What exactly do you mean by a lot of jobs?

B.

Barry,

Before I deleted the QBMDBQueueProcess: handle object update for object type ADSAccount it had spawned:

Set EMail Attributes in Person and ADSAccount = 184 jobs

ADS_ADSAccount_Update/(De-)activate = 444 jobs

VI_UnsAccountB_Generic = 85 jobs

as well as other jobs that our specific to our environment and are triggered by the jobs above.

Thanks,

Blane

Hi Blane,

Well when I see uid = 00000000-0000-0000-0000-000000000000 I'm assuming that was just you obfuscating the actual values.

If you update the email on one person that may spawn other jobs if they have templates that react on the change.

e.g. ADSAccount my get updated from person if the email on ADSAccount is updated by person email and the account is linked.

In the example I sent:

'isnull(UID_Person,'''') <> '''' AND isnull(UID_TSBAccountDef,'''') <> '''' And isGroupAccount=0 '

is a where clause to the target table i.e. ADSAccount.

I would start with a single update to person for a specific UID_Person ...... then check the results ...... sounds like ADSAccount and UNSAccountB are linked so they may get updated ...... resulting in provisionign jobs to the target.

If all is as expected then you can expand your where clause appropriately.

HTH, B.

Barry,

You are correct I put the zeros in my example to redact the actual. I did run for a specific UID_Person.  I reran using just the person table and it worked much better.

Thanks again for the help,

Blane