posts - 310, comments - 6939, trackbacks - 0, articles - 3
  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

Spring连接MS SQL Server中出现的错误

Posted on 2008-01-29 09:16 诗特林 阅读(9262) 评论(1)  编辑  收藏 所属分类: J2EE


采用Spring中配置Hibernate数据库连接,但边不上SQL Server2000,配置如下:

 <bean id="dataSource"
  class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName">
   <value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
  </property>
  <property name="url">
   <value>
    jdbc:microsoft:sqlserver://cyc:1433;DatabaseName=officeArticle;SelectMethod=cursor
   </value>
  </property>
  <property name="username">
   <value>sa</value>
  </property>
  <property name="password">
   <value>sa</value>
  </property>
 </bean>


==============================================
错误如下:

WARN - Could not obtain connection metadata
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory ([Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.)
 at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:855)
 at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
 at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
 at org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:72)
 at org.hibernate.cfg.Configuration.buildSettings(Configuration.java:1463)
 at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1004)
 at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:825)
 at org.springframework.orm.hibernate3.LocalSessionFactoryBean.afterPropertiesSet(LocalSessionFactoryBean.java:751)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1091)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:396)
 at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:233)
 at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:145)
 at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:277)
 at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:313)
 at org.springframework.web.context.support.AbstractRefreshableWebApplicationContext.refresh(AbstractRefreshableWebApplicationContext.java:139)
 at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:252)
 at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:190)
 at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:49)
 at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3669)
 at org.apache.catalina.core.StandardContext.start(StandardContext.java:4104)
 at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:759)
 at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:739)
 at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:524)
 at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:894)
 at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:857)
 at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:475)
 at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1102)
 at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:311)
 at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
 at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1020)
 at org.apache.catalina.core.StandardHost.start(StandardHost.java:718)
 at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1012)
 at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:442)
 at org.apache.catalina.core.StandardService.start(StandardService.java:450)
 at org.apache.catalina.core.StandardServer.start(StandardServer.java:683)
 at org.apache.catalina.startup.Catalina.start(Catalina.java:537)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
 at java.lang.reflect.Method.invoke(Unknown Source)
 at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:271)
 at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:409)
Caused by: 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.<init>(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 org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:37)
 at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
 at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:877)
 at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:851)
 ... 41 more

==============================================

解决办法:

首先查看数据库服务器端口1433是否打开

在命令提示符中输入命令:netstat -an如果没有tcp:0.0.0.0:1433说明1433端口没打开,需要下载SQL Server2000的sp3或sp4补丁。
我试了几个补丁,最后成功的是SQL2000-KB884525-SP4-x86-CHS.EXE
下载地址:http://www.microsoft.com/downloads/details.aspx?FamilyID=8e2dfc8d-c20e-4446-99a9-b7f0213f8bc5&DisplayLang=zh-cn
下载后执行EXE文件,会出现安装包SQL2KSP4,执行setup.bat安装程序。
如果有挂起需要重启计算机现象,请在运行中输入regedit打开注册表编辑器,在HKEY_LOCAL_MACHINE\SYSTEM\ CurrentControlSet\Control\Session Manager中找到PendingFileRenameOperations项目,并删除它,这样就可以清除安装暂挂项目。
安装成功后再执行netstat -an有tcp:0.0.0.0:1433说明1433端口已打开。

需要有msbase.jar、mssqlserver.jar、msutil.jar这三个jar包
下载地址:http://www.microsoft.com/downloads/details.aspx?FamilyID=9f1874b6-f8e1-4bd6-947c-0fc5bf05bf71&DisplayLang=en
setup.exe文件。
下载后执行exe文件,这三个jar包将被默认安装在C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib文件夹下,这里只有这三个jar包是有用的,完全可以把它们放在其它位置,只要知道路径即可。


同时:
   <value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
一定要写在同一行,不然会报如下的错误:

org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta data; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class 'com.microsoft.jdbc.sqlserver.SQLServerDriver' for connect URL '
   jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=officeArticle;SelectMethod=cursor
   '
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class 'com.microsoft.jdbc.sqlserver.SQLServerDriver' for connect URL '
   jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=officeArticle;SelectMethod=cursor
   '
