Java connection ms SQL Server troubleshooting

First, let's talk about the connection between SQL 2000 and SQL 2005 when using the rack package developed by Microsoft to connect the ms SQL Server database;

1. The drivers are different first. There are three connection drivers for SQL 2000: msbase. jar, MSSQLServer. jar, and msutil. jar;

SQL 2005 only has one connection driver package: sqljdbc. jar;

2. Because the connection drivers are different, the connection methods are also different;

Web SQL 2000:

Class. forname ("com. Microsoft. JDBC. sqlserver. sqlserverdriver ");

Url = "JDBC: Microsoft: sqlserver: // localhost: 1433; databasename = Demo ";

Web SQL 2005:

Class. forname ("com. Microsoft. sqlserver. JDBC. sqlserverdriver ");

Url = "JDBC: sqlserver: // localhost: 1433; databasename = Demo ";

 

The following is a more common way: jtds-1.2.jar package, this package is provided by a third-party open-source organization, using this package for the connection of ms SQL Server database, you don't have to worry about the differences between databases of different versions;

The unified connection mode is as follows:

Class. forname ("net. SourceForge. jtds. JDBC. Driver"). newinstance ();
String url = "JDBC: jtds: sqlserver: // localhost: 1433; databasename = Demo ";

 

Next I will discuss several considerations for connecting to the database:

1. If you are using SQL Server 2000, you must install the SP3 patch when using JDBC for database connection. If you are using SQL Server 2005, it is not required;

SQL Server 2000 cannot open port 1433

You can run the netstat-a command to check whether port 1433 is enabled (default)
1. If you are using win2003, you must install the SQL patch sp3a.

Check whether your SQL has been patched. If not, install a patch. You can run the patch in the query Analyzer:
Select @ version

If the version is earlier than 8.00.760, it indicates that you have not installed the SP3 patch.

SQL patch download:
Positions of All Patches
Http://www.microsoft.com/downloads/details.aspx? Displaylang = ZH-CN & familyid = 9032f608-160a-4537-a2b6-4cb265b80766

Note that after downloading the package, decompress the package and execute setup. bat in the decompressed directory.

2. If the above information has been confirmed correct but still cannot connect to the database, we will check the elements of your connection one by one:

A. First check whether the connection statement is correct, this here will not be said, if you still have problems, you can see http://www.cnblogs.com/shenliang123/archive/2012/04/24/2467702.html

You can also use the following main function for testing. Change the above data name, port number, and password to your own (the jtds package is used for connection here)

 

Public static void main (string [] ARGs) {try {class. forname ("net. sourceForge. jtds. JDBC. driver "). newinstance ();} catch (instantiationexception e) {e. printstacktrace ();} catch (illegalaccessexception e) {e. printstacktrace ();} catch (classnotfoundexception e) {e. printstacktrace ();} string url = "JDBC: jtds: sqlserver: // localhost: 9433; databasename = sportsunion2"; string user = "sa "; string psw = "123 @ Sports"; connection conn = NULL; try {conn = drivermanager. getconnection (URL, user, psw); system. out. println ("database connected successfully");} catch (sqlexception e) {system. out. println ("failed to connect to the Database"); E. printstacktrace ();}

Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------------------

B. view the login method of your database (we generally choose the login mode as SQL Server and Windows Authentication Mode ):

In the open database view, Select Server (for example) ------> right-click Properties -----> select security in the view that appears -----> select SQL Server and Windows Authentication Mode ------> OK

Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------------------

C. Set the password: click Security in your database View> SA in the displayed level-2 menu> Reset the SA password in the displayed view:

For the sake of security, you can log out, re-open the logon, and use SA to log on. If you can log on, the password is successfully set and SA is available.

Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------------

D. Configure the port number:

SQL 2000: Start ----> all programs -----> Microsoft SQL Server -----> server network tools click ----->

-----> Select TCP/IP -----> click Properties -----> set the desired port number in the displayed Properties window. The default value is 1433 (it is better not to set the default value, which is vulnerable to attacks)

Restart the server.

Web SQL 2005:

Start ----> all programs -----> Microsoft SQL Server 2005 -----> Configuration tool -----> SQL Server Configuration Manager -----> SQL Server 2005 Network Configuration

------> MSSQLServer protocol; Enable "TCP/IP ";

Double-click "TCP/IP" --------> "ip address" --------> "ipall" -------> "TCP port" to set as desired.

Restart the sql2005 Server

Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------------------

E. Here, if you are using SQL Server 2000, there should be no problem, but if you are using SQL Server 2005, there may still be a problem that cannot be connected:

(1) The database engine is not started. The following two methods are used:

A. start> program> Microsoft SQL Server 2005> SQL Server 2005 peripheral application configurator. On the displayed page, click "peripheral application configurator for service connection ", find

Database Engine, click "service", and check whether it is started on the right side. If it is not started, click "start" and make sure "Start type" is automatic, not manual, otherwise, manual start is required at the next boot;

B. Open start> program> Microsoft SQL Server 2005> Configuration tool> SQL Server Configuration Manager, and select SQL Server (MSSQLServer) in SQL Server 2005)

Click "Start Service" in the toolbar to change the service status to start;

(2) check whether the SQL Server database has enabled remote connection
Start -----> program -----> Microsoft SQL Server 2005 -----> Configuration tool -----> SQL Server peripheral application configurator ---->

Click the peripheral application configurator of the service and connection ------> click Database Engine -----> click remote connection -----> select local connection and remote connection on the right

Select the Protocol to use (TCP/IP and named pipe service should be enabled here !) Click "application" -----> restart server;

(3) Enable the sqlserver Browser Service
Start ------> program ------> Microsoft SQL Server 2005 ------> Configuration tool ------> SQL Server peripheral application Configurator
------> On the "SQL Server 2005 peripheral application configurator" page, click "service and connected peripheral application configurator"
------> Click expand "SQL Server Browser", select "service", select "automatic" in "Startup Type" on the right -------> then click "start", and click "OK" to return

 

It should be connected now! Hahaha