Global Change - DBNull to Integer Error

Christopher Kowalske

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>

Christopher Kowalske

Nevermind, I think I found my error. I wasn't setting the values for @NUMBERADDED, @NUMBEREDITED, and @NUMBERDELETED