关于Sql Server text 字段

zhugeliang 2003-12-12 01:55:55
在Sql Server 插入和更新text 字段,内容少了没问题,内容多了就显示如下错误:

查询处理器未能用优化程序生成查询计划,因为查询无法同时更新聚集键和 text、ntext 或 image 列。
...全文
613 15 打赏 收藏 举报
写回复
15 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
zhugeliang 2003-12-12
谢谢各位,我已解决。是SQL语句太长造成,字段内容长,则动态SQL语句就长,缩短就好了。来者有分。
  • 打赏
  • 举报
回复
elite2018 2003-12-12
It's transparent that when you update the data, It has a conflict in DB

so you can set Isolation Level to avoid that
  • 打赏
  • 举报
回复
loulanlouzhu 2003-12-12
当更新查询既更新聚集键又更新一个或多个 text、image 或 Unicode 列时,如果可以更改不止一行,则更新操作失败,SQL Server 返回错误信息。

用 UPDATE 修改 text、ntext 或 image 列时将对列进行初始化,向其指派有效文本指针,并且分配至少一个数据页(除非用 NULL 更新该列)。
  • 打赏
  • 举报
回复
zhugeliang 2003-12-12
我的代码,其中pro_apply_reason,string pro_contract,pro_evaluate,pro_exam_remark为text字段
public void pro_info_table(string pro_id,
string pro_name,
string pro_duty_department,
string pro_manager,
string pro_apply_reason,
string pro_apply_date,
string pro_client_name,
string pro_client_leader,
string pro_client_address,
string pro_client_register_no,
string pro_client_telephone,
string pro_contract,
string pro_evaluate,
string pro_start_date,
string pro_setup_date,
string pro_end_date,
int pro_limit_time,
double pro_sum,
double pro_intend_profit,
double pro_tax_balance,
double pro_before_exam_balance,
double pro_balance,
double pro_real_profit,
double pro_prize_scale,
double pro_prize_cash,
string pro_final_account_state,
string pro_final_account_date,
string pro_exam_man,
string pro_exam_remark,
string pro_exam_date,
string pro_exam_state
)
{

string My_SQL_Str = "Update "+ class_global.pro_info_table_name +
" SET "+
class_global.pro_name +" ='"+ pro_name + "',"+
class_global.pro_duty_department +" ='"+ pro_duty_department + "',"+
class_global.pro_manager +" ='"+ pro_manager + "',"+
class_global.pro_apply_reason +" ='"+ pro_apply_reason + "',"+
class_global.pro_apply_date +" ='"+ pro_apply_date + "',"+
class_global.pro_client_name +" ='"+ pro_client_name + "',"+
class_global.pro_client_leader +" ='"+ pro_client_leader + "',"+
class_global.pro_client_address +" ='"+ pro_client_address + "',"+
class_global.pro_client_register_no +" ='"+ pro_client_register_no + "',"+
class_global.pro_client_telephone +" ='"+ pro_client_telephone + "',"+
class_global.pro_contract +" ='"+ pro_contract + "',"+
class_global.pro_evaluate +" ='"+ pro_evaluate + "',"+
class_global.pro_setup_date +" ='"+ pro_setup_date + "',"+
class_global.pro_start_date +" ='"+ pro_start_date + "',"+
class_global.pro_end_date +" ='"+ pro_end_date + "',"+
class_global.pro_limit_time +" ="+ pro_limit_time + ","+
class_global.pro_sum +" ="+ pro_sum + ","+
class_global.pro_intend_profit +" ="+ pro_intend_profit + ","+
class_global.pro_tax_balance +" ="+ pro_tax_balance + ","+
class_global.pro_before_exam_balance +" ="+ pro_before_exam_balance + ","+
class_global.pro_balance +" ="+ pro_balance + ","+
class_global.pro_real_profit +" ="+ pro_real_profit + ","+
class_global.pro_prize_scale +" ="+ pro_prize_scale + ","+
class_global.pro_prize_cash +" ="+ pro_prize_cash + ","+
class_global.pro_final_account_state +" ='"+ pro_final_account_state + "',"+
class_global.pro_final_account_date +" ='"+ pro_final_account_date + "',"+
class_global.pro_exam_man +" ='"+ pro_exam_man + "',"+
class_global.pro_exam_remark +" ='"+ pro_exam_remark + "',"+
class_global.pro_exam_date +" ='"+ pro_exam_date + "',"+
class_global.pro_exam_state +" ='"+ pro_exam_state + "'"+
" WHERE "+class_global.pro_id +" ='"+ pro_id + "'";
class_Exec_SQL_Comm My_Exec_SQL= new class_Exec_SQL_Comm();
My_Exec_SQL.Exec_SQL_Command(My_SQL_Str);
  • 打赏
  • 举报
回复
zhugeliang 2003-12-12
我的代码,其中pro_apply_reason,string pro_contract,pro_evaluate,pro_exam_remark
public void pro_info_table(string pro_id,
string pro_name,
string pro_duty_department,
string pro_manager,
string pro_apply_reason,
string pro_apply_date,
string pro_client_name,
string pro_client_leader,
string pro_client_address,
string pro_client_register_no,
string pro_client_telephone,
string pro_contract,
string pro_evaluate,
string pro_start_date,
string pro_setup_date,
string pro_end_date,
int pro_limit_time,
double pro_sum,
double pro_intend_profit,
double pro_tax_balance,
double pro_before_exam_balance,
double pro_balance,
double pro_real_profit,
double pro_prize_scale,
double pro_prize_cash,
string pro_final_account_state,
string pro_final_account_date,
string pro_exam_man,
string pro_exam_remark,
string pro_exam_date,
string pro_exam_state
)
{

string My_SQL_Str = "Update "+ class_global.pro_info_table_name +
" SET "+
class_global.pro_name +" ='"+ pro_name + "',"+
class_global.pro_duty_department +" ='"+ pro_duty_department + "',"+
class_global.pro_manager +" ='"+ pro_manager + "',"+
class_global.pro_apply_reason +" ='"+ pro_apply_reason + "',"+
class_global.pro_apply_date +" ='"+ pro_apply_date + "',"+
class_global.pro_client_name +" ='"+ pro_client_name + "',"+
class_global.pro_client_leader +" ='"+ pro_client_leader + "',"+
class_global.pro_client_address +" ='"+ pro_client_address + "',"+
class_global.pro_client_register_no +" ='"+ pro_client_register_no + "',"+
class_global.pro_client_telephone +" ='"+ pro_client_telephone + "',"+
class_global.pro_contract +" ='"+ pro_contract + "',"+
class_global.pro_evaluate +" ='"+ pro_evaluate + "',"+
class_global.pro_setup_date +" ='"+ pro_setup_date + "',"+
class_global.pro_start_date +" ='"+ pro_start_date + "',"+
class_global.pro_end_date +" ='"+ pro_end_date + "',"+
class_global.pro_limit_time +" ="+ pro_limit_time + ","+
class_global.pro_sum +" ="+ pro_sum + ","+
class_global.pro_intend_profit +" ="+ pro_intend_profit + ","+
class_global.pro_tax_balance +" ="+ pro_tax_balance + ","+
class_global.pro_before_exam_balance +" ="+ pro_before_exam_balance + ","+
class_global.pro_balance +" ="+ pro_balance + ","+
class_global.pro_real_profit +" ="+ pro_real_profit + ","+
class_global.pro_prize_scale +" ="+ pro_prize_scale + ","+
class_global.pro_prize_cash +" ="+ pro_prize_cash + ","+
class_global.pro_final_account_state +" ='"+ pro_final_account_state + "',"+
class_global.pro_final_account_date +" ='"+ pro_final_account_date + "',"+
class_global.pro_exam_man +" ='"+ pro_exam_man + "',"+
class_global.pro_exam_remark +" ='"+ pro_exam_remark + "',"+
class_global.pro_exam_date +" ='"+ pro_exam_date + "',"+
class_global.pro_exam_state +" ='"+ pro_exam_state + "'"+
" WHERE "+class_global.pro_id +" ='"+ pro_id + "'";
class_Exec_SQL_Comm My_Exec_SQL= new class_Exec_SQL_Comm();
My_Exec_SQL.Exec_SQL_Command(My_SQL_Str);
  • 打赏
  • 举报
回复
charlse12 2003-12-12
up
  • 打赏
  • 举报
回复
zhugeliang 2003-12-12
我用的是 WINDOWS 2000 SERVER, SQL SERVER 2000 无论是用SQL 的企业管理器还是用代码,text的长度(16)都不让改。
  • 打赏
  • 举报
回复
elite2018 2003-12-12
show your codes
  • 打赏
  • 举报
回复
elite2018 2003-12-12
???楼上: 字段的长度:16改大些

有没有 搞错 ,不懂不要 乱说 ! text 固定长度 16 !!!
  • 打赏
  • 举报
回复
zhugeliang 2003-12-12
单引号也早就替换了。
  • 打赏
  • 举报
回复
zhugeliang 2003-12-12
可是,text的长度(16)是不让改的。
  • 打赏
  • 举报
回复
贴出你的语句。。
  • 打赏
  • 举报
回复
AmoyCzc 2003-12-12
我认为楼上是对的!你sql字段的长度问题
  • 打赏
  • 举报
回复
tianweima 2003-12-12
字段的长度:16改大些
  • 打赏
  • 举报
回复
houjianxun 2003-12-12
你的sql语句贴出来看看

还有更新的内容单引号替换了没有
  • 打赏
  • 举报
回复
相关推荐
发帖
.NET社区
加入

6.0w+

社区成员

.NET技术交流专区
帖子事件
创建了帖子
2003-12-12 01:55
社区公告

.NET 社区是一个围绕开源 .NET 的开放、热情、创新、包容的技术社区。社区致力于为广大 .NET 爱好者提供一个良好的知识共享、协同互助的 .NET 技术交流环境。我们尊重不同意见,支持健康理性的辩论和互动,反对歧视和攻击。

希望和大家一起共同营造一个活跃、友好的社区氛围。