Tuesday, April 14, 2009

SQL Native Client (SQLNCLI )

SQl server uses a new database provider model. This provider succeeds OLEDB. OLEDB
was used for SQL Server databases. Now even it's possible to access MS Access databases
that way. ODBC is only for backward compatability. The new SQLNCLI provider has
the following new features

  • - Support for the new SLQ 2005 large datatypes (Max String, with no limited length)
  • - XML Datatype (Not just a handle, but a real type)
  • - Custom Datatypes (UDT)
  • - The new snapshot isolation level.This level does not lock dirty reads. It uses
    optimistic (locking). It's the best of both worlds. Enabled to read all actual committed
    data, skipping locks, but reading the data (dirty reads),and persisting data if
    the data is not locked.
  • - Supprot for multiple active recordsets. It was already possible to get multiple
    recordsets. In Ado.Net, with movenext, the next result could be read. But now, it's
    possible to have multiple connections open. So each recordset (multiple retrieval)
    can have it's own connection.
  • - Password can be changed at the client, without the need of the administrator.
  • - Support for asynchoon operations. Lots of complex database actions take a time.
    Now SQL server can create threads to do jobs on its own.
  • - It supports SQLXML 4.0. The new connection string (just another provider) "Provider=SQLNCLI;Server=(local);Database=Northwind;IntegratedSecurity=SSPI;" For DSN connections (who uses them still?) "Driver={SQL Native Client}; Server=(local); Database=Northwind; Trusted_Connection=yes;"

2 comments:

Ideal SQL Query For Handling Error & Transcation in MS SQL

BEGIN TRY BEGIN TRAN --put queries here COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT>0 BEGIN SELECT @@ERROR,ERRO...