自动数据库抽取想要的查询结果，自动生成txt(utf-8)文件，然后自动ftp上传到外网服务器...

需求：无人值守的把数据库中的数据，生成txt文本，自动上传到ftp服务器，与外部客户进行数据对接；

===============================================

步骤：1  写存储过程

    2  数据库中建立计划任务

    3  写转换txt编码格式的插件

    4  windows任务计划

    5  flashfxp定时上传数据到ftp服务器

==============================================

详细信息：

1 生成文本文件的存储过程代码如下：

  

USE [XXXXXX]

GO

/****** Object:  StoredProcedure [dbo].[Proc_sql_to_file]    Script Date: 2018/5/17 15:09:08 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:<Author,,Name>

-- Create date: <Create Date,,>

-- Description:<Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[Proc_sql_to_file](

@servername varchar(50),--服务器名

@username varchar(50),--用户名

@password varchar(50) ,--密码

@sql_query varchar(2000) , --SQL查询语句,有字符条件时用单引号

@filePath_name varchar(1000), --导出路径和文件名

@date datetime, --保存文件时的生成日期戳

@lx varchar(100) ----分类型执行

)AS

DECLARE @sql varchar(8000)

if @lx='库存'

begin

SET @sql='bcp "'+@sql_query + '" queryout '+' "'+@filePath_name+'_'+

convert(varchar(100),@date,112)+'06'+'.txt " /t "" /w'

+' /S '+@servername

+CASE WHEN ISNULL(@username,'')='' THEN '' ELSE ' /U '+@username END

+' /P '+ISNULL(@password,'')

--set @sql='sqlcmd -S"'+@servername+'"  -U"'+@username+'" -P"'+@password+'" -d"XXXXXX" -Q"'+@sql_query + '" -f 65001 -s "" -o "'+@filePath_name+'_'+

--convert(varchar(100),@date,112)+'06'+'.txt " '

end

else  if @lx='采购'

begin

SET @sql='bcp "'+@sql_query + '" queryout '+' "'+@filePath_name+'_'+

convert(varchar(100),@date,112)+'06'+'.txt " /t "" /w'

+' /S '+@servername

+CASE WHEN ISNULL(@username,'')='' THEN '' ELSE ' /U '+@username END

+' /P '+ISNULL(@password,'')

end

else  if @lx='销售'

begin

SET @sql='bcp "'+@sql_query + '" queryout '+' "'+@filePath_name+'_'+

convert(varchar(100),@date,112)+'06'+'.txt " /t "" /w'

+' /S '+@servername

+CASE WHEN ISNULL(@username,'')='' THEN '' ELSE ' /U '+@username END

+' /P '+ISNULL(@password,'')

end

select @sql

EXEC master..xp_cmdshell @sql

IF @@Error<>0 BEGIN

print '生成文件时出错了，请查看!'+@@Error

END

2 新建数据库任务计划，晚上执行生成操作代码如下：

 

if exists(select 1 from sysobjects where name='temp11')

drop table temp11;

if exists(select 1 from sysobjects where name='temp12')

drop table temp12;

declare @date1 datetime;set @date1 =CONVERT(varchar(10),Getdate(), 21);

----采购；

  select 'CN' 国家,'河北' 省份,'保定' 城市,

t4.FSERIALNO,tsl.fname,ts.fnumber,convert(varchar(100),t1.fdate,111) Fdate,tl.fname FstockName,tlk.fnumber FstockNnumber,cast(case when t2.FREALQTY=0 then 0 else t3.FAPJOINAMOUNT/t2.FREALQTY end as decimal(12,2))  fprice,'*' Fbl1,'*' Fbl2,'*' Fbl3 

into cg 

from (select fdate,FSUPPLIERID,fid from T_STK_INSTOCK where fid>1 and FDOCUMENTSTATUS='C' ) t1

inner join (select fid,FMATERIALID,FREALQTY,FSTOCKID from T_STK_INSTOCKentry where FENTRYID>1)  t2 on t1.fid=t2.fid

inner join  (select fid,FENTRYID,FAPJOINAMOUNT from T_STK_INSTOCKENTRY_F  where FENTRYID>1) t3 on t2.fid=t3.fid

inner join (select FENTRYID,FSERIALNO from T_STK_INSTOCKSERIAL where FENTRYID>1) t4 on t3.FENTRYID=t4.FENTRYID and len(FSERIALNO)=15

inner join (select FMATERIALID,fname from t_bd_material_l where FLOCALEID=2052 and FMATERIALID>1) t6 on t2.FMATERIALID=t6.FMATERIALID 

left join (select FSTOCKID,fname from T_BD_STOCK_L where FLOCALEID=2052) tl on t2.FSTOCKID=tl.FSTOCKID 

left join T_BD_STOCK tlk on tl.FSTOCKID=tlk.FSTOCKID 

inner join ( select distinct m.FMATERIALID,m.FCATEGORYID,w.fname from T_BD_MATERIALBASE m join T_BD_MATERIALCATEGORY_L w on m.FCATEGORYID=w.FCATEGORYID) u on t6.FMATERIALID=u.FMATERIALID

left join T_BD_SUPPLIER ts on ts.FSUPPLIERID=t1.FSUPPLIERID

left join T_BD_SUPPLIER_L tsl on ts.FSUPPLIERID=tsl.FSUPPLIERID

left join (

select  fid,fdate,xh,FSERIALNO From (select t0.fid,t0.fdate,t8.FSERIALNO,row_number() over (partition by t8.FSERIALNO order by t0.fdate desc) xh From T_STK_INSTOCK t0

left  join  T_STK_INSTOCKentry t1 on t0.FID =t1.fid

inner  join T_STK_INSTOCKSERIAL t8 on t1.FENTRYID=t8.FENTRYID and FSERIALNO<>'' and len(FSERIALNO)=15

where convert(varchar(10),t0.FDATE,21)=convert(varchar(10),getdate()-1,21)) a where a.xh=1 ) b on t1.fid=b.fid and t4.FSERIALNO=b.FSERIALNO

where u.fname like '%XXX%'  and t6.fname like '%XXX%'

and convert(varchar(10),t1.FDATE,21)=convert(varchar(10),getdate()-1,21)

--库存

SELECT   t0.FNUMBER ch,convert(varchar(100),getdate()-1,111) riqi,ck.fnumber cknumber,ckl.FNAME FstockName,'' mddm,'' mdname,cast(tms.F_PAEZ_INPRICE  as decimal(12,2)) F_PAEZ_INPRICE,'CN' 国家,'河北' 省份,'保定' 城市,'*' Fbl1,'*' Fbl2,'*' Fbl3 

into kc 

FROM (select FNUMBER,FSERIALID,FMATERIALID from V_BD_OVERASERIALVIEW where FSERIALID>1 and  FDOCUMENTSTATUS = 'C' AND FFORBIDSTATUS = 'A' and len(FNUMBER)=15) t0 

 INNER   JOIN (select FSERIALID from V_BD_OVERASERIALVIEW_L where FSERIALID>1  AND FLocaleId = 2052) t0_L ON (t0.FSERIALID = t0_L.FSERIALID)

 INNER    JOIN (select FSERIALID From V_BD_OVERASERIALORGVIEW where FSERIALID>1  and  FSTOCKSTATUS = 1 ) uu on uu.FSERIALID=t0.FSERIALID

 inner   join (select FMATERIALID,FNAME FSPMC From t_bd_material_l where FMATERIALID>1 and FLocaleId = 2052) tll on t0.FMATERIALID =tll.FMATERIALID 

 INNER   join (select FCATEGORYID,FMATERIALID From T_BD_MATERIALBASE where FCATEGORYID>1 and FCATEGORYID ='2092334' ) m on t0.FMATERIALID=m.FMATERIALID

 left   join (SELECT FSERIALID,MIN(FINPUTDATE) FINPUTDATE  FROM (select a.FSERIALID FSERIALID,case when b.FINPUTDATE not like '' then b.FINPUTDATE else   a.FBILLDATE

end FINPUTDATE from (select FSERIALID,FBILLDATE  From T_BD_SERIALBILLTRACE_e where FSERIALID>1 ) a  INNER   join (select FSERIALID,FINPUTDATE From T_BD_SERIALMASTEROTHER where FSERIALID>1) b on a.FSERIALID=b.FSERIALID ) a GROUP BY FSERIALID) b on t0.fserialid=b.FSERIALID

INNER   JOIN (select FSERIALID,FBILLTRACEID,FINVID from T_BD_SERIALBILLTRACE  where FBILLTRACEID>1 and FSERIALID>1 ) t5 ON T0.FSERIALID  = t5.FSERIALID  AND t5.FBILLTRACEID = (SELECT top 1 EE.FBILLTRACEID AS FBILLTRACEID FROM T_BD_SERIALBILLTRACE  EE WHERE T0.FSERIALID = EE.FSERIALID and( FINVID is not null and FINVID <> '' ) order by FBILLTRACEID desc)

inner   join (select fid,FSTOCKID,FSTOCKSTATUSID,FSTOCKORGID from T_STK_INVENTORY where   FSTOCKSTATUSID=10000 and FOBJECTTYPEID= 'STK_Inventory'  and FKEEPERTYPEID='BD_KeeperOrg'and FOWNERTYPEID='BD_OwnerOrg' and FISEFFECTIVED=1 and FBASEQTY<>0) inv on inv.fid=t5.FINVID 

inner   join (select FSTOCKID,FNAME from T_BD_STOCK_L where FSTOCKID>1 and FLocaleId = 2052   ) ckl on inv.FSTOCKID=ckl.FSTOCKID 

 inner   join (select FSTOCKID,FNUMBER from T_BD_STOCK where FSTOCKID>1   ) ck on ckl.FSTOCKID=ck.FSTOCKID 

 left join T_ORG_ORGANIZATIONS torg on torg.FORGID=inv.FSTOCKORGID

 left join T_ORG_ORGANIZATIONS_L torgl on torg.FORGID=torgl.FORGID

 left join T_BD_SERIALMASTER tms on tms.FNUMBER=t0.FNUMBER and t0.FMATERIALID=tms.FMATERIALID

where FSPMC like '%XXX%'

option(maxdop 0)

/*销售*/

