SQLSERVER: Format Number with comma

-- ตัวอย่าง
SELECT CONVERT(varchar,CONVERT(money,1234567.89),1)

-- ทดสอบ SIMPLE SELECT
SELECT CONVERT(varchar,CONVERT(money,SUM(bcbill)),1)
FROM ARDebit
WHERE year(billdate) = 2011 and month(billdate) = 10

-- ทดสอบการลบกันระหว่าง 2 query
SELECT CONVERT(varchar,CONVERT(money,
 (
 (SELECT SUM(bcBill) FROM ARDebit WHERE Year(BillDate)=2011 and Month(BillDate) = 10)
 -
 (SELECT SUM(BcPayment) FROM ARCredit WHERE Year(ReceiptDate) = 2011 and MONTH(ReceiptDate) = 10)
 )
),1)

เมื่อก่อนตอนเจอการตำนวณหลัก 10 ล้าน 100 ล้าน ต้องมานั่งเพ่งดูตัวเลขว่าเท่าไหร่กันแน่ หรือไม่งั้นก็ copy ไป excel พอตอนนี้สามารถสั่ง format ได้ พอมี comma กับจุดทศนิยมก็ง่ายขึ้นเยอะครับ

แต่ว่าพอ convert เป็น money แล้วทศนิยมมันปัดเป็น 2 ตำแหน่ง อันนี้ต้องระวังด้วยครับ

-- ทดสอบ ได้ผลลัพธ์ 1,234,567.90
SELECT CONVERT(varchar,CONVERT(money,1234567.8987),1)

SQL Server - เก็บผลลัพธ์จาก Stored Procedure ลงในตาราง

ปกติผมก็ใช้ Stored Procedure ใน SQL Server เสมอๆครับ ซึ่งพอเรา execute stored procedure ก็จะได้ result set มาใช้งาน แต่ทีนี้ถ้าเราต้องการทำ query result set ที่ได้ละจะทำยังไงดี ถ้าเป็นเมื่อก่อนผมก็ copy stored procedure มาสร้างใหม่แล้วแก้คำสั่งข้างในให้ทำ query เพิ่มไปเลย เช่นไป join object อื่น หรือสั่ง where สั่ง group by ฯลฯ แล้วแต่ต้องการ

หรืออีกวิธีก็สั่ง CREATE TABLE ก่อน แล้วก็ INSERT ข้อมูลที่ได้จาก Stored Procedure ซึ่งวิธีนี้ถ้ามันมีหลาย field ตอนสั่ง CREATE TABLE ก็เหนื่อยไม่ใช่เล่น ซึ่งวิธีนี้ใช้บ่อยโดยเฉพาะอย่างยิ่ง stored procedure ของระบบ เช่นพวก sp_who2, sp_lock เป็นต้น ลองดูตัวอย่างครับ

CREATE TABLE #locks (spid int, dbid int, objid int,
 indid int, [type] varchar(4), resource varchar(50), mode varchar(2), status varchar(10));

INSERT INTO #locks (spid, dbid, objid, indid, [type], resource, mode, status)
EXEC dbo.sp_lock;

SELECT * FROM #locks;

DROP TABLE #locks;

อันนี้ตัวอย่างแค่ 8 fields ยังเหงื่อตก เพราะเทสกันหลายรอบครับ ทีแรกผมกำหนด datatype ไม่ถูกมันก็ error ใส่ ขนาดน้อยไปเช่น resource varchar(10) ก็ error ครับ

วันนี้ผมมีวิธีใหม่มาเสนอครับ ลองดูโค้ดละกัน

SELECT * INTO #tmpWho FROM OPENROWSET('SQLNCLI', 'Server=testSQLServer;Trusted_Connection=yes;', 'EXEC sp_who') ;

SELECT * FROM #tmpWho Where dbname='master' and loginame = 'sa';

DROP TABLE #tmpWho;

ครับ โค้ดนี้เราใช้คำสั่ง OPENROWSET ร่วมกับ SELECT INTO นั่นเอง สะดวกดีมากทีเดียว 555

คราวนี้มาดูตัวอย่างการใช้งานจริงบ้าง

