Tip4Geek, tips for every one who geek: ตั้งScheduleเพื่อExportข้อมูลจากMS SQLออกมาเป็นtext file

Exportข้อมูลจากMS SQLออกมาเป็นText Fileตามเวลาเพื่อวัตถุประสงค์เช่นเป็นรายงาน

ก่อนอื่นEnableฟังก์ชั่นของshell commandในMS sQL Serverเสียก่อน โดยเปิด MS SQL Management Studio ขึ้นมาแล้วรันQuery(กดปุ่มF5เพื่อExecute)ดังนี้

Use Master
GO

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

สร้างไฟล์sqlเพื่อเก็บคำสั่งExportข้อมูลเป็นtext file

สร้างไฟล์sqlแล้วนำCommandดังตัวอย่างวางในไฟล์ดังกล่าว (ทดสอบCommandในMS SQL Management Studioก่อนก็ได้ครับแล้วSaveเป็นไฟล์sql เช่น exptxt.sql)

ตัวอย่างด้านล่างเป็นการเอาข้อมูล1000ข้อมูลล่าสุดจากตารางA1ในดาต้าเบสชื่อtest และServerชื่อ(local)\SQLEXPRESS2 ออกมาใส่ในไฟล์ชื่อmytestตามชื่อไฟล์ด้วยวันที่เวลาก่อนต่อด้วยนามสกุล.txt

declare @sql varchar(8000)
declare @filename varchar(50)

SET @filename = 'c:\mytest' + replace(replace(convert(varchar(20), getdate()),' ','_'),':','-') + '.txt'

SELECT @sql = 'bcp "select top 1000 * from test.dbo.A1 order by date_time desc" queryout ' + @filename + ' -c -t -T -S (local)\SQLEXPRESS2'

exec xp_cmdshell @sql

ส่วนสำคัญของCommand

SET @filename = 'c:\mytest' + replace(replace(convert(varchar(20), getdate()),' ','_'),':','-') + '.txt'

จะเป็นการกำหนดที่เก็บไฟล์และชื่อไฟล์ โดยเปลี่ยนช่องว่างในวันที่เวลาที่เป็นส่วนหนึ่งของชื่อไฟล์เป็น “_” และเปลี่ยน “:” เป็น “-“

select top 1000 * from test.dbo.A1 order by date_time desc

คือการเอาข้อมูลล่าสุด1000แถวออกมา

สร้างTask Schedule

หลังจากสร้างไฟล์sqlแล้ว ให้สร้างTaskในWindows Task Scheduler

ดำเนินการตามวิซาร์ดและกำหนดระยะเวลาเช่น Monthly จนถึงขั้นตอนเลือกActionที่จะให้Taskทำงานให้เลือก “Start a program”

ตั้งเวลาที่จะให้ทำงานโดยคลิ้กNew

สร้างActionขึ้นมาโดยให้ไปรันโปรแกรม SQLCMD.EXE ซึ่งอยูในพาธโปรแกรม MS SQL Server เช่น "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" (คลิ้กปุ่ม New เพื่อสร้างAction)