MSSQL

Microsoft SQL

คราวที่แล้วเขียน SP สร้าง ตารางวันที่ แต่ไอ้ตอนที่แปลง Format มันแปลง ดุ้นๆ ไปไม่ค่อยเวิ๊ก อันนี้เป็นการจัด Format Datetime อย่างเป็นทางการ

อันนี้เป็น format นี้ 05-04-2008

SELECT CONVERT(varchar(10), getdate()+1, 105)

อันนี้เป็น format นี้ 05/04/2008

SELECT CONVERT(varchar(10), getdate()+1, 103)

ปล. ที่ต้อง +1 ให้กับ getdate() ก็เพราะว่า มันเป็นวันที่ 4 เดือน 4 เดี๋ยวจะงง

การเอา Textfile เข้า ฐานข้อมูล MSSQL จริงๆ ไม่ต้องใช้ Temptable ก็ได้ ถ้าใช้บ่อย สร้างเป็นถาวรก็น่าจะใช้งานได้ง่ายกว่า แต่สำหรับผมเองผมว่าเอาเข้า Temptable ไว้ก่อน แล้วค่อยเอามาใช้จริงๆ แต่สำหรับการเขียน Webที่ Connection เปิดหนึ่งครั้ง ปิดหนึ่งครั้ง Temptable ก็หายไปบางครั้งก็คงไม่เหมาะ

ขั้นแรกก็สร้าง Temptable ซะก่อน ด้วยคำสั่ง Create table ธรรมดานี่แหละครับ เพียงแต่ใส่ # ไว้ข้างหน้าเพื่อบอกว่าเป็น Temptable

1
2
3
4
5

CREATE TABLE #tempdata (
id varchar(4),
usr VARCHAR(100),
pwd VARCHAR(100)
)

อ่านต่อ

เขียน MSSQL store procedure เพื่อทำ Crosstab หรือ Pivot Table

วันก่อน มีโอกาสต้องเขียน การแสดงผลข้อมูลเป็นแบบ แนวนอน หรือที่เค้าเรียกกันว่า Crosstab แต่เนื่องด้วย ฐานข้อมูลที่ใช้ มันเป็น MSSQL 2000 มันไม่มีพวก Pivot Table มาให้ (Access ยังมีเลย) เลยต้องหาเอาเอง ไปเจอเว็บนึงเค้า เขียนเป็น Store Procedure ไว้ เอามาใช้ Work เลยทีเดียว ตอบโจทย์ได้เลย

ตัวอย่างของ Data

Year Region Income
Y2007 North 20
Y2007 South 10
Y2007 East 30
Y2006 North 10

Y2006 South 10

Y2006 East 10

เมื่อทำ Crosstab แล้วจะได้แบบนี้
ํ
Year North South East
Y2006 10 10 10
Y2007 20 10 30

อ้าวแล้วทำยังไงให้ได้แบบนี้ล่ะ มาดูวิธีการกันเลยครับ

อ่านต่อ

วันนี้มีความต้องการสร้างฐานข้อมูลวันที่ในตารางของ MSSQL แต่ให้ไปนั่ง Key เอาหรือไป Copy เอาคงไม่ไหว เลยเขียน Script เอาไว้สร้างวันที่เอาไว้เผื่อใครเอาไปใช้ครับ

declare @i int
declare  @CAL_DATE datetime
SET @i=0
while (@i < 1825)
begin
	-- เอาวันพรุ่งนี้เข้า
	SET @CAL_DATE = (SELECT REPLACE(CONVERT(varchar(11), GETDATE()+@i, 111),'/','-'))
	INSERT INTO MS_CALENDAR (CAL_DATE) VALUES (@CAL_DATE)
	-- เอาวันพรุ่งนี้เข้า
	-- เอาเมื่อวานเข้า
	SET @CAL_DATE = (SELECT REPLACE(CONVERT(varchar(11), GETDATE()-@i, 111),'/','-'))
	INSERT INTO MS_CALENDAR (CAL_DATE) VALUES (@CAL_DATE)
	-- เอาเมื่อวานเข้า
	SET @i=@i+1
