SQL Server数据库-无法预定义语句

Question

程序功能是从甲方db定时同步数据到乙方db，执行到jdbcTemplate.update()时，偶而会出现异常——无法预定义语句，不用处理会恢复，有时恢复不了，重启程序恢复正常，异常提示如下：

 07:10:08 [SimpleAsyncTaskExecutor-1] ERROR c.r.m.Receiver - 处理消息时发送错误!
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [update PATS_IN_HOSPITAL set INP_NO=?, DEPT_CODE=?, DEPT_NAME=?, NAME=?, DATE_OF_BIRTH=?, NAME_PHONETIC=?, ADMISSION_DATE_TIME=?, DIAGNOSIS=?, DISCHARGE_DATE_TIME=?, PATIENT_STATUS=?,INHOSP_NO=?,DIAGNOSIS_DESC=?,BED_NO=?,CHARGE_TYPE=?,ID_NO=?,AGE=?,SEX=?,WARD_CODE=? where PATIENT_ID=? and VISIT_ID=?]; SQL state [S0001]; error code [8180]; 无法预定义语句。; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 无法预定义语句。
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:870)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:931)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:941)
    at cn.rivamed.biz.DataBiz.saveInHospitalInfo(DataBiz.java:290)
    at cn.rivamed.biz.DataBiz$$FastClassBySpringCGLIB$$14cf4487.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:721)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:656)
    at cn.rivamed.biz.DataBiz$$EnhancerBySpringCGLIB$$4476651e.saveInHospitalInfo(<generated>)
    at cn.rivamed.mq.Receiver.processMessage(Receiver.java:93)
    at sun.reflect.GeneratedMethodAccessor14.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.messaging.handler.invocation.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:180)
    at org.springframework.messaging.handler.invocation.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:112)
    at org.springframework.amqp.rabbit.listener.adapter.HandlerAdapter.invoke(HandlerAdapter.java:49)
    at org.springframework.amqp.rabbit.listener.adapter.MessagingMessageListenerAdapter.invokeHandler(MessagingMessageListenerAdapter.java:126)
    at org.springframework.amqp.rabbit.listener.adapter.MessagingMessageListenerAdapter.onMessage(MessagingMessageListenerAdapter.java:106)
    at org.springframework.amqp.rabbit.listener.AbstractMessageListenerContainer.doInvokeListener(AbstractMessageListenerContainer.java:779)
    at org.springframework.amqp.rabbit.listener.AbstractMessageListenerContainer.invokeListener(AbstractMessageListenerContainer.java:702)
    at org.springframework.amqp.rabbit.listener.SimpleMessageListenerContainer.access$001(SimpleMessageListenerContainer.java:95)
    at org.springframework.amqp.rabbit.listener.SimpleMessageListenerContainer$1.invokeListener(SimpleMessageListenerContainer.java:186)
    at org.springframework.amqp.rabbit.listener.SimpleMessageListenerContainer.invokeListener(SimpleMessageListenerContainer.java:1227)
    at org.springframework.amqp.rabbit.listener.AbstractMessageListenerContainer.executeListener(AbstractMessageListenerContainer.java:683)
    at org.springframework.amqp.rabbit.listener.SimpleMessageListenerContainer.doReceiveAndExecute(SimpleMessageListenerContainer.java:1181)
    at org.springframework.amqp.rabbit.listener.SimpleMessageListenerContainer.receiveAndExecute(SimpleMessageListenerContainer.java:1165)
    at org.springframework.amqp.rabbit.listener.SimpleMessageListenerContainer.access$1500(SimpleMessageListenerContainer.java:95)
    at org.springframework.amqp.rabbit.listener.SimpleMessageListenerContainer$AsyncMessageProcessingConsumer.run(SimpleMessageListenerContainer.java:1367)
    at java.lang.Thread.run(Thread.java:745)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 无法预定义语句。
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:426)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:372)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1793)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:315)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:877)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:870)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
    ... 34 common frames omitted

Answer 1

PowerDesigner反向数据库时遇到[Microsoft][ODBC SQL Server Driver][SQL Server]无法预定义语句。SQLSTATE = 37错误解决方法
逆向工程中，有时会出现如下错误

...

[Microsoft][ODBC SQL Server Driver][SQL Server]无法预定义语句

SQLSTATE = 37000

解决方案：

  1、选择菜单栏中的Database -> Change Current DBMS ,给DBMS选择SQL Server2005。

  2、重新配置ODBC连接，管理工具 -> 数据源ODBC -> 在用户DSN Tab选项中点添加 ->出现创建新数据源的选择框口,拖动滚动条到最下方，选择SQL Srever点完成，出现新数据源命名窗口，名称：BinODBC,在服务器下拉框中，选择本地数据库实例。点下一步 -> 选择使用用户ＩＤ和密码的Sql server验证 -> 输入sql用户名密码 ->不要选择"更改默认的数据库为"选框。直接下一步 -> 完成

  3、从PD里选择File -> Reverse Engineer -> Database

  4、选择数据库，选择表，OK，大功造成。

大致按以上方法基本能解决，其实大多数时候，只要第一步基本可以解决了，原因可以定位为没有对于数据库类型导致的。 参考：
http://www.cnblogs.com/EasonJim/p/4757415.html