Home Error not raising from BULK INSERT IN try block
Reply: 1

Error not raising from BULK INSERT IN try block

Sambhu
1#
Sambhu Published in 2017-11-13 12:53:21Z

hi i have written the below code inside a try block but does not raising error to catch block . but stand alone bulk insert generating error messages.

BEGIN TRY

SET @sql = 
                '
                    BULK INSERT dbo.vw_Data_Import_ISO_CSP_OPMHC_Premium--<-- table view
                    FROM ''' + @SourceFilePath + '''
                    WITH ( FIRSTROW = 2, FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''\n'' );
                '

EXECUTE (@sql)

END TRY
BEGIN CATCH

        SELECT 
            @error_number = ERROR_NUMBER(), 
            @error_message = ERROR_MESSAGE(), 
            @error_line = ERROR_LINE()
        SELECT @error_description = 'T-SQL error number ' + CAST(@error_number AS VARCHAR(10))  + ' on line ' + CAST(@error_line AS VARCHAR(10)) + '.'

END CATCH 
Dan Guzman
2#
Dan Guzman Reply to 2017-11-15 12:29:37Z

You are not raising an error in the CATCH block, only assigning a variable value. Try adding RAISERROR:

BEGIN TRY
    SET @sql = ' BULK INSERT dbo.vw_Data_Import_ISO_CSP_OPMHC_Premium --<-- table view 
FROM ''' + @SourceFilePath + ''' WITH ( FIRSTROW = 2, FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''\n'', MAXERRORS = 0 );'
    EXECUTE (@sql);
END TRY BEGIN CATCH
    SELECT 
        @error_number = ERROR_NUMBER(), 
        @error_message = ERROR_MESSAGE(), 
        @error_line = ERROR_LINE();
    SELECT @error_description = 'T-SQL error number ' + CAST(@error_number AS VARCHAR(10))  + ' on line ' + CAST(@error_line AS VARCHAR(10)) + '.';
    RAISERROR(@error_description, 16, 1);
END CATCH; 

In SQL Server 2012 and later, the CATCH block can be simplified using THROW:

BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.306372 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO