With SQL 2000 server using com.microsoft.jdbc.sqlserver.SQLServerDriver as the SQL driver class from msbase.jar, mssqlserver.jar and msutil.jar if you hit a error like
SEVERE: Standard SQL Exception Info for exception at level 0 - SQL State: 'HY000'; SQL Error Number: '0'; SQL Error Text: '[Microsoft][SQLServer 2000 Driver for JDBC]The DBMS returned an unspecified error.';[Microsoft][SQLServer 2000 Driver for JDBC]The DBMS returned an unspecified error. at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source) at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source) at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source) at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source) at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source) at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source) at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source) at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source) at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source) at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source) at com.microsoft.jdbc.base.BaseStatement.executeUpdateInternal(Unknown Source) at com.microsoft.jdbc.base.BasePreparedStatement.executeUpdate(Unknown Source) at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
Then the issue is with the jdbc driver, upgrade your driver to SQL 2005 server driver. This will resolve the problem. The jar will be sqljdbc.jar. If you are planning to use the latest jar of about 570KB this is good but you might hit the other error.
SEVERE: Standard SQL Exception Info for exception at level 0 - SQL State: '08S01'; SQL Error Number: '0'; SQL Error Text: 'The TDS protocol stream is not valid.';SEVERE: com.microsoft.sqlserver.jdbc.SQLServerException: The TDS protocol stream is not valid. at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.throwInvalidTDS(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSReader.throwInvalidTDS(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSParser.throwUnexpectedTokenException(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onRetValue(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(Unknown Source) at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:169)
To resolve this download and use the Microsoft SQL Server 2005 JDBC Driver 1.2 or 1.1 from
http://msdn.microsoft.com/data/jdbc
SQL SERVER database and user
1) First create the user in the sercurity/Logins by right clicking in the right window.
2) Enter the login name and the password. If you are allowing the external users to connect then select the sql server authentication. Also select the database you allow the user to access.
3) Select the appropriate roles for the user
4) Finally in the Database Access page select the databases you allow the user to access. Click Ok and save the user and the credentials. Do not confuse with the one selected in the section 2. That is the default database, the ones you select now are the databases the user is permited.5) Now open the database you want the user just created to access. Select the user and in the right window right click and select New Database User.
6) Enter the user name and the password and you are all set. The above can be done by selecting the Now when you try to write a java application to connect to this database, you may see the error/exception as
Exception in thread "main" java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)at com.microsoft.jdbc.sqlserver.tds.TDSConnection.(Unknown Source)at com.microsoft.jdbc.sqlserver.SQLServerImplConnection.open(Unknown Source)at com.microsoft.jdbc.base.BaseConnection.getNewImplConnection(Unknown Source)at com.microsoft.jdbc.base.BaseConnection.open(Unknown Source)at com.microsoft.jdbc.base.BaseDriver.connect(Unknown Source)at java.sql.DriverManager.getConnection(DriverManager.java:512)at java.sql.DriverManager.getConnection(DriverManager.java:193)
This can be solved by following the steps below.
First try the telnet
1. Make sure that SQL Server is set to mixed authentication. To do this, open enterprise manager, right click on server registered on there, go to security tab and select SQL Server and Windows Authentication.
2. Go to the general tab and click the network configuration button. Enable TCP/IP. Click properties to change the default port (1433).
3. Add an SQL Server login account (NOT a WINDOWS account (see 1)). You can also use your sa account.
Happy SQLing..
Subscribe to:
Posts (Atom)