I'm at an absolute loss and need help please
I can run this query no problem in SQL Management Studio.
It returns exactly what I want. The BatchID from the import.
However the moment i start running this in python it returns an error
"[ODBC Driver 17 for SQL Server][SQL Server]Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements."
or
[Execution failed on sql 'EXEC [CHAMP_DW].[dbo].[ETL_stage_evestment_performance_to_champ]': ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '#stagingPerformance'. (208) (SQLExecDirectW)")"
Does anybody see anything wrong in this SQL
Example Works in bash
sqlcmd -S localhost -U SA -P 'Password' -Q "EXEC [CHAMP_DW].[dbo].[ETL_stage_evestment_performance_to_champ];"
Fails message and only 1 row exectuted
my_conn.execute("EXEC [CHAMP_DW].[dbo].[ETL_stage_evestment_performance_to_champ]")
Its a pretty simple TRY CATCH with some Validation
USE [CHAMP_DW]
GO
/****** Object: StoredProcedure [dbo].[ETL_stage_evestment_performance_to_champ] Script Date: 21/02/2019 4:48:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ETL_stage_evestment_performance_to_champ]
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON; --> the only change
/*Variable Declaration*/
DECLARE @BatchImportID UNIQUEIDENTIFIER
DECLARE
@FileProcessedDate DATETIME,
@FileProcessedUTCDate DATETIME,
@ImportStatus TINYINT,
@ErrorCode VARCHAR(2000),
@ErrorMessage VARCHAR(4000),
@StagingID int,
@value_date varchar(255),
@firm_id varchar(255),
@product_id varchar(255),
@vehicle_id varchar(255),
@ccy varchar(255),
@reporting_method varchar(255),
@performance_return varchar(255),
@log_return varchar(255)
/*Assign static value for whole one time process*/
SELECT
@BatchImportID = NEWID(),
@FileProcessedDate = GETDATE(),
@FileProcessedUTCDate = GETUTCDATE()
/*Create staging table so we can re-run procedure instead of having to rerun complete task again and again*/
CREATE TABLE #stagingPerformance
(
[index] [bigint] NULL,
[value_date] [datetime] NULL,
[firm_id] [bigint] NULL,
[ccy] [varchar](max) NULL,
[product_id] [bigint] NULL,
[reporting_method] [varchar](max) NULL,
[vehicle_id] [bigint] NULL,
[performance_return] [float] NULL,
[log_return] [float] NULL
)
CREATE INDEX [IX_EvestmentData_1] ON #stagingPerformance
(
[index] ASC,
[value_date] ASC,
[firm_id] ASC
)
-- POPULATE TEMP TABLE
INSERT INTO #stagingPerformance
SELECT TOP 50 a.[index], a.value_date, a.firm_id, a.ccy,
a.product_id, a.reporting_method, a.vehicle_id, a.performance_return, a.log_return
FROM CHAMP_DW.dbo.champ_dw_staging_evestment_performance a
--WHERE a.reporting_method<>'Index'
/*While loop started to get process record one by one*/
WHILE EXISTS(SELECT 1 FROM #stagingPerformance AS FHD WITH (NOLOCK))
BEGIN
BEGIN TRY
BEGIN TRANSACTION
/*To assign 1 record values to respective variables*/
SELECT TOP 1
@StagingID = FHD.[index],
@firm_id = NULLIF(FHD.[firm_id],''),
@product_id = NULLIF(FHD.[product_id],''),
@vehicle_id = NULLIF(FHD.[vehicle_id],''),
@value_date = NULLIF(FHD.[value_date],''),
@reporting_method = NULLIF(FHD.[reporting_method],''),
@ccy = NULLIF(FHD.[ccy],''),
@performance_return = NULLIF(FHD.[performance_return],''),
@log_return = NULLIF(FHD.[log_return],'')
FROM #stagingPerformance AS FHD WITH (NOLOCK)
--WHERE reporting_method <> 'Index'
ORDER BY FHD.[index]
/*Record wise validation start*/
IF @firm_id IS NULL BEGIN
SELECT
@ErrorCode = ISNULL(@ErrorCode,'') + ',' + '101',
@ErrorMessage = ISNULL(@ErrorMessage,'') + ',' + 'Firm ID is required.'
END
IF @product_id IS NULL BEGIN
SELECT
@ErrorCode = ISNULL(@ErrorCode,'') + ',' + '102',
@ErrorMessage = ISNULL(@ErrorMessage,'') + ',' + 'Product ID is required.'
END
IF @value_date IS NULL BEGIN
SELECT
@ErrorCode = ISNULL(@ErrorCode,'') + ',' + '103',
@ErrorMessage = ISNULL(@ErrorMessage,'') + ',' + 'Value Date is required.'
END
IF @reporting_method IS NULL BEGIN
SELECT
@ErrorCode = ISNULL(@ErrorCode,'') + ',' + '104',
@ErrorMessage = ISNULL(@ErrorMessage,'') + ',' + 'Reporting Method is required.'
END
IF @reporting_method IS NOT NULL AND NOT EXISTS(SELECT 1 WHERE @reporting_method in ('Gross', 'Net', 'Index')) BEGIN
SELECT
@ErrorCode = ISNULL(@ErrorCode,'') + ',' + '115',
@ErrorMessage = ISNULL(@ErrorMessage,'') + ',' + 'Reporting Method is invalid'
END
IF @ccy IS NULL BEGIN
SELECT
@ErrorCode = ISNULL(@ErrorCode,'') + ',' + '105',
@ErrorMessage = ISNULL(@ErrorMessage,'') + ',' + 'ccy is required.'
END
IF @value_date IS NOT NULL AND ISDATE(@value_date) = 0 BEGIN
SELECT
@ErrorCode = ISNULL(@ErrorCode,'') + ',' + '106',
@ErrorMessage = ISNULL(@ErrorMessage,'') + ',' + 'Invalid value of Value Date.'
END
IF @performance_return IS NOT NULL AND ISNUMERIC(@performance_return) = 0 BEGIN
SELECT
@ErrorCode = ISNULL(@ErrorCode,'') + CASE WHEN @ErrorCode LIKE '%112%' THEN '' ELSE ',' + '112' END,
@ErrorMessage = ISNULL(@ErrorMessage,'') + ',' + 'Invalid value of performance.'
END
IF @log_return IS NOT NULL AND ISNUMERIC(@log_return) = 0 BEGIN
SELECT
@ErrorCode = ISNULL(@ErrorCode,'') + CASE WHEN @ErrorCode LIKE '%112%' THEN '' ELSE ',' + '112' END,
@ErrorMessage = ISNULL(@ErrorMessage,'') + ',' + 'Invalid value of log_return.'
END
IF @firm_id IS NOT NULL AND NOT EXISTS(SELECT 1 FROM CHAMP_DW.dbo.champ_dw_fund_manager_mapping AS DFM WITH (NOLOCK) WHERE DFM.evestment_fund_manager_id = @firm_id)
BEGIN
SELECT
@ErrorCode = ISNULL(@ErrorCode,'') + ',' + '113',
@ErrorMessage = ISNULL(@ErrorMessage,'') + ',' + 'Firm ID is not Mapped'
END
IF @product_id IS NOT NULL AND NOT EXISTS(SELECT 1 FROM dbo.champ_dw_product_mapping AS DAM WITH (NOLOCK) WHERE DAM.evestment_product_id =@product_id)
BEGIN
SELECT
@ErrorCode = ISNULL(@ErrorCode,'') + ',' + '114',
@ErrorMessage = ISNULL(@ErrorMessage,'') + ',' + 'Product ID is not Mapped'
END
IF @ccy IS NOT NULL AND NOT EXISTS(SELECT 1 FROM [dbo].champ_dw_dim_currency AS DC WITH (NOLOCK) WHERE DC.currency = @ccy) BEGIN
SELECT
@ErrorCode = ISNULL(@ErrorCode,'') + ',' + '112',
@ErrorMessage = ISNULL(@ErrorMessage,'') + ',' + 'ccy is not found in DIM_ccy.'
END
SELECT
@ErrorCode = NULLIF(STUFF(@ErrorCode,1,1,''),''),
@ErrorMessage = NULLIF(STUFF(@ErrorMessage,1,1,''),'')
IF @ErrorMessage IS NOT NULL
BEGIN
;THROW 50552,'VALIDATION RAISE ERROR.',1
END
/*Record wise validation end*/
/*To get unique record value based on FundID, AssetID, AsAtDate columns*/
DECLARE
@performance_id INT = 0,
@vfirm_id INT,
@vproduct_id INT,
@vvehicle_id INT,
@vvalue_date datetime2,
@vreporting_method nvarchar(max),
@vccy nvarchar(3),
@vperformance_return float,
@vlog_return float,
@map_firm_id INT,
@map_product_id INT
SELECT
@vfirm_id = TRY_PARSE(@firm_id AS INT),
@vproduct_id = TRY_PARSE(@product_id AS INT),
@vvehicle_id = TRY_PARSE(@vehicle_id AS INT),
@vvalue_date = TRY_PARSE(@value_date AS datetime2),
@vreporting_method = @reporting_method,
@vccy = cast(@ccy as nvarchar(3)),
@vperformance_return = TRY_PARSE(@performance_return AS float),
@vlog_return = TRY_PARSE(@log_return AS float)
SELECT @map_firm_id = (SELECT fund_manager_mapping_id FROM CHAMP_DW.dbo.champ_dw_fund_manager_mapping fm WHERE fm.evestment_fund_manager_id = @vfirm_id)
SELECT @map_product_id = (SELECT product_mapping_id FROM CHAMP_DW.dbo.champ_dw_product_mapping fp WHERE fp.product_mapping_id=@vvehicle_id)
-- changing to vehicle mapping
--SELECT @map_product_id = (SELECT product_mapping_id FROM CHAMP_DW.dbo.champ_dw_product_mapping fp WHERE fp.evestment_vehicle_id=@vvehicle_id)
SELECT
@performance_id = FFHD.performance_id
FROM dbo.[champ_dw_fact_performance] AS FFHD WITH (NOLOCK)
WHERE FFHD.fund_manager_id = @map_firm_id
AND FFHD.product_id = @map_product_id
AND FFHD.value_date = @vvalue_date
AND FFHD.data_source = 'eVestment'
AND FFHD.ccy_id = @vccy
AND FFHD.reporting_method = @vreporting_method
/*Update record, if any value has different then already exist in DB.*/
UPDATE FFHD
SET
FFHD.ccy_id = @ccy,
FFHD.performance_return = @vperformance_return,
FFHD.log_return = @vlog_return
FROM dbo.[champ_dw_fact_performance] AS FFHD WITH (NOLOCK)
WHERE FFHD.performance_id = @performance_id
AND
(
ISNULL(FFHD.performance_return,'0') <> @vperformance_return
OR
ISNULL(FFHD.log_return,'0') <> @vlog_return
)
/*Add new record if record is not found based on FundID, AssetID, AsAtDate columns*/
INSERT INTO dbo.[champ_dw_fact_performance]
(
value_date,
reporting_method,
valuation_quality,
data_source,
performance_return,
log_return,
ccy_id,
fund_manager_id,
product_id
)
SELECT
@vvalue_date AS value_date,
@vreporting_method AS reporting_method,
'Actual' as valuation_quality,
'eVestment' as data_source,
@vperformance_return AS performance_return,
@vlog_return AS log_return,
@vccy AS ccy_id,
@map_firm_id AS fund_manager_id_id,
@map_product_id AS product_id_id
WHERE NOT EXISTS
(
SELECT
1
FROM dbo.[champ_dw_fact_performance] AS FFHD WITH (NOLOCK)
WHERE FFHD.fund_manager_id = @map_firm_id
AND FFHD.product_id = @map_product_id
AND FFHD.value_date = @vvalue_date
AND FFHD.data_source = 'eVestment'
AND FFHD.ccy_id = @vccy
AND FFHD.reporting_method = @vreporting_method
)
--/*To add success record in log table*/
INSERT INTO dbo.[champ_dw_fact_performance_log]
( BatchImportID,
FileProcessedDate,
FileProcessedUTCDate,
ImportStatus,
ErrorCode,
ErrorMessage,
StagingID,
value_date,
reporting_method,
valuation_quality,
data_source,
ccy_id,
performance_return,
log_return,
firm_id,
product_id,
vehicle_id
)
SELECT
@BatchImportID AS BatchImportID,
@FileProcessedDate AS FileProcessedDate,
@FileProcessedUTCDate AS FileProcessedUTCDate,
1 AS ImportStatus,
'100' AS ErrorCode,
'Success' AS ErrorMessage,
@StagingID AS StagingID,
@value_date AS value_date,
@reporting_method AS reporting_method,
'Actual' AS valuation_quality,
'eVestment' AS data_source,
@ccy AS ccy_id,
@performance_return AS performance_return,
@log_return AS log_return,
@firm_id AS firm_id,
@product_id AS product_id,
@vehicle_id AS vehicle_id
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK
--BEGIN
-- ROLLBACK TRANSACTION
--END
DECLARE
@ERROR_MESSAGE NVARCHAR(4000)
SELECT
@ERROR_MESSAGE = ERROR_MESSAGE()
IF @ERROR_MESSAGE <> 'VALIDATION RAISE ERROR.'
BEGIN
SELECT
@ErrorCode = ISNULL(@ErrorCode,'') + CASE WHEN ISNULL(@ERROR_MESSAGE,'') <> '' THEN ',' + '999' ELSE '' END,
@ErrorMessage = ISNULL(@ErrorMessage,'') + CASE WHEN ISNULL(@ERROR_MESSAGE,'') <> '' THEN ',' + CONVERT(VARCHAR(4000),@ERROR_MESSAGE) ELSE '' END
END
SELECT
@ErrorCode = NULLIF(CASE WHEN @ErrorCode LIKE '%999%' THEN STUFF(@ErrorCode,1,1,'') ELSE @ErrorCode END,''),
@ErrorMessage = NULLIF(CASE WHEN @ErrorCode LIKE '%999%' THEN STUFF(@ErrorMessage,1,1,'') ELSE @ErrorMessage END,'')
/*To add failed record in log table*/
INSERT INTO dbo.[champ_dw_fact_performance_log]
(
BatchImportID,
FileProcessedDate,
FileProcessedUTCDate,
ImportStatus,
ErrorCode,
ErrorMessage,
StagingID,
value_date,
reporting_method,
valuation_quality,
data_source,
ccy_id,
performance_return,
log_return,
firm_id,
product_id,
vehicle_id
)
SELECT
@BatchImportID AS BatchImportID,
@FileProcessedDate AS FileProcessedDate,
@FileProcessedUTCDate AS FileProcessedUTCDate,
0 AS ImportStatus,
@ErrorCode AS ErrorCode,
@ErrorMessage AS ErrorMessage,
@StagingID AS StagingID,
@value_date AS value_date,
@reporting_method AS reporting_method,
'Actual' AS valuation_quality,
'eVestment' AS data_source,
@ccy AS ccy_id,
@performance_return AS performance_return,
@log_return AS log_return,
@firm_id AS firm_id,
@product_id AS product_id,
@vehicle_id AS vehicle_id
END CATCH
DELETE FHD
FROM #stagingPerformance AS FHD
WHERE FHD.[index] = @StagingID
SELECT
@firm_id = NULL,
@product_id = NULL,
@vehicle_id = NULL,
@value_date = NULL,
@reporting_method = NULL,
@ccy = NULL,
@performance_return = NULL,
@log_return = NULL,
@ImportStatus = NULL,
@ErrorCode = NULL,
@ErrorMessage = NULL
END
SELECT @BatchImportID as 'BatchID'
DROP TABLE #stagingPerformance
END