EXEC master..xp_cmdshell 'bcp "select top 1 '商品??'as pcode,'商品名稱'as pname,cast ((case saf2008_customer_code when 'T9180' then '莱茵店' else '莱茵店' end) as nvarchar(20) ) as [T9180],cast ((case saf2008_customer_code when 'T9140' then '景德店' else '景德店' end) as nvarchar(20) ) as [T9140],cast ((case saf2008_customer_code when 'T9520' then '长欣店' else '长欣店' end) as nvarchar(20) ) as [T9520],cast ((case saf2008_customer_code when 'T9530' then '相城店' else '相城店' end) as nvarchar(20) ) as [T9530],cast ((case saf2008_customer_code when 'T9110' then '甪直店' else '甪直店' end) as nvarchar(20) ) as [T9110],cast ((case saf2008_customer_code when 'T9170' then '环北店' else '环北店' end) as nvarchar(20) ) as [T9170],cast ((case saf2008_customer_code when 'T9150' then '金山店' else '金山店' end) as nvarchar(20) ) as [T9150],cast ((case saf2008_customer_code when 'T9540' then '珠江店' else '珠江店' end) as nvarchar(20) ) as [T9540],cast ((case saf2008_customer_code when 'T9130' then '三香店' else '三香店' end) as nvarchar(20) ) as [T9130],cast ((case saf2008_customer_code when 'T9120' then '十全店' else '十全店' end) as nvarchar(20) ) as [T9120],cast ((case saf2008_customer_code when 't9510' then '邓尉店' else '邓尉店' end) as nvarchar(20) ) as [t9510] from WEB_DB_T9100.dbo.saf20 saf20 group by saf20.saf2008_customer_code Union all select saf20.saf2002_pcode as pcode,inf01.inf0113_psname as pname ,cast(sum(case saf2008_customer_code when 'T9180' then saf20.saf2016_total_qty else 0 end) as varchar(20)) as [T9180莱茵店],cast(sum(case saf2008_customer_code when 'T9140' then saf20.saf2016_total_qty else 0 end) as varchar(20)) as [T9140景德店],cast(sum(case saf2008_customer_code when 'T9520' then saf20.saf2016_total_qty else 0 end) as varchar(20)) as [T9520长欣店],cast(sum(case saf2008_customer_code when 'T9530' then saf20.saf2016_total_qty else 0 end) as varchar(20)) as [T9530相城店],cast(sum(case saf2008_customer_code when 'T9110' then saf20.saf2016_total_qty else 0 end) as varchar(20)) as [T9110甪直店],cast(sum(case saf2008_customer_code when 'T9170' then saf20.saf2016_total_qty else 0 end) as varchar(20)) as [T9170环北店],cast(sum(case saf2008_customer_code when 'T9150' then saf20.saf2016_total_qty else 0 end) as varchar(20)) as [T9150金山店],cast(sum(case saf2008_customer_code when 'T9540' then saf20.saf2016_total_qty else 0 end) as varchar(20)) as [T9540珠江店],cast(sum(case saf2008_customer_code when 'T9130' then saf20.saf2016_total_qty else 0 end) as varchar(20)) as [T9130三香店],cast(sum(case saf2008_customer_code when 'T9120' then saf20.saf2016_total_qty else 0 end) as varchar(20)) as [T9120十全店],cast(sum(case saf2008_customer_code when 't9510' then saf20.saf2016_total_qty else 0 end) as varchar(20)) as [t9510邓尉店] from WEB_DB_T9100.dbo.saf20 saf20 left join WEB_DB_T9100.dbo.cnf07 cnf07 on saf20.saf2008_customer_code=cnf07.cnf0701_bcode inner join inf01 on saf20.saf2002_pcode=inf01.inf0102_pcode where saf20.status is not null and saf20.saf2006_order_date>='20070201' and saf20.saf2006_order_date<='20070228' and saf20.saf2001_bcode>='T9100' and saf20.saf2001_bcode<='T9100' group by saf20.saf2002_pcode,inf01.inf0113_psname " queryout "C:\test.xls" /c -S"XP2006" /U"sa" -P"windows"'----
执行以上语句会出错
如果改成
EXEC master..xp_cmdshell 'bcp "select top 10 * from WEB_DB_T9001.dbo.temp_Einp967a2 " queryout "C:\test.xls" /c -S"XP2006" /U"sa" -P"windows"'
则执行成功..以上的差别在于里面的查询语句..为什么会这样呢!!!
会不会语句太长,bcp要求sql语句长度限制吗??? 盼高手指点!!!
------
服务器: 消息 103,级别 15,状态 7,行 1
以 'select top 1 '商品??'as pcode,'商品名稱'as pname,cast ((case saf2008_customer_code when 'T9180' then '莱茵店' else '莱茵店' end) as nv' 开头的 标识符 太长。最大长度为 128。
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: 'queryout' 附近有语法错误。-------------