org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class 'com.microsoft.jdbc.sqlserver.SQLServerDriver' for connect URL '
   jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=officeArticle;SelectMethod=cursor
   '
 at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:780)
 at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
 at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:112)
 at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
 at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:167)
 at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:207)
 at org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes(SQLErrorCodesFactory.java:187)
 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.setDataSource(SQLErrorCodeSQLExceptionTranslator.java:127)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
 at java.lang.reflect.Method.invoke(Unknown Source)
 at org.springframework.beans.BeanWrapperImpl.setPropertyValue(BeanWrapperImpl.java:707)
 at org.springframework.beans.BeanWrapperImpl.setPropertyValue(BeanWrapperImpl.java:572)
 at org.springframework.beans.BeanWrapperImpl.setPropertyValue(BeanWrapperImpl.java:737)
 at org.springframework.beans.BeanWrapperImpl.setPropertyValues(BeanWrapperImpl.java:764)
 at org.springframework.beans.BeanWrapperImpl.setPropertyValues(BeanWrapperImpl.java:753)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1057)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:857)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:378)
 at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:233)
 at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:145)
 at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:283)
 at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:313)
 at org.springframework.web.context.support.AbstractRefreshableWebApplicationContext.refresh(AbstractRefreshableWebApplicationContext.java:139)
 at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:252)
 at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:190)
 at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:49)
 at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3669)
 at org.apache.catalina.core.StandardContext.start(StandardContext.java:4104)
 at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:759)
 at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:739)
 at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:524)
 at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:894)
 at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:857)
 at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:475)
 at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1102)
 at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:311)
 at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
 at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1020)
 at org.apache.catalina.core.StandardHost.start(StandardHost.java:718)
 at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1012)
 at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:442)
 at org.apache.catalina.core.StandardService.start(StandardService.java:450)
 at org.apache.catalina.core.StandardServer.start(StandardServer.java:683)
 at org.apache.catalina.startup.Catalina.start(Catalina.java:537)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
 at java.lang.reflect.Method.invoke(Unknown Source)
 at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:271)
 at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:409)
Caused by: java.sql.SQLException: No suitable driver
 at java.sql.DriverManager.getDriver(Unknown Source)
 at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:773)
 ... 51 more

评论

# re: Spring连接MS SQL Server中出现的错误  回复  更多评论   

2008-01-29 09:35 by BeanSoft
寒一个 这个和 Spring 没任何关系,就是 SQL Server 没启用 TCP 支持。可以不下补丁也能解决。

jdbc配置语句为: jdbc:microsoft:sqlserver://server_name:1433

如运行程序时出现 "Error establishing socket" 错误,则应进行如下调试:


1 检查SQL SERVER 是否允许远程访问.具体步骤:

1)打开"企业管理器",打开控制台根目录>SQL Server 组>数据库
2)在相应"数据库"上单击右键,选择"属性"
3)选择"连接"选项卡,检查"远程服务器连接"下,RPC服务是否选择.

2 使用telnet IP地址 1433,系统是否提示连接出错,如系统提示出错
检查是否防火墙屏蔽了SQL SERVER 或 java IDE 的网络访问端口
如果是,关闭防火墙,重新启动SQL SERVER和java IDE,进行测试,
如果系统仍提示上述错误,尝试下列步骤

3 检查SQL SERVER 端口号及是否启用了TCP/IP协议,具体步骤:

1)打开"企业管理器",打开控制台根目录>SQL Server 组>数据库
2)在相应"数据库"上单击右键,选择"属性"
3)选择"常规"选项卡,点击"网络配置",如启用的协议中无"TCP/IP协议"将其加入
4)选择"TCP/IP协议",点击"属性",检查其端口号是否为1433
5)如端口号为1433将其修改为其它端口号,修改jdbc连接语句,将端口号同样改为新启用的端口号,如jdbc:microsoft:sqlserver://server_name:1400(假设新端口号为 1400)

以下的方法你每种的去试试:
#检查一下sql server的connection有没有full.

#如果是认证的问题.
可到sql enterprice manager->指定server->内容->安全性.
把验证改为 SQL server 及 windows.

#点选属性检查一下port是否正确.

#执行%MSSQL_HOME%\80\Tools\Binn\SVRNETCN.exe
把TCP/IP启用.

#关掉防火墙.

#把SQL server update到sp3:
http://www.microsoft.com/sql/downloads/2000/sp3.asp




只有注册用户登录后才能发表评论。


网站导航: