How to Handle Exception and Error in SQL Server 2000 and SQL Server 2005 (Transactions – Rollback Transaction, Commit Transaction)

SQL Server 2000 doesn’t have a standard and structured way to handle exception in SQL Query. However, we can user the standard error variable @@ERROR to check whether any error has occurred in order to take the necessary action.

Example for SQL Server 2000 to handle exception:-

BEGIN TRANSACTION
DECLARE @Err AS INT
-- Put your statements here.
SET @Err = @@ERROR
IF @Err <> 0
  BEGIN
    ROLLBACK TRANSACTION
    SELECT 'The transaction is rollbacked due to exception or error.' AS RESULT
  END
ELSE
  BEGIN
  COMMIT TRANSACTION
  SELECT 'The Transaction is committed.' AS RESULT
  END

However, SQL Server 2005 has come with a structured way to handle the exception (Try-Catch blocks). This structure is very similar to those used in C#, VB.Net and Java.

Example for SQL Server 2005 to handle exception :-

BEGIN TRY
  BEGIN TRANSACTION
  -- Put your statements here.
  COMMIT TRANSACTION
  SELECT 'The Transaction is committed.' AS RESULT
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
  SELECT 'The transaction is rollbacked due to exception or error.' AS RESULT
END CATCH

Query a field size in Database (SQL Server)

If you want to query the size in bytes for a record or field in database (SQL Server), you can use the DATALENGTH function.
The DATALENGTH function can return the length in bytes for any data types including text, ntext, image and varbinary.

For example you want to know the size of Field1 in TableA just run the following query:

SELECT Field1, DATALENGTH(Field1) AS [Size Field1 in bytes] FROM TableA

The output will be the value of Field1 and it's size in bytes for each rows record in TableA

Syntax Differences of C# and VB.NET

Sometimes, I need to convert the codes from C# to VB.NET or from VB.NET to C#. So, I need to know some of the syntax differences of C# and VB.NET. The Table below shows some syntax differences of them:

C#	VB.NET	Description
using	Imports	Allows methods to be called without fully qualifying with Namespace name
this	Me	Refer to the current object
base	MyBase	Refer to the base class
static	Shared	Specifies that a member is shared by all instances of a class. To call the member, an instance of the class is not required.
no equivalent	Static	Specifies that a local variable's value is preserved between calls.
not required	ByVal	Passing parameters by value
ref	ByRef	Passing parameters by reference
enum	Enum	Declare an enumerator
struct	Structure	Declare a structure
obj == null	obj = Nothing	Check whether an object is not ponit to anything
on by default and not changable	Option Explicit	Specifies that all variables must be declared.
delegate	AddressOf	Use the address of a method
no equivalent	With  ... End With	Evaluate an object one and use many times.
int[] x = new int[4] {1,2,3,4};	Dim a() as	Initialise an array.
lock	SynLock	Threading primitives.
sealed	NotInheritable	Specifies that a class cannot be used as a base class (cannot be inherited).
sealed	NotOverridable	Specifies that a method cannot be overriden.
abstract	MustInherit	Specifies that a class can only be inherited (an instance of the class cannot be created).
abstract	MustOverride	Specifies that a method must be implemented in a derived class.
virtual	Overridable	Specifies that a member can be overriden
override	Overrides	Specifies that a member is overriding another member.
not required	Overloads	Specifies that a member is overloading another member.
class Class1:I1	Implements I1	Specifies that the class (Class1) implements the interface I1.
class Class1:BaseClass1	Inherits BaseClass1	Specifies that the class (Class1) inherits class BaseClass1.
Class1	New	Constructor method, called when object is created. Class1 is classname.
~Class1	Finalize	Method called by system just before garbage collection reclaims object, known as destrutor method. Class1 is class name.

Err Msg: “The system cannot log you on to this domain because the system’s machine account in its primary domain is missing or the password..."

Error Message: “The system cannot log you on to this domain because the system’s machine account in its primary domain is missing or the password on that account is incorrect”

Today, I need to login my ex-colleague’s PC to setup something at that PC. The PC had not been used anyone for a few months. When I trying to log on to the domain, the error message mentioned in above is displayed. However, I can log into locally.

I tried to do some configuration setting of the PC but the issue still not been solved. After that, I tried to search with the error message in Google and find the solution to resolve the issue.

Solution:
If you face this problem, determine whether the PC can be logged into locally. If can, try to perform the following steps to resolve the issue:

Remove the Server from the Domain:

1. Click [Start], point to [Settings], click [Control Panel], and double-click [System]
   
2. On the [Network Identification] tab, click [Properties]
   
3. Under [Member Of], click [Workgroup], type the name of a workgroup to join, then click [OK]
   
4. Click [OK] again
   
5. Restart the PC

Add the Server back to the Domain:

1. Click [Start], point to [Settings], click [Control Panel] then double click [System]
   
2. On the [Network Identification] tab, click [Properties]
   
3. Under [Member Of], click [Domain], type the name of a workgroup to join, then click [OK]
   When prompted, provide a user name and password, and join the PC to the domain.
   
4. Restart the PC

Then try to log on the domain, the problem solved.

Different between Authorization and Authentication (.NET)

Authentication is the mechanism where a system uses to identify their user. In ASP .NET, the authentication may be set in web.config file to verify the credentials of the user and decides whether the user is authenticated or not.

Authorization is the mechanism where a system uses to determine what level of access for a particular user.

So, when a user logs on to a system, the user will be authenticated first before be authorized.

Ways to authenticate user in ASP .NET:

1. Forms Authentication
   This authentication depends on code written in the system. Credentials are entered on web forms then used to match with the user information in the database table.
   
2. Windows Authentication
   This authentication is the default authentication provider for ASP .NET application. The user logs in to an application using this authentication, the credentials are matched with the Windows domain through IIS.
   There are 4 types of Window Authentication methods:
   
   • Anonymous Authentication – IIS allows any user
     
   • Basic Authentication – windows username and password (credentials) has to be sent across the network in plain text format (not very secure).
     
   • Digest Authentication – Same as Basic Authentication but the credentials are encrypted.
     
   • Integrated Windows Authentication – Depend on Kerberos technology, with strong credential encryption.
3. Passport Authentication
   This authentication already obsolete in .NET 2.0
   
4. “None” mean no authentication

Types Authorization in .NET

1. File Authorization
   Relying on the NTFS system for granting permission
   
2. Url Authorization
   Specify authorization rules in web.config for different web URLs.