Useful Operations Manager 2007 SQL queries

 

 

http://blogs.technet.com/b/kevinholman/archive/2007/10/18/useful-operations-manager-2007-sql-queries.aspx

System Admin but no rights to log into Sql server

After sql server 2008, MS removed system Admin to the access list of sql server automatically. If you did not add to the installation step, you will have trouble. Blog below will help you on this issue

http://element533.blogspot.ca/2010/01/breaking-into-sql-server-using-local.html

Exception handling and nested transactions

This template below is very useful if you decide to add Transactions into your Store Procedure. Your SP could be called by other scripts and rollback may be issued inside the script. You need to use Safepoint if in this case. However, if there is no transaction exists, using savepoint will cause errors.

Another good article abuot Transaction Savepoints;  http://www.blackwasp.co.uk/SQLSavepoints.aspx

Template for error handling and nested transactions in Store Procedure

Original URL: http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/

create procedure [usp_my_procedure_name]
as
begin
	set nocount on;
	declare @trancount int;
	set @trancount = @@trancount;
	begin try
		if @trancount = 0
			begin transaction
		else
			save transaction usp_my_procedure_name;

		-- Do the actual work here
	
lbexit:
		if @trancount = 0	
			commit;
	end try
	begin catch
		declare @error int, @message varchar(4000), @xstate int;
		select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
		if @xstate = -1
			rollback;
		if @xstate = 1 and @trancount = 0
			rollback
		if @xstate = 1 and @trancount > 0
			rollback transaction usp_my_procedure_name;

		raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
	end catch	
end
go

@@Trancount and XACT_STATE()

Recently, I’ve encountered code that includes both @@Trancount and XACT_STATE().

 

XACT_STATE() is a scalar function that gives the user transaction state of a current running request. It indicates whether the request has an active user transaction, and whether the transaction is capable of being committed or not.

XACT_STATE returns the following three values

• 1: The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.

• 0: There is no active user transaction for the current request.

• -1: The current request has an active user transaction, but an error has occurred that has
  caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.

So before commit or rollback always test XACT_STATE for 0, 1, or -1.

• If 1, the transaction is committable.
• If -1, the transaction is uncommittable and should be rolled back.
• If 0, there is no transaction and a commit or rollback operation will generate an error.

 

Below is the difference between them

both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction.

@@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction.

XACT_STATE cannot be used to determine whether there are nested transactions.

URL:  http://www.advancesharp.com/blog/1017/sql-transaction-status-and-xact-state

test from my new pc

test 1 2 3