SQL Server 2000
It is a full-featured relational database management system (RDBMS) that offers
a variety of administrative tools to ease the burdens of database development,
maintenance and administration.
Microsoft SQL Server 2000 is an application used to create computer databases
for the Microsoft Windows family of server operating systems. Microsoft SQL
Server provides an environment used to generate databases that can be accessed
from workstations, the Internet, or other media such as a personal digital
assistant (PDA). It provides the following features:
- Variety of User Interfaces
- Physical data independence
- Logical data independence
- Query optimization
Providers for SQL Server 2000, 7.0
- .NET Framework Data Provider for SQL Server
- .NET Framework Data Provider for OLE DB (OleDbconnection)
- .NET Framework Data Provider for ODBC (Odbcconnection)
- Microsoft OLE DB Provider for SQL Server
- Microsoft SQL Server ODBC Driver
- SQL Server Native Client 9.0 OLE DB Provider
- SQL Server Native Client 9.0 ODBC Driver
- SQL Server Native Client 10.0 OLE DB Provider
- SQL Server Native Client 10.0 ODBC Driver
- SQLXML 3.0 OLEDB Provider
- MSDataShape
.NET Framework Data Provider for SQL Server
A .NET Framework data provider is used for connecting to a database, executing
commands, and retrieving results. Those results are either processed directly,
placed in an ADO.NET Dataset in order to be exposed to the user in an ad hoc
manner, combined with data from multiple sources, or remoted between tiers. It
Provides data access for Microsoft SQL Server version 7.0 or later. Uses the
System.Data,sqlclient namespace.
This is the number one to use if we want our .NET application or website to
connect to an SQL Server.
The .NET Framework Data Provider for SQL Server uses its own protocol to
communicate with SQL Server. It is lightweight and performs well because it is
optimized to access a SQL Server directly without adding an OLE DB or Open
Database Connectivity (ODBC) layer.
Sample Code
using System.Data.SqlClient;
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
//execute queries, etc
myConnection.Close();
Standard Security
Syntax:
Data Source=urServerAddress;Initial
Catalog=urDataBase;UserId=urUsername;
Password=urPassword;
OR:
Server=urServerAddress;Database=urDataBase;UserID=urUsername;Password=urPassword;
Trusted_Connection=False;
Trusted Connection
Syntax:
Data Source=urServerAddress;Initial
Catalog=urDataBase;Integrated
Security=SSPI;
OR:
Server=urServerAddress;Database=urDataBase;Trusted_Connection=True;
Using SQL Server instance
Syntax:
Server=urServerName\theInstanceName;Database=urDataBase;Trusted_Connection=True;
Using IP address,Port
Syntax:
Data Source=192.168.1.11,1433;Network
Library=DBMSSOCN;Initial
Catalog=urDataBase;
User ID=urUsername;Password=urPassword;
Asynchronous processing
Syntax:
Server=urServerAddress;Database=urDataBase;Integrated
Security=True;Asynchronous
Processing=True;
Database mirroring
Syntax:
Data Source=urServerAddress;Failover
Partner=urMirrorServerAddress;
Initial Catalog=urDataBase;Integrated
Security=True;
Using a database file SQL Server Express instance
Syntax:
Server=.\SQLExpress;AttachDbFilename=c:\database\testproject\urdbfile.mdf;Database=urdbname;Trusted_Connection=Yes;
Using the data directory SQL Server Express instance
Syntax:
Server=.\SQLExpress;AttachDbFilename=|DataDirectory|urdbfile.mdf;Database=dbname;Trusted_Connection=Yes;
Using an User Instance on SQL Server Express instance
Syntax:
Data Source=.\SQLExpress;Integrated
Security=true; AttachDbFilename=|DataDirectory|\urdb.mdf;User
Instance=true;
The User Instance functionality creates a new SQL Server instance on the fly
during connects.
Enabling MARS (Multiple Active Result Sets)
Syntax:
Server=urServerAddress;Database=urDataBase;Trusted_Connection=True;
MultipleActiveResultSets=true;
Trusted Connection from a CE device
Syntax:
Data Source=urServerAddress;Initial
Catalog=urDataBase;Integrated
Security=SSPI;
User ID=urDomain\urUsername;Password=urPassword;
.NET Framework Data Provider for OLE DB
The .NET Framework Data Provider for OLE DB is recommended for middle-tier
applications using Microsoft SQL Server 6.5 or earlier, or any OLE DB provider
that supports the OLE DB interfaces listed in OLE DB Interfaces Used by the OLE
DB .NET Data Provider in the .NET Framework SDK. The .NET Framework Data
Provider for OLE DB automatically pools connections using OLE DB session
pooling. Connection string arguments can be used to enable or disable OLE DB
services including pooling.
Bridging to OLE DB Provider for SQL Server
Syntax:
Provider=SQLOLEDB;Data
Source=myServerAddress;Initial
Catalog=myDataBase;UserId=myUsername;
Password=myPassword;
.NET Framework Data Provider for ODBC
The .NET Data Provider for ODBC access to data sources that are connected to
using an ODBC driver.
The ODBC .NET Data Provider is an add-on component to the .NET Framework. It
provides access to native ODBC drivers the same way that the OLE DB .NET Data
Provider provides access to native OLE DB Providers A .NET Framework data
provider also serves as a bridge between a data source and an ADO.NET DataSet.
For data sources exposed by using ODBC. Uses the System.Data.Odbc namespace.
Sample Code
using System.Data.Odbc;
OdbcConnection myConnection = new OdbcConnection();
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
//execute queries, etc
myConnection.Close();
Bridging to SQL Native Client 10.0 ODBC Driver
Syntax:
Driver={SQL
Server Native Client 10.0};Server=urServerAddress;Database=urDataBase;
Uid=urUsername;Pwd=urPassword;
Microsoft OLE DB Provider for SQL Server
The Microsoft OLE DB Provider for SQL Server allows ADO to access Microsoft SQL
Server, and it translate character data from the client code page to the server code page
automatically by default.
Using the OLE DB Provider for SQL Server, SQL Server distributed queries can
query data in remote instances of SQL Server.
Code
Include "Provider=sqloledb" in the connection string to use this provider.
Standard security
Syntax:
Provider=sqloledb;Data
Source=urServerAddress;Initial
Catalog=urDataBase;User
Id=urUsername;Password=urPassword;
Trusted
connection
Syntax:
Provider=sqloledb;Data
Source=urServerAddress;Initial
Catalog=urDataBase;Integrated
Security=SSPI
Disable connection pooling
Syntax:
Provider=sqloledb;Data
Source=urServerAddress;Initial
Catalog=urDataBase;User
ID=urUsername;Password=urPassword;OLE
DB Services=-2;
Disable connection
pooling is useful while receiving the errors
Using SQL Server Instance
Syntax:
Provider=sqloledb;Data
Source=urServerName\theInstanceName;Initial
Catalog=urDataBase;Integrated
Security=SSPI;
Using IP address,Port
Syntax:
Provider=sqloledb;Data
Source=192.168.1.11,1433;Network
Library=DBMSSOCN;Initial
Catalog=urDataBase;User
ID=urUsername;Password=urPassword;
Prompt for username and password
Syntax:
oConn.Provider = "sqloledb"oConn.Properties("Prompt") =
adPromptAlways
Data Source=urServerAddress;Initial
Catalog=urDataBase;
Microsoft SQL Server for ODBC Driver
ODBC-SQL Server Driver provides ODBC access to Microsoft SQL Server from Linux
and Unix platforms . The ODBC-SQL Server Driver supports SQL Server 7.0, SQL
Server 2000, SQL Server 2005, SQL Server 2008 and SQL Server Express. ODBC-SQL
Server Driver to connect to SQL Server from Linux distributions such as Debian,
Fedora, Mandrake, Red Hat, SUSE and Ubuntu and Unix platforms such as AIX, HP-UX
and Solaris
Standard security
Syntax:
Driver={SQL
Server};Server=urServerAddress;Database=urDataBase;Uid=urUsername;
Pwd=urPassword;
Trusted
connection
Syntax:
Driver={SQL
Server};Server=urServerAddress;Database=urDataBase;Trusted_Connection=Yes;
Prompt for username and password
Syntax:
oConn.Properties("Prompt") = adPromptAlways
Driver={SQL
Server};Server=urServerAddress;Database=urDataBase;
SQL Server Native Client 9.0 OLE DB Provider
Microsoft SQL Client Server Native client 9.0 contains the SQL OLE DB provider
and SQL ODBC driver in one native dynamic link library (DLL) supporting
applications using native-code APIs (ODBC, OLE DB and ADO) to Microsoft SQL
Server.
The SQL Server Native Client 9.0 OLE DB provider is a native, high performance
provider that accesses the SQL Server Tabular Data Stream (TDS) protocol
directly.
Code
Include "Provider=SQLNCLI" in the connection string to use this provider.
Standard security
Syntax:
Provider=SQLNCLI;Server=urServerAddress;Database=urDataBase;Uid=urUsername; Pwd=urPassword;
Trusted
connection
Syntax:
Provider=SQLNCLI;Server=urServerAddress;Database=urDataBase;
Trusted_Connection=yes;
Using SQL Server Instance
Syntax:
Provider=SQLNCLI;Server=urServerName\theInstanceName;Database=urDataBase;
Trusted_Connection=yes;
Prompt for username and password
Syntax:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Provider=SQLNCLI;Server=urServerAddress;DataBase=urDataBase;
Encrypt data sent over network
Syntax:
Provider=SQLNCLI;Server=urServerAddress;Database=urDataBase;Trusted_Connection=yes;
Encrypt=yes;
SQL Server Native Client 9.0 ODBC Driver
SQL Server Native Client 9.0 ODBC Driver supports applications written
using earlier versions of ODBC in the manner defined in the ODBC 3.51
specification. Following versions of SQL are suppoted by the ODBC Driver:
Microsoft SQL Server 7.0
Microsoft SQL Server 2000
Microsoft SQL Server 2005
Microsoft SQL Server 2008
SQL Server Native Client 9.0 ODBC Driver supports the following Operating
System:
Microsoft Windows 2000 Service Pack 4 or later
Microsoft Windows Server 2003 or later
Microsoft Windows XP Service Pack 1 or later
Microsoft Windows Vista
Microsoft Windows 2008 Server
Programs that are written using the SQL Native Client ODBC driver communicate
with SQL Server through function calls
code
Include "Driver={SQL Native Client}" in the connection string to use this
driver.
Standard security
Syntax:
Driver={SQL
Native Client};Server=urServerAddress;Database=urDataBase;Uid=urUsername;
Pwd=urPassword;
Trusted
connection
Syntax:
Driver={SQL
Native Client};Server=urServerAddress;Database=urDataBase;
Trusted_Connection=yes;
Using SQL Server Instance
Syntax:
Driver={SQL
Native Client};Server=urServerName\theInstanceName;Database=urDataBase;
Trusted_Connection=yes;
Prompt for username and password
Syntax:
oConn.Properties("Prompt") = adPromptAlways
Driver={SQL
Native Client};Server=urServerAddress;Database=urDataBase;
Encrypt data sent over network
Syntax:
Driver={SQL
Native Client};Server=myServerAddress;Database=myDataBase;
Trusted_Connection=yes;Encrypt=yes;
SQL Server Native Client 10.0 OLE DB Provider
Microsoft SQL Server 2000 Native Client (SQL Native Client) is a single
dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC
driver. Native Client should be used to create new applications or enhance
existing applications that need to take advantage of new SQL Server 2008
features such as Multiple Active Result Sets (MARS), User-Defined Types (UDT),
and XML data type support. While also providing new functionality above and
beyond that supplied by the Microsoft Data Access Components (MDAC such as
Multiple Active Result Sets (MARS), User-Defined Types (UDT), and XML data type
support.
This redistributable installer for SQL Native Client installs the client
components needed during run time to take advantage of new SQL Server 2005
features, and optionally installs the header files needed to develop an
application that uses the SQL Native Client API.
Code
Include "Provider=SQLNCLI10" in the connection string to use this provider.
Standard Security
Syntax:
Provider=SQLNCLI10;Server=urServerAddress;Database=urDataBase;Uid=urUsername;
Pwd=urPassword;
Trusted Connection
Syntax:
Provider=SQLNCLI10;Server=urServerAddress;Database=urDataBase; Trusted_Connection=yes;
Using SQL Server Instance
Syntax:
Provider=SQLNCLI10;Server=urServerName\theInstanceName;Database=urDataBase;
Trusted_Connection=yes;
Prompt for username and password
Syntax:
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Provider=SQLNCLI10;Server=urServerAddress;DataBase=urDataBase;
Encrypt data sent over network
Syntax:
Provider=SQLNCLI10;Server=urServerAddress;Database=urDataBase;Trusted_Connection=yes;
Encrypt=yes;
SQL Server Native Client 10.0 ODBC Driver
The SQL Server Native Client 10.0 driver supports connecting to SQL 7.0 and
later.
SQL Server supports ODBC, via the SQL Server Native Client ODBC driver, as one
of the native APIs for writing C, C++, and Microsoft Visual Basic applications
that communicate with SQL Server. The SQL Server-specific versions of the ODBC
functions are implemented in the SQL Server Native Client ODBC driver. The
driver passes SQL statements to SQL Server and returns the results of the
statements to the application.
Code
Include "Driver={SQL Server Native Client 10.0}" in the connection string to use
this driver.
Standard security
Syntax:
Driver={SQL
Server Native Client 10.0};Server=urServerAddress;Database=urDataBase;
Uid=urUsername;Pwd=urPassword;
Trusted
Connection
Syntax:
Driver={SQL
Server Native Client 10.0};Server=urServerAddress;Database=urDataBase;
Trusted_Connection=yes;
Using SQL
Server Instance
Syntax:
Driver={SQL
Server Native Client10.0};Server=urServerName\theInstanceName;Database=
urDataBase;Trusted_Connection=yes;
Prompt for
username and password
Syntax:
oConn.Properties("Prompt") = adPromptAlways
Driver={SQL
Server Native Client 10.0};Server=urServerAddress;Database=urDataBase;
Encrypt data
sent over network
Syntax:
Driver={SQL
Server Native Client10.0};Server=urServerAddress;Database=urDataBase;
Trusted_Connection=yes;Encrypt=yes;
SQL XML 4.0 OLE DB Provider
SQLXML 3.0 includes client-side XML processing, which converts a relational
result set to a hierarchical XML document Format on the client-side eg:- If you
call a SELECT FOR XML Query, with client-side XML formatting enabled, only the
select statement (without the FOR XML) is passed to SQL Server. The row set is
then converted to an XML document by SQLXML on the client workstation. It
restricts the data provider to SQLOLEDB only.
Code
Include "Provider=SQLXMLOLEDB.3.0;Data Provider=sqloledb" in the connection
string to use this provider.
Using SQL Server OLE DB
Syntax:
Provider=SQLXMLOLEDB.3.0;Data
Provider=SQLOLEDB;Data
Source=urServerAddress;Initial
Catalog=urDataBase;User
Id=urUsername;Password=urPassword;
MSDataShape
MSDataShape is used to create hierarchial Recordsets, so that we can be able to
browse relational data in a convenient way.
ADO support for data shaping enables you to query a data source and return a
Recordset in which a (parent) record represents a (child) Recordset. data
shaping in ADO allows you to create new Recordset objects without an underlying
data source by using the NEW keyword to describe the fields of the parent and
child Recordsets.
Code
Include "Provider=MSDataShape;Data Provider=providername" in the connection
string to use this wrapper COM component.
MSDataShape
Syntax:
Provider=MSDataShape;Data
Provider=SQLOLEDB;Data
Source=urServerAddress;Initial
Catalog=urDataBase;User
ID=urUsername;Password=urPassword;