SELECT * INTO tempTestOutput FROM OPENROWSET ('SQLNCLI', 'Server=testSQLServer;Trusted_Connection=yes;', 'EXEC testDB.dbo.spTestOutput ''testParameter1'''); 

SELECT t.*, e.Department, e.HiredDate
FROM tempTestOutput t INNER JOIN Employee e ON t.EmployeeId = e.EmployeeId
WHERE e.ResignedDate IS NULL
ORDER BY t.EmployeeId

คราวนี้ผมก็ได้ temp table เอาไว้ยำข้อมูลแล้วครับ เสร็จแล้วก็อย่าลืม DROP TABLE ด้วยนะ

Referrence
www.stackoverflow.com - How to SELECT * INTO [temp table] FROM [Stored Procedure]

SQLServer ดึงข้อมูลจาก dbf

จริงๆผมต้องเขียนโปรแกรมเพื่อดึงข้อมูลจาก dbf เข้า SQLServer บ่อยๆ ถ้า dbf ตัวไหนที่ต้องใช้ประจำก็จะสร้าง Linked Server เก็บไว้ แต่ถ้าทำเป็น ad hoc ก็จะใช้คำสั่ง OPENROWSET แทน แต่ก็ลืมวิธีทุกครั้ง ต้องเสียเวลาไป search ใน google ทุกที คราวนี้เลยมาเขียนไว้ใน blog ดีกว่า ถ้าลืมอีกคราวหน้าก็มาหาที่นี่ได้เลย 555

สำหรับการสร้างใช้ OPENROWSET ก็ไม่ยากครับ เขียนแบบนี้

SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBase 5.0;HDR=NO;IMEX=2;DATABASE={path to dbf}','select * from {filename}.dbf')

แต่ที่สำคัญคือต้องปิดโปรแกรม foxpro หรือโปรแกรมที่กำลังเปิดไฟล์ dbf นั้นๆไปก่อนครับ ไม่งั้นมันจะขึ้นว่าติดปัญหาเรื่อง permission ไปนั่ง search หาสาเหตุตั้งนาน

ส่วนสร้าง Linked Server นั้นก็ทำดังนี้ครับ
1. ไปที่ Linked Server คลิ๊กเมาส์ขวาเลือก New Linked Server
2. ใส่ชื่อ Linked Server ที่ต้องการครับ สมมติชื่อ DBLink
3. Provider ให้เลือกเป็น Microsoft Jet 4.0 ครับ (จริงๆมันมี Visual Fox Pro ให้เลือกด้วย และในบอร์ดต่างๆเค้าว่ากันว่าจะทำให้ตอน select ข้อมูล มันเร็วกว่า Jet แต่ผมก็ยังไม่ได้ลองครับ)
4. Product Name ใส่อะไรก็ได้ครับ แต่อย่าทิ้งว่าง สมมติใส่เป็น Microsoft Jet
5. Data source ใส่ path ที่เก็บ dbf ครับ เช่น c:\dbfFiles
6. Provider String ใส่ dBase 5.0
7. เปลี่ยนมาที่ Security Page ครับ ตรง option สำหรับ log in เลือกตัวล่างสุด ที่เขียนว่า Be made using this security context เสร็จแล้วตรง Remote Login ให้ใส่ Admin ส่วน With Password ให้เว้นว่างไว้ครับ
8. กด OK เป็นอันเสร็จพิธี

ทีนี้เวลาเขียนคำสั่ง SQL ก็เขียนประมาณนี้ครับ สมมติว่าต้องการดูข้อมูลจาก testdata.dbf

SELECT * FROM DBLink...testdata

ลองเปรียบเทียบกับการใช้ OPENROWSET

SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','dBase 5.0;HDR=NO;IMEX=2;DATABASE=c:\dbfFiles', 'select * from testdata.dbf')

ไม่ยากใช่ไหมครับ แต่ทำไมผมลืมทุกทีก็ไม่รู้สิ

โค้ด Restore Database สำหรับ SQL Server

จากบทความที่แล้วเราสามารถทำการ backup database ออกมาเป็น file ทีนี้ถ้าเราต้องการทำ restore ละ ก็ใช้ T-SQL เหมือนเดิม ลองดู syntax กันก่อนครับ

