sql server 存储输出 长度超过4000,就被截取的解决方式

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/luoww1/article/details/62233158


如果 sendStringParametersAsUnicode 属性设置为“true”,则字符串参数将以 Unicode 格式发送给服务器。

如果 sendStringParametersAsUnicode 属性设置为“false”,则字符串参数将以非 Unicode 格式(例如 ASCII/MBCS 而不是 Unicode)发送给服务器。

sendStringParametersAsUnicode 属性的默认值为“true”。

如果 sendStringParametersAsUnicode 连接字符串属性设置为 false,则可以将长度大于 4000 个字符的 String 数据转换为 char 或 varchar 基础列

若定义存储过程中 varchar(max)会出现错误。



解决方法:

url = "jdbc:jtds:sqlserver://" + path +";useLOBs=false"+ ";DatabaseName=" + dbName+";sendStringParametersAsUnicode=false";


展开阅读全文

谁来帮忙精简下此条SQL长度超过4000

11-20

[code=SQL]rn Select Top 11 cell_cn_name FROM (select top 11 Row_Number() rnover(order by incr_value desc) as rownum1 ,* from ( select top 5 rnRow_Number() over(order by incr_value desc) as rnrownum,replace(replace(replace(replace(replacern(replace(replace(replace(replace(replace(replacern(replace(cell_cn_name,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'.',''),' | 各项贷款','') as cell_cn_name, round(incr_value/10000,2) as rnincr_value,rept_year,rept_month,org_id,data_range_id,cur_id,freq_id rnfrom (select cell_cn_name,cast(isnull(curr_term_value,0) as float)rn - cast(isnull(prev_term_value,0)as float) as incr_value, rnrept_year,rept_month,org_id,data_range_id,cur_id,freq_id fromrn ( select t1.cell_cn_name,rd.curr_term_value, rnrd.rept_year,rd.rept_month,rd.org_id,rd.data_range_id,rd.cur_id,rnrd.freq_id ,(select curr_term_value fromrn report_data d where d.cell_index=rd.cell_index and d.rept_year=dbo.getlastyear(2,9,2010,1) and rnd.rept_month=dbo.getlastmonth(2,9,1) and d.cur_id=rd.cur_id and rnd.freq_id=rd.freq_id and (d.status='1' or d.status is null) ) as rnprev_term_value from report_data rd, (select rncell_index,cell_cn_name,version_id from cell_info where rntemplate_id='G1101' and col_id='C' and ((row_id>=10 and row_id<=29) rnor row_id=35 or row_id=36)) t1 where rd.cell_index=t1.cell_index rnand rd.version_id=t1.version_id and rd.rept_year=2010 and rd.rept_month=9 and rd.org_id='F093H101510101001' and rnrd.data_range_id=1 and rd.cur_id=1 and (rd.status='1'rn or rd.status is null) ) t2) as tmpDR union all select top 5 Row_Number() over(order by incr_value asc) as rownum,replace(replace(replace(replace(replace(replacern(replace(replace(replace(replace(replace(replacern(cell_cn_name,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6','')rn,'7',''),'8',''),'9',''),'.',''),' | 各项贷款','') as cell_cn_name, rnround(incr_value/10000,2) as rnincr_value,rept_year,rept_month,org_id,data_range_id,rncur_id,freq_id from (select cell_cn_name,cast(isnull(curr_term_value,0) as float) - cast(isnull(prev_term_value,0)as float) as incr_value,rn rept_year,rept_month,org_id,data_range_id,rncur_id,freq_id from ( select t1.cell_cn_name,rd.curr_term_value, rd.rept_year,rd.rept_month,rd.org_id,rd.data_range_id,rnrd.cur_id,rd.freq_id ,(select curr_term_value from report_data d where d.cell_index=rd.cell_index and d.rept_year=dbo.getlastyear(2,9,2010,1) and d.rept_month=dbo.getlastmonth(2,9,1) and rnd.cur_id=rd.cur_id and d.freq_id=rd.freq_id and (d.status='1' or d.status is null) ) as prev_term_value rnfrom report_data rd,rn (select cell_index,cell_cn_name,version_id from cell_info where template_id='G1101' and col_id='C' and ((row_id>=10 and row_id<=29) or row_id=35 or row_id=36)) t1rn where rd.cell_index=t1.cell_index and rd.version_id=t1.version_id and rd.rept_year=2010 and rd.rept_month=9 and rd.org_id='F093H101510101001' rnand rd.data_range_id=1 and rd.cur_id=1 and (rd.status='1' or rd.status is null) ) t2) as tmpDR rnunion all rnselect 6,'行业平均' as cell_cn_name , rnavg(incr_value),rept_year,rept_month,org_id,data_range_id,cur_id,freq_id from (select cell_cn_name,cast(isnull(curr_term_value,0) as float) - cast(isnull(prev_term_value,0)as float) as incr_value, rnrept_year,rept_month,org_id,data_range_id,cur_id,rnfreq_id from ( select t1.cell_cn_name,rd.curr_term_value, rd.rept_year,rd.rept_month,rd.org_id,rd.data_range_id,rnrd.cur_id,rd.freq_id ,(select curr_term_value from report_data d where d.cell_index=rd.cell_index and rnd.rept_year=dbo.getlastyear(2,9,2010,1) and rnd.rept_month=dbo.getlastmonth(2,9,1) and d.cur_id=rd.cur_id andrn d.freq_id=rd.freq_id and (d.status='1' or d.status is null) ) as rnprev_term_value from report_data rd, (select rncell_index,cell_cn_name,version_id from cell_info where rntemplate_id='G1101' and col_id='C' and ((row_id>=10 rnand row_id<=29) or row_id=35 or row_id=36)) t1 where rd.cell_index=t1.cell_index and rd.version_id=t1.version_id andrn rd.rept_year=2010 and rd.rept_month=9 and rd.org_id='F093H101510101001' and rd.data_range_id=1 rnand rd.cur_id=1 and (rd.status='1' or rd.status is null) ) t2)as rntmpDR group by rept_year,rept_month,org_id,data_range_id,cur_id,freq_id ) rntemp order by rownum1 ) as t where Org_ID='F093H101510101001' AND Rept_Year=2010 AND Rept_Month=9 AND rnData_Range_ID=1 AND Cur_ID=1 AND Freq_ID<=2rn rn[/code] 论坛

没有更多推荐了,返回首页