如题, 现在需要每月定时将表内查询结果输出到指定文件夹,生成一个csv文件, 做备份用.declare @sql nvarchar(256);
declare @filename nvarchar(100);
set @filename='f:\MonthlyReports\'+convert(char(8),getdate(),112)+'.csv';
set @sql = 'bcp "select top 100 percent 块料识别号,尺寸,重量,数量,入炉角,出炉角,convert(varchar,入炉时间,120) as "入炉时间",convert(varchar,出炉时间,120) as "出炉时间" from CC_asdf_13_09_18_15_23_15.dbo.materialDetail
where datepart(mm,入炉时间) between datepart(mm,getdate())-1 and datepart(mm,getdate()) order by 入炉时间 desc" out @filename -S"KIDD\WINCC" -U"sa" -P"wincc"';
exec master..xp_cmdshell @sql;
可是bcp之后的output都是bcp的功能介绍,无任何返回结果,望各位大侠帮忙查下是什么原因.结果如下:usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
NULL
declare @filename nvarchar(100);
set @filename='f:\MonthlyReports\'+convert(char(8),getdate(),112)+'.csv';
set @sql = 'bcp "select top 100 percent 块料识别号,尺寸,重量,数量,入炉角,出炉角,convert(varchar,入炉时间,120) as "入炉时间",convert(varchar,出炉时间,120) as "出炉时间" from CC_asdf_13_09_18_15_23_15.dbo.materialDetail
where datepart(mm,入炉时间) between datepart(mm,getdate())-1 and datepart(mm,getdate()) order by 入炉时间 desc" out @filename -S"KIDD\WINCC" -U"sa" -P"wincc"';
exec master..xp_cmdshell @sql;
可是bcp之后的output都是bcp的功能介绍,无任何返回结果,望各位大侠帮忙查下是什么原因.结果如下:usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
NULL
@filename的引入我重新改过了,不过输出结果还是一样.
@filename的引入我重新改过了,不过输出结果还是一样.关键现在就是不知道报的是什么错, 在目标文件夹也找不到输出job生成的文件.
可只要加上where条件或者是用@filename拼接的指定路径就还会出现刚才的问题,也没有报错, 能麻烦告诉我一下是什么原因造成的这种问题么?
set @strSQL='bcp "SELECT applicationID,Category FROM test.dbo.temtable" queryout C:\Temp\DBA_date\'+@filename+' -c -T -t","'
--print @strSQL
EXEC master..xp_cmdshell @strSQL这是我在公司部署的脚本,你改一下试试
注意一下双引号,问题解决了,谢了!!
不过还是想知道到底是什么原因造成的这个output~
呵呵..直接说我小白就是了,本来sqlserver就是小白,没办法.我也只是想搞懂到底是什么原因造成的,以后不会再出现这种问题,呵呵,既然你懒得解释,那就算了.
求教
这里有一个解答,好像是这个问题
说是 bcp 后面不能有换行