end

ทำการ Add Server ด้วย System Store Procedure ก่อน ด้วยคำสั่ง

use Master

EXEC sp_addlinkedserver
   @server = servername ,
   @provider = ‘MSDASQL’,
   @provstr = ‘DRIVER={SQL Server};SERVER=servername;UID=user;PWD=password;’

หลังจากนั้น ก็รัน SP เพื่อ ปรับ Config Data Access

EXEC sp_serveroption ‘178.55.1.3′, ‘data access’, ‘TRUE’

ถ้าไม่รันคำสั่งนี้ จะขึ้น Error อันนี้

servername is not configured for DATA ACCESS.

เท่านี้เราก็สามารถเรียก Table ของ MSSQL ข้าม Server ได้แล้ว

เช่นถ้าต้องการเรียก Table ABC บน Database ADatabase ที่อยู่ใน AServer ก้อจะเรียกดังนี้

select * from [Aserver].ADatabase.dbo.ABC เป็นต้น

Powered by ScribeFire.

Script สำหรับสร้าง Datadict สำหรับ MSSQL

select  b.colid as [ID] , a.NAME as TABLE_NAME  , b.name  as COLUMN_NAME ,   c.name  as [TYPE], b.length as SIZE
from sysobjects a,syscolumns b,systypes c
where a.id = b.id  and a.xtype = 'U' and a.name in ('MS_TRANSPORT_MODEL') and
b.xusertype = c.xusertype

Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.

เหตุเกิดที่ว่าผมต้องสร้าง View เพื่อทำการตรวจสอบผลการทำงานทั้งหมดของระบบ เนื่องจากต้องไปวิ่งไล่ Table แทบทั้ง Database เพื่อทำการตรวจสอบความถูกต้องของข้อมูล ปรากฏว่ามัน Join กันเยอะเกินไป จากหลายๆ Table มันเลย แจ้งว่า The maximum number of tables in a query (260) was exceeded. คือ เลือก Table มามากเกินไปใน Query วิธีแก้ไขของผมคือไปสร้าง Function มาแทน แล้วใน View นี้ไปเรียก Function ที่ไปเรื่อง Table เยอะๆ อีกทีนึง (จริงๆมันก็ช้านะครับ แต่ผมรับได้กับการตรวจสอบความถูกต้องของข้อมูลก่อนนำเข้าระบบหลักแบบนี้)

ไม่ได้เขียน Blog เสียนาน เพราะไม่ค่อยมีเรื่องอะไรใหม่ๆ ในชีวิต แล้วก็หนีไปบวชมา เลยไม่ได้เขียนมาซะนาน วันนี้ได้โจทย์ว่าต้อง Delay การแสดงผล หรือ การอัพเดตข้อมูลชุด หนึ่ง ก่อนอีกชุดนึงมา ลองค้นๆ MSSQL มี คำสั่งในการชะลอการทำงาน ดังนี้

waitfor delay ‘00:00:01′

อันนี้คือ รอ 1 วินาที

ตัวอย่าง

select getdate()
waitfor delay ‘00:00:02′
select getdate()

ผลลัพท์

2007-12-11 13:26:19.163
2007-12-11 13:26:21.180

ต่างกัน 2 วินาที

เสร็จครับ

ไม่ได้เขียนมาซะนานเนื่องจากงานยุ่ง วันก่อนมีพี่คนนึงถามว่า

“เอ๊ะ ใน MSSQL มี Function Hex To Decimal และ Decimal to Hex มั้ย?”

ผมก็เอาเลยเริ่มค้นหา ก็ทำให้รู้ว่ามันไม่มี Function นี้ในตัวของมันเอง ต้องเขียนเอง เลยลองค้นๆใน Google ดู ก็พบ Function ที่ Work!! ลองเอาไปใช้งานกันดูครับ

อ่านต่อ