select 'CN' 国家,'河北' 省份,'保定' 城市,convert(varchar(100),t0.fdate,111)  日期,t8.FSERIALNO 串号,

'' FstockNumber,'' FstockName,

case when ISNULL((select FNUMBER from t_bd_department where FDEPTID= t0.FOLDDEPARTMENT),'')<>'' then ISNULL((select FNUMBER from t_bd_department where FDEPTID= t0.FOLDDEPARTMENT),'') else isnull((select FNUMBER from t_bd_department where FDEPTID= t0.FSALEDEPTID ),'') end FdeptNumber,

case when ISNULL((select fname from t_bd_department_l where FDEPTID= t0.FOLDDEPARTMENT and FLOCALEID=2052),'')<>'' then ISNULL((select fname from t_bd_department_l where FDEPTID= t0.FOLDDEPARTMENT and FLOCALEID=2052),'') else isnull((select fname from t_bd_department_l where FDEPTID= t0.FSALEDEPTID and FLOCALEID=2052),'') end FdeptName,cast( case when FALLAMOUNT/FREALQTY=0 then  FARJOINAMOUNT/FREALQTY else FALLAMOUNT/FREALQTY end as decimal(12,2))  [含税金额],' ' Fzq,'线下' Fxsxx,'*' Fbl1