RESTORE DATABASE { database_name @database_name_var }
[ FROM <> [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number @file_number } ]
[ [ , ] PASSWORD = { password @password_variable } ]
[ [ , ] MEDIANAME = { media_name @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword @mediapassword_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] { NORECOVERY RECOVERY STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND REWIND } ]
[ [ , ] { NOUNLOAD UNLOAD } ]
[ [ , ] REPLACE ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]

จะเห็นว่ามันมี option เยอะแยะเลย รายละเอียดของ option ไปดูใน Online book นะครับ

ในตัวอย่างผมจะทำการ Full Recovery ไป

สำหรับการ Restore มันมีจุดสำคัญคือ ต้องไม่มี user ใช้งาน datbase ครับ
ดังนั้นก่อนจะทำการ restore ให้บอก user ที่ใช้งานให้ออกไปก่อน (เราสามารถใช้ store procedure ดูรายชื่อคนที่ใช้งาน database อยู่ครับ แล้วจะให้ดีในโค้ดเราควรจะเตะ user ที่ใช้งานอยู่ออกไปด้วยเพื่อความปลอดภัย

ก่อนอื่นผมจะไปสร้าง table ใหม่ใน Northwind ก่อน สมมติชื่อ table1 จากนั้นก็ backup เป็นไฟล์ชื่อ mybackup.bak

เมื่อ backup เสร็จแล้วก็ทำการ drop table1 ทิ้งไปครับ เดี๋ยวเราจะลอง restore database ถ้าผ่าน table1 ก็จะกลับมาหาเราอีกครั้ง เอาละ หายไปเรียบร้อยแล้วครับ

คราวนี้มาดูโค้ดกันบ้าง จากบทความที่แล้วเราได้สร้างปุ่มเผื่อไว้แล้วชื่อ btnRestore

Private Sub btnRestore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRestore.Click

Dim strSQL As String
Dim strCon As String

strCon = "Data Source=NITHI;Initial Catalog=master;Integrated Security=True"

Dim cmdRestore As SqlClient.SqlCommand = New SqlClient.SqlCommand

sqlConnection1.ConnectionString = strCon
SqlConnection1.Open()
cmdRestore.Connection = SqlConnection1
Cursor = Cursors.WaitCursor
Try
strSQL = "ALTER DATABASE Northwind SET SINGLE_USER"
cmdRestore.CommandText = strSQL
cmdRestore.ExecuteNonQuery()
strSQL = "RESTORE DATABASE Northwind FROM DISK = 'C:\mybackup.bak' "
cmdRestore.CommandText = strSQL
cmdRestore.ExecuteNonQuery()
MsgBox("finish")
Catch ex As Exception
MsgBox("Error")
Finally
strSQL = "ALTER DATABASE Northwind SET MULTI_USER"
cmdRestore.CommandText = strSQL
cmdRestore.ExecuteNonQuery()
End Try

Cursor = Cursors.Arrow
SqlConnection1.Close()
cmdRestore.Dispose()
cmdRestore = Nothing

End Sub

จุดสังเกตุ
1. ใน Connection String ผมกำหนด Initial Catalog เป็น master (คือจริงๆเป็น database ตัวไหนก็ได้ที่ไม่ใช่ตัวที่เราต้องการ restore) ถ้าเรากำหนด Initial Catalog เป็น Northwind ก็เท่ากับว่าเรากำลังล๊อก database ด้วยตัวเองครับ (ผมก็เป็น กว่าจะรู้ตัว error ไปแล้ว)
2. ผมเลือกใช้คำสั่ง ALTER TABLE เพื่อ SET SINGLE_USER เพื่อกัน user อื่นออกจาก database
3. รันคำสั่ง Restore เสร็จแล้วก็อย่าลืม SET กลับเป็น MULTI_USER นะครับ
4. เนื่องจากบางครั้งกระบวนการ restore มันจะนานก็เลยสั่งให้เปลี่ยน cursor จะได้บอกให้ user รู้ว่ายังทำงานไม่เสร็จนะจ๊ะ

ลองรันโค้ดดูครับ

จะเห็นว่าระหว่างทำงาน Database จะเปลี่ยน mode เป็น Single User

เสร็จการ restore

Table1 กลับมาแล้ว การ restore ประสบผลสำเร็จ

Note: อยากให้ไปศึกษา option ต่างๆของการ restore เพิ่มนะครับ เพราะมันทำได้หลายอย่างมาก

โค้ด Backup Database สำหรับ SQLServer

โดยปกติแล้วการ Backup หรือ Restore รวมทั้งการ Maintenance RDBMS อย่าง SQLServer หรือ Oracle ควรให้ DBA ทำที่ตัว RDBMS เอง แต่ในบางกรณีเราอาจอยากให้ admin ของ application ที่เราพัฒนาขึ้นสามารถ backup/restore database จากหน้า form ที่เราสร้างขึ้น

กรณีนี้เราสามารถใช้คำสั่ง T-SQL (Transact SQL) ได้ครับ เรามาดู syntax ของคำสั่งก่อนครับ

BACKUP DATABASE { database_name @database_name_var }

TO <> [ ,...n ]

[ WITH

[ BLOCKSIZE = { blocksize @blocksize_variable } ]

[ [ , ] DESCRIPTION = { 'text' @text_variable } ]

[ [ , ] DIFFERENTIAL ]

[ [ , ] EXPIREDATE = { date @date_var }

RETAINDAYS = { days @days_var } ]

[ [ , ] PASSWORD = { password @password_variable } ]

[ [ , ] FORMAT NOFORMAT ]

[ [ , ] { INIT NOINIT } ]

[ [ , ] MEDIADESCRIPTION = { 'text' @text_variable } ]

[ [ , ] MEDIANAME = { media_name @media_name_variable } ]

[ [ , ] MEDIAPASSWORD = { mediapassword @mediapassword_variable } ]

[ [ , ] NAME = { backup_set_name @backup_set_name_var } ]

[ [ , ] { NOSKIP SKIP } ]

[ [ , ] { NOREWIND REWIND } ]

[ [ , ] { NOUNLOAD UNLOAD } ]

[ [ , ] RESTART ]

[ [ , ] STATS [ = percentage ] ] ]

สำหรับรายละเอียดลองดูใน Online Book ของ SQLServer นะครับ

สมมติว่าเราต้องการ backup database ลงใน folder ที่ต้องการ คำสั่งจะประมาณนี้ครับ

BACKUP DATABASE Northwind

TO DISK 'C:\Northwind.bak'

WITH FORMAT, NAME = 'NorthwindBackup'

คราวนี้เราลองมาดูการเขียนโปรแกรมเลยครับ สมมติว่า ผมสร้าง Form มา 1 ฟอร์ม สร้าง ปุ่มชื่อ btnBackup ขึ้นมา 1 ปุ่ม
แล้วก็สร้าง sqlConnection ชื่อ sqlConnection1 สำหรับ Form นี้ด้วยครับ

ทีนี้ก็มาดูโค้ด

Private Sub btnBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBackup.Click

Dim strSQL As String

Dim strCon As StringstrCon = "Data Source=NITHI;Initial Catalog=master;Integrated Security=True"
Dim cmdBackup As SqlClient.SqlCommand = New sqlClient.SqlCommandSqlConnection1.ConnectionString = strConSqlConnection1.Open()
Cursor = Cursors.WaitCursor
Try

strSQL = "BACKUP DATABASE Northwind "

strSQL &= "TO DISK = 'C:\mybackup.bak' "

strSQL &= "WITH FORMAT, "

strSQL &= "NAME = 'myBackup'"
cmdBackup.Connection = SqlConnection1

cmdBackup.CommandText = strSQL

cmdBackup.ExecuteNonQuery()
MsgBox("finish")
Catch ex As Exception
MsgBox("Error")
End Try

Cursor = Cursors.Arrow
SqlConnection1.Close()cmdBackup.Dispose()cmdBackup = Nothing

End Sub

ลองรันดูครับ
เมื่อรันเสร็จ จะมี message box บอกว่า Finish แล้วไปดูที่ C: จะพบว่ามีไฟล์ mybackup.bak

ก็เป็นอันเสร็จเรียบร้อยครับ

ถ้าเราตัองการเก็บไฟล์ backup เป็นหลายๆไฟล์ เราก็อาจจะเขียนโปรแกรมให้สร้างชื่อตามวันที่ก็ได้ครับ เช่น myBackup20070311.bak เป็นต้น