Automatic database extraction of desired query results, automatic generation of txt (utf-8) files, and then automatic FTP upload to the external network server

Requirements: Unattended data in the database, generate TXT text, automatically uploaded to the FTP server, and external customers for data docking;

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

Step: 1 Write the stored procedure

2 Creating a scheduled task in the database

3 Write a plugin for converting TXT encoded format

4 Windows Task Scheduler

5 FLASHFXP upload data to FTP server regularly

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

More information:

1 The stored procedure code that generates the text file is as follows:

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 (,--server name)

@username varchar (,--username)

@password varchar (,--password)

@sql_query varchar,--sql query statement with single quote when character condition

@filePath_name varchar (1000),--Export path and file name

@date datetime,--build date stamp when saving the file

@lx varchar (+)----Classification type execution

) as

DECLARE @sql varchar (8000)

If @lx = ' inventory '

Begin

SET @sql = ' bcp ' [email protected]_query + ' "queryout ' + '" ' [email protected]_name+ ' _ ' +

CONVERT (varchar), @date,/w) + ' + ' + ' txt '/t' ""

+ '/S ' [email protected]

+case when ISNULL (@username, ") = ' Then ' ELSE ' u ' [email protected] END

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

--set @sql = ' sqlcmd-s ' [email protected]+ ' "-u" ' [email protected]+ ' "-P" ' [email protected]+ ' "-D" XXXXXX "-Q" ' [Email prot Ected]_query + ' "-F 65001-s" "-o" ' [email protected]_name+ ' _ ' +

--convert (varchar), @date, (+) + ' + ' txt '

End

else if @lx = ' purchase '

Begin

SET @sql = ' bcp ' [email protected]_query + ' "queryout ' + '" ' [email protected]_name+ ' _ ' +

CONVERT (varchar), @date,/w) + ' + ' + ' txt '/t' ""

+ '/S ' [email protected]

+case when ISNULL (@username, ") = ' Then ' ELSE ' u ' [email protected] END

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

End

else if @lx = ' Sales '

Begin

SET @sql = ' bcp ' [email protected]_query + ' "queryout ' + '" ' [email protected]_name+ ' _ ' +

CONVERT (varchar), @date,/w) + ' + ' + ' txt '/t' ""

+ '/S ' [email protected]

+case when ISNULL (@username, ") = ' Then ' ELSE ' u ' [email protected] END

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

End

Select @sql

EXEC Master. xp_cmdshell @sql

IF @ @Error <>0 BEGIN

print ' error occurred while generating file, please check! ' [Email protected] @Error

END

2 Create a new database task schedule and execute the build operation code at night as follows:

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), Getdate (), 21);

----procurement;

Select ' CN ' Country, ' Hebei ' province, ' Baoding ' city,

T4. Fserialno,tsl.fname,ts.fnumber,convert (varchar (+), 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

The LEFT join (select Fstockid,fname from t_bd_stock_l where flocaleid=2052) is TL on T2. Fstockid=tl. Fstockid

Left joins 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 o n M.fcategoryid=w.fcategoryid) u on T6. Fmaterialid=u.fmaterialid

Left joins T_bd_supplier ts on TS. Fsupplierid=t1. Fsupplierid

The LEFT join t_bd_supplier_l the 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 joins 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), t0. fdate,21) =convert (varchar (ten), 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), T1. fdate,21) =convert (varchar), GETDATE () -1,21)

--Inventory

SELECT t0. Fnumber ch,convert (varchar), 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 ' country, ' Hebei ' province, ' Baoding ' City, ' * ' Fbl1, ' * ' Fbl2, ' * ' FBL3

into KC

From (select Fnumber,fserialid,fmaterialid from V_bd_overaserialview where fserialid>1 and fdocumentstatus = ' C ' and F Forbidstatus = ' 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.F Materialid =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 Li Ke ' then b.finputdate else a.fbilldate

End Finputdate from (select Fserialid,fbilldate from T_bd_serialbilltrace_e where fserialid>1) a INNER join (Selec T fserialid,finputdate from T_bd_serialmasterother where fserialid>1) b in A.fserialid=b.fserialid) a GROUP by FSERIAL ID) 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 Fobject Typeid= ' 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 joins T_org_organizations Torg on Torg. Forgid=inv. Fstockorgid

Left joins 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)

/* Sales */

Select ' CN ' Country, ' Hebei ' province, ' Baoding ' city, convert (varchar (+), t0.fdate,111) date, T8. Fserialno serial number,

"' Fstocknumber, ' fstockname,

Case is 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 is 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 is fallamount/frealqty=0 then Farjoinamount/frealqty else Fallamount/frealqty End as Decimal (12,2) [incl. tax amount], ' fzq, ' offline ' fxsxx, ' * ' FBL1

Into xsck

From T_sal_outstock t0

Left joins T_sal_outstockentry T1 on t0. FID =t1.fid

Left joins T_sal_outstockentry_f T2 on T1. Fentryid =t2. Fentryid

Left joins T_sal_outstockentry_r T24 on T2. Fentryid=t24. Fentryid

Left joins 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 joins 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 joins 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 joins 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), t0. fdate,21) =convert (varchar (ten), GETDATE () -1,21)) a where a.xh=1) B on T0.fid=b.fid and T8. Fserialno=b.fserialno

where convert (varchar), t0. fdate,21) =convert (varchar), 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, ' sourcing '

EXEC XXXXXX. Proc_sql_to_file ' cloud-db ', ' sa ', ' 123 ', ' select * from XXXXXX.DBO.KC ', ' F:\3241\3241_CN_INV ', @date1, ' inventory '

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

drop table Kc,cg,xsck

3 because the bcp command generated a utf-8 encoded file txt, so I wrote a C # executable file, you can batch update files under all TXT file encoding format code is as follows:

Using System;

Using System.Collections.Generic;

Using System.IO;

Using System.Linq;

Using System.Text;

Namespace auto-Convert file format

{

Class Program

{

static void Main (string[] args)

{

Define the directories that need to be retrieved; G:\3241\3241

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

DirectoryInfo Dir2 = new DirectoryInfo (dir);

Defines the generic type that holds the directory;

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

The converted file information;

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

Traverse the list of files in the directory;

foreach (FileInfo fi in Dir2. GetFiles ())

{

Files that contain only 3241 of the file name;

if (FI. Name.contains ("3241") ==true)

{fillist. Add (FI. Name); }

}

Traverse the name of the file after generating the transform;

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 ("Done! ");

}

}

}

}

4 with this plugin, the next step is to put him in the right place, in the Windows Task plan to create a new trigger to select the development of the executable file, updated every day!

5 Download FLASHFXP software, set up scheduled tasks, set up custom rules, the program will upload files under the corresponding files every day

Automatic database extraction of desired query results, automatic generation of txt (utf-8) files, and then automatic FTP upload to the external network server