into xsck  

 From T_SAL_OUTSTOCK t0

left  join  T_SAL_OUTSTOCKENTRY t1 on t0.FID =t1.fid

left  join T_SAL_OUTSTOCKENTRY_F t2 on t1.FENTRYID =t2.FENTRYID

  left join T_SAL_OUTSTOCKENTRY_R t24 on t2.FENTRYID=t24.FENTRYID 

left join t_bd_material_l t5 on t1.FMATERIALID =t5.FMATERIALID 

inner  join  T_SAL_OUTSTOCKSERIAL t8 on t1.FENTRYID=t8.FENTRYID and FSERIALNO<>'' and len(FSERIALNO)=15

left join t_BD_Stock_L t13 on t1.FSTOCKID=t13.FSTOCKID

 inner   join (select FSTOCKID,FNUMBER from T_BD_STOCK where FSTOCKID>1   ) ck on t13.FSTOCKID=ck.FSTOCKID 

  inner join T_BD_MATERIALBASE tt on tt.FMATERIALID=t1.FMATERIALID and FCATEGORYID ='2092334' 

 left join T_BD_MATERIALCATEGORY_L u on tt.FCATEGORYID=u.FCATEGORYID

  left join (

select  fid,fdate,xh,FSERIALNO From (select t0.fid,t0.fdate,t8.FSERIALNO,row_number() over (partition by t8.FSERIALNO order by t0.fdate desc) xh From T_SAL_OUTSTOCK t0

left  join  T_SAL_OUTSTOCKENTRY t1 on t0.FID =t1.fid

inner  join T_SAL_OUTSTOCKSERIAL t8 on t1.FENTRYID=t8.FENTRYID and FSERIALNO<>'' and len(FSERIALNO)=15

where convert(varchar(10),t0.FDATE,21)=convert(varchar(10),getdate()-1,21)) a where a.xh=1 ) b on t0.fid=b.fid and t8.FSERIALNO=b.FSERIALNO

