初次使用bcp 各种问题

USE [USP30Log]
GO
EXEC sp_configure 'show advanced options', 1 
GO 
-- 重新配置 
RECONFIGURE 
GO 
-- 启用xp_cmdshell 
EXEC sp_configure 'xp_cmdshell', 1 
GO 
--重新配置 
RECONFIGURE 
GO 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].USP30Log_JOB_UserOrderBillsRank_Month') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].USP30Log_JOB_UserOrderBillsRank_Month
GO
CREATE PROCEDURE USP30Log_JOB_UserOrderBillsRank_Month
AS

DECLARE @path VARCHAR(1000)       --文件存放路径
DECLARE @filename VARCHAR(200)    --文件名称
DECLARE @servername VARCHAR(200)  --服务器名称
DECLARE @username VARCHAR(20)     --用户名
DECLARE @password VARCHAR(20)     --密码

DECLARE @database VARCHAR(50)     --数据库名称
DECLARE @tempquery VARCHAR(2000)  --子查询语句
DECLARE @querysql VARCHAR(8000)   --查询语句
SET @path='D:\TEST\'
SET @filename='USP_'+CONVERT(VARCHAR(100),GETDATE(),23)+'_订购排行榜.xls'
SET @servername='192.168.85.22'
SET @username='sa'
SET @password='usptest'
SET @database='USP30Log'
IF right(@path,1)<>'\' set @path=@path+'\' --确保路径格式合法
IF RIGHT(@filename,4)<>'.xls' SET @filename=@filename+'.xls'--确保文件名正确

PRINT @filename
PRINT @path+@filename
IF  OBJECT_ID('tempdb..#UserOrderBills') IS NOT NULL
DROP TABLE #UserOrderBills
IF  OBJECT_ID('tempdb..#CALC_RING') IS NOT NULL
DROP TABLE #CALC_RING
IF  OBJECT_ID('tempdb..#CALC_RINGBOX') IS NOT NULL
DROP TABLE #CALC_RINGBOX

SELECT * INTO #UserOrderBills FROM [UserOrderBills] UNION ALL SELECT * FROM [UserOrderBills_BAK]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Temp_CALC]') AND type in (N'U'))
DROP TABLE [Temp_CALC]
CREATE TABLE [Temp_CALC]
(
	下载排名 VARCHAR(20),
	铃音盒名称 VARCHAR(50),
	铃音盒订购次数 VARCHAR(50),
	单曲名称 VARCHAR(50),
	单曲订购次数 VARCHAR(50)
)
INSERT INTO [Temp_CALC] VALUES('下载排名','铃音盒名称','订购次数','单曲名称','订购次数')

SELECT IDENTITY(int,1,1) AS '下载排名', ISNULL(ResourceName,ResourceNo) AS '铃音盒名称',COUNT(Price) AS '铃音盒订购次数' INTO #CALC_RINGBOX FROM #UserOrderBills
WHERE BillResult='1'  AND BillType='1'
AND CreatedTime BETWEEN CONVERT(VARCHAR(100),DATEADD(mm,-1,GETDATE()),23) AND CONVERT(VARCHAR(100),GETDATE(),23)
AND ResourceType='2' GROUP BY ResourceName,ResourceNo ORDER BY 铃音盒订购次数 DESC

SELECT IDENTITY(int,1,1) AS '下载排名', ISNULL(ResourceName,ResourceNo) AS '单曲名称',COUNT(Price) AS '单曲订购次数' INTO #CALC_RING FROM #UserOrderBills
WHERE BillResult='1'  AND BillType='1'
AND CreatedTime BETWEEN CONVERT(VARCHAR(100),DATEADD(mm,-1,GETDATE()),23) AND CONVERT(VARCHAR(100),GETDATE(),23)
AND ResourceType='1' GROUP BY ResourceName,ResourceNo ORDER BY 单曲订购次数 DESC

INSERT INTO [Temp_CALC]
SELECT #CALC_RINGBOX.下载排名,#CALC_RINGBOX.铃音盒名称,#CALC_RINGBOX.铃音盒订购次数,
	#CALC_RING.单曲名称,#CALC_RING.单曲订购次数 FROM #CALC_RINGBOX FULL JOIN #CALC_RING 
ON #CALC_RINGBOX.下载排名=#CALC_RING.下载排名 AND #CALC_RINGBOX.下载排名<'21'

SET @tempquery='SELECT * FROM '+@database+'.dbo.[Temp_CALC]'--需要指定数据库名称

SET @querysql = 'bcp "'+REPLACE(REPLACE(@tempquery,CHAR(10),''),CHAR(13),'')+'" queryout '+@path+@filename+' -w -S '+@servername+' -U"'+@username+'" -P"'+@password+'"'

--查询语句中不能存在“换行符”，“回车符”，否则会报错

EXEC master..xp_cmdshell @querysql

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Temp_CALC]') AND type in (N'U'))
DROP TABLE [Temp_CALC]--bcp查询语句中可能无法获取#临时表，因此此处采用普通表