关于Sql Server text 字段

[zhugeliang]

在Sql Server 插入和更新text 字段，内容少了没问题，内容多了就显示如下错误：

查询处理器未能用优化程序生成查询计划，因为查询无法同时更新聚集键和 text、ntext 或 image 列。

[houjianxun]

你的sql语句贴出来看看

还有更新的内容单引号替换了没有

[tianweima]

字段的长度：16改大些

[AmoyCzc]

我认为楼上是对的！你sql字段的长度问题

[悟空师傅来了]

贴出你的语句。。

[zhugeliang]

可是，text的长度（16）是不让改的。

[zhugeliang]

单引号也早就替换了。

[elite2018]

？？？楼上： 字段的长度：16改大些

有没有 搞错 ，不懂不要 乱说 ！ text 固定长度 16 ！！！

[elite2018]

show your codes

[zhugeliang]

我用的是 WINDOWS 2000 SERVER, SQL SERVER 2000 无论是用SQL 的企业管理器还是用代码，text的长度（16）都不让改。

[charlse12]

up

[zhugeliang]

我的代码，其中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);

[zhugeliang]

我的代码，其中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);

[loulanlouzhu]

当更新查询既更新聚集键又更新一个或多个 text、image 或 Unicode 列时，如果可以更改不止一行，则更新操作失败，SQL Server 返回错误信息。

用 UPDATE 修改 text、ntext 或 image 列时将对列进行初始化，向其指派有效文本指针，并且分配至少一个数据页（除非用 NULL 更新该列）。

[elite2018]

It's transparent that when you update the data, It has a conflict in DB

so you can set Isolation Level to avoid that

[zhugeliang]

谢谢各位，我已解决。是SQL语句太长造成，字段内容长，则动态SQL语句就长，缩短就好了。来者有分。