在这篇文章中，我们将为您详细介绍SQLServer报错：com.microsoft.sqlserver.jdbc.SQLServerException:The"variant"datatype...的
在这篇文章中，我们将为您详细介绍SQL Server 报错：com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type ...的内容。此外，我们还会涉及一些关于### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 必须声明标量变量 &qu...、Cannot open connection] with root cause com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset、Caused by com microsoft sqlserver jdbc SQLServerException、Class.forName ("com.microsoft.jdbc.sqlserver.SQLServerDriver") 抛出 ClassNotFoundException 异常的知识，以帮助您更全面地了解这个主题。
本文目录一览：- SQL Server 报错：com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type ...
- ### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 必须声明标量变量 &qu...
- Cannot open connection] with root cause com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset
- Caused by com microsoft sqlserver jdbc SQLServerException
- Class.forName ("com.microsoft.jdbc.sqlserver.SQLServerDriver") 抛出 ClassNotFoundException 异常
SQL Server 报错：com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type ...
查询 SQL SERVER 中某张表结构，sql 语句如下：
SELECT
tb.name AS tableName,
col.name AS columnName,
col.max_length AS length,
col.is_nullable AS isNullable,
t.name AS type,
(
SELECT
TOP 1 ind.is_primary_key
FROM
sys.index_columns ic
LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id AND ic.index_id= ind.index_id AND ind.name LIKE ''PK_%''
WHERE
ic.object_id = tb.object_id AND ic.column_id= col.column_id
) AS isPrimaryKey,
com.value AS comment
FROM
sys.TABLES tb
INNER JOIN sys.columns col ON col.object_id = tb.object_id
LEFT JOIN sys.types t ON t.user_type_id = col.user_type_id
LEFT JOIN sys.extended_properties com ON com.major_id = col.object_id
AND com.minor_id = col.column_id
WHERE
tb.name = ''表名''
该 sql 可以正常执行，但是当把 sql 放到 jdbcTemplate 中执行时报一下错误：
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not supported.
原因是 sql 语句 select 后面有 sql_variant 类型的属性，在 JDBC 中不支持它。使用 sp_columns 命令最终查出 sys.extended_properties 表的 value 属性的 TYPE_NAME 是 sql_variant 类型的，sql 如下：
sp_columns extended_properties
解决方法是使用 CONVERT 函数将该属性转成 varchar 类型。
CONVERT 函数的用法参考：SQL Server 中 CONVERT () 函数的使用。
修改后的 sql 语句为：
SELECT
tb.name AS tableName,
col.name AS columnName,
col.max_length AS length,
col.is_nullable AS isNullable,
t.name AS type,
(
SELECT
TOP 1 ind.is_primary_key
FROM
sys.index_columns ic
LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id AND ic.index_id= ind.index_id AND ind.name LIKE ''PK_%''
WHERE
ic.object_id = tb.object_id AND ic.column_id= col.column_id
) AS isPrimaryKey,
CONVERT(varchar(200), com.value) AS comment
FROM
sys.TABLES tb
INNER JOIN sys.columns col ON col.object_id = tb.object_id
LEFT JOIN sys.types t ON t.user_type_id = col.user_type_id
LEFT JOIN sys.extended_properties com ON com.major_id = col.object_id
AND com.minor_id = col.column_id
WHERE
tb.name = ''表名''
参考：
com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not supported.
### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 必须声明标量变量 &qu...
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,【??】,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 必须声明标量变量 "@P23@P24"。
### The error may involve com.foen.cloud.mapping.CustMemoMapper.insert-Inline
### The error occurred while setting parameters
### SQL: insert into ERP_BASIC_CUS_Memorandum(djno,custno,custname,sales_company_projectno,Projectname,Contact_phone,contractno,Contact,Contact_account,ywy,qyjl,kfzy,qht,shop_number,project_area,qyzzwj,if_idcard,sales_area,hb,province,prefecture_level_city,county_level_city,Delivery_address,aluminum_lb,aluminum_fs,weight_jsfs,aluminum_lb_shop,pj_price_type,if_deposit,contract_dt,if_js_days,receivment_no,ztz,bhm,ssm,fwbz,gsc,zzm,bz_number,qtyq,bzfs,all_number,out_day,if_stop,begin_date,end_date,validity,ex_ysdm,kaifa_company,yfzf,wd_xl,lrj,lrj_price,cust_class,modifier,modify_dt,oper,date1)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,??,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 必须声明标量变量 "@P23@P24"。
; uncategorized SQLException for SQL []; SQL state [S0002]; error code [137]; 必须声明标量变量 "@P23@P24"。; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 必须声明标量变量 "@P23@P24"。
![Cannot open connection] with root cause com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset](http://www.gvkun.com/zb_users/upload/2025/01/f16a5f3d-25f2-43fe-aef7-973042cd896a1738316823464.jpg "Cannot open connection] with root cause com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset")
Cannot open connection] with root cause com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset
<hibernate-configuration>
<session-factory>
<property name="dialect">
org.hibernate.dialect.SQLServerDialect
</property>
<property name="connection.url">
jdbc:sqlserver://localhost:1433;databaseName=test
</property>
<!-- C3P0连接池设定 -->
<property name="hibernate.connection.provider_class">
org.hibernate.connection.C3P0ConnectionProvider
</property>
<property name="hibernate.c3p0.max_size">100</property>
<property name="hibernate.c3p0.min_size">10</property>
<property name="hibernate.c3p0.timeout">120</property>
<property name="hibernate.c3p0.max_statements">100</property>
<property name="hibernate.c3p0.idle_test_period">120</property>
<property name="hibernate.c3p0.acquire_increment">2</property>
<!-- 每次都验证连接 是否可用 -->
<property name="hibernate.c3p0.validate">true</property>
<!--最大空闲时间 ,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->
<property name="hibernate.c3p0.maxIdleTime">0</property>
<!--c3p0是异步操作的，缓慢的JDBC操作通过帮助进程 完成。扩展这些操作可以有效的提升性能通过多线程实现多个操作同时被执行。Default: 3-->
<property name="hibernate.c3p0.numHelperThreads">10</property>
<!--每60秒 检查所有连接池中的空闲连接 。Default: 0 -->
<property name="hibernate.c3p0.idleConnectionTestPeriod">
60
</property>
<!--如果设为true那么在取得连接的同时将校验连接的有效性 。Default: false -->
<property name="hibernate.c3p0.testConnectionOnCheckin">
true
</property>
<!--因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的时候 都将校验其有效性 。建议使用idleConnectionTestPeriod或automaticTestTable等方法来提升连接测试的性能。Default: false -->
<property name="hibernate.c3p0.testConnectionOnCheckout">
false
</property>
<!-- C3P0连接池设定 -->
<property name="connection.username">sa</property>
<property name="connection.password">sa</property>
<property name="connection.driver_class">
com.microsoft.sqlserver.jdbc.SQLServerDriver
</property>
<property name="connection.autocommit">true</property>
<property name="show_sql">true</property>
</ssion-factory>
</hibernate-configuration>
Caused by com microsoft sqlserver jdbc SQLServerException
1、错误描述
org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; sql []; 索引 1 超出范围。; nested exception is com.microsoft.sqlserver.jdbc.sqlServerException: 索引 1 超出范围。 at org.springframework.jdbc.support.sqlStatesqlExceptionTranslator.doTranslate(sqlStatesqlExceptionTranslator.java:106) at org.springframework.jdbc.support.AbstractFallbacksqlExceptionTranslator.translate(AbstractFallbacksqlExceptionTranslator.java:73) 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:660) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:727) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:737) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:787) at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:877) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20) at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74) at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83) at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runchild(SpringJUnit4ClassRunner.java:233) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runchild(SpringJUnit4ClassRunner.java:87) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52) at org.junit.runners.ParentRunner.runchildren(ParentRunner.java:191) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184) at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71) at org.junit.runners.ParentRunner.run(ParentRunner.java:236) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:176) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197) Caused by: com.microsoft.sqlserver.jdbc.sqlServerException: 索引 1 超出范围。 at com.microsoft.sqlserver.jdbc.sqlServerException.makeFromDriverError(sqlServerException.java:190) at com.microsoft.sqlserver.jdbc.sqlServerPreparedStatement.setterGetParam(sqlServerPreparedStatement.java:714) at com.microsoft.sqlserver.jdbc.sqlServerPreparedStatement.setobjectNoType(sqlServerPreparedStatement.java:910) at com.microsoft.sqlserver.jdbc.sqlServerPreparedStatement.setobject(sqlServerPreparedStatement.java:935) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setobject(FilterChainImpl.java:2923) at com.alibaba.druid.filter.Filteradapter.preparedStatement_setobject(Filteradapter.java:1298) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setobject(FilterChainImpl.java:2920) at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.setobject(PreparedStatementProxyImpl.java:398) at com.alibaba.druid.pool.DruidPooledPreparedStatement.setobject(DruidPooledPreparedStatement.java:476) at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:402) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:235) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:166) at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.doSetValue(ArgumentPreparedStatementSetter.java:66) at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.setValues(ArgumentPreparedStatementSetter.java:47) at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:701) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644) ... 36 more
2、错误原因
在Java连接Sql Server数据库，做查询语句时，利用静态常量编写sql语句
private static final String QUERY_STUDENT = "";
sql语句没有加进去，并且student表中存在索引，导致报错
3、解决办法
将sql语句插入到静态常量里，重新运行测试用例
再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！https://blog.csdn.net/jiangjunshow
Class.forName ("com.microsoft.jdbc.sqlserver.SQLServerDriver") 抛出 ClassNotFoundException 异常
使用 SQLServer JDBC Driver，数据库是 sql server 2005，已经把 jqljdbc.jar 导入 lib。
在测试类的 public static void main 中可以正常连接数据库。
但是在 Servlet 中反复调试总是会出现 Class.forName ("com.microsoft.jdbc.sqlserver.SQLServerDriver") 抛出 ClassNotFoundException 异常的错误，不知道为什么。。。
关于SQL Server 报错：com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type ...的问题就给大家分享到这里，感谢你花时间阅读本站内容，更多关于### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 必须声明标量变量 &qu...、Cannot open connection] with root cause com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset、Caused by com microsoft sqlserver jdbc SQLServerException、Class.forName ("com.microsoft.jdbc.sqlserver.SQLServerDriver") 抛出 ClassNotFoundException 异常等相关知识的信息别忘了在本站进行查找喔。
本文标签：
