Error converting data type nvarchar to numeric

  • Hi,

    I have this stored proc that I run nightly. I keep on having issues with it failing because it's having issues converting a nvarchar. This is the exact error I'm getting:

    Error converting data type nvarchar to numeric

    Here are the data types for my upsert:

    [Id] [nchar](18) NULL,

    [Error] [nvarchar](255) NULL,

    [BillingCity] [nvarchar](40) NULL,

    [BillingCountry] [nvarchar](40) NULL,

    [BillingPostalCode] [nvarchar](20) NULL,

    [BillingState] [nvarchar](20) NULL,

    [BillingStreet] [nvarchar](255) NULL,

    [Category__c] [nchar](18) NULL,

    [Description] [ntext] NULL,

    [Fax] [nvarchar](40) NULL,

    [Industry] [nvarchar](80) NULL,

    [Market__c] [nchar](18) NULL,

    [Name] [nvarchar](255) NOT NULL,

    [Phone] [nvarchar](40) NULL,

    [Rank__c] [decimal](18, 0) NULL,

    [SPID__c] [nvarchar](10) NULL,

    [Type] [nvarchar](40) NULL,

    [Website] [nvarchar](255) NULL,

    [Advertiser_Type__c] [nvarchar](255) NULL,

    [Big_Deal_Rank__c] [decimal](18, 0) NULL,

    [Big_Deal_Rep__c] [nchar](18) NULL,

    [Ownerid] [nchar](18) NOT NULL,

    [Registered__c] [nvarchar](255) NULL,

    [Registered_Date__c] [datetime] NULL,

    [SP_Total_Grade__c] [nvarchar](255) NULL,

    [SP_Total_GPA__c] [decimal](18, 8) NULL,

    [SP_Total_Reports__c] [decimal](18, 0) NULL,

    [SP_Current_Reports__c] [decimal](18, 0) NULL,

    [SP_Current_GPA__c] [decimal](18, 8) NULL,

    [SP_Past_30_Days_Unique_Reviews__c] [decimal](18, 0) NULL,

    [SP_Past_90_Days_Unique_Reviews__c] [decimal](18, 0) NULL,

    [SP_Past_365_Days_Unique_Review__c] [decimal](18, 0) NULL,

    [SP_Total_Unique_Reviews__c] [decimal](18, 0) NULL,

    [SP_Associated_to_AL_Membership__c] [nvarchar](255) NULL,

    [X12_Month_SP_Report__c] [decimal](18, 0) NULL,

    [X12_Month_SP_Reported_Revenue__c] [decimal](18, 2) NULL,

    [X12_Month_SP_GPA__c] [decimal](18, 8) NULL

    Here's my proc:

    USE [SalesForceLocal]

    GO

    /****** Object: StoredProcedure [dbo].[proc_Upload_SalesForce_Account_Production] Script Date: 09/02/2012 15:27:32 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[proc_Upload_SalesForce_Account_Production] (@SalesForceInstance VARCHAR(100))

    AS

    BEGIN TRY

    DECLARE @TableErrorCount INT = 0

    DECLARE @TableSuccessCount INT = 0

    DECLARE @TableOtherCount INT = 0

    DECLARE @CurrentTableInProcess VARCHAR(100) = NULL

    DECLARE @CurrentTableInProcessDescription VARCHAR(100) = NULL

    DECLARE @SubmittedTableCount INT = 0

    DECLARE @RowTableCount INT = 0

    --DECLARE @SalesForceInstance VARCHAR(100) = 'SALESFORCESANDBOX'

    DECLARE @BatchID UNIQUEIDENTIFIER = [dbo].[ufn_GetSalesForceBatchInfoID]()

    SELECT @BatchID

    DECLARE @BatchErrorCount INT = 0

    DECLARE @BatchSuccessCount INT = 0

    DECLARE @BatchOtherCount INT = 0

    DECLARE @BatchNumberOfTablesProcessed INT = 0

    SET @CurrentTableInProcess = 'Account_UpdateRank'

    SET @CurrentTableInProcessDescription = 'Update Ranking For Service Providers In Account'

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Insert'

    -- Insert ProcessStep For Batch

    INSERT INTO [dbo].[SalesForceBatchDetailInfo]

    ( [SalesForceBatchID] ,

    [ProcessDescription] ,

    [TableName]

    )

    VALUES (@BatchID, @CurrentTableInProcessDescription, @CurrentTableInProcess )

    -- Update Rank For ServiceProviders. This Is Done Seperately Since Rank Should Change Daily.

    INSERT INTO [dbo].[Account_UpdateRank]

    ( [Id], [Rank__c], [Big_Deal_Rank__c] )

    SELECT a.[Id], [lfcl].[RANK], BigDeal.[RANK]

    FROM SQLSRV8.[angie].[dbo].[LeadFairyCallList] AS lfcl WITH (NOLOCK)

    INNER JOIN [SQLSRV8].[angie].[dbo].[Employees] AS e WITH (NOLOCK) ON lfcl.[EmployeeId] = e.[lngUniqueID]

    LEFT JOIN(

    SELECT lfcl.[RANK], lfcl.[SPID]

    FROM [SQLSRV8].[angie].[dbo].[LeadFairyCallList] AS lfcl WITH (NOLOCK)

    INNER JOIN [SQLSRV8].[angie].[dbo].[Employees] AS e2 WITH (NOLOCK) ON lfcl.[EmployeeId] = e2.[lngUniqueID]

    AND e2.[DepartmentID] = 22

    ) BigDeal ON lfcl.[SPID] = [BigDeal].[SPID]

    INNER JOIN [dbo].[Account] AS a WITH (NOLOCK) ON lfcl.[SPID] = a.[SPID__c]

    WHERE ((ISNULL([lfcl].[RANK],-999) <> ISNULL(a.[Rank__c],-999)) OR (ISNULL([BigDeal].[RANK],-999) <> ISNULL(a.[Big_Deal_Rank__c],-999)))

    AND e.[DepartmentID] IN (10,13) -- Ad Sales/Account Management

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Insert'

    -- Update Current Service Providers To InEligible

    IF (SELECT ISNULL(COUNT(*),0) FROM Account_UpdateRank WITH (NOLOCK)) > 0

    BEGIN

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Calling Bulk Update API'

    EXECUTE [dbo].[SF_BulkOps]

    @operation = 'Update:bulkapi' , -- nvarchar(50)

    @table_server = @SalesForceInstance , -- sysname

    @table_name = @CurrentTableInProcess -- sysname

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Calling Bulk Update API'

    END

    --SET @CurrentTableInProcess = 'Account_Upsert'

    --SET @CurrentTableInProcessDescription = 'Update/Insert Any Modified Fields For Service Providers'

    ---- Insert ProcessStep For Batch

    --INSERT INTO [dbo].[SalesForceBatchDetailInfo]

    -- ( [SalesForceBatchID] ,

    -- [ProcessDescription] ,

    -- [TableName]

    -- )

    --VALUES (@BatchID, @CurrentTableInProcessDescription, @CurrentTableInProcess )

    -- Logging Step

    --EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    -- @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    -- @TableName = @CurrentTableInProcess , -- varchar(100)

    -- @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    -- @StepAction = 'Start' , -- varchar(100)

    -- @StepDescription = 'Insert'

    -- Insert Our Eligibles/Advertisers Which Are Assigned To Reps

    -- This Is For Ad Sales Health - Do Not Include The Employee

    --INSERT INTO [dbo].[Account_Upsert]

    -- (

    -- [BillingCity] ,

    -- [BillingCountry] ,

    -- [BillingPostalCode] ,

    -- [BillingState] ,

    -- [BillingStreet] ,

    -- [Category__c] ,

    -- [Description] ,

    -- [Fax] ,

    -- [Industry] ,

    -- [Market__c] ,

    -- [Name] ,

    -- [Phone] ,

    -- [Rank__c] ,

    -- [SPID__c] ,

    -- [Type] ,

    -- [Website] ,

    -- [Advertiser_Type__c],

    -- [Big_Deal_Rank__c],

    -- Big_Deal_Rep__c

    -- )

    -- SELECT DISTINCT

    -- LEFT(NULLIF(sp.City,''), 40) AS BillingCity , -- nvarchar(40)

    -- LEFT(NULLIF(c.CountryCode,''), 40) AS BillingCountry , -- nvarchar(40)

    -- LEFT(NULLIF(sp.Zip,''), 20) AS BillingPostalCode , -- nvarchar(20)

    -- LEFT(NULLIF(sp.[State],''), 20) AS BillingState , -- nvarchar(20)

    -- CASE WHEN (LEN(sp.[Address2]) = 0 AND LEN(sp.[Address1]) = 0) THEN NULL

    -- WHEN (LEN(sp.[Address1]) = 0 AND LEN(sp.[Address2]) > 0) THEN LEFT(sp.[Address2],255)

    -- WHEN (LEN(sp.[Address1]) > 0 AND LEN(sp.[Address2]) = 0) THEN LEFT(sp.[Address1],255)

    --ELSE LEFT((ISNULL(sp.Address1,'') + ' ' + ISNULL(sp.[Address2],'')),255) END AS BillingStreet , -- nvarchar(255)

    -- cc.[Id] AS Category__c ,-- nchar(18)

    -- NULLIF(sp.BusDesc,'') AS [Description] , -- ntext

    -- LEFT(NULLIF(sp.Fax,''), 40) AS Fax , -- nvarchar(40)

    -- 'Other' AS Industry , -- nvarchar(80),

    -- mc.[Id] AS Market__c , -- nchar(18)

    -- LEFT(NULLIF(sp.[CompanyName],''),255) , -- Name - nvarchar(255)

    -- LEFT(NULLIF(sp.Phone,''), 40) AS Phone , -- nvarchar(40)

    -- lfc.[RANK] AS Rank__c , -- decimal

    -- LEFT(sp.SPID, 10) AS SPID__c , -- nvarchar(10)

    -- CASE spe.departmentid

    -- WHEN 10 THEN 'Eligible'

    -- WHEN 13 THEN 'Advertiser'

    -- END AS [Type] , -- nvarchar(40)

    -- LEFT(NULLIF(sp.URL,''),255) AS Website, -- nvarchar(255)

    -- CASE WHEN spe.[SubTerritoryGroupId] = 4 THEN 'P1'

    -- WHEN spe.[SubTerritoryGroupId] = 5 THEN 'Y1'

    -- WHEN spe.[SubTerritoryGroupId] = 6 THEN 'Health'

    -- ELSE NULL END AS Advertiser_Type__c,

    -- BigDeal.[RANK], -- Big Deal Rank

    -- BigDeal.[Id] -- Big Deal Employee

    -- FROM SQLSRV8.angie.dbo.ServiceProvider sp WITH ( NOLOCK )

    -- INNER JOIN SQLSRV8.angie.dbo.SPEligibility spe WITH ( NOLOCK ) ON spe.spid = sp.spid

    -- INNER JOIN SQLSRV8.[angie].[dbo].[SubTerritoryGroup] AS stg WITH (NOLOCK) ON spe.[SubTerritoryGroupId] = stg.[SubTerritoryGroupId]

    -- --INNER JOIN angie.dbo.SPMarket SPM WITH ( NOLOCK ) ON SPM.Spid = spe.Spid

    -- -- AND PrimaryMarket = 1

    -- LEFT JOIN [dbo].[Market__c] AS mc WITH ( NOLOCK ) ON ISNULL(spe.[PrimaryMarketId],0) = mc.[Market_ID__c]

    -- INNER JOIN SQLSRV8.angie.dbo.LeadFairyCallList lfc WITH ( NOLOCK ) ON lfc.spid = sp.spid

    -- INNER JOIN [SQLSRV8].[angie].[dbo].[Employees] AS e WITH (NOLOCK) ON spe.[DepartmentID] = e.[DepartmentID]

    --AND lfc.[EmployeeId] = e.[lngUniqueID]

    -- LEFT JOIN [dbo].[Category__c] AS cc WITH ( NOLOCK ) ON ISNULL([lfc].[CategoryID],0) = cc.[Category_ID__c]

    -- --LEFT JOIN [angie].[dbo].[Employees] AS employee WITH ( NOLOCK ) ON lfc.[EmployeeId] = employee.[lngUniqueID]

    -- --LEFT JOIN [dbo].[User] AS empUser WITH ( NOLOCK ) ON [employee].[lngUniqueID] = [empUser].[Employee_ID__c]

    -- --LEFT OUTER JOIN angie.dbo.W9TaxInformation w WITH ( NOLOCK ) ON w.spid = sp.spid

    -- --LEFT OUTER JOIN angie.dbo.BusinessType bt WITH ( NOLOCK ) ON bt.BusinessTypeId = w.BusinessTypeId

    -- INNER JOIN SQLSRV8.angie.dbo.CountryCode c WITH ( NOLOCK ) ON c.CountryCodeId = sp.CountryCodeId

    -- INNER JOIN SQLSRV8.angie.dbo.Departments D WITH ( NOLOCK ) ON D.Departmentid = spe.departmentid

    -- LEFT JOIN(

    --SELECT lfcl.[RANK], lfcl.[SPID], u.[Id]

    --FROM [SQLSRV8].[angie].[dbo].[LeadFairyCallList] AS lfcl WITH (NOLOCK)

    --INNER JOIN [SQLSRV8].[angie].[dbo].[Employees] AS e2 WITH (NOLOCK) ON lfcl.[EmployeeId] = e2.[lngUniqueID]

    --AND e2.[DepartmentID] = 22

    --INNER JOIN [dbo].[User] AS u WITH (NOLOCK) ON e2.[lngUniqueID] = u.[Employee_ID__c]

    --) BigDeal ON [spe].[SPID] = [BigDeal].[SPID]

    -- WHERE spe.[DepartmentID] = 10 -- Ad Sales

    -- AND stg.[SalesGroupId] = 2 -- Health

    -- AND NULLIF(sp.[CompanyName],'') IS NOT NULL

    -- JN - Commented Out Below Code Per Update On 10/28/2011 By Reqeust Of Matt Dooley.

    ---- Since Top Category Is Not Defined For These We Need To Figure Out Top Rank

    --CREATE TABLE #SPEligibleSPIDRank

    --(

    --SPID INT,

    --CategoryID INT,

    --[Rank] INT

    --)

    --INSERT INTO [#SPEligibleSPIDRank]

    -- ( [SPID], [CategoryID], [Rank] )

    --SELECT se.[SPID], sct.CategoryID, ROW_NUMBER() OVER (PARTITION BY se.[SPID] ORDER BY sct.[CurrentReports] DESC, sct.[TotalReports] DESC, ISNULL(tct.[Tier],999) ASC) AS [Rank]

    --FROM SQLSRV8.[angie].[dbo].[SPEligibility] AS se WITH (NOLOCK)

    --LEFT JOIN [dbo].[Account_Upsert] AS au WITH (NOLOCK) ON se.[SPID] = au.[SPID__c]

    --INNER JOIN SQLSRV8.[angie].[dbo].[CategoryGroup] AS cg WITH (NOLOCK) ON se.[PreferredCategoryGroupTypeId] = cg.[CategoryGroupTypeId]

    --INNER JOIN SQLSRV8.[angie].[dbo].[Categories] AS c WITH (NOLOCK) ON [cg].[CategoryGroupId] = [c].[CategoryGroupId]

    --LEFT JOIN SQLSRV8.[angie].[dbo].[tblCategoryTier] AS tct WITH (NOLOCK) ON [c].[CategoryID] = [tct].[Categoryid]

    --INNER JOIN SQLSRV8.[angie].[dbo].[SPCategoryTotal] AS sct WITH (NOLOCK) ON [se].[SPID] = [sct].[SPID]

    --AND [c].[CategoryID] = [sct].[CategoryId]

    --WHERE au.[SPID__c] IS NULL -- Not Already Ready To Load

    --AND sct.[Unavailable] = 0 -- Available

    ---- Insert From SPElgibility Which Are Not Assigned To Reps But Eligible/Advertisers

    --INSERT INTO [dbo].[Account_Upsert]

    -- (

    -- [BillingCity] ,

    -- [BillingCountry] ,

    -- [BillingPostalCode] ,

    -- [BillingState] ,

    -- [BillingStreet] ,

    -- [Category__c] ,

    -- [Description] ,

    -- [Fax] ,

    -- [Industry] ,

    -- [Market__c] ,

    -- [Name] ,

    -- [Phone] ,

    -- [Rank__c] ,

    -- [SPID__c] ,

    -- [Type] ,

    -- [Website],

    -- [Advertiser_Type__c]

    -- )

    -- SELECT DISTINCT

    -- LEFT(NULLIF(sp.City,''), 40) AS BillingCity , -- nvarchar(40)

    -- LEFT(NULLIF(c.CountryCode,''), 40) AS BillingCountry , -- nvarchar(40)

    -- LEFT(NULLIF(sp.Zip,''), 20) AS BillingPostalCode , -- nvarchar(20)

    -- LEFT(NULLIF(sp.[State],''), 20) AS BillingState , -- nvarchar(20)

    -- CASE WHEN (LEN(sp.[Address2]) = 0 AND LEN(sp.[Address1]) = 0) THEN NULL

    -- WHEN (LEN(sp.[Address1]) = 0 AND LEN(sp.[Address2]) > 0) THEN LEFT(sp.[Address2],255)

    -- WHEN (LEN(sp.[Address1]) > 0 AND LEN(sp.[Address2]) = 0) THEN LEFT(sp.[Address1],255)

    --ELSE LEFT((ISNULL(sp.Address1,'') + ' ' + ISNULL(sp.[Address2],'')),255) END AS BillingStreet , -- nvarchar(255)

    -- cc.[Id] AS Category__c ,-- nchar(18)

    -- NULLIF(sp.BusDesc,'') AS [Description] , -- ntext

    -- LEFT(NULLIF(sp.Fax,''), 40) AS Fax , -- nvarchar(40)

    -- 'Other' AS Industry , -- nvarchar(80),

    -- mc.[Id] AS Market__c , -- nchar(18)

    -- LEFT(NULLIF(sp.[CompanyName],''),255) , -- Name - nvarchar(255)

    -- LEFT(NULLIF(sp.Phone,''), 40) AS Phone , -- nvarchar(40)

    -- NULL AS Rank__c , -- decimal

    -- LEFT(sp.SPID, 10) AS SPID__c , -- nvarchar(10)

    -- CASE spe.departmentid

    -- WHEN 10 THEN 'Eligible'

    -- WHEN 13 THEN 'Advertiser'

    -- END AS [Type] , -- nvarchar(40)

    -- LEFT(NULLIF(sp.URL,''),255) AS Website, -- nvarchar(255)

    -- CASE WHEN spe.[SubTerritoryGroupId] = 4 THEN 'P1'

    -- WHEN spe.[SubTerritoryGroupId] = 5 THEN 'Y1'

    -- WHEN spe.[SubTerritoryGroupId] = 6 THEN 'Health'

    -- ELSE NULL END AS Advertiser_Type__c

    -- FROM SQLSRV8.angie.dbo.ServiceProvider sp WITH ( NOLOCK )

    -- INNER JOIN SQLSRV8.angie.dbo.SPEligibility spe WITH ( NOLOCK ) ON spe.spid = sp.spid

    -- LEFT JOIN [dbo].[Account_Upsert] AS au WITH (NOLOCK) ON sp.[SPID] = au.[SPID__c]

    -- LEFT JOIN #SPEligibleSPIDRank speRank ON [sp].[SPID] = [speRank].[SPID]

    --AND speRank.[Rank] = 1

    -- LEFT JOIN [dbo].[Market__c] AS mc WITH ( NOLOCK ) ON ISNULL(spe.[PrimaryMarketId],0) = mc.[Market_ID__c]

    -- LEFT JOIN [dbo].[Category__c] AS cc WITH ( NOLOCK ) ON ISNULL(speRank.[CategoryID],0) = cc.[Category_ID__c]

    -- INNER JOIN SQLSRV8.angie.dbo.CountryCode c WITH ( NOLOCK ) ON c.CountryCodeId = sp.CountryCodeId

    -- INNER JOIN SQLSRV8.angie.dbo.Departments D WITH ( NOLOCK ) ON D.Departmentid = spe.departmentid

    -- WHERE

    -- -- We Are Not Already Loading Them

    -- au.[SPID__c] IS NULL

    ---- Since Top Category Is Not Defined For These We Need To Figure Out Top Rank

    --CREATE TABLE #HealthSPIDRank

    --(

    --SPID INT,

    --CategoryID INT,

    --[Rank] INT

    --)

    --INSERT INTO [#HealthSPIDRank]

    -- ( [SPID], [CategoryID], [Rank] )

    --SELECT sp.[SPID], sct.CategoryID, ROW_NUMBER() OVER (PARTITION BY sp.[SPID] ORDER BY sct.[CurrentReports] DESC, sct.[TotalReports] DESC, ISNULL(tct.[Tier],999) ASC) AS [Rank]

    --FROM SQLSRV8.[angie].[dbo].[ServiceProvider] AS sp WITH (NOLOCK)

    --INNER JOIN (

    --SELECT scgt.[Spid]

    --FROM SQLSRV8.[angie].[dbo].[ServiceProvider] AS sp WITH (NOLOCK)

    --INNER JOIN SQLSRV8.[angie].[dbo].[SPCategoryTotal] AS sct WITH (NOLOCK) ON [sp].[SPID] = [sct].[SPID]

    --INNER JOIN SQLSRV8.[angie].[dbo].[SPTotal] AS st WITH (NOLOCK) ON [sp].[SPID] = [st].[SPID]

    --INNER JOIN SQLSRV8.[angie].[dbo].[SPStatus] AS ss WITH (NOLOCK) ON [sp].[SPStatusId] = [ss].[SPStatusId]

    --INNER JOIN SQLSRV8.[angie].[dbo].[SPCategoryGroupType] AS scgt WITH (NOLOCK) ON [sp].[SPID] = [scgt].[Spid]

    --WHERE scgt.[CategoryGroupTypeId] = 2

    --AND ss.[SPStatus] IN ('Active','Contact')

    --AND st.[IsExcluded] = 0

    --AND sct.[TotalReports] > 1

    --GROUP BY scgt.[Spid]

    --) TargetHealthSPID ON sp.[SPID] = [TargetHealthSPID].[Spid]

    --LEFT JOIN [dbo].[Account_Upsert] AS au WITH (NOLOCK) ON sp.[SPID] = au.[SPID__c]

    --INNER JOIN SQLSRV8.[angie].[dbo].[SPCategories] AS sc WITH (NOLOCK) ON sp.[SPID] = sc.[SPID]

    ----INNER JOIN [angie].[dbo].[Categories] AS c WITH (NOLOCK) ON [sc].[CategoryGroupId] = [c].[CategoryGroupId]

    --INNER JOIN SQLSRV8.[angie].[dbo].[SPCategoryTotal] AS sct WITH (NOLOCK) ON [sp].[SPID] = [sct].[SPID]

    --AND [sc].[CategoryID] = [sct].[CategoryId]

    --INNER JOIN SQLSRV8.[angie].[dbo].[Categories] AS c WITH (NOLOCK) ON [sc].[CategoryID] = [c].[CategoryID]

    --INNER JOIN SQLSRV8.[angie].[dbo].[CategoryGroup] AS cg WITH (NOLOCK) ON [c].[CategoryGroupId] = [cg].[CategoryGroupId]

    --LEFT JOIN SQLSRV8.[angie].[dbo].[tblCategoryTier] AS tct WITH (NOLOCK) ON [c].[CategoryID] = [tct].[Categoryid]

    --WHERE au.[SPID__c] IS NULL -- Not Already Ready To Load

    --AND sct.[Unavailable] = 0 -- Available

    --AND cg.[CategoryGroupTypeId] = 2 -- Health

    ---- Insert From SPElgibility Which Are Not Assigned To Reps But Eligible/Advertisers

    --INSERT INTO [dbo].[Account_Upsert]

    -- (

    -- [BillingCity] ,

    -- [BillingCountry] ,

    -- [BillingPostalCode] ,

    -- [BillingState] ,

    -- [BillingStreet] ,

    -- [Category__c] ,

    -- [Description] ,

    -- [Fax] ,

    -- [Industry] ,

    -- [Market__c] ,

    -- [Name] ,

    -- [Phone] ,

    -- [Rank__c] ,

    -- [SPID__c] ,

    -- [Type] ,

    -- [Website] ,

    -- [Advertiser_Type__c]

    -- )

    -- SELECT DISTINCT

    -- LEFT(NULLIF(sp.City,''), 40) AS BillingCity , -- nvarchar(40)

    -- LEFT(NULLIF(c.CountryCode,''), 40) AS BillingCountry , -- nvarchar(40)

    -- LEFT(NULLIF(sp.Zip,''), 20) AS BillingPostalCode , -- nvarchar(20)

    -- LEFT(NULLIF(sp.[State],''), 20) AS BillingState , -- nvarchar(20)

    -- CASE WHEN (LEN(sp.[Address2]) = 0 AND LEN(sp.[Address1]) = 0) THEN NULL

    -- WHEN (LEN(sp.[Address1]) = 0 AND LEN(sp.[Address2]) > 0) THEN LEFT(sp.[Address2],255)

    -- WHEN (LEN(sp.[Address1]) > 0 AND LEN(sp.[Address2]) = 0) THEN LEFT(sp.[Address1],255)

    --ELSE LEFT((ISNULL(sp.Address1,'') + ' ' + ISNULL(sp.[Address2],'')),255) END AS BillingStreet , -- nvarchar(255)

    -- cc.[Id] AS Category__c ,-- nchar(18)

    -- NULLIF(sp.BusDesc,'') AS [Description] , -- ntext

    -- LEFT(NULLIF(sp.Fax,''), 40) AS Fax , -- nvarchar(40)

    -- 'Other' AS Industry , -- nvarchar(80),

    -- mc.[Id] AS Market__c , -- nchar(18)

    -- LEFT(NULLIF(sp.[CompanyName],''),255) , -- Name - nvarchar(255)

    -- LEFT(NULLIF(sp.Phone,''), 40) AS Phone , -- nvarchar(40)

    -- NULL AS Rank__c , -- decimal

    -- LEFT(sp.SPID, 10) AS SPID__c , -- nvarchar(10)

    -- NULL AS [Type] , -- nvarchar(40)

    -- LEFT(NULLIF(sp.URL,''),255) AS Website, -- nvarchar(255)

    -- NULL AS Advertiser_Type__c

    -- FROM SQLSRV8.angie.dbo.ServiceProvider sp WITH ( NOLOCK )

    --LEFT JOIN [dbo].[Account_Upsert] AS au WITH (NOLOCK) ON sp.[SPID] = au.[SPID__c]

    -- INNER JOIN #HealthSPIDRank HealthSPIDRank ON [sp].[SPID] = [HealthSPIDRank].[SPID]

    --AND HealthSPIDRank.[Rank] = 1

    --LEFT JOIN SQLSRV8.[angie].[dbo].[SPMarket] AS sm WITH (NOLOCK) ON sp.[SPID] = sm.[Spid]

    --AND sm.[PrimaryMarket] = 1

    -- LEFT JOIN [dbo].[Market__c] AS mc WITH ( NOLOCK ) ON ISNULL(sm.[MarketId],0) = mc.[Market_ID__c]

    -- LEFT JOIN [dbo].[Category__c] AS cc WITH ( NOLOCK ) ON ISNULL(HealthSPIDRank.[CategoryID],0) = cc.[Category_ID__c]

    -- INNER JOIN SQLSRV8.angie.dbo.CountryCode c WITH ( NOLOCK ) ON c.CountryCodeId = sp.CountryCodeId

    -- WHERE

    ---- We Are Not Already Loading Them

    -- au.[SPID__c] IS NULL

    -- Logging Step

    --EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    -- @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    -- @TableName = @CurrentTableInProcess , -- varchar(100)

    -- @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    -- @StepAction = 'Finish' , -- varchar(100)

    -- @StepDescription = 'Insert'

    ---- Logging Step

    --EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    -- @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    -- @TableName = @CurrentTableInProcess , -- varchar(100)

    -- @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    -- @StepAction = 'Start' , -- varchar(100)

    -- @StepDescription = 'Deleting Matches'

    -- Remove All Items That Match (Exclude Rank), Rank Is Handled In Different Updates

    --DELETE FROM [dbo].[Account_Upsert]

    --FROM [dbo].[Account_Upsert] AS au

    --INNER JOIN [dbo].[Account] AS a ON [au].[SPID__c] = [a].[SPID__c]

    --AND ISNULL([au].[BillingCity],'') = ISNULL([a].[BillingCity],'')

    --AND ISNULL([au].[BillingCountry],'') = ISNULL([a].[BillingCountry],'')

    --AND ISNULL([au].[BillingPostalCode],'') = ISNULL([a].[BillingPostalCode],'')

    --AND ISNULL([au].[BillingState],'') = ISNULL([a].[BillingState],'')

    --AND ISNULL([au].[BillingStreet],'') = ISNULL([a].[BillingStreet],'')

    --AND ISNULL([au].[Category__c],'') = ISNULL([a].[Category__c],'')

    ----AND ISNULL(CAST([au].[Description] AS NVARCHAR(MAX)),'') = ISNULL(CAST([a].[Description] AS NVARCHAR(MAX)),'')

    --AND ISNULL([au].[Fax],'') = ISNULL([a].[Fax],'')

    --AND ISNULL([au].[Industry],'') = ISNULL([a].[Industry],'')

    --AND ISNULL([au].[Market__c],'') = ISNULL([a].[Market__c],'')

    --AND ISNULL([au].[Name],'') = ISNULL([a].[Name],'')

    --AND ISNULL([au].[Phone],'') = ISNULL([a].[Phone],'')

    --AND ISNULL([au].[Type],'') = ISNULL([a].[Type],'')

    --AND ISNULL([au].[Website],'') = ISNULL([a].[Website],'')

    --AND ISNULL([au].[Advertiser_Type__c],'') = ISNULL([a].[Advertiser_Type__c],'')

    --AND ISNULL(au.[Big_Deal_Rep__c],'') = ISNULL(a.[Big_Deal_Rep__c],'')

    ---- Logging Step

    --EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    -- @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    -- @TableName = @CurrentTableInProcess , -- varchar(100)

    -- @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    -- @StepAction = 'Finish' , -- varchar(100)

    -- @StepDescription = 'Deleting Matches'

    -- Upsert Current Account Information

    --IF (SELECT ISNULL(COUNT(*),0) FROM Account_Upsert WITH (NOLOCK)) > 0

    --BEGIN

    ---- Logging Step

    --EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    -- @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    -- @TableName = @CurrentTableInProcess , -- varchar(100)

    -- @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    -- @StepAction = 'Start' , -- varchar(100)

    -- @StepDescription = 'Calling Bulk API Upsert'

    --EXECUTE [dbo].[SF_BulkOps]

    -- @operation = 'Upsert:bulkapi' , -- nvarchar(50)

    -- @table_server = @SalesForceInstance , -- sysname

    -- @table_name = @CurrentTableInProcess , -- sysname

    -- @ext_id = 'SPID__c' -- nvarchar(255)

    ---- Logging Step

    --EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    -- @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    -- @TableName = @CurrentTableInProcess , -- varchar(100)

    -- @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    -- @StepAction = 'Finish' , -- varchar(100)

    -- @StepDescription = 'Calling Bulk API Upsert'

    --END

    SET @CurrentTableInProcess = 'Account_UpsertWithOwnerID'

    SET @CurrentTableInProcessDescription = 'Update/Insert Any Modified Fields For Service Providers Including The Assigned AL Rep'

    -- Insert ProcessStep For Batch

    INSERT INTO [dbo].[SalesForceBatchDetailInfo]

    ( [SalesForceBatchID] ,

    [ProcessDescription] ,

    [TableName]

    )

    VALUES (@BatchID, @CurrentTableInProcessDescription, @CurrentTableInProcess )

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Insert'

    -- Insert Our Eligibles/Advertisers Which Are Assigned To Reps

    -- This Is For Ad Sales Health - Do Not Include The Employee

    INSERT INTO [dbo].[Account_UpsertWithOwnerID]

    (

    [BillingCity] ,

    [BillingCountry] ,

    [BillingPostalCode] ,

    [BillingState] ,

    [BillingStreet] ,

    [Category__c] ,

    [Description] ,

    [Fax] ,

    [Industry] ,

    [Market__c] ,

    [Name] ,

    [Phone] ,

    [Rank__c] ,

    [SPID__c] ,

    [Type] ,

    [Website] ,

    [Advertiser_Type__c],

    [Big_Deal_Rank__c],

    [Big_Deal_Rep__c],

    [OwnerId],

    [Registered__c],

    [Registered_Date__c],

    [SP_Total_Grade__c],

    [SP_Total_GPA__c],

    [SP_Total_Reports__c],

    [SP_Current_Reports__c],

    --[SP_Current_Grade__c],

    [SP_Current_GPA__c],

    [SP_Past_30_Days_Unique_Reviews__c],

    [SP_Past_90_Days_Unique_Reviews__c],

    [SP_Past_365_Days_Unique_Review__c],

    [SP_Total_Unique_Reviews__c],

    [SP_Associated_to_AL_Membership__c],

    [X12_Month_SP_Report__c],

    [X12_Month_SP_Reported_Revenue__c],

    [X12_Month_SP_GPA__c]

    --[X12_Month_SP_Grade__c]

    )

    SELECT DISTINCT

    LEFT(NULLIF(sp.City,''), 40) AS BillingCity , -- nvarchar(40)

    LEFT(NULLIF(c.CountryCode,''), 40) AS BillingCountry , -- nvarchar(40)

    LEFT(NULLIF(sp.Zip,''), 20) AS BillingPostalCode , -- nvarchar(20)

    LEFT(NULLIF(sp.[State],''), 20) AS BillingState , -- nvarchar(20)

    CASE WHEN (LEN(sp.[Address2]) = 0 AND LEN(sp.[Address1]) = 0) THEN NULL

    WHEN (LEN(sp.[Address1]) = 0 AND LEN(sp.[Address2]) > 0) THEN LEFT(sp.[Address2],255)

    WHEN (LEN(sp.[Address1]) > 0 AND LEN(sp.[Address2]) = 0) THEN LEFT(sp.[Address1],255)

    ELSE LEFT((ISNULL(sp.Address1,'') + ' ' + ISNULL(sp.[Address2],'')),255) END AS BillingStreet , -- nvarchar(255)

    cc.[Id] AS Category__c ,-- nchar(18)

    NULLIF(sp.BusDesc,'') AS [Description] , -- ntext

    LEFT(NULLIF(sp.Fax,''), 40) AS Fax , -- nvarchar(40)

    'Other' AS Industry , -- nvarchar(80),

    mc.[Id] AS Market__c , -- nchar(18)

    LEFT(NULLIF(sp.[CompanyName],''),255) , -- Name - nvarchar(255)

    LEFT(NULLIF(sp.Phone,''), 40) AS Phone , -- nvarchar(40)

    lfc.[RANK] AS Rank__c , -- decimal

    LEFT(sp.SPID, 10) AS SPID__c , -- nvarchar(10)

    'SPID Type' = CASE WHEN [EsandAdvs].[EType] = 'Advertiser' THEN 'Advertiser' ELSE 'Eligible' END,

    LEFT(NULLIF(sp.URL,''),255) AS Website, -- nvarchar(255)

    CASE WHEN spe.[SubTerritoryGroupId] = 4 THEN 'P1'

    WHEN spe.[SubTerritoryGroupId] = 5 THEN 'P2+'

    WHEN spe.[SubTerritoryGroupId] = 6 THEN 'Health'

    ELSE NULL END AS Advertiser_Type__c,

    BigDeal.[RANK], -- Big Deal Rank

    BigDeal.[Id], -- Big Deal Employee

    Rep.[Id], -- Ad Sales/Account Management Rep

    'Registered' = CASE WHEN [Regs].[SPID] IS NULL THEN 'No' ELSE 'Yes' END,

    'Registered Date' = [Regs].[RegisteredDate],

    'SP Total Grade' = st.[TotalGrade],

    'SP Total GPA' = st.[TotalGradeDisplay],

    'SP Total Reports' = st.[TotalReports],

    'SP Current Reports' = sps.[CurrentReport],

    --'SP Current Grade' = CASE WHEN CurrentYearSPReports.Reports IS NULL THEN 'N/A'

    -- WHEN CurrentYearSPReports.[GPA] >= 3.5 THEN 'A'

    -- WHEN CurrentYearSPReports.[GPA] >= 2.5 AND CurrentYearSPReports.[GPA] < 3.5 THEN 'B'

    -- WHEN CurrentYearSPReports.[GPA] >= 1.5 AND CurrentYearSPReports.[GPA] < 2.5 THEN 'C'

    -- WHEN CurrentYearSPReports.[GPA] >= 0.5 AND CurrentYearSPReports.[GPA] < 1.5 THEN 'D'

    -- ELSE 'F' END,

    'SP Current GPA' = CurrentYearSPReports.[GPA],

    'SP Past 30 Days Unique Reviews' = sps.[Last30_UniqueuePhReviews] + sps.[Last30_UniqueWebReviews],

    'SP Past 90 Days Unique Reviews' = sps.[Last90_UniqueuePhReviews] + sps.[Last90_UniqueWebReviews],

    'SP Past 365 Days Unique Reviews' = sps.[Last1Yr_UniqueuePhReviews] + sps.[Last1Yr_UniqueWebReviews],

    'SP Total Unique Reviews' = sps.[Total_UniqueuePhReviews] + sps.[Total_UniqueWebReviews],

    'SP Associated to AL Membership' = CASE WHEN ka.[SpId] IS NULL THEN 'No' ELSE 'Yes' END,

    '12 Month SP Report' = [PastYearSPReports].[Reports],

    '12 Month SP Reported Revenue' = [PastYearSPReports].[ReportedRevenue],

    '12 Month SP GPA' = [PastYearSPReports].[GPA]

    --'12 Month SP Grade' = CASE WHEN pastyearSPReports.Reports IS NULL THEN 'N/A'

    -- WHEN [PastYearSPReports].[GPA] >= 3.5 THEN 'A'

    -- WHEN [PastYearSPReports].[GPA] >= 2.5 AND [PastYearSPReports].[GPA] < 3.5 THEN 'B'

    -- WHEN [PastYearSPReports].[GPA] >= 1.5 AND [PastYearSPReports].[GPA] < 2.5 THEN 'C'

    -- WHEN [PastYearSPReports].[GPA] >= 0.5 AND [PastYearSPReports].[GPA] < 1.5 THEN 'D'

    -- ELSE 'F' END

    FROM angie.dbo.ServiceProvider AS sp WITH ( NOLOCK )

    INNER JOIN (SELECT vaeh.SPID, vaeh.[EType]

    FROM Reports.dbo.VOAR_AdEligibilityHistory vaeh WITH (NOLOCK)

    INNER JOIN (SELECT vaeh.SPID, ID = MAX(vaeh.ID)

    FROM Reports.dbo.VOAR_AdEligibilityHistory vaeh WITH (NOLOCK)

    WHERE vaeh.StatusDate <= GETDATE()

    GROUP BY vaeh.SPID

    ) mid ON vaeh.SPID = mid.SPID

    AND vaeh.ID = mid.ID

    WHERE vaeh.EType IN ('Eligible', 'Qualified', 'Advertiser', 'Member Qualified', 'Selling Qualified')

    ) AS EsandAdvs ON [sp].[SPID] = [EsandAdvs].[SPID]

    INNER JOIN angie.dbo.SPEligibility spe WITH ( NOLOCK ) ON spe.spid = sp.spid

    INNER JOIN [angie].[dbo].[SubTerritoryGroup] AS stg WITH (NOLOCK) ON spe.[SubTerritoryGroupId] = stg.[SubTerritoryGroupId]

    LEFT JOIN [dbo].[Market__c] AS mc WITH ( NOLOCK ) ON ISNULL(spe.[PrimaryMarketId],0) = mc.[Market_ID__c]

    INNER JOIN angie.dbo.LeadFairyCallList lfc WITH ( NOLOCK ) ON lfc.spid = sp.spid

    INNER JOIN [angie].[dbo].[Employees] AS e WITH (NOLOCK) ON spe.[DepartmentID] = e.[DepartmentID]

    AND lfc.[EmployeeId] = e.[lngUniqueID]

    INNER JOIN [dbo].[User] AS Rep WITH (NOLOCK) ON e.[lngUniqueID] = [Rep].[Employee_ID__c]

    LEFT JOIN [dbo].[Category__c] AS cc WITH ( NOLOCK ) ON ISNULL([lfc].[CategoryID],0) = cc.[Category_ID__c]

    INNER JOIN angie.dbo.CountryCode c WITH ( NOLOCK ) ON c.CountryCodeId = sp.CountryCodeId

    INNER JOIN angie.dbo.Departments D WITH ( NOLOCK ) ON D.Departmentid = spe.departmentid

    LEFT JOIN(

    SELECT lfcl.[RANK], lfcl.[SPID], u.[Id]

    FROM [angie].[dbo].[LeadFairyCallList] AS lfcl WITH (NOLOCK)

    INNER JOIN [angie].[dbo].[Employees] AS e2 WITH (NOLOCK) ON lfcl.[EmployeeId] = e2.[lngUniqueID]

    AND e2.[DepartmentID] = 22

    INNER JOIN [dbo].[User] AS u WITH (NOLOCK) ON e2.[lngUniqueID] = u.[Employee_ID__c]

    ) BigDeal ON [spe].[SPID] = [BigDeal].[SPID]

    LEFT JOIN (SELECT tsr.[SPID], MIN(tsr.[UADate]) AS RegisteredDate

    FROM angie.[dbo].[tblSPMemberships] AS tsm with (nolock)

    INNER JOIN angie.dbo.[tblSPRelationships] AS tsr with (nolock) ON [tsm].[SPMembershipID] = [tsr].[SPMembershipID]

    WHERE tsr.[Active] = 1

    AND [tsr].[UADate] IS NOT NULL

    AND tsr.[UserAgreement] = 1

    AND tsm.[Status] = 'active'

    GROUP BY tsr.[SPID]

    ) AS Regs ON [sp].[SPID] = [Regs].[SPID]

    INNER JOIN angie.dbo.[SPTotal] AS st with (nolock) ON [sp].[SPID] = [st].[SPID]

    LEFT JOIN (SELECT [List].[SpId], COUNT(DISTINCT r2.[ReportId]) AS Reports, SUM(r2.[Cost]) AS ReportedRevenue,

    GPA = (ISNULL(SUM(CASE WHEN r2.[WorkNotDone] = 1 THEN g.[MemberResponse]*0.2 END),0) + ISNULL(SUM(CASE WHEN r2.worknotdone = 0 THEN [g].memberresponse END),0)) / COUNT(DISTINCT r2.[ReportId])

    FROM angie.dbo.[Report] AS r2 with (nolock)

    LEFT JOIN (SELECT rgr.[ReportId], rgr.[MemberResponse]

    FROM angie.dbo.[ReportGradableResponse] AS rgr with (nolock)

    WHERE rgr.[ReportGradableQuestionId] = 1

    ) AS G ON [r2].[ReportId] = [G].[ReportId]

    INNER JOIN (SELECT rsc.[SpId], rsc.[ReportId]

    FROM angie.dbo.[ReportSPCategory] AS rsc with (nolock)

    GROUP BY rsc.[SpId], rsc.[ReportId]

    ) AS List ON [r2].[ReportId] = [List].[ReportId]

    WHERE r2.[ReportDate] >= GETDATE() - 365

    AND r2.[GradeExcludeTypeId] = 1

    AND r2.[ReportStatusId] = 1 -- Active

    GROUP BY [List].[SpId]

    ) AS PastYearSPReports ON [sp].[SPID] = [PastYearSPReports].[SpId]

    LEFT JOIN (SELECT [List].[SpId], COUNT(DISTINCT r2.[ReportId]) AS Reports, SUM(r2.[Cost]) AS ReportedRevenue,

    GPA = (ISNULL(SUM(CASE WHEN r2.[WorkNotDone] = 1 THEN g.[MemberResponse]*0.2 END),0) + ISNULL(SUM(CASE WHEN r2.worknotdone = 0 THEN [g].memberresponse END),0)) / COUNT(DISTINCT r2.[ReportId])

    FROM angie.dbo.[Report] AS r2 with (nolock)

    LEFT JOIN (SELECT rgr.[ReportId], rgr.[MemberResponse]

    FROM angie.dbo.[ReportGradableResponse] AS rgr with (nolock)

    WHERE rgr.[ReportGradableQuestionId] = 1

    ) AS G ON [r2].[ReportId] = [G].[ReportId]

    INNER JOIN (SELECT DISTINCT rsc.[SpId], rsc.[ReportId]

    FROM angie.dbo.[ReportSPCategory] AS rsc with (nolock)

    ) AS List ON [r2].[ReportId] = [List].[ReportId]

    WHERE r2.[ReportDate] >= GETDATE() - 1095

    AND r2.[GradeExcludeTypeId] = 1

    AND r2.[ReportStatusId] = 1 -- Active

    GROUP BY [List].[SpId]

    ) AS CurrentYearSPReports ON [sp].[SPID] = CurrentYearSPReports.[SpId]

    LEFT JOIN (SELECT KA.[SpId]

    FROM angie.dbo.KnownAssociate AS KA WITH ( NOLOCK )

    GROUP BY ka.[SpId]

    ) KA ON SP.SPID = KA.SpId

    INNER JOIN angie.dbo.[ServiceProviderSummary] AS sps with (nolock) ON [sp].[SPID] = [sps].[SPID]

    WHERE spe.[DepartmentID] IN (10,13) -- Ad Sales/Account Management

    AND spe.DepartmentID <> 22

    AND NULLIF(sp.[CompanyName],'') IS NOT NULL

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Insert'

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Deleting Matches'

    -- Remove All Items That Match (Exclude Rank), Rank Is Handled In Different Updates

    DELETE FROM [dbo].[Account_UpsertWithOwnerID]

    FROM [dbo].[Account_UpsertWithOwnerID] AS au

    INNER JOIN [dbo].[Account] AS a ON [au].[SPID__c] = [a].[SPID__c]

    AND ISNULL([au].[BillingCity],'') = ISNULL([a].[BillingCity],'')

    AND ISNULL([au].[BillingCountry],'') = ISNULL([a].[BillingCountry],'')

    AND ISNULL([au].[BillingPostalCode],'') = ISNULL([a].[BillingPostalCode],'')

    AND ISNULL([au].[BillingState],'') = ISNULL([a].[BillingState],'')

    AND ISNULL([au].[BillingStreet],'') = ISNULL([a].[BillingStreet],'')

    AND ISNULL([au].[Category__c],'') = ISNULL([a].[Category__c],'')

    --AND ISNULL(CAST([au].[Description] AS NVARCHAR(MAX)),'') = ISNULL(CAST([a].[Description] AS NVARCHAR(MAX)),'')

    AND ISNULL([au].[Fax],'') = ISNULL([a].[Fax],'')

    AND ISNULL([au].[Industry],'') = ISNULL([a].[Industry],'')

    AND ISNULL([au].[Market__c],'') = ISNULL([a].[Market__c],'')

    AND ISNULL([au].[Name],'') = ISNULL([a].[Name],'')

    AND ISNULL([au].[Phone],'') = ISNULL([a].[Phone],'')

    AND ISNULL([au].[Type],'') = ISNULL([a].[Type],'')

    AND ISNULL([au].[Website],'') = ISNULL([a].[Website],'')

    AND ISNULL([au].[Advertiser_Type__c],'') = ISNULL([a].[Advertiser_Type__c],'')

    AND ISNULL(au.[Big_Deal_Rep__c],'') = ISNULL(a.[Big_Deal_Rep__c],'')

    AND ISNULL(au.OwnerId,'') = ISNULL(a.OwnerId,'')

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Deleting Matches'

    -- Upsert Current Account Information

    IF (SELECT ISNULL(COUNT(*),0) FROM Account_UpsertWithOwnerID WITH (NOLOCK)) > 0

    BEGIN

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Calling Bulk API Upsert'

    EXECUTE [dbo].[SF_BulkOps]

    @operation = 'Upsert:bulkapi' , -- nvarchar(50)

    @table_server = @SalesForceInstance , -- sysname

    @table_name = @CurrentTableInProcess , -- sysname

    @ext_id = 'SPID__c' -- nvarchar(255)

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Calling Bulk API Upsert'

    END

    SET @CurrentTableInProcess = 'Account_UpdateInEligibles'

    SET @CurrentTableInProcessDescription = 'Update Previous Service Providers As Ineligible'

    -- Insert ProcessStep For Batch

    INSERT INTO [dbo].[SalesForceBatchDetailInfo]

    ( [SalesForceBatchID] ,

    [ProcessDescription] ,

    [TableName]

    )

    VALUES (@BatchID, @CurrentTableInProcessDescription, @CurrentTableInProcess )

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Insert'

    -- Update Previous Service Providers Whom Where Eligible/Advertiser But No Longer Are

    INSERT INTO [dbo].[Account_UpdateInEligibles]

    ( [Id] ,

    [Type]

    )

    SELECT a.[Id] ,

    'Ineligible'

    FROM [dbo].[Account] AS a WITH ( NOLOCK )

    LEFT JOIN

    (

    SELECT lfcl.SPID

    FROM SQLSRV8.[angie].[dbo].[LeadFairyCallList] AS lfcl WITH (NOLOCK)

    INNER JOIN [SQLSRV8].[angie].[dbo].[ServiceProvider] AS sp WITH (NOLOCK) ON [lfcl].[SPID] = [sp].[SPID]

    INNER JOIN [SQLSRV8].[angie].[dbo].[SPStatus] AS ss WITH (NOLOCK) ON [sp].[SPStatusId] = [ss].[SPStatusId]

    INNER JOIN [SQLSRV8].[angie].[dbo].[Employees] AS e WITH (NOLOCK) ON lfcl.[EmployeeId] = e.[lngUniqueID]

    WHERE e.[DepartmentID] IN (10,13)-- Ad Sales/Account Management

    AND ss.[SPStatus] <> 'Deleted'

    GROUP BY lfcl.SPID

    ) ValidSPID ON a.[SPID__c] = ValidSPID.[SPID]

    WHERE a.[Type] IN ( 'Advertiser', 'Eligible' )

    AND ValidSPID.[SPID] IS NULL

    UNION

    SELECT a.[Id] ,

    'Deleted'

    FROM [dbo].[Account] AS a WITH ( NOLOCK )

    INNER JOIN SQLSRV8.[angie].[dbo].[ServiceProvider] AS sp WITH (NOLOCK) ON a.[SPID__c] = sp.[SPID]

    INNER JOIN SQLSRV8.[angie].[dbo].[SPStatus] AS ss WITH (NOLOCK) ON [sp].[SPStatusId] = [ss].[SPStatusId]

    WHERE ISNULL(a.[Type],'') <> 'Deleted'

    AND ss.[SPStatus] = 'Deleted'

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Insert'

    -- Update Current Service Providers To InEligible

    IF (SELECT ISNULL(COUNT(*),0) FROM Account_UpdateInEligibles WITH (NOLOCK)) > 0

    BEGIN

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Calling Update Bulk API'

    EXECUTE [dbo].[SF_BulkOps]

    @operation = 'Update:bulkapi' , -- nvarchar(50)

    @table_server = @SalesForceInstance , -- sysname

    @table_name = @CurrentTableInProcess -- sysname

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Calling Update Bulk API'

    END

    SET @CurrentTableInProcess = 'Account_Upsert'

    SET @CurrentTableInProcessDescription = 'Update/Insert Any Modified Fields For Service Providers'

    -- Try and Get Status For Our Bulk Operations

    IF (SELECT ISNULL(COUNT(*),0) FROM Account_Upsert WITH (NOLOCK)) > 0

    BEGIN

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API'

    EXECUTE [dbo].[SF_BulkOps]

    @operation = 'Status' , -- nvarchar(50)

    @table_server = @SalesForceInstance , -- sysname

    @table_name = @CurrentTableInProcess -- sysname

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API'

    END

    SET @SubmittedTableCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_Upsert] WITH (NOLOCK) WHERE [Error] LIKE '%Submitted%' OR [Error] LIKE '%InProgress%')

    SET @RowTableCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_Upsert] WITH (NOLOCK))

    WHILE (@SubmittedTableCount > 0 AND @RowTableCount > 0)

    BEGIN

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Waiting' , -- varchar(100)

    @StepDescription = 'Waiting On Status'

    WAITFOR DELAY '00:01:00'

    SET @SubmittedTableCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_Upsert] WITH (NOLOCK) WHERE [Error] LIKE '%Submitted%' OR [Error] LIKE '%InProgress%')

    SELECT CONVERT(VARCHAR,@CurrentTableInProcess) + ':Number Of Records Still In Submitted Status: ' + CONVERT(VARCHAR,@SubmittedTableCount)

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API From Waiting'

    -- Try and Get Status For Our Bulk Operations

    IF (SELECT ISNULL(COUNT(*),0) FROM Account_Upsert WITH (NOLOCK)) > 0

    BEGIN

    EXECUTE [dbo].[SF_BulkOps]

    @operation = 'Status' , -- nvarchar(50)

    @table_server = @SalesForceInstance , -- sysname

    @table_name = @CurrentTableInProcess -- sysname

    END

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API From Waiting'

    END

    -- Update Our Stats

    SET @TableErrorCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_Upsert] WHERE [Error] LIKE '%Error%')

    SET @TableSuccessCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_Upsert] WHERE [Error] LIKE '%Operation Successful%')

    SET @TableOtherCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_Upsert] WHERE [Error] NOT LIKE '%Operation Successful%' AND [Error] NOT LIKE '%Error%')

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Processing Errors And Successes'

    INSERT INTO [dbo].[Account_UpsertErrors]

    ( [Id] ,

    [Error] ,

    [BillingCity] ,

    [BillingCountry] ,

    [BillingPostalCode] ,

    [BillingState] ,

    [BillingStreet] ,

    [Category__c] ,

    [Description] ,

    [Fax] ,

    [Industry] ,

    [Market__c] ,

    [Name] ,

    [Phone] ,

    [Rank__c] ,

    [SPID__c] ,

    [Type] ,

    [Website] ,

    [InsertedDate] ,

    [SalesForceBatchInfoID],

    [Advertiser_Type__c],

    [Big_Deal_Rank__c],

    [Big_Deal_Rep__c]

    )

    SELECT [Id] ,

    [Error] ,

    [BillingCity] ,

    [BillingCountry] ,

    [BillingPostalCode] ,

    [BillingState] ,

    [BillingStreet] ,

    [Category__c] ,

    [Description] ,

    [Fax] ,

    [Industry] ,

    [Market__c] ,

    [Name] ,

    [Phone] ,

    [Rank__c] ,

    [SPID__c] ,

    [Type] ,

    [Website] ,

    GETDATE(),

    @BatchID,

    [Advertiser_Type__c],

    [Big_Deal_Rank__c],

    [Big_Deal_Rep__c]

    FROM [dbo].[Account_Upsert] AS aur

    WHERE [Error] LIKE '%Error%'

    DELETE FROM [dbo].[Account_Upsert]

    WHERE [Error] LIKE '%Error%'

    DELETE FROM [dbo].[Account_Upsert]

    WHERE [Error] LIKE '%Operation Successful%'

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Processing Errors And Successes'

    -- Update Our Stats For This Run - Detail Level

    UPDATE [dbo].[SalesForceBatchDetailInfo]

    SET [NumberOfErrors] = @TableErrorCount,

    [NumberOfSuccesses] = @TableSuccessCount,

    [NumberOfOther] = @TableOtherCount,

    [BatchDetailCompleted] = 1,

    [SalesForceBatchDetailInfoEnd] = GETDATE()

    WHERE [SalesForceBatchID] = @BatchID

    AND [TableName] = @CurrentTableInProcess

    SET @CurrentTableInProcess = 'Account_UpsertWithOwnerID'

    SET @CurrentTableInProcessDescription = 'Update/Insert Any Modified Fields For Service Providers Including The Assigned AL Rep'

    -- Try and Get Status For Our Bulk Operations

    IF (SELECT ISNULL(COUNT(*),0) FROM Account_UpsertWithOwnerID WITH (NOLOCK)) > 0

    BEGIN

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API'

    EXECUTE [dbo].[SF_BulkOps]

    @operation = 'Status' , -- nvarchar(50)

    @table_server = @SalesForceInstance , -- sysname

    @table_name = @CurrentTableInProcess -- sysname

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API'

    END

    SET @SubmittedTableCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpsertWithOwnerID] WITH (NOLOCK) WHERE [Error] LIKE '%Submitted%' OR [Error] LIKE '%InProgress%')

    SET @RowTableCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpsertWithOwnerID] WITH (NOLOCK))

    WHILE (@SubmittedTableCount > 0 AND @RowTableCount > 0)

    BEGIN

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Waiting' , -- varchar(100)

    @StepDescription = 'Waiting On Status'

    WAITFOR DELAY '00:01:00'

    SET @SubmittedTableCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpsertWithOwnerID] WITH (NOLOCK) WHERE [Error] LIKE '%Submitted%' OR [Error] LIKE '%InProgress%')

    SELECT CONVERT(VARCHAR,@CurrentTableInProcess) + ':Number Of Records Still In Submitted Status: ' + CONVERT(VARCHAR,@SubmittedTableCount)

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API From Waiting'

    -- Try and Get Status For Our Bulk Operations

    IF (SELECT ISNULL(COUNT(*),0) FROM Account_UpsertWithOwnerID WITH (NOLOCK)) > 0

    BEGIN

    EXECUTE [dbo].[SF_BulkOps]

    @operation = 'Status' , -- nvarchar(50)

    @table_server = @SalesForceInstance , -- sysname

    @table_name = @CurrentTableInProcess -- sysname

    END

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API From Waiting'

    END

    -- Update Our Stats

    SET @TableErrorCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpsertWithOwnerID] WHERE [Error] LIKE '%Error%')

    SET @TableSuccessCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpsertWithOwnerID] WHERE [Error] LIKE '%Operation Successful%')

    SET @TableOtherCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpsertWithOwnerID] WHERE [Error] NOT LIKE '%Operation Successful%' AND [Error] NOT LIKE '%Error%')

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Processing Errors And Successes'

    INSERT INTO [dbo].[Account_UpsertWithOwnerIDErrors]

    ( [Id] ,

    [Error] ,

    [BillingCity] ,

    [BillingCountry] ,

    [BillingPostalCode] ,

    [BillingState] ,

    [BillingStreet] ,

    [Category__c] ,

    [Description] ,

    [Fax] ,

    [Industry] ,

    [Market__c] ,

    [Name] ,

    [Phone] ,

    [Rank__c] ,

    [SPID__c] ,

    [Type] ,

    [Website] ,

    [InsertedDate] ,

    [SalesForceBatchInfoID],

    [Advertiser_Type__c],

    [Big_Deal_Rank__c],

    [Big_Deal_Rep__c],

    [Ownerid],

    [Registered__c],

    [Registered_Date__c],

    [SP_Total_Grade__c],

    [SP_Total_GPA__c],

    [SP_Total_Reports__c],

    [SP_Current_Reports__c],

    --[SP_Current_Grade__c],

    [SP_Current_GPA__c],

    [SP_Past_30_Days_Unique_Reviews__c],

    [SP_Past_90_Days_Unique_Reviews__c],

    [SP_Past_365_Days_Unique_Review__c],

    [SP_Total_Unique_Reviews__c],

    [SP_Associated_to_AL_Membership__c],

    [X12_Month_SP_Report__c],

    [X12_Month_SP_Reported_Revenue__c],

    [X12_Month_SP_GPA__c]

    --[X12_Month_SP_Grade__c]

    )

    SELECT [Id] ,

    [Error] ,

    [BillingCity] ,

    [BillingCountry] ,

    [BillingPostalCode] ,

    [BillingState] ,

    [BillingStreet] ,

    [Category__c] ,

    [Description] ,

    [Fax] ,

    [Industry] ,

    [Market__c] ,

    [Name] ,

    [Phone] ,

    [Rank__c] ,

    [SPID__c] ,

    [Type] ,

    [Website] ,

    GETDATE(),

    @BatchID,

    [Advertiser_Type__c],

    [Big_Deal_Rank__c],

    [Big_Deal_Rep__c],

    [Ownerid],

    [Registered__c],

    [Registered_Date__c],

    [SP_Total_Grade__c],

    [SP_Total_GPA__c],

    [SP_Total_Reports__c],

    [SP_Current_Reports__c],

    --[SP_Current_Grade__c],

    [SP_Current_GPA__c],

    [SP_Past_30_Days_Unique_Reviews__c],

    [SP_Past_90_Days_Unique_Reviews__c],

    [SP_Past_365_Days_Unique_Review__c],

    [SP_Total_Unique_Reviews__c],

    [SP_Associated_to_AL_Membership__c],

    [X12_Month_SP_Report__c],

    [X12_Month_SP_Reported_Revenue__c],

    [X12_Month_SP_GPA__c]

    --[X12_Month_SP_Grade__c]

    FROM [dbo].[Account_UpsertWithOwnerID] AS aur

    WHERE [Error] LIKE '%Error%'

    DELETE FROM [dbo].[Account_UpsertWithOwnerID]

    WHERE [Error] LIKE '%Error%'

    DELETE FROM [dbo].[Account_UpsertWithOwnerID]

    WHERE [Error] LIKE '%Operation Successful%'

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Processing Errors And Successes'

    -- Update Our Stats For This Run - Detail Level

    UPDATE [dbo].[SalesForceBatchDetailInfo]

    SET [NumberOfErrors] = @TableErrorCount,

    [NumberOfSuccesses] = @TableSuccessCount,

    [NumberOfOther] = @TableOtherCount,

    [BatchDetailCompleted] = 1,

    [SalesForceBatchDetailInfoEnd] = GETDATE()

    WHERE [SalesForceBatchID] = @BatchID

    AND [TableName] = @CurrentTableInProcess

    SET @CurrentTableInProcess = 'Account_UpdateInEligibles'

    SET @CurrentTableInProcessDescription = 'Update Previous Service Providers As Ineligible'

    -- Try and Get Status For Our Bulk Operations

    IF (SELECT ISNULL(COUNT(*),0) FROM Account_UpdateInEligibles WITH (NOLOCK)) > 0

    BEGIN

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API'

    EXECUTE [dbo].[SF_BulkOps]

    @operation = 'Status' , -- nvarchar(50)

    @table_server = @SalesForceInstance , -- sysname

    @table_name = @CurrentTableInProcess -- sysname

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API'

    END

    SET @SubmittedTableCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpdateInEligibles] WITH (NOLOCK) WHERE [Error] LIKE '%Submitted%' OR [Error] LIKE '%InProgress%')

    SET @RowTableCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpdateInEligibles] WITH (NOLOCK))

    WHILE (@SubmittedTableCount > 0 AND @RowTableCount > 0)

    BEGIN

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Waiting' , -- varchar(100)

    @StepDescription = 'Waiting Status Bulk API'

    WAITFOR DELAY '00:01:00'

    SET @SubmittedTableCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpdateInEligibles] WITH (NOLOCK) WHERE [Error] LIKE '%Submitted%' OR [Error] LIKE '%InProgress%')

    SELECT CONVERT(VARCHAR,@CurrentTableInProcess) + ':Number Of Records Still In Submitted Status: ' + CONVERT(VARCHAR,@SubmittedTableCount)

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API From Waiting'

    -- Try and Get Status For Our Bulk Operations

    IF (SELECT ISNULL(COUNT(*),0) FROM Account_UpdateInEligibles WITH (NOLOCK)) > 0

    BEGIN

    EXECUTE [dbo].[SF_BulkOps]

    @operation = 'Status' , -- nvarchar(50)

    @table_server = @SalesForceInstance , -- sysname

    @table_name = @CurrentTableInProcess -- sysname

    END

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API From Waiting'

    END

    -- Update Our Stats

    SET @TableErrorCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpdateInEligibles] WHERE [Error] LIKE '%Error%')

    SET @TableSuccessCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpdateInEligibles] WHERE [Error] LIKE '%Operation Successful%')

    SET @TableOtherCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpdateInEligibles] WHERE [Error] NOT LIKE '%Operation Successful%' AND [Error] NOT LIKE '%Error%')

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Processing Errors And Successes'

    INSERT INTO [dbo].[Account_UpdateInEligiblesErrors]

    ( [Id] ,

    [Error] ,

    [Type] ,

    [InsertedDate] ,

    [SalesForceBatchInfoID]

    )

    SELECT [Id] ,

    [Error] ,

    [Type],

    GETDATE(),

    @BatchID

    FROM [dbo].[Account_UpdateInEligibles] AS auie WITH (NOLOCK)

    WHERE [Error] LIKE '%Error%'

    DELETE FROM [dbo].[Account_UpdateInEligibles]

    WHERE [Error] LIKE '%Error%'

    DELETE FROM [dbo].[Account_UpdateInEligibles]

    WHERE [Error] LIKE '%Operation Successful%'

    -- Update Our Stats For This Run - Detail Level

    UPDATE [dbo].[SalesForceBatchDetailInfo]

    SET [NumberOfErrors] = @TableErrorCount,

    [NumberOfSuccesses] = @TableSuccessCount,

    [NumberOfOther] = @TableOtherCount,

    [BatchDetailCompleted] = 1,

    [SalesForceBatchDetailInfoEnd] = GETDATE()

    WHERE [SalesForceBatchID] = @BatchID

    AND [TableName] = @CurrentTableInProcess

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Processing Errors And Successes'

    SET @CurrentTableInProcess = 'Account_UpdateRank'

    SET @CurrentTableInProcessDescription = 'Update Ranking For Service Providers In Account'

    -- Try and Get Status For Our Bulk Operations

    IF (SELECT ISNULL(COUNT(*),0) FROM Account_UpdateRank WITH (NOLOCK)) > 0

    BEGIN

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API'

    EXECUTE [dbo].[SF_BulkOps]

    @operation = 'Status' , -- nvarchar(50)

    @table_server = @SalesForceInstance , -- sysname

    @table_name = @CurrentTableInProcess -- sysname

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API'

    END

    SET @SubmittedTableCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpdateRank] WITH (NOLOCK) WHERE [Error] LIKE '%Submitted%' OR [Error] LIKE '%InProgress%')

    SET @RowTableCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpdateRank] WITH (NOLOCK))

    WHILE (@SubmittedTableCount > 0 AND @RowTableCount > 0)

    BEGIN

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Waiting' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API'

    WAITFOR DELAY '00:01:00'

    SET @SubmittedTableCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpdateRank] WITH (NOLOCK) WHERE [Error] LIKE '%Submitted%' OR [Error] LIKE '%InProgress%')

    SELECT CONVERT(VARCHAR,@CurrentTableInProcess) + ': Number Of Records Still In Submitted Status: ' + CONVERT(VARCHAR,@SubmittedTableCount)

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API From Waiting'

    -- Try and Get Status For Our Bulk Operations

    IF (SELECT ISNULL(COUNT(*),0) FROM Account_UpdateRank WITH (NOLOCK)) > 0

    BEGIN

    EXECUTE [dbo].[SF_BulkOps]

    @operation = 'Status' , -- nvarchar(50)

    @table_server = @SalesForceInstance , -- sysname

    @table_name = @CurrentTableInProcess -- sysname

    END

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Calling Status Bulk API From Waiting'

    END

    -- Update Our Stats

    SET @TableErrorCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpdateRank] WHERE [Error] LIKE '%Error%')

    SET @TableSuccessCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpdateRank] WHERE [Error] LIKE '%Operation Successful%')

    SET @TableOtherCount = (SELECT ISNULL(COUNT(*),0) FROM [dbo].[Account_UpdateRank] WHERE [Error] NOT LIKE '%Operation Successful%' AND [Error] NOT LIKE '%Error%')

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Start' , -- varchar(100)

    @StepDescription = 'Processing Errors And Successes'

    INSERT INTO [dbo].[Account_UpdateRankErrors]

    ( [Id] ,

    [Error] ,

    [Rank__c] ,

    [Big_Deal_Rank__c],

    [InsertedDate] ,

    [SalesForceBatchInfoID]

    )

    SELECT [Id] ,

    [Error] ,

    [Rank__c],

    [Big_Deal_Rank__c],

    GETDATE(),

    @BatchID

    FROM [dbo].[Account_UpdateRank] AS aur WITH (NOLOCK)

    WHERE [Error] LIKE '%Error%'

    DELETE FROM [dbo].[Account_UpdateRank]

    WHERE [Error] LIKE '%Error%'

    DELETE FROM [dbo].[Account_UpdateRank]

    WHERE [Error] LIKE '%Operation Successful%'

    -- Update Our Stats For This Run - Detail Level

    UPDATE [dbo].[SalesForceBatchDetailInfo]

    SET [NumberOfErrors] = @TableErrorCount,

    [NumberOfSuccesses] = @TableSuccessCount,

    [NumberOfOther] = @TableOtherCount,

    [BatchDetailCompleted] = 1,

    [SalesForceBatchDetailInfoEnd] = GETDATE()

    WHERE [SalesForceBatchID] = @BatchID

    AND [TableName] = @CurrentTableInProcess

    -- Logging Step

    EXECUTE [dbo].[proc_Insert_SalesForceBatchDetailStepInfo]

    @SalesForceBatchInfoID = @BatchID , -- uniqueidentifier

    @TableName = @CurrentTableInProcess , -- varchar(100)

    @StepName = @CurrentTableInProcessDescription , -- varchar(100)

    @StepAction = 'Finish' , -- varchar(100)

    @StepDescription = 'Processing Errors And Successes'

    END TRY

    BEGIN CATCH

    SET @BatchErrorCount = (SELECT ISNULL(SUM([NumberOfErrors]),0) FROM [dbo].[SalesForceBatchDetailInfo] WITH (NOLOCK) WHERE [SalesForceBatchID] = @BatchID AND [BatchDetailCompleted] = 1)

    SET @BatchSuccessCount = (SELECT ISNULL(SUM([NumberOfSuccesses]),0) FROM [dbo].[SalesForceBatchDetailInfo] WITH (NOLOCK) WHERE [SalesForceBatchID] = @BatchID AND [BatchDetailCompleted] = 1)

    SET @BatchOtherCount = (SELECT ISNULL(SUM([NumberOfOther]),0) FROM [dbo].[SalesForceBatchDetailInfo] WITH (NOLOCK) WHERE [SalesForceBatchID] = @BatchID AND [BatchDetailCompleted] = 1)

    SET @BatchNumberOfTablesProcessed = (SELECT ISNULL(COUNT(DISTINCT TableName),0) FROM [dbo].[SalesForceBatchDetailInfo] WITH (NOLOCK) WHERE [SalesForceBatchID] = @BatchID AND [BatchDetailCompleted] = 1)

    UPDATE [dbo].[SalesForceBatchInfo] WITH (ROWLOCK)

    SET [SalesForceBatchEnd] = GETDATE(),

    [NumberOfErrors] = @BatchErrorCount,

    [NumberOfSuccesses] = @BatchSuccessCount,

    [NumberOfOther] = @BatchOtherCount,

    [NumberOfTablesProcessed] = @BatchNumberOfTablesProcessed,

    [BatchErrored] = 1,

    [BatchCompleted] = 1,

    [BatchErrorDescription] = ERROR_MESSAGE()

    WHERE SalesForceBatchID = @BatchID

    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()

    DECLARE @ErrorSeverity INT =ERROR_SEVERITY()

    DECLARE @ErrorState INT = ERROR_STATE()

    RAISERROR (@ErrorMessage, -- Message text.

    @ErrorSeverity, -- Severity.

    @ErrorState -- State.

    );

    END CATCH

  • This was removed by the editor as SPAM

  • Something some where is trying to put a value like ABC into a column which will only take a value like 123.

    With such a large procedure, take it a chunk at a time and run it until you find the faulting part then fix the problem with the data.

  • rachelbr 87580 (9/2/2012)


    Hi,

    I have this stored proc that I run nightly. I keep on having issues with it failing because it's having issues converting a nvarchar. This is the exact error I'm getting:

    Error converting data type nvarchar to numeric

    ...

    On line 1553 of theat stored procedure you have a BEGIN CATCH statement. The first statement(s) within the block should capture the error information, something like this:

    BEGIN CATCH

    -- Capture error variables before running any other statements

    SELECT @EventText =

    'The procedure [' + ERROR_PROCEDURE() + '] in [' + OBJECT_NAME(@@PROCID) + '] '

    + 'failed with error message "' + ERROR_MESSAGE() + '" '

    + 'at line ' + CAST(ERROR_LINE() AS VARCHAR(10)) + '. '

    + 'Errornumber = ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ', '

    + 'Errorseverity = ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) + ', '

    + 'Errorstate = ' + CAST(ERROR_STATE() AS VARCHAR(10));

    - which can help a lot with debugging.

    Next thing if you still can't identify the statement causing the problem, is to open the sproc for editing in a SSMS window, comment out the CREATE PROC statement, any RETURNs and the error-trapping statements, and run it.

    Post the offending statement here - the rest should be easy.

    Edit - having said all that, you should be able to work out which statement it is from the logging table. Can you tell us?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • The procedure does several Inserts/Updates, some of which are executions of other stored procedures. If you can identify which table and column is causing the error then check the datatype of the target column. It could be that the procedure is trying to insert/update into a numeric datatype column with an nvarchar. (If you don't know exactly which column is causing the error, you will just have to laboriously compare all the parameters to their eventual destination columns where applicable.)

    A common example of this might be where a procedure has a parameter value such as 'Y' or 'N' (a non-numeric value) and it's trying to insert it into a column that has a BIT or INT datatype. Whether this is good db design or not is another topic altogether, but a situation like the example would require some sort of IF or CASE statement to catch the parameter of one type and CAST/CONVERT it to the proper type.

     

  • Change the CATCH to show the line number of the error to help you at least narrow it down to a section of the code. You might also check all the logging the proc is doing. If it starts up a certain section according to the log and nothing follows that, you've probably just found the bad section.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply