jtds 连接Sql Server 2005数据库

使用java连接sqlserver 2005 搞了好久，终于连接上了，记录如下：
设置SQLEXPRESS服务器:

a. 打开SQL Server Configuration Manager -> SQLEXPRESS的协议 -> TCP/IP
b.右键单击启动 TCP/IP 
c.双击进入属性,把IP地址中的IP all中的TCP端口设置为 1433
d.重新启动SQL Server 2005服务中的SQLEXPRESS服务器
e. 关闭SQL Server Configuration Manager
f.起动 Sql Server(SqlExpress)服务 。
一定不要忘记了，启动 Sql server browser服务。我用.net 连接是不用启动的，java的就是不行，非启动不可.
就是这个异常了：org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Unable to get information from SQL Server: localhost.)


java 连接：
连接串：jdbc:jtds:sqlserver://localhost:1433;instance=SQLEXPRESS;DatabaseName=test
或 jdbc:jtds:sqlserver://localhost:1433/test;instance=SQLEXPRESS
driverClassName="net.sourceforge.jtds.jdbc.Driver"

查找的jtds原文如下：http://jtds.sourceforge.net/faq.html

What is the URL format used by jTDS?

The URL format for jTDS is:

    jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]

where <server_type> is one of either 'sqlserver' or 'sybase' (their meaning is quite obvious), <port> is the port the database server is listening to (default is 1433 for SQL Server and 7100 for Sybase) and <database> is the database name -- JDBC term: catalog -- (if not specified, the user's default database is used). The set of properties supported by jTDS is:

appName (default - "jTDS" )

Application name. No practical use, it's displayed by Enterprise Manager or Profiler associated with the connection.

batchSize (default - 0 for SQL Server; 1000 for Sybase)

Controls how many statements are sent to the server in a batch. The actual batch is broken up into pieces this large that are sent separately. The reason for this is to avoid Sybase "hangs" caused by running out of space with very large batches. The problem doesn't seem to occur with SQL Server, hence the default limit of 0 (unlimited) in this case.

bindAddress (default - determined by the Java implementation; requires Java 1.4 or later)

Specifies the local IP address to bind to for outgoing TCP/IP connections to the database. Useful for multi-homed systems (those with more than one external IP address) where the default IP address picked by Java will not connect to the database. Currently has no effect when using named pipes to connect to a database (see namedPipe ). Since after jTDS-1.2.

bufferDir (default - System.getProperty("java.io.tmpdir") )

Controls the destination where data is buffered to disk.
See also bufferMaxMemory and bufferMinPackets .

bufferMaxMemory (default - 1024 )

Controls the global buffer memory limit for all connections (in kilobytes). When the amount of buffered server response packets reaches this limit additional packets are buffered to disk; there is however one exception: each Statement gets to buffer at least <bufferMinPackets> to memory before this limit is enforced. This means that this limit can and will usually be exceeded.
Server responses are buffered to disk only when a request is made on a Statement while another Statement belonging to the same Connection still hasn't processed all its results. These situations can be avoided in most cases by setting the useCursors property, but this will also affect performance.
See also bufferMinPackets .

bufferMinPackets (default - 8 )

Controls the minimum number of packets per statement to buffer to memory. Each Statement will buffer at least this many packets before being forced to use a temporary file if the <bufferMaxMemory> is reached, to ensure good performance even when one Statement caches a very large amount of data.
Server responses are buffered to disk only when a request is made on a Statement while another Statement belonging to the same Connection still hasn't processed all its results. These situations can be avoided in most cases by setting the useCursors property, but this will also affect performance.
See also bufferMaxMemory .

cacheMetaData (default - false )

When used with prepareSQL=3, setting this property to true will cause the driver to cache column meta data for SELECT statements. Caching the meta data will reduce the processing overhead when reusing statements that return small result sets that have many columns but may lead to unexpected errors if the database schema changes after the statement has been prepared. Use with care. Only applicable to SQL Server (there is no prepareSQL=3 mode for Sybase).

charset (default - the character set the server was installed with)

Very important setting, determines the byte value to character mapping for CHAR /VARCHAR /TEXT values. Applies for characters from the extended set (codes 128-255). For NCHAR /NVARCHAR /NTEXT values doesn't have any effect since these are stored using Unicode.

domain

Specifies the Windows domain to authenticate in. If present and the user name and password are provided, jTDS uses Windows (NTLM) authentication instead of the usual SQL Server authentication (i.e. the user and password provided are the domain user and password). This allows non-Windows clients to log in to servers which are only configured to accept Windoes authentication.
If the domain parameter is present but no user name and password are provided, jTDS uses its native Single-Sign-On library and logs in with the logged Windows user's credentials (for this to work one would obviously need to be on Windows, logged into a domain, and also have the SSO library installed -- consult README.SSO in the distribution on how to do this).

instance

Named instance to connect to. SQL Server can run multiple so-called "named instances" (i.e. different server instances, running on different TCP ports) on the same machine. When using Microsoft tools, selecting one of these instances is made by using "<host_name>\<instance_name>" instead of the usual "<host_name>". With jTDS you will have to split the two and use the instance name as a property.

lastUpdateCount (default - true )

If true only the last update count will be returned by executeUpdate() . This is useful in case you are updating or inserting into tables that have triggers (such as replicated tables); there's no way to make the difference between an update count returned by a trigger and the actual update count but the actual update count is always the last as the triggers execute first. If false all update counts are returned; use getMoreResults() to loop through them.

lobBuffer (default - 32768 )

The amount of LOB data to buffer in memory before caching to disk. The value is in bytes for Blob data and chars for Clob data.

loginTimeout (default - 0 for TCP/IP connections or 20 for named pipe connections)

The amount of time to wait (in seconds) for a successful connection before timing out.
If a TCP/IP connection is used to connect to the database and Java 1.4 or newer is being used, the loginTimeout parameter is used to set the initial connection timeout when initially opening a new socket. A value of zero (the default) causes the connection to wait indefinitely, e.g.,until a connection is established or an error occurs. See also socketTimeout .
If a named pipe connection is used (namedPipe is true ) and loginTimeout is greater than zero, the value of loginTimeout is used for the length of the retry period when "All pipe instances are busy" error messages are received while attempting to connect to the server. If loginTimeout is zero (the default), a value of 20 seconds is used for the named pipe retry period.

macAddress (default - "000000000000" )

Network interface card MAC address. It's displayed by Enterprise Manager or Profiler associated with the connection and is needed to resolve some issues regarding the number of clients allowed by the SQL Server license. The MAC address cannot be determined automatically from Java (i.e. without using native code) so you'll have to specify it yourself if you need it.

maxStatements (default - 500 )

The number of statement prepares each connection should cache. A value of 0 will disable statement caching. A value of Integer.MAX_VALUE (2147483647 ) will enable fast caching (uses less memory and has no overhead associated with removing statements); the cache will never release any cached statements, so although experience has shown that this is usually not a problem with most applications, use with care.

namedPipe (default - false )

When set to true , named pipe communication is used to connect to the database instead of TCP/IP sockets. When the os.name system property starts with "windows" (case-insensitive), named pipes (both local and remote) are accessed through the Windows filesystem by opening a RandomAccessFile to the path. When the SQL Server and the client are on the same machine, a named pipe will usually have better performance than TCP/IP sockets since the network layer is eliminated. Otherwise the JCIFS library is used. JCIFS provides a pure Java named pipe implementation and uses NTLM authentication, so the domain parameter is required.
This feature supports the instance parameter (which changes the named pipe URL), but it does not currently support the named pipe at a location other than /sql/query on the server. The port parameter is ignored if set.

packetSize (default - 4096 for TDS 7.0/8.0; 512 for TDS 4.2/5.0)

The network packet size (a multiple of 512).

password (required)

Password to use for login. When using getConnection(String url, String user, String password) it's not required to set this property as it is passed as parameter, but you will have to set it when using getConnection(String url, Properties info) or JtdsDataSource .

prepareSQL (default - 3 for SQL Server, 1 for Sybase)

This parameter specifies the mechanism used for Prepared Statements.