sql2005自动发邮件出错

[littleredhorse2]

sql2005自动发邮件出错

我的原理是：启动发邮件功能，在网上找的。
然后建立一个存储过程，使用存储导出XLS文件
再将导出的XLS发送到相关人员邮件中。

问题：目前在存储过程导出的时候出错，我的语句如下：

xls_to_erp @sqlstr='SELECT ov_sal_ord_detail.sheet_no as 销售订单号,
ov_sal_ord_detail.sheet_date as 销售订单日期,
ov_sal_ord_detail.cust_item_no as 客户代码,
ov_sal_ord_detail.cust_name1 as 客户名称,
ov_sal_ord_detail.sheet_lot as 销售订单批号,
ov_sal_ord_detail.part_item_no as 料品编码,
ov_sal_ord_detail.part_name as 料品名称,
ov_sal_ord_detail.part_spec as 规格型号,
ov_sal_ord_detail.sheet_qty as 订单数量,
ov_sal_ord_detail.rem_a as 备注,
ov_sal_ord_detail.ask_date as 客户要求交期,
ov_sal_ord_detail.affirm_date as 公司承诺交期,
ov_sal_ord_detail.mo_qty as 已投制造单数量,
ov_sal_ord_detail.in_temqty as 待入库数量,
ov_sal_ord_detail.in_qty as 已入库数量,
ov_sal_ord_detail.out_qty as 已发货数量,
ov_sal_ord_detail.out_dif_qty as 未发货数量
FROM ov_sal_ord_detail left outer join obas_cust on obas_cust.cust_no= ov_sal_ord_detail.cust_no
where ov_sal_ord_detail.out_state in ('1', '3')'
,@path='c:\',@fname='昨日订单情况.xls',@sheetname='录入订单'

提示：
消息 102，级别 15，状态 1，第 18 行
'1' 附近有语法错误。

经仔细查看和测试，只要我把WHERE后面语句删除，该语句是执行成功的，只要一加上WHERE语句就不可以，所以请各位高手帮忙看看。谢谢！！

[唐诗三百首]

2楼正解,
拼接的SQL字符串中,单引号须用2个单引号或双引号代替,
如 in (''1'', ''3'') 或者 in ("1", "3")

[--小F--]

 in (''1'', ''3'')'

[-晴天]

xls_to_erp @sqlstr='SELECT ov_sal_ord_detail.sheet_no as 销售订单号,   
  ov_sal_ord_detail.sheet_date as 销售订单日期,   
  ov_sal_ord_detail.cust_item_no as 客户代码,  
  ov_sal_ord_detail.cust_name1 as 客户名称,  
  ov_sal_ord_detail.sheet_lot as 销售订单批号,  
  ov_sal_ord_detail.part_item_no as 料品编码,   
  ov_sal_ord_detail.part_name as 料品名称,   
  ov_sal_ord_detail.part_spec as 规格型号,   
  ov_sal_ord_detail.sheet_qty as 订单数量,   
  ov_sal_ord_detail.rem_a as 备注,   
  ov_sal_ord_detail.ask_date as 客户要求交期,   
  ov_sal_ord_detail.affirm_date as 公司承诺交期,  
  ov_sal_ord_detail.mo_qty as 已投制造单数量,  
  ov_sal_ord_detail.in_temqty as 待入库数量,   
  ov_sal_ord_detail.in_qty as 已入库数量,   
  ov_sal_ord_detail.out_qty as 已发货数量,   
  ov_sal_ord_detail.out_dif_qty as 未发货数量
  FROM ov_sal_ord_detail left outer join obas_cust on obas_cust.cust_no= ov_sal_ord_detail.cust_no  
 where ov_sal_ord_detail.out_state in (''1'', ''3'')'
  ,@path='c:\',@fname='昨日订单情况.xls',@sheetname='录入订单'

[AcHerat]

in ('1', '3')'


在拼接SQL字符串的时候要注意引号的拼接，一个单引要用两个单引来处理，也就是

in (''1'', ''3'')'

[vcane]

如果对存储过程不太熟悉，建议按标准文档操作，免得出问题排错都来不及