Error converting data type nvarchar to numeric

Question

Error converting data type nvarchar to numeric

rachelbr 87580

SSC Veteran

Points: 224
More actions
September 2, 2012 at 1:30 pm

#266148

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
September 3, 2012 at 1:33 am

This was removed by the editor as SPAM

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

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

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)