SUMMARY
This article describes how to programmatically specify the
client network library in the connection string when you connect to a SQL
Server database.
In Microsoft Data Access Components (MDAC) 2.6 and
later, you can specify the client access library by using the
server name parameter in connection string.
Therefore, you can specify a specific client access library when you are
prompted by an application for a server name to which to connect. This behavior
can be very useful when you are testing and troubleshooting connectivity issues
for SQL Server.
For example, you can use the
Osql command-line utility to connect to SQL Server and to force it to
use the TCP/IP network library:
osql -Stcp:myServer,portNumber -E
back to the top
Code Sample
The following Microsoft Visual C# .NET code sample demonstrates
how to set the connection string. The connection string has the same format
irrespective of the language that you use:
using System;
using System.Data;
using System.Data.SqlClient;
namespace getCurrentProtocol
{
/// <summary>
/// Main Application Driver Class
/// </summary>
class Driver
{
static void Main(string[] args)
{
string sCxn = "server=myServer;Integrated Security=SSPI; database=master";
//string sCxn = "server=np:myServer;Integrated Security=SSPI; database=master";
//string sCxn = "server=tcp:myServer;Integrated Security=SSPI; database=master";
//string sCxn = "server=rpc:myServer;Integrated Security=SSPI; database=master";
//string sCxn = "server=lpc:myServer;Integrated Security=SSPI; database=master";
string sCmd = "SELECT net_library from sysprocesses where spid=@@spid";
SqlConnection cxn = new SqlConnection(sCxn);
SqlCommand sqlCmd = new SqlCommand(sCmd, cxn);
SqlDataAdapter sqlDa = new SqlDataAdapter(sCmd, cxn);
DataTable dt = new DataTable();
try
{
sqlDa.Fill(dt);
Console.WriteLine("Hit ENTER to continue ...");
Console.ReadLine();
foreach (DataRow dr in dt.Rows)
Console.WriteLine(dr["net_library"]);
}
catch (SqlException e)
{
Console.WriteLine(e.StackTrace);
Console.WriteLine("SQL Error Number: " + e.Number);
Console.WriteLine("SQL Error Message: " + e.Message);
}
}
}
}
Note the connection string and particularly the value of the
server parameter:
string sCxn = "server=myServer;Integrated Security=SSPI; database=northwind"
back to the top
Use the Code Sample with Various Network Libraries
The following code samples demonstrate how to use the value of
the
server parameter to specify various network libraries:
- TCP/IP:
server=tcp:hostname
You can optionally specify a specific port number. By default, the port
is 1433.server=tcp:hostname, portNumber
- Named Pipes:
server=np:hostname
You can optionally specify a specific named pipe.
server=np:\\hostname\pipe\pipeName
By default, the pipe name is sql\query. If you connect to a named
instance, the pipe name is typically in the following format:MSSQL$instnaceName\sql\query
- Multiprotocol:
server=rpc:hostname
You can optionally specify the underlying protocol.
server=rpc:hostname, protocol
The default value of the underlying protocol is determined by the
operating system settings where a protocol can have any one of the following
values:
|
ncacn_np | Named Pipes |
ncacn_ip_tcp | Transmission Control
Protocol/Internet Protocol (TCP/IP) |
ncacn_nb_nb | Network basic input/output system
(NetBIOS) over NetBIOS Enhanced User Interface (NetBEUI) |
ncacn_spx | Sequenced Packet Exchange (SPX) |
ncacn_vns_spp | Banyan VINES |
ncadg_ip_udp | User Datagram Protocol (UDP)
datagram TCP/IP |
ncadg_ipx | Internetwork Packet Exchange (IPX)
datagram IPX |
ncalrpc | Local procedure call |
- Shared Memory:
server=lpc:hostname
- NWlink IPX/SPX:
server=spx:hostname
- Banyan VINES:
server=vines:hostname
- Apple Talk:
server=adsp:hostname
back to the top