T-SQL error handling

An easy solution?

SET XACT_ABORT ON

SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

Copied from the Net to save typing.

Please avoid this style:

Begin transaction
Update…
Set…
Where…
Update…
Set…
Where…
Commit transaction

Because if errors occurred while processing the second UPDATE, SQL Server would still unconditionally commit the first (successful) UPDATE.

A more common way on error handling before try {}catch {} is introduced.
The idea is that all SQL statements inside a stored procedure should be covered with error-handling code. No transaction is considered here.

Declare @ErrorCode int Select @ErrorCode = @@Error If @ErrorCode = 0 Begin    --Some statement    Update …    Select @ErrorCode = @@Error End If @ErrorCode = 0 Begin    --Another statement    Insert …    Select @ErrorCode = @@Error End If @ErrorCode = 0 Begin    --Another statement    Update …    Select @ErrorCode = @@Error End
--this is a case of calling a stored procedure
If @ErrorCode = 0 Begin    execute @ErrorCode = MyStoredProcedure parm1, param2… End … Return @ErrorCode

There are cases when you wish to read the value of some other global variables immediately after the statement, and you should handle them with the same select statement that reads @@Error. For example, you often require something like this when you're using identity columns.

Insert … Select @id = @@identity,    @ErrorCode = @@Error

Work with Transaction

Transaction processing can be perfectly integrated with this solution. At the beginning of a stored procedure (or transaction), the developer should add the following:

Declare @TransactionCountOnEntry int
If @ErrorCode = 0
Begin  
Select @TransactionCountOnEntry = @@TranCount  
BEGIN TRANSACTION
End

At the end of the procedure/transaction, the developer should complete the transaction as follows:

If @@TranCount > @TransactionCountOnEntry
Begin  
If @ErrorCode = 0     
COMMIT TRANSACTION  
Else    
ROLLBACK TRANSACTION
End

The solution will also perform well in the case of nested stored procedures. All procedures will be rolled back using the same cascading mechanism. Here, the local variable @TransactionCountOnEntry is used to track the number of opened transactions upon the entry of a stored procedure. If the number was unaffected inside the stored procedure, there's no reason to either commit or rollback inside the procedure.

SQL Server Table Level Backup

BACKUP DATABASE does not support table level backup.
1.you can use use SQL Server Management Studio to script it out.
right clicking Database > Tasks > Generate Script
2.you can use export function to export it into csv file
3.you can use bcp to do similar thing

Calculation of 2's Complement

Note:
To calculate the 2's complement of an integer, invert the binary equivalent of the number by changing all of the ones to zeroes and all of the zeroes to ones (also called 1's complement), and then add one.

For example,
0001 0001(binary 17) ==> such that 1110 1111(two's complement -17)

1. NOT(0001 0001) = 1110 1110 (Invert bits)
2. 1110 1110 + 0000 0001 = 1110 1111 (Add 1)

Nesting Transactions in T-SQL

it's primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or processes that have no active transaction.

"Commit" statement won't take effect in an inner transaction,"rollback" statement does rollback all the changes made either by itself or the outer transaction. "rollback transaction savepoint" is a better choice to use to rollback only the changes made by the stored procedure.

an impression, in T-SQL, savepoint is for rolling back purpose. transaction name is for commit purpose.

if a stored procedure is called when a transaction is active, the nested transaction in it is largely ignored. the transaction will be governed by the out transaction. if it's executed by a process that does not have an active transaction, the commit statement in the stored procedure will effectively commit the changes to database.

a rollback statement will rollback everything, wherever the place it's executed. the @@trancount will be reset to 0. to be safe, it should check the @@trancount before issuing the "commit transaction" or "rollback " statements.

in a stored procedure, using the "save transaction savepointname" is a better way to rollback the changes made by the stored procedure.

sql server automatically starts a implicit transaction when enters in a trigger.

updates after a rollback statement will still be stored into database.

the following code will raise error since the second "rollback transaction" is executed without a transaction content.

BEGIN TRANSACTION
BEGIN TRANSACTION
-– Some Codes
ROLLBACK TRANSACTION
ROLLBACK TRANSACTION

Java -Xmx

It's hard to believe that I can't find this out from google. Put it here for quick reference.

-Xmxn
Specify the maximum size, in bytes, of the memory allocation pool. This value must a multiple of 1024 greater than 2MB. Append the letter k or K to indicate kilobytes, or m or M to indicate megabytes. The default value is 64MB. The upper limit for this value will be approximately 4000m on Solaris 7 and Solaris 8 SPARC platforms and 2000m on Solaris 2.6 and x86 platforms, minus overhead amounts. Examples:

-Xmx83886080
-Xmx81920k
-Xmx80m

Sample of XML date type

as long as the xml can be converted into table, thing becomes familiar and simpler

--1.orange values like xxxxxx into table
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE xmlTest
@msg xml
AS
BEGIN
declare @tmpTable table(col1 int,col2 varchar(20))
insert into @tmpTable(col1,col2)
select tbl.col.value('./abc[1]','int'),tbl.col.value('./d[1]','varchar(20)')
from @msg.nodes('/test') as tbl(col)
select * from @tmpTable
END
GO

exec xmlTest '12345testing88888test888'

--2.orange values like into table
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE xmlTest
@msg xml
AS
BEGIN
declare @tmpTable table(col1 int,col2 varchar(20))
insert into @tmpTable(col1,col2)
select tbl.col.value('@abc','int'),tbl.col.value('@d','varchar(20)')
from @msg.nodes('/test') as tbl(col)
select * from @tmpTable
END
GO

exec xmlTest exec xmlTest ''

--accept string instead
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE xmlTest
@msgStr varchar(MAX)
AS
BEGIN
declare @msg xml
set @msg=@msgStr
CREATE PROCEDURE xmlTest
@msgStr varchar(MAX)
AS
BEGIN
declare @msg xml
set @msg=@msgStr
declare @tmpTable table(col1 int,col2 varchar(20))
insert into @tmpTable(col1,col2)
select tbl.col.value('@abc','int'),tbl.col.value('@d','varchar(20)')
from @msg.nodes('/test') as tbl(col)
select * from @tmpTable
END
GO

Drop columns with default constraint in SQL Server 2000

here's a sample I used to drop all the columns with default constraint in a table. it can be further changed to work better.

declare dfName cursor for
SELECT o2.name,c.name
FROM syscolumns c
JOIN sysobjects o ON c.id = o.id
JOIN sysobjects o2 ON c.cdefault = o2.id
WHERE o.name = 'table name'

open dfName
declare @dn varchar(100),@cn varchar(50)
declare @tSQL varchar(300)
fetch next from dfName into @dn,@cn
print @@fetch_status
print @dn+':'+@cn
while (@@fetch_status<>-1)
begin
set @tSQL='alter table tablename drop constraint '+@dn+' alter table tablename drop column '+@cn
exec(@tSQL)
fetch next from dfName into @dn,@cn
print @@fetch_status
print @dn+':'+@cn
end
close dfName
deallocate dfName