Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error converting data type nvarchar to numeric Expand / Collapse
Author
Message
Posted Sunday, September 2, 2012 1:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 13, 2012 6:50 AM
Points: 2, Visits: 10
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

Post #1353272
Posted Monday, September 3, 2012 1:33 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 3,902, Visits: 5,074
Have you tried using the ISNUMERIC() function on the column you need to convert to a numeric data type?
While the ISNUMERIC() is not a "golden bullet", it may assist in identifying the relevant "faulty" values.

Remember, this error could be either from the INSERT / UPDATE itself, or from the relevant columns in your predicate(s) (either in the WHERE / HAVING / JOIN segments of your queries).


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1353371
Posted Monday, September 3, 2012 1:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:55 AM
Points: 5,216, Visits: 5,064
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1353372
Posted Monday, September 3, 2012 2:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,175, Visits: 13,621
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1353386
Posted Monday, September 3, 2012 1:06 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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.

 
Post #1353621
Posted Monday, September 3, 2012 7:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 36,941, Visits: 31,443
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1353666
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse