We’ve got a global change to pull some data into BBEC from an external database. The global change correctly executed one time, but then we noticed it pulled back more records than we wanted, so we modified the SQL to correct that. Now, every time we try to process the global change, we get the following error message (see image below). Even if we roll the SQL back to the version that executed the first time, we get the same error. I also included the GlobalChange XML Spec below; Apologies for the sloppy SQL, this was supposed to be a quick temporary thing. The SQL does execute directly from SSMS.
Anyone have any ideas on this one?
<GlobalChangeSpec
xmlns="bb_appfx_globalchange"
xmlns:c="bb_appfx_commontypes"
ID="c473c058-c5ba-440f-9bc9-42f8ed7a2f94"
Name="Load Grad Advance PendingDEA Integrations Global Change"
Description="Load Grad Advance Pending DEAIntegrations Global Change"
Author="SMU\\DMS"
DisplayName="Load Grad Advance Pending DEAIntegrations Global Change"
GlobalChangeFolder="Grad Advance"
SPName="USR_USP_GLOBALCHANGE_LOADGRADADVANCEPENDINGDEAINTEGRATIONS"
>
<!-- describe the SP that performs the global change operation -->
<CreateProcedureSQL>
<![CDATA[
create procedure dbo.USR_USP_GLOBALCHANGE_LOADGRADADVANCEPENDINGDEAINTEGRATIONS
(
@CHANGEAGENTID uniqueidentifier = null,
@StartDate date = null,
@Status nvarchar(10) = null,
@ASOF as datetime = null,
@NUMBERADDED int = 0 output,
@NUMBEREDITED int = 0 output,
@NUMBERDELETED int = 0 output
)
as
set nocount off;
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate();
if @StartDate is null
set @StartDate = '2024-03-01';
if @Status is null
set @Status = 'paid';
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
insert into USR_SMU_GRADADVANCEPENDINGTRANSACTIONS
(id,
BBMSTransactionId,
GRADADVANCE_SPLITID,
CAMPAIGNID,
CAMPAIGNNAME,
CAMPAIGNEXTERNALID,
DESIGNATIONID,
DESIGNATIONVANITYNAME,
FIRSTNAME,
LASTNAME,
EMAILADDRESS,
AMOUNT,
[STATUS],
[PUBLIC],
APPEALID,
DONOREXTERNALID,
PHONENUMBER,
DONORAFFINITY,
IS_IGNORED,
IS_DOWNLOADED,
EPOCH,
IS_READYFORBATCHUPLOAD,
BATCHID,
--BATCHGUID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
ADDRESSONE,
ADDRESSTWO,
ADDRESSTHREE,
CITY,
[STATE],
Zip,
[DATE])
select newid() id,
transact_id as BBMSTransactionId,
d.id as GRADADVANCE_SPLITID,
BENEFICIARY_ID CAMPAIGNID,
BENEFICIARY_NAME CAMPAIGNNAME,
BENEFICIARY_EXTERNAL_ID CAMPAIGNEXTERNALID,
(Select DESIGNATIONID from DESIGNATIONALTLOOKUPID where ALTLOOKUPID = LEFT(BENEFICIARY_EXTERNAL_ID,5)) DESIGNATIONID,
LEFT(BENEFICIARY_EXTERNAL_ID,5) DESIGNATIONVANITYNAME,
isnull(donor_first_name, '') FIRSTNAME,
isnull(donor_last_name, '') LASTNAME,
donor_email EMAILADDRESS,
donation_amount AMOUNT,
d.[Status] [STATUS],
case when donation_public = 1 then 'True' else 'False' end [PUBLIC],
isnull(conversion_appeal_external_id, '') APPEALID,
isnull(donor_external_id, '') DONOREXTERNALID,
isnull(donor_phone, '') PHONENUMBER,
affinities DONORAFFINITY,
0 IS_IGNORED,
0 IS_DOWNLOADED,
'' EPOCH,
0 IS_READYFORBATCHUPLOAD,
'' BATCHID,
--NULL BATCHGUID,
@CHANGEAGENTID ADDEDBYID,
@CHANGEAGENTID CHANGEDBYID,
@CURRENTDATE DATEADDED,
@CURRENTDATE DATECHANGED,
isnull(donor_address1, '') ADDRESSONE,
isnull(donor_address2, '') ADDRESSTWO,
isnull(donor_address3, '') ADDRESSTHREE,
isnull(donor_city, '') CITY,
isnull(donor_region, '') [STATE],
isnull(donor_zip,'' ) Zip,
created_at DATE
from DEAIntegrations.GradAdvance.donations d
left join USR_SMU_GRADADVANCEPENDINGTRANSACTIONS b on d.id = b.GRADADVANCE_SPLITID
where b.GRADADVANCE_SPLITID is null
and convert(nvarchar(10),created_at,120) >= @StartDate
and d.[status] = @Status
and d.transact_id is not null
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
]]>
</CreateProcedureSQL>
<ParametersFormMetaData>
<FormMetaData xmlns="bb_appfx_commontypes">
<FormFields>
<FormField FieldID="StartDate" Caption="StartDate" DataType="Date" Required="true"/>
<FormField FieldID="Status" Caption="Status" DataType="String" DefaultValueText="Paid">
<ValueList>
<Items>
<Item>
<Value>Paid</Value>
<Label>Paid</Label>
</Item>
<Item>
<Value>Unpaid</Value>
<Label>Unpaid</Label>
</Item>
</Items>
</ValueList>
</FormField>
</FormFields>
</FormMetaData>
</ParametersFormMetaData>
</GlobalChangeSpec>
