jtds 连接Sql Server 2005数据库

sdoghds88888

使用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.