Category: SQL Server

After trying and trying we researched to understand why SQL Server refused to let us run GETDATE() inside a user-defined-function. This is why:

User Defined Functions cannot be used to modify base table information. The DML statements INSERT, UPDATE, and DELETE cannot be used on base tables. Another disadvantage is that SQL functions that return non-deterministic values are not allowed to be called from inside User Defined Functions. GETDATE is an example of a non-deterministic function. Every time the function is called, a different value is returned. Therefore, GETDATE cannot be called from inside a UDF you create.

(From DatabaseJournal.com)

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

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