求助：请大侠帮我把下面的查询语句改写为可以在SQL"作业"中定时执行导出查询结果到EXCEL的语句

[weixin_35920646]

下面是我的查询语句

求助：请大侠帮我把下面的查询语句改写为可以在SQL"作业"中定时执行导出查询结果到EXCEL的语句，
导出目录为D:\每个品牌每个区域.XLS

select e.zipcode,f.name_short,convert(char(6),a.time_conf,112) as ymth,c.classid,c.name_class,count(
distinct a.custid ) as 客户数,count(distinct b.shopid) as SKU数,
sum(b.value_ok) as [成交额],sum
(b.num_in / d.num_box) as [成交件数]
from t_bp_wsalebrk_h a with(nolock),
t_bp_wsalebrk_d b with(nolock) ,
t_bp_class c with(nolock),
t_bp_shop d with(nolock),
t_bp_cust e with(nolock),
t_bp_wsalewrkgroup f with(nolock)
where a.bill = b.bill and b.shopid = d.shopid and e.zipcode=f.groupid and
left(d.classid,len(c.classid)) = c.classid and a.flag_conf = '1' and a.custid = e.custid
and a.time_conf >= '2010-01-01' and a.time_conf < '当天日期’ --修改起止时间
and c.classid like '0%' and c.remark like '%基本%'
group by e.zipcode,f.name_short,convert(char(6),a.time_conf,112),c.classid,c.name_class
order by e.zipcode,c.classid

[weixin_35920646]

版主你好！先万分感谢你的指导！
我把你二楼的那些复制到查询器，出现都是红色，麻烦你帮我改一改，我的服务器名 jwl86 数据库名 drp_old 数据库登录名sa 空密码，请版主帮我重新写个完整的存储过程！

[weixin_35920646]

版主你好！先万分感谢你的指导！
我把你二楼的那些复制到查询器，出现都是红色，麻烦你帮我改一改，我的服务器名 jwl86 数据库名 drp_old 数据库登录名sa 空密码，请版主帮我重新写个完整的存储过程！

[中国风]

你的密码为什么设置为空格

[中国风]

在语句中表对象 全加上数据库 TestDB.dbo.t_bp_wsalebrk 把你的bcp /S--这是语法，输入时去掉，你最好看一下联要帮助 经常调用时把你的语句生成存储过程P1 --调用方法格式，以say为空密码为例 ,-c改为-w有unicode字符导出

EXEC   master..xp_cmdshell   'bcp   "EXEC SampleDB.dbo.P1"   queryout   "c:\test.xls"   -w   -S"PCRoy"   -U"sa"   -P""'

[weixin_35920646]

版主，我运行又碰到这个问题，怎么弄？

[中国风]

安装我上面的贴的语句改机器名和语句部份？这都不会做 有没有安装OFFICE引擎

[中国风]

Create procedure P1 as ...你上面的语句可封装为存储过程 go EXEC master..xp_cmdshell 'bcp "EXEC SampleDB.dbo.P1" queryout "c:\test.xls" -w -S"." -U"sa" -P""' 红色按照自己的去改 本机用(./IP/机器名) ，是本机不用改-S，把语句那部份替换就行了，这是调用存储过程方法 没封装时，参照#6

[weixin_35920646]

版主，谢谢你！虽然最终我没弄成功，还是真感谢你！我 自己太菜，没办法！

[weixin_35920646]

我的查询语句改了一下。好像能运行，就是无法输出文件。能不能麻烦你帮我看看,错的帮我修改一下！ EXEC master..xp_cmdshell 'bcp "select convert(char(6) ,a.time_wsale,112), a.custid,a.name_full,c.trademark，sum(a.value),a.zipcode,a.tel2,a.province,b.time_def from v_wsale_plant a,t_bp_cust b,t_bp_shop c where a.shopid=c.shopid and a.custid=b.custid and a.time_wsale>“2016-05-01” and a.time_wsale<“2016-08-01” group by convert(char(6),a.time_wsale,112),a.custid,a.name_full,c.trademark,a.zipcode,a.tel2,a.province,b.time_def order by a.time_wsale，a.zipcode " queryout "d:\test2.xls" -c -d"drp_hk" -S"192.168.0.88" -U"sa" -P '

[中国风]

本机用以下方法去测，把数据库名改改就行 以数据库名:SampleDB为例 e.g.

USE SampleDB
GO
--第1步在SampleDB数据库建存储过程P1
CREATE PROC P1
AS
DECLARE @Dt DATE= GETDATE();
--修改起止时间，取日期
SELECT  e.zipcode ,
        f.name_short ,
        CONVERT(CHAR(6), a.time_conf, 112) AS ymth ,
        c.classid ,
        c.name_class ,
        COUNT(DISTINCT a.custid) AS 客户数 ,
        COUNT(DISTINCT b.shopid) AS SKU数 ,
        SUM(b.value_ok) AS [成交额] ,
        SUM(b.num_in / d.num_box) AS [成交件数]
