关于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);