barcode generator vb.net free Composite Transact-SQL Constructs Batches, Scripts, and Transactions in Software

Creation PDF417 in Software Composite Transact-SQL Constructs Batches, Scripts, and Transactions

The server has successfully compiled a batch, since the name resolution is not part of the compilation. The first command was executed without a problem. When a problem was encountered in the second command, the server canceled all further processing and returned a runtime error.

Keep this problem in mind when writing batches. Developers in modern programming languages like Visual Basic or Visual C++ usually employ sophisticated error-handling strategies to avoid situations like this. Transact-SQL also contains programming constructs for error handling. We will explore them in the next chapter. The situation could be worse. Particular runtime errors (for example, constraint violations) do not stop execution of the batch. In the following case, we attempt to use an Insert statement to insert a value in the identity column.

NOTE: Identity columns are a feature used by SQL Server to generate unique, sequential numbers for each record inserted in a table. It is equivalent to the AutoNumber datatype in Microsoft Access. Naturally, you should not attempt to specify values in such columns.

Select PartId, Make + ' ' + Model Part from Part Insert into Part (PartId, Make, Model, Type) Values (1, 'IBM', 'Thinkpad 390D', 'Notebook') Select PartId, Make + ' ' + Model Part from Part Go

The result is a partial failure :

PartId 1 Part Toshiba Portege 7020CT ----------- --------------------------------------------------

(1 row(s) affected)

Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table 'Part' when IDENTITY_INSERT is set to OFF. PartId 1 Part Toshiba Portege 7020CT ----------- ----------------------------------

(1 row(s) affected)

In some cases partial success may be tolerable, but in the real world it is generally not acceptable. Let s investigate a case in which several batches are written, divided by a Go statement, and executed together. Although the user has issued a single command to execute them, the client application will divide the code into batches and send them to the server separately. If an error occurs in any batch, the server will cancel its execution. However, this does not mean that execution of the other batches is canceled. The server will try to execute the next batch automatically. In some cases this may be useful, but in most it may not be what the user expects to happen. In the following example, a user tries to delete one column from the Part table. One way to perform this action (very popular until DBAs got spoiled with fancy tools like Enterprise Manager or the Alter Table Drop Column statement) would be to

Create a temporary table to preserve the information that is currently in the Part table. Copy information from the Part table to the temporary table. Drop the existing Part table. Create a Part table without the irrelevant columns. Copy the preserved information back to the Part table. Drop the temporary table.

A code to implement this functionality could be created in a set of five batches:

6:

Create Table TmpPart (PartId int identity, Make varchar(50), Model varchar(50), Type varchar(50)) Go Insert into TmpPart (PartId, Make, Model, EqTypeId) Select PartId, Make, Model, EqTypeId from Part Go Drop Table Part Go Create Table Part (PartId int identity, Make varchar(50), Model varchar(50)) Go Insert into Part (PartId, Make, Model) Select PartId, Make, Model from TmpPart Go Drop Table TmpPart Go

In theory, this set of batches would work perfectly. However, there is just one problem the developer didn t take errors into account. For example, if a syntax error occurs in the first batch, a temporary table will not be created, Part information will not be preserved in it, and when the code drops the table, the information will be lost. To observe a method that experienced developers use to handle errors, read the next chapter.