FROM    t_bp_wsalebrk_h a WITH ( NOLOCK ) ,
        t_bp_wsalebrk_d b WITH ( NOLOCK ) ,
        t_bp_class c WITH ( NOLOCK ) ,
        t_bp_shop d WITH ( NOLOCK ) ,
        t_bp_cust e WITH ( NOLOCK ) ,
        t_bp_wsalewrkgroup f WITH ( NOLOCK )
WHERE   a.bill = b.bill
        AND b.shopid = d.shopid
        AND e.zipcode = f.groupid
        AND LEFT(d.classid, LEN(c.classid)) = c.classid
        AND a.flag_conf = '1'
        AND a.custid = e.custid
        AND a.time_conf >= '2010-01-01'
        AND a.time_conf < @Dt
        AND c.classid LIKE '0%'
        AND c.remark LIKE '%基本%'
GROUP BY e.zipcode ,
        f.name_short ,
        CONVERT(CHAR(6), a.time_conf, 112) ,
        c.classid ,
        c.name_class
ORDER BY e.zipcode ,
        c.classid
GO
--第二步（在作业里调度） 执行导出文件 D:\每个品牌每个区域.XLS

EXEC   master..xp_cmdshell   'bcp   "EXEC SampleDB.dbo.P1"   queryout   "D:\每个品牌每个区域.XLS"   -w   -S"."   -U"sa"   -P""'

[中国风]

EXEC   master..xp_cmdshell   'bcp   "SELECT  e.zipcode ,
        f.name_short ,
        CONVERT(CHAR(6), a.time_conf, 112) AS ymth ,
        c.classid ,
        c.name_class ,
        COUNT(DISTINCT a.custid) AS 客户数 ,
        COUNT(DISTINCT b.shopid) AS SKU数 ,
        SUM(b.value_ok) AS [成交额] ,
        SUM(b.num_in / d.num_box) AS [成交件数]
FROM    t_bp_wsalebrk_h a WITH ( NOLOCK ) ,
        t_bp_wsalebrk_d b WITH ( NOLOCK ) ,
        t_bp_class c WITH ( NOLOCK ) ,
        t_bp_shop d WITH ( NOLOCK ) ,
        t_bp_cust e WITH ( NOLOCK ) ,
        t_bp_wsalewrkgroup f WITH ( NOLOCK )
WHERE   a.bill = b.bill
        AND b.shopid = d.shopid
        AND e.zipcode = f.groupid
        AND LEFT(d.classid, LEN(c.classid)) = c.classid
        AND a.flag_conf = ''1''
        AND a.custid = e.custid
        AND a.time_conf >= ''2010-01-01''
        AND a.time_conf < ''当天日期''  --修改起止时间
        AND c.classid LIKE ''0%''
        AND c.remark LIKE ''%基本%''
GROUP BY e.zipcode ,
        f.name_short ,
        CONVERT(CHAR(6), a.time_conf, 112) ,
        c.classid ,
        c.name_class
ORDER BY e.zipcode ,
        c.classid;"   queryout   "c:\test.xls"   /c   -/S"服务器名"   /U"用户名"   -P"密码"'

参照以上语法，改服务器和用户密码

[中国风]

提示不能运行xp_cmdshell时启用

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

[weixin_35920646]

伟大的版主你好！已经全部搞定，万分感激！最后成功的语句发在这里，也许有其他人也需要这些，请用到的人记得感谢这位 ”账号:roy_88“ 昵称:中国风”的版主。 --每个客户每月每个品牌金额的存储过程 用于定时输出到excel文件 --第1步在old_hk数据库建存储过程 usp_FCScustduty USE old_hk GO --在数据库建存储过程 usp_FCScustduty CREATE PROC usp_FCScustduty AS DECLARE @Dt DATE= GETDATE(); --修改起止时间，取日期 select convert(char(6) ,a.time_wsale,112) as '月份', 'A'+a.custid,a.name_full,'A'+c.trademark,sum(a.value) as '金额','A'+a.zipcode,'A'+a.tel2,'A'+a.province,b.time_def,'A'+b.duty_by from v_wsale_plant a,t_bp_cust b,t_bp_shop c where a.shopid=c.shopid and a.custid=b.custid and a.time_wsale>'2014-05-01' and a.time_wsale< @Dt group by convert(char(6),a.time_wsale,112),a.custid,a.name_full,c.trademark,a.zipcode,a.tel2,a.province,b.time_def,b.duty_by order by '月份',a.zipcode,'金额' GO --第二步（在作业里调度） 执行导出文件 D:\每月每个客户每个品牌.XLS,把下面语句设定到作业中 --22点30每月最后1天22点30调用存储过程usp_FCScustduty输出每月每个客户每个品牌.XLS文件到excel EXEC master..xp_cmdshell 'bcp "EXEC old_hk.dbo.usp_FCScustduty" queryout "D:\sql_excel数据\每月每个客户每个品牌.XLS" -w -S"." -U"sa" -P""' --用到的人请感谢“”中国风“”版主！！！！！