where convert(varchar(10),t0.FDATE,21)=convert(varchar(10),getdate()-1,21) and   t5.fname like '%XXX%' AND T0.FDOCUMENTSTATUS='C' 

 EXEC XXXXXX..Proc_sql_to_file 'cloud-db','sa','123','select * from XXXXXX.dbo.cg','F:\3241\3241_CN_WHIN',@date1,'采购'

 EXEC XXXXXX..Proc_sql_to_file 'cloud-db','sa','123','select * from XXXXXX.dbo.kc','F:\3241\3241_CN_INV',@date1,'库存'

 EXEC XXXXXX..Proc_sql_to_file 'cloud-db','sa','123','select * from XXXXXX.dbo.xsck','F:\3241\3241_CN_SO',@date1,'销售'

drop table kc,cg,xsck

3 因为bcp命令生成步了utf-8编码文件txt，所以我自己写了个c#可执行文件，可以批量更新文件下的所有txt文件编码格式代码如下：

using System;

using System.Collections.Generic;

using System.IO;

using System.Linq;

using System.Text;

namespace 自动转换文件格式

{

    class Program

    {

        static void Main(string[] args)

        {

            //定义需要检索的目录;G:\3241\3241

            string dir = @"G:\3241\3241";

      DirectoryInfo dir2 = new DirectoryInfo(dir);

       //定义存放目录的泛型类型；

     List<string> fillist = new List<string>();

       //转换后的文件信息；

     List<string> destfiles = new List<string>();

    //遍历目录内的文件列表；

     foreach(FileInfo fi in dir2.GetFiles())

     {

         //只包含3241的文件名的文件；

         if (fi.Name.Contains("3241")==true)

         { fillist.Add(fi.Name); }

     }

    //遍历生成转换后的文件名称；

     foreach (string filter in fillist)

     {

         foreach (var eachfileinfo in new DirectoryInfo(dir).GetFiles(filter,SearchOption.TopDirectoryOnly))

         {

             destfiles.Add(eachfileinfo.FullName);

             Console.WriteLine(eachfileinfo.FullName);

         }

     }

     if (destfiles.Count()>0)

     {

         foreach (string destfile in destfiles)

         {

             File.WriteAllText(destfile,File.ReadAllText(destfile,Encoding.Default), Encoding.UTF8);

         }

         Console.WriteLine("完成！");

     }

    

 }

        }

    }

4 有了这个插件，接下来就是把他放到合适的位置，在windows任务计划中新建触发器选择开发的可执行文件，每天更新即可！

5 下载flashfxp软件，设置计划任务，设置自定义规则，程序会每天上传相应文件下的文件

转载于:https://blog.51cto.com/yataigp/2117532