In this post we are going to combine what we learned on importing a text file into SQL Server and how we can handle exceptions. The beginning of each series of posts is listed below. Please have a look at those series for more information. In this post I will start with the requirements and how the user interacts with the program. In the next post I will provide the source code.

• SQL Server Extract Data from a Text File
• SQL Server Exception Handling Part 7

For a complete code listing, see Part 9. For an improved code listing, with our own error checking that checks that we found all of the necessary data in the input file, see Part 11.

We are going to put transactions and error handling into the Extracting Data project referenced above. The next post will show the source code.

Recall that we have a stored procedure that the user runs to run the entire process in an all-or-nothing fashion. The user runs the [AllProcedures] stored procedure, which calls the other stored procedures. Part of the [AllProcedures] stored procedure code is shown below. There are four stored procedures that it calls.

EXEC dbo.DeleteRowsInReport  -- delete rows in table InReport
EXEC dbo.BulkInsertFile -- import from file to table InReport (one column: InRows)
EXEC dbo.CopyInRepToInRepHist -- copy to provide an audit trail
EXEC dbo.FromInReportToReports -- extract data into table dbo.Reports

With the addition of transactions in our T-SQL code, all of the stored procedures, including [AllProcedures] will be running their own transactions. Transactions can be rolled back if an error occurs.

Business Requirements

Our objective is to handle the errors and roll back transactions the way that the business requirements specify. If one of the stored procedures in the group of four encounters an error, it will roll back only the code that is in that stored procedure itself, and then raise an error so that the calling procedure, [AllProcedures] will then deal with the error. {AllProcedures] will then roll back the entire transaction that it started, resulting in a state that is the same as if the [AllProcedures] was never even run in the first place. In other words, fix the error or forget about it. It is all-or-nothing.

Use Case

1. Preapre the input text file rpt2.txt
2. Run the stored procedure [AllProcedures]

There are two steps to using this program. The user first checks the input file rpt2.txt to be sure that the data from the incoming report is correctly copied to this file. Each time the user receives an incoming report from a client, they will copy that data into the rpt2.txt file and then import that file’s data into SQL Server by running the stored procedure called [AllProcedures]. If this is the first time this has been run, then there will be no rows in any of the tables. When the user runs it in SSMS, they will see something like the following in the Results pane, if there were no errors.

In SProc [AllProcedures], @TranCountAtStart is zero; begin transaction
In SProc [AllProcedures], We are now inside the BEGIN TRY block.
In SProc [DeleteRowsInReport], @TranCountAtStart is greater than zero; save transaction.
0 rows were deleted from table dbo.InReport
In SProc [BulkInsertFile], @TranCountAtStart is greater than zero; save transaction.
In SProc [BulkInsertFile], We are now inside the BEGIN TRY block.
In SProc [CopyInRepToInRepHist], @TranCountAtStart is greater than zero; save transaction.
In SProc [FromInReportToReports], @TranCountAtStart is greater than zero; save transaction.
Return Value
------------
0

(1 row(s) affected)

Below is a listing of the input file (“report”) and our three table s in SQL Server after we run a SELECT on those three tables. There are nine rows in the input file. This is important and we expect there to always be nine rows. This may be an unrealistic expectation in the real world, however. But perhaps not as reports have footers, “End of Report” for example, and they might always be on a specific row, resulting in a fixed number of rows in the report. We also expect several other things about this file including for example that the source starts on row 4 and column 9.

Input File (C:\incoming\rpt2.txt)

Report Header from ABC Inc.

File name: 123.txt
Source: ABC Inc.
Four-digit code number: 5578
Transaction number: 12345   Transaction Amount:    12000
Transaction number: 12346   Transaction Amount:   770000

End of Report

Table [dbo].[InReport]

Table [dbo].[InReportHistory]

Table [dbo].[Reports]