I have a pair of Elastic Job agents running on separate Azure SQL Servers.

On server A, I can create a new target group in SQL:

sp_add_target_group @target_group_name = 'mygroup'

On server B, I cannot create any new target groups in SQL. They all fail with a table level check constraint error:

The INSERT statement conflicted with the CHECK constraint “CK_sql_database_target_database_name_not_null”. The conflict occurred in database “sthft-Jobs”, table “jobs_internal.targets”.

Adding the same group with the Elastic Job agent in the Azure Portal just works (ie, using the same name, the UI creates the group & I can read the data from the table, just like I expected the stored procedure to add it).

Why does Server B fail with the table constraint but server A does not?

Underpinning sp_add_target_group is a simple, direct insert into what turns out to be a view:

[jobs_internal].targets

This is a simple filter on jobs_internal.targets.

The stored procedure does a uniqueness check, then creates a new guid & sets the target type to be TargetGroup before attempting the insert.

The check constraint is defined as:

[target_type]<>'SqlDatabase' OR [database_name] IS NOT NULL

It is the same constraint definition as per the table in the database on Server A, which works just fine.

Since the target type is hard-coded into the stored procedure, the new row will always pass the check.

This suggests that the insert is tripping the constraint check, which is then failing some row of data already in the table.

I can’t find a row in the table that defies the check either though.
Every row with targettype='SqlDatabase' has a non-null value in database_name

Every row with targettype<>'SqlDatabase' has database_name of NULL

There are no rows where targettype IS NULL

I have rebuilt all the indexes on the table, in case it was that.

Annoyingly, the Azure UI appears happy to create whatever target groups it wants in the db.

I’m sure there is a very simple explanation for my error, since I expect the Azure UI is using the same underlying stored procedure that I am successfully using on server A & unsuccessfully calling on server B. So what have I missed here?

(there is no connection between the 2x servers & there is no multi-server targeting that could confuse this)