This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 31%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENN%3C/text%3E%3C/svg%3E)
Hi everybody,
I have a strange problem. I have a long procedure which has 2 places that send notes for other procedure. This procedure outputs NULL by this line of code:
DECLARE @message VARCHAR(200) = ' Notes is ' + ISNULL(@notes, 'NULL')
RAISERROR (@message, 0, 1) WITH NOWAIT
It outputs Notes is NULL.
In the calling procedure I have the following:
SET @notes = 'NPI ' + ISNULL(@npi,'') + ' excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD ('
+ CONVERT(VARCHAR(10), ISNULL(@inp_partition_id,0)) +
'LEN(@Snapshot) = ' + CAST(LEN(ISNULL(@Snapshot, 'NULL')) AS VARCHAR (10)) +
') ' + ISNULL(ERROR_MESSAGE(),'');
RAISERROR (@notes, 0, 1) WITH NOWAIT
EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod
@ExcludedId = @ProviderID
, @ExtractType = @inp_extract_type
, @notes = @notes;
FETCH NEXT FROM SplitProviderCursor
INTO
@myrowid
, @Npi;
SET @excludedProviders1 = @excludedProviders1+1
RAISERROR (@notes, 0, 1) WITH NOWAIT
RAISERROR (@excludedProviders1, 0, 1) WITH nowait
END TRY
BEGIN CATCH
-- Add exclusion
BEGIN TRY
SET @notes = 'NPI ' + ISNULL(@npi,'') + ' excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD ('
+ CONVERT(VARCHAR(10), ISNULL(@inp_partition_id,0)) + ') ' + ISNULL(ERROR_MESSAGE(),
'');
EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod
@ExcludedId = @ProviderID
, @ExtractType = @inp_extract_type
, @notes = @notes;
SET @excludedProviders2 = @excludedProviders1+2
RAISERROR (@excludedProviders2, 0, 1) WITH NOWAIT
RAISERROR (@notes, 0, 1) WITH NOWAIT
As you can see the Notes can't be null -I take every possible precaution against NULLs.
Also none of the RAISERROR messages are outputted and I don't understand why.
Can someone please explain this to me?
Thanks
Programming language used to interact with SQL Server databases
I added select @notes as notes and time this I got output NULL 2 times. There are two puzzling questions. First of all, I ran the procedure right before, so I except it to return nothing yet evetime it outputs 2 records. Secondly first time it produces a lot of records which is not excepted. And finally, why notes are NULL when every part of it in ISNULL statement. I'm going to add ISNULL for the whole @notes.
I changed the code to
SET @notes
= ISNULL('NPI ' + ISNULL(@Npi, '') + ' excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD ('
+ CONVERT(VARCHAR(10), ISNULL(@inp_partition_id, 0)) + 'LEN(@Snapshot) = '
+ CAST(LEN(ISNULL(@Snapshot, 'NULL')) AS VARCHAR(10)) + ') '
+ ISNULL(ERROR_MESSAGE(), ''),'');
RAISERROR(@notes, 0, 1) WITH NOWAIT;
SELECT @notes AS notes1
So, I expect to at least to see empty output both times (Actually, I don't expect to see any output after running procedure second time). Why am I still seeing NULL. This makes so sense.
Also, I tried a few times to correct several typos in the question itself and it gave me an error every time.
I don't see what you are doing inside that procedure, but you could use concat, to make your life easier:
SET @notes = conact('NPI ', @npi, ' excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD ('
ISNULL(@inp_partition_id,0), 'LEN(@Snapshot) = ' + LEN(@Snapshot, ')', ERROR_MESSAGE())
With concat all arguments are implicitly converted to string and a NULL value becomes an empty string.
Why RASERROR messages don't output? Also, almost all notes now show output, but the first one still shows NULL. Also, I am currently running this procedure and I'm unable to cancel the execution - there is no red cancel button.
SET @notes = CONCAT( 'NPI ' , isnull(@npi, '') , ' is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition (',
CONVERT(VARCHAR(10), ISNULL(@inp_partition_id,0)),
' for region ' , CAST(@inp_region_nbr AS VARCHAR(20)),') ', ERROR_MESSAGE()) ;
EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod
@ExcludedId = @ProviderID
, @ExtractType = @inp_extract_type
, @notes = @notes;
SET @excludedProviders2 = @excludedProviders1 + 2;
RAISERROR(@excludedProviders2, 0, 1) WITH NOWAIT;
RAISERROR(@notes, 0, 1) WITH NOWAIT;
Now outputs
I don't understand text about error number 2, etc.
I think that the unexpected messages from your image are caused by RAISERROR(@excludedProviders2, 0, 1), where @excludedProviders2 is an integer number like 22, 23, 24, etc., which has a special meaning (see documentation for RAISERROR). Remove this line if not needed. To display the number, try something like this:
declare @t varchar(max) = cast(@excludedProviders2 as varchar(max))
RAISERROR(@t, 0, 1) WITH NOWAIT
Check if there are other statements to adjust, like RAISERROR(@excludedProviders1, 0, 1), which cause the same situation.
Another thing to watch out for is that your text may include a per cent character, which has a special meaning for RAISERROR. To avoid that risk, do this:
RAISERROR('%s', 0, 1, @notes)
In the same vein, to use a number:
RAISERROR('%d', 0, 1, @excludedProviders2)
Why you get back NULL, I don' t know. I don't see all of your code, so I can't tell what you are doing wrong.
Also, I am currently running this procedure and I'm unable to cancel the execution - there is no red cancel button.
Seems like you have managed to deleted from the toolbar. Restore it from the arrow at the end of the toolbar. You can also cancel from the Query menu.
The procedure has the following code which still produces NULL for notes somehow:
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
-- TRUNCATE TABLE #tempTable;
EXECUTE dbo.usp_File_Extract_ProviderMasterFile_VA_SPLIT @inp_region_nbr = @inp_region_nbr,
@inp_npi = @Npi,
@XMLVARCHAR = @Snapshot OUTPUT,
@providerid = @ProviderID OUTPUT;
--INSERT INTO #tempTable
--(
-- entity_id,
-- run_id,
-- partition_id, -- for splitting providers by Run/Partition ID
-- provider_id,
-- sha_value,
-- SNAPSHOT,
-- etl_create_dttm
--)
-- VALUES
--(@Npi
--, @inp_run_id
--, @inp_partition_id
--, @ProviderID
--, HASHBYTES('SHA2_512', @Snapshot)
--, @Snapshot
--, CAST(SYSDATETIMEOFFSET()AT TIME ZONE 'Central Standard Time' AS DATETIME2(7)));
/*
INSERT INTO
dbo.PROVIDER_EXTRACT_TEMP_MOD
(
entity_id
, run_id
, partition_id -- for splitting providers by Run/Partition ID
, provider_id
, sha_value
, snapshot
, etl_create_dttm
)
VALUES
(@Npi
, @inp_run_id
, @inp_partition_id
, @ProviderID
, HASHBYTES('SHA2_512', @Snapshot)
, @Snapshot
, CAST(SYSDATETIMEOFFSET()AT TIME ZONE 'Central Standard Time' AS DATETIME2(7)));
*/
IF LEN(@Snapshot) < 1067142
AND @Snapshot IS NOT NULL
INSERT INTO dbo.PROVIDER_EXTRACT_TEMP_MOD
(
entity_id,
run_id,
partition_id, -- for splitting providers by Run/Partition ID
provider_id,
sha_value,
snapshot,
etl_create_dttm
)
--SELECT entity_id,
-- run_id,
-- partition_id, -- for splitting providers by Run/Partition ID
-- provider_id,
-- sha_value,
-- SNAPSHOT,
-- etl_create_dttm
--FROM #tempTable;
VALUES
(@Npi
, @inp_run_id
, @inp_partition_id
, @ProviderID
, HASHBYTES('SHA2_512', @Snapshot)
, @Snapshot
, CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Central Standard Time' AS DATETIME2(7)));
ELSE
SET @notes = CONCAT('NPI ', @npi, ' is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for patrition ',
ISNULL(@inp_partition_id,0), ' LEN(@Snapshot) = ' , LEN(ISNULL(@Snapshot,'')) ,
' for region ' , CAST(@inp_region_nbr AS VARCHAR(20)), ERROR_MESSAGE())
EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod @ExcludedId = @ProviderID,
@ExtractType = @inp_extract_type,
@notes = @notes;
FETCH NEXT FROM SplitProviderCursor
INTO @myrowid,
@Npi;
END TRY
BEGIN CATCH
-- Add exclusion
BEGIN TRY
SET @notes = CONCAT( 'NPI ' , isnull(@npi, '') , ' is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition ',
CONVERT(VARCHAR(10), ISNULL(@inp_partition_id,0)),
' for region ' , CAST(@inp_region_nbr AS VARCHAR(20)),' ', ERROR_MESSAGE()) ;
SET @notes = ISNULL(@notes,'No notes')
EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod
@ExcludedId = @ProviderID
, @ExtractType = @inp_extract_type
, @notes = @notes;
END TRY
BEGIN CATCH
-- If this doesn't work, just audit error and keep going.
SET @audit_message_txt = CONCAT( 'Failed to exclude NPI ' , CONVERT(VARCHAR(20), ISNULL(@Npi, 0)) ,
' for region ' , CAST(@inp_run_id AS VARCHAR(10)) , ' ', ERROR_MESSAGE());
EXECUTE dbo.usp_write_audit
@inp_process_txt = @inp_process_txt
, @inp_sub_process_txt = @sub_process_txt
, @inp_type_id = 0
, @inp_action_txt = @audit_message_txt
, @inp_load_file_detail_id = @efd
, @inp_job_nbr = @inp_load_report_job_nbr;
END CATCH;
END CATCH;
END;
Which produces :
Notes is NULL
Notes is NULL
Notes is NULL
Notes is NULL
Notes is NULL
Notes is NULL
Notes is NULL
Notes is NULL
Notes is NULL
Notes is NULL
Notes is NULL
Notes is NULL
Notes is NPI 1053353896 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
NPI 1053353896 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1053353896 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
NPI 1053353896 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
NPI 1053353896 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1053353896 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
NPI 1053353896 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
NPI 1053353896 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1053353896 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
NPI 1053353896 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
NPI 1053353896 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1053353896 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
NPI 1053353896 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
NPI 1053353896 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
which I cannot figure out.
My colleague's code which is practically the same doesn't output messages at all:
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
EXECUTE dbo.usp_File_Extract_ProviderMasterFile_VA_SPLIT
@inp_region_nbr = @inp_region_nbr
, @inp_npi = @Npi
, @XMLVARCHAR = @Snapshot OUTPUT
, @providerid = @ProviderID OUTPUT;
INSERT INTO
dbo.PROVIDER_EXTRACT_TEMP_MOD
(
entity_id
, run_id
, partition_id -- for splitting providers by Run/Partition ID
, provider_id
, sha_value
, snapshot
, etl_create_dttm
)
VALUES
(@Npi
, @inp_run_id
, @inp_partition_id
, @ProviderID
, HASHBYTES('SHA2_512', @Snapshot)
, @Snapshot
, CAST(SYSDATETIMEOFFSET()AT TIME ZONE 'Central Standard Time' AS DATETIME2(7)));
FETCH NEXT FROM SplitProviderCursor
INTO
@myrowid
, @Npi;
END TRY
BEGIN CATCH
-- Add exclusion
BEGIN TRY
SET @notes = 'NPI excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD ('
+ CONVERT(VARCHAR(10), ISNULL(@inp_partition_id,0)) + ')';
EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod
@ExcludedId = @ProviderID
, @ExtractType = @inp_extract_type
, @notes = @notes;
--RAISERROR (@notes, 0, 1)
END TRY
BEGIN CATCH
I don't see much difference.
I'm running it now again and getting this output:
Notes is NULL
Notes is NPI 1003009051 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1003009051 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Mutator 'modify()' on '@ContractProviderFileXML' cannot be called on a null value.
Notes is NPI 1003009051 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Mutator 'modify()' on '@ContractProviderFileXML' cannot be called on a null value.
Notes is NPI 1003009051 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Mutator 'modify()' on '@ContractProviderFileXML' cannot be called on a null value.
Notes is NPI 1003009051 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Mutator 'modify()' on '@ContractProviderFileXML' cannot be called on a null value.
Notes is NPI 1003009051 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Mutator 'modify()' on '@ContractProviderFileXML' cannot be called on a null value.
What is strange is why I'm getting NULL still and the second puzzle is why the same exact NPI repeated multiple times and why the first line shows different error than all other errors.
So, if I remove this condition
--IF LEN(@Snapshot) < 1067142
-- AND @Snapshot IS NOT NULL
then there is no error.
Yeah, the code you posted above will either insert a row or set the @notes variable.
Which is not that easy to see, because the code is inconsistently indented and cluttered with code that is commented out. It would be a courtesy to the people that you are asking to help to clean up the code before you post. And who knows, you may spot the error yourself.
This is my latest code:
DROP TABLE IF EXISTS #tempTable;
CREATE TABLE #tempTable
(
entity_id BIGINT NOT NULL,
run_id INT,
partition_id INT, -- for splitting providers by Run/Partition ID
provider_id BIGINT,
sha_value VARBINARY(64),
SNAPSHOT NVARCHAR(MAX),
etl_create_dttm DATETIME
);
DECLARE @myrowid INT;
DECLARE SplitProviderCursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT rowid,
npi
FROM #tmp_npi_list;
OPEN SplitProviderCursor;
FETCH NEXT FROM SplitProviderCursor
INTO @myrowid,
@Npi;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
TRUNCATE TABLE #tempTable;
EXECUTE dbo.usp_File_Extract_ProviderMasterFile_VA_SPLIT @inp_region_nbr = @inp_region_nbr,
@inp_npi = @Npi,
@XMLVARCHAR = @Snapshot OUTPUT,
@providerid =@ProviderID OUTPUT;
INSERT INTO #tempTable
(
entity_id,
run_id,
partition_id,
provider_id,
sha_value,
SNAPSHOT,
etl_create_dttm
)
VALUES
(@Npi
, @inp_run_id
, @inp_partition_id
, @ProviderID
, HASHBYTES('SHA2_512', @Snapshot)
, @Snapshot
, CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Central Standard Time' AS DATETIME2(7)));
DECLARE @snapshotLen bigint
SELECT @snapshotLen = LEN(CAST(snapshot as nvarchar(max))) FROM #tempTable
RAISERROR ('%d', 0, 1, @snapshotLen)
IF @SnapshotLen < 1067142
INSERT INTO dbo.PROVIDER_EXTRACT_TEMP_MOD
(
entity_id,
run_id,
partition_id, -- for splitting providers by Run/Partition ID
provider_id,
sha_value,
snapshot,
etl_create_dttm
)
SELECT
entity_id,
run_id,
partition_id, -- for splitting providers by Run/Partition ID
provider_id,
sha_value,
snapshot,
etl_create_dttm
FROM #tempTable;
--VALUES
-- (@Npi
-- , @inp_run_id
-- , @inp_partition_id
-- , @ProviderID
-- , HASHBYTES('SHA2_512', @Snapshot)
-- , @Snapshot
-- , CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Central Standard Time' AS DATETIME2(7)));
/**/
ELSE
SET @notes = CONCAT('NPI ', CAST(@npi AS VARCHAR(100)), ' is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for patrition ',
CAST(ISNULL(@inp_partition_id,0) AS VARCHAR(10)), ' LEN(@Snapshot) = ' , CAST(ISNULL(@snapshotLen,0) AS varchar(10)) ,
' for region ' , CAST(ISNULL(@inp_region_nbr,0) AS VARCHAR(20)), ISNULL(ERROR_MESSAGE(),''))
SET @notes = ISNULL(@notes, 'Empty')
EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod @ExcludedId = @ProviderID,
@ExtractType = @inp_extract_type,
@notes = @notes;
END TRY
BEGIN CATCH
-- Add exclusion
BEGIN TRY
SET @notes = CONCAT( 'NPI ' , isnull(@npi, '') , ' is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition ',
CONVERT(VARCHAR(10), ISNULL(@inp_partition_id,0)),
' for region ' , CAST(ISNULL(@inp_region_nbr,0) AS VARCHAR(20)),' ', ERROR_MESSAGE()) ;
SET @notes = ISNULL(@notes,'No notes')
EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod
@ExcludedId = @ProviderID
, @ExtractType = @inp_extract_type
, @notes = @notes;
END TRY
BEGIN CATCH
-- If this doesn't work, just audit error and keep going.
SET @audit_message_txt = CONCAT( 'Failed to exclude NPI ' , CONVERT(VARCHAR(20), ISNULL(@Npi, 0)) ,
' for region ' , CAST(@inp_run_id AS VARCHAR(10)) , ' ', ERROR_MESSAGE());
EXECUTE dbo.usp_write_audit
@inp_process_txt = @inp_process_txt
, @inp_sub_process_txt = @sub_process_txt
, @inp_type_id = 0
, @inp_action_txt = @audit_message_txt
, @inp_load_file_detail_id = @efd
, @inp_job_nbr = @inp_load_report_job_nbr;
END CATCH;
END CATCH;
FETCH NEXT FROM SplitProviderCursor
INTO @myrowid,
@Npi;
END;
And the output is:
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is Empty
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar.
Notes is NPI 1578513016 is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition 1 for region 2 Arithmetic overflow error converting expression to data type varchar
which I don't understand. Why is shows Notes is Empty and why it outputs this strange error multiple times for the same NPI?
As indicated, you should use BEGIN...END with each complex IF and ELSE to clearly delimit the block of instructions.
Exactly, I changed the code to the following it it worked (no more messages), but the Raiserror didn't output ShortLen:
DROP TABLE IF EXISTS #tempTable;
CREATE TABLE #tempTable
(
entity_id BIGINT NOT NULL,
run_id INT,
partition_id INT, -- for splitting providers by Run/Partition ID
provider_id BIGINT,
sha_value VARBINARY(64),
SNAPSHOT NVARCHAR(MAX),
etl_create_dttm DATETIME
);
DECLARE @myrowid INT;
DECLARE SplitProviderCursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT rowid,
npi
FROM #tmp_npi_list;
OPEN SplitProviderCursor;
FETCH NEXT FROM SplitProviderCursor
INTO @myrowid,
@Npi;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
TRUNCATE TABLE #tempTable;
EXECUTE dbo.usp_File_Extract_ProviderMasterFile_VA_SPLIT @inp_region_nbr = @inp_region_nbr,
@inp_npi = @Npi,
@XMLVARCHAR = @Snapshot OUTPUT,
@providerid = @ProviderID OUTPUT;
INSERT INTO #tempTable
(
entity_id,
run_id,
partition_id,
provider_id,
sha_value,
SNAPSHOT,
etl_create_dttm
)
VALUES
(@Npi, @inp_run_id, @inp_partition_id, @ProviderID, HASHBYTES('SHA2_512', @Snapshot), @Snapshot,
CURRENT_TIMESTAMP); -- CAST(SYSDATETIMEOFFSET()AT TIME ZONE 'Central Standard Time' AS DATETIME2(7
DECLARE @snapshotLen BIGINT;
SELECT @snapshotLen = ISNULL(LEN(CAST(SNAPSHOT AS NVARCHAR(MAX))), 0)
FROM #tempTable;
RAISERROR('%d', 0, 1, @snapshotLen);
IF ISNULL(@snapshotLen, 0) < 1067142
INSERT INTO dbo.PROVIDER_EXTRACT_TEMP_MOD
(
entity_id,
run_id,
partition_id, -- for splitting providers by Run/Partition ID
provider_id,
sha_value,
snapshot,
etl_create_dttm
)
SELECT entity_id,
run_id,
partition_id, -- for splitting providers by Run/Partition ID
provider_id,
sha_value,
SNAPSHOT,
etl_create_dttm
FROM #tempTable;
--VALUES
-- (@Npi
-- , @inp_run_id
-- , @inp_partition_id
-- , @ProviderID
-- , HASHBYTES('SHA2_512', @Snapshot)
-- , @Snapshot
-- , CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Central Standard Time' AS DATETIME2(7)));
/**/
ELSE
BEGIN
SET @notes
= CONCAT(
'NPI ',
CAST(ISNULL(@Npi, '') AS VARCHAR(100)),
' is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for patrition ',
CAST(ISNULL(@inp_partition_id, 0) AS VARCHAR(10)),
' LEN(@Snapshot) = ',
CAST(ISNULL(@snapshotLen, 0) AS VARCHAR(10)),
' for region ',
CAST(ISNULL(@inp_region_nbr, 0) AS VARCHAR(20)),
ISNULL(ERROR_MESSAGE(), '')
);
SET @notes = ISNULL(@notes, 'Empty');
EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod @ExcludedId = @ProviderID,
@ExtractType = @inp_extract_type,
@notes = @notes;
END;
END TRY
BEGIN CATCH
-- Add exclusion
BEGIN TRY
SET @notes
= CONCAT(
'NPI ',
ISNULL(@Npi, ''),
' is excluded by usp_CREATE_SNAPSHOT_PER_PROVIDER_SPLIT_MOD for partition ',
CONVERT(VARCHAR(10), ISNULL(@inp_partition_id, 0)),
' for region ',
CAST(ISNULL(@inp_region_nbr, 0) AS VARCHAR(20)),
' ',
ISNULL(ERROR_MESSAGE(), '')
);
SET @notes = ISNULL(@notes, 'No notes');
EXECUTE dbo.usp_DevOps_Add_Exclusion_Mod @ExcludedId = @ProviderID,
@ExtractType = @inp_extract_type,
@notes = @notes;
END TRY
BEGIN CATCH
-- If this doesn't work, just audit error and keep going.
SET @audit_message_txt
= CONCAT(
'Failed to exclude NPI ',
CONVERT(VARCHAR(20), ISNULL(@Npi, 0)),
' for region ',
CAST(@inp_run_id AS VARCHAR(10)),
' ',
ERROR_MESSAGE()
);
EXECUTE dbo.usp_write_audit @inp_process_txt = @inp_process_txt,
@inp_sub_process_txt = @sub_process_txt,
@inp_type_id = 0,
@inp_action_txt = @audit_message_txt,
@inp_load_file_detail_id = @efd,
@inp_job_nbr = @inp_load_report_job_nbr;
END CATCH;
END CATCH;
FETCH NEXT FROM SplitProviderCursor
INTO @myrowid,
@Npi;
END;
Change
' LEN(@Snapshot) = ',
CAST(ISNULL(@snapshotLen, 0) AS VARCHAR(10)),
To
' LEN(@Snapshot) = ', @snapshotLen,
Same goes for all the other unnecessary CAST and ISNULL. Concat handles that for you.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(227.2, 35%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELN%3C/text%3E%3C/svg%3E)
Hi @Naomi Nosonovsky,
I’m following up to check if the response provided below was helpful.
Please let us know if your issue has been resolved. If you still have any questions or need further assistance, don’t hesitate to get in touch we’re happy to continue supporting you.
We appreciate your participation and thank you for being an active member of the community.
Best regards,
Lakshmi
Hi @Naomi Nosonovsky,
I’m following up to check if the response provided below was helpful.
Please let us know if your issue has been resolved. If you still have any questions or need further assistance, don’t hesitate to get in touch we’re happy to continue supporting you.
We appreciate your participation and thank you for being an active member of the community.
Best regards,
Lakshmi
Answer recommended by moderator
I haven't figured out why Raiserror didn't output messages, bit with my colleague we determined that this PBI doesn't need to be implemented since my changes from UDF to HASHBYTES changed the output of the procedure and large extracts were handled automatically.
Hi @Naomi Nosonovsky,
hank you for your detailed explanation. It looks like you've already followed the recommended steps, but the issue persists.
To help resolve this further, I recommend raising a support ticket so our support team can assist you directly. Please refer to the following link for instructions on how to create a support ticket:
https://support.serviceshub.microsoft.com/supportforbusiness/create
We appreciate your engagement and thank you for being an active part of the community.
Thanks,
Lakshmi.