October 2, 2024 at 9:44 am
I use FileStream Directory for Interface CSV Files. With an after insert trigger i split the csv columns and insert values to other non filestream Tables. Sometimes in splitting or insert errors occurs but the wrong formattet csv is anyway copied to the filestream directory. now i wanted to alert myself when this happens. I tried a lot of constructions with try catch and begin transaction and rollback, but it's never exuted when an error within the after insert trigger occurs. I just wanted to log the error somewhere. Any ideas would be appreciated. thanks in advance
October 2, 2024 at 9:48 am
--here the code:
CREATE OR ALTER TRIGGER [dbo].[tri_SAPA_UPDATE]
ON [dbo].[tbl_SAPA]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @AnzahlZeichenKopfzeile INT = 393;
DECLARE @Text NVARCHAR(MAX);
SELECT
@Text = CONVERT(VARCHAR(MAX), [Inserted].[file_stream])
FROM
[Inserted];
SET @Text = REPLACE(@Text, CHAR(13) + CHAR(10), '');
SET @Text = REPLACE(REPLACE(@Text, '; ;', ';;'), '; ;', ';;');
SET @Text = SUBSTRING(@Text, @AnzahlZeichenKopfzeile, LEN(@Text) - @AnzahlZeichenKopfzeile + 1);
TRUNCATE TABLE [dbo].[stg_SAPA];
TRUNCATE TABLE [dbo].[stg_SAPB];
INSERT INTO [dbo].[stg_SAPB]
(
[inhalt]
)
SELECT
[value]
FROM
STRING_SPLIT(@Text, ';');
UPDATE
[dbo].[stg_SAPB]
SET
[fn] = CHOOSE(
[id_num], 'SapArtNr', 'ArtText', 'ArtTextEN', 'ArtStatus', 'BezQuelle', 'Kreditor',
'ArtNrLief', 'QmOrderSperre', 'QmStatus', 'HWG', 'Kassenspeicher', 'EAN', 'MbmNL',
'MbmME', 'Warentarifnummer', 'VZTA', 'VZTAvon', 'VZTAbis', 'Zollsatz', 'VPEouterME',
'VPEouterLaenge', 'VPEouterBreite', 'VPEouterHoehe', 'VPEouterStkInBME',
'VPEouterGewichtBruttoInKG', 'VPEouterGewichtNettoInKG', 'PalWE', 'PalLaenge',
'PalBreite', 'PalHoehe', 'PalStkBE', 'PalGewichtBrutto', 'PalStkVPEouter', 'FSC',
'FSCvalidTil', 'Dispo'
)
WHERE
[id_num] <= 36;
INSERT INTO [dbo].[stg_SAPA]
(
[SapArtNr],
[ArtText],
[ArtTextEN],
[ArtStatus],
[BezQuelle],
[Kreditor],
[ArtNrLief],
[QmOrderSperre],
[QmStatus],
[HWG],
[Kassenspeicher],
[EAN],
[MbmNL],
[MbmME],
[Warentarifnummer],
[VZTA],
[VZTAvon],
[VZTAbis],
[Zollsatz],
[VPEouterME],
[VPEouterLaenge],
[VPEouterBreite],
[VPEouterHoehe],
[VPEouterStkInBME],
[VPEouterGewichtBruttoInKG],
[VPEouterGewichtNettoInKG],
[PalWE],
[PalLaenge],
[PalBreite],
[PalHoehe],
[PalStkBE],
[PalGewichtBrutto],
[PalStkVPEouter],
[FSC],
[FSCvalidTil],
[Dispo]
)
SELECT
*
FROM
(
SELECT
[inhalt],
[fn]
FROM
[dbo].[stg_SAPB]
) AS [p]
PIVOT
(
MAX([inhalt])
FOR [fn] IN ([SapArtNr], [ArtText], [ArtTextEN], [ArtStatus], [BezQuelle], [Kreditor], [ArtNrLief],
[QmOrderSperre], [QmStatus], [HWG], [Kassenspeicher], [EAN], [MbmNL], [MbmME],
[Warentarifnummer], [VZTA], [VZTAvon], [VZTAbis], [Zollsatz], [VPEouterME],
[VPEouterLaenge], [VPEouterBreite], [VPEouterHoehe], [VPEouterStkInBME],
[VPEouterGewichtBruttoInKG], [VPEouterGewichtNettoInKG], [PalWE], [PalLaenge],
[PalBreite], [PalHoehe], [PalStkBE], [PalGewichtBrutto], [PalStkVPEouter], [FSC],
[FSCvalidTil], [Dispo]
)
) AS [a];
DECLARE @LastWriteTime DATETIMEOFFSET(7);
DECLARE @TmeStmp DATETIME;
SELECT
@LastWriteTime = [Inserted].[last_write_time],
@TmeStmp
= CONVERT(
DATETIME,
SUBSTRING([Inserted].[name], 17, 8) + ' ' + SUBSTRING([Inserted].[name], 25, 2) + ':'
+ SUBSTRING([Inserted].[name], 27, 2) + ':' + SUBSTRING([Inserted].[name], 29, 2)
)
FROM
[Inserted];
BEGIN TRY
INSERT INTO [BSQL1200].[dbo].[tbl_SAPA]
(
[last_write_time],
[SapArtNr],
[WG],
[ArtText],
[ArtTextEN],
[ArtStatus],
[BezQuelle],
[Kreditor],
[ArtNrLief],
[QmOrderSperre],
[QmStatus],
[HWG],
[Kassenspeicher],
[EAN],
[MbmNL],
[MbmME],
[Warentarifnummer],
[VZTA],
[VZTAvon],
[VZTAbis],
[Zollsatz],
[VPEouterME],
[VPEouterLaenge],
[VPEouterBreite],
[VPEouterHoehe],
[VPEouterStkInBME],
[VPEouterGewichtBruttoInKG],
[VPEouterGewichtNettoInKG],
[PalWE],
[PalLaenge],
[PalBreite],
[PalHoehe],
[PalStkBE],
[PalGewichtBrutto],
[PalStkVPEouter],
[FSC],
[FSCvalidTil],
[Zeitstempel],
[erledigt],
[UID],
[Dispo]
)
SELECT
@LastWriteTime,
.[SapArtNr],
LEFT(.[Kassenspeicher], 1),
TRIM(.[ArtText]),
TRIM(.[ArtTextEN]),
.[ArtStatus],
.[BezQuelle],
.[Kreditor],
.[ArtNrLief],
.[QmOrderSperre],
.[QmStatus],
.[HWG],
.[Kassenspeicher],
RIGHT(('0000000000000' + TRIM(ISNULL(.[EAN], ''))), 13),
TRY_CONVERT(INT, CONVERT(DECIMAL(18, 3), .[MbmNL])),
.[MbmME],
.[Warentarifnummer],
.[VZTA],
.[VZTAvon],
.[VZTAbis],
TRY_CONVERT(DECIMAL(6, 3), .[Zollsatz]) / 100,
.[VPEouterME],
TRY_CONVERT(DECIMAL(8, 4), .[VPEouterLaenge]),
TRY_CONVERT(DECIMAL(8, 4), .[VPEouterBreite]),
TRY_CONVERT(DECIMAL(8, 4), .[VPEouterHoehe]),
.[VPEouterStkInBME],
TRY_CONVERT(DECIMAL(8, 4), .[VPEouterGewichtBruttoInKG]),
TRY_CONVERT(DECIMAL(8, 4), .[VPEouterGewichtNettoInKG]),
.[PalWE],
.[PalLaenge],
.[PalBreite],
.[PalHoehe],
.[PalStkBE],
.[PalGewichtBrutto],
.[PalStkVPEouter],
.[FSC],
IIF(.[FSCvalidTil] = CHAR(10), '19000101', LEFT(.[FSCvalidTil], 8)),
@TmeStmp,
0,
NULL,
CONVERT(TINYINT, LEFT(.[Dispo], 3))
FROM
[dbo].[stg_SAPA] AS ;
END TRY
BEGIN CATCH
INSERT INTO [BSQL1200].[dbo].[tbl_ERROR]
(
[ErrorNumber],
[ErrorSeverity],
[ErrorState],
[ErrorProcedure],
[ErrorLine],
[ErrorMessage]
)
SELECT
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE();
END CATCH;
TRUNCATE TABLE [dbo].[stg_SAPA];
TRUNCATE TABLE [dbo].[stg_SAPB];
-- Zuordnung Disponenten aus SAP Dispo ID für Import DB
UPDATE
SET
.[UID] = .[lngUserID]
FROM
[BSQL1200].[dbo].[tbl_SAPA] AS
INNER JOIN [BSQL1200].[dbo].[tbl_9999] AS ON .[Dispo] = .[DisponentenNrSAP]
WHERE
.[Dispo] IS NOT NULL AND
.[UID] IS NULL;
END;
GO
ALTER TABLE [dbo].[tbl_SAPA] ENABLE TRIGGER [tri_SAPA_UPDATE]
GO
October 2, 2024 at 10:09 am
What is the exact text of the error you are receiving? How do you even know that there is an error, if you cannot see it or trap it?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 2, 2024 at 10:15 am
hi phil, there is no error message, the wrong file ends in file stream directory and the trigger is somehow executed or not. i can see it only, because i am missing the results of the trigger.
October 2, 2024 at 11:31 am
hi phil, there is no error message, the wrong file ends in file stream directory and the trigger is somehow executed or not. i can see it only, because i am missing the results of the trigger.
It's starting to sound like a logic or data issue, rather than an error.
Maybe you could try adding some logging to your trigger, to help you determine what is happening?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 3, 2024 at 2:24 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