Categories
SQL Server

Transactions not rolling back in SQL Server? How’s your SET XACT_ABORT today?

Every database rookie knows about the ACID principles.
ACID is what makes Oracle snobs look down at MySQL and scoff that it is not a real database.
I am neither a database snob nor an absolute rookie, but a certain behavior in SQL Server 2000 did baffle me. I would create transactions and they would fail in the middle without rolling back (and atomicity was just like, not there).
Rummaging through the beloved server’s documentation (RTFM, RTFM) discovered this flag and the somewhat hair-raising behavior:

If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in SQL Server is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

That is, you need to set your SET XACT_ABORT ON before you execute your transaction.

XC3113n7

Share
Categories
SQL Server

Searching your stored procedures in SQL Server 2000

SQL Server enables you – in its infinite kindness – to extract all its stored procedure code into one big file, or into one file per stored procedure.
This is very useful if you need to search the stored procedure for a column you modified or want to take a snapshot for a backup.
To do this:

  • right-click the database in which the stored procedures exist and choose ‘All Tasks’.
  • From the popup menu, choose ‘Generate SQL Script’.
  • A window will pop up. Click on the ‘Show All’ button. You will now see all the objects in the database.
  • Select the stored procedure check box (or any other part of the code)
  • From the ‘Options’ tab, select whether you want one big file or separate files for each stored procedure
  • Back in the ‘General’ tab, click OK
  • Select where to store the file(s)
  • Click ok
Share
Categories
Java

JDBC: Finding a ResultSet column was null

Supposed you just executed a database call using JDBC and the results were stored in a ResultSet object called rs. Then, you would expect the following to work if you test whether you returned a null value:


if (rs.getString("field") == null) {
// do something
}

You would be wrong.
Instead, JDBC offers the oh so intuitive wasNull() method:

String val = rs.getString("field");
if (rs.wasNull())
{
// do something
}

The logic behind this is that the ResultSet object is not pre-fetched and hence we can not test for its value before JDBC actually retrieved the value. Am I right?…

This is based on a posting to the SQL Server 2000 JDBC newsgroup.

Share
Share