Tag Archive sql server

SQL Server: Kill process ของข้าให้หมด

มีแก้งานที่เขียน query ได้โหดยูเครนมาก (รัสเซียได้พิสูจน์ให้เห็นแล้วว่ากาก) ใช้ cpu สูงมาก ใช้ ram เกือบหมดเม็ค แล้วมีคนใช้งานอยู่พร้อม ๆ กัน T-T เลยหา script มาจัดการดีด connection ที่ใช้อยู่ในเครื่องตัวเองทิ้งยกเว้นที่ใช้อยู่ใน sql server management studio (ssms) และโปรแกรม app ที่กำลังเขียนอยู่

DECLARE @SqlCmd VARCHAR(1000)
  
	,@HostName VARCHAR(100)-- Set the hostname name from which to kill the connections

  
	--SET @HostName = 'Pitt_P'

SET @HostName = HOST_NAME()

SET @SqlCmd = ''  SELECT @Sqlcmd = @SqlCmd + CHAR(13) + CHAR(10) + 'KILL ' + convert(CHAR(10), spid) + ' '
 
FROM master.dbo.sysprocesses
 
WHERE hostname = @HostName
 
	AND DBID <> 0
 
	AND spid <> @@spid PRINT @sqlcmd EXEC(@Sqlcmd)
 
GO

แก้จากต้นแบบ Kill all processes associated with a hostname โดยดึงข้อมูล HOST HOST_NAME แทนที่จะใส่ชื่อเครื่องตัวเองไป

​ Cannot resolve the collation conflict between​ "Thai_CI_AS" and "Thai_CI_AI"

ดึงข้อมูลจากหลาย database เจอ error

​[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Cannot resolve the collation conflict between “Thai_CI_AS” and “Thai_CI_AI” in the UNION operation.

สาเหตุคือทั้ง 4 database มี table ชื่อเดียวกัน โครงสร้างเหมือนกัน แต่ collation ใช้ ต่างกัน แถมตรวจดูตารางอื่นๆ ก็ใช้กระจัดกระจาย Thai_CI_AI บ้าง Thai_CI_AS บ้าง ดีที่เจอ query ที่แก้ให้ได้ง่ายๆ เอามาดัดแปลงอักนิดให้เรียงตามชื่อตารางก็ใช้ง่ายเลย

DECLARE @collate SYSNAME
SELECT @collate = 'Thai_CI_AS'
SELECT
      '[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] -> ' + c.name AS a
    , 'ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']
        ALTER COLUMN [' + c.name + '] ' +
        UPPER(t.name) +
        CASE WHEN t.name NOT IN ('ntext', 'text')
            THEN '(' +
                CASE
                    WHEN t.name IN ('nchar', 'nvarchar') AND c.max_length != -1
                        THEN CAST(c.max_length / 2 AS VARCHAR(10))
                    WHEN t.name IN ('nchar', 'nvarchar') AND c.max_length = -1
                        THEN 'MAX'
                    ELSE CAST(c.max_length AS VARCHAR(10))
                END + ')'
            ELSE ''
        END + ' COLLATE ' + @collate +
        CASE WHEN c.is_nullable = 1
            THEN ' NULL'
            ELSE ' NOT NULL'
        END
FROM sys.columns c WITH(NOLOCK)
JOIN sys.objects o WITH(NOLOCK) ON c.[object_id] = o.[object_id]
JOIN sys.types t WITH(NOLOCK) ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN ('char', 'varchar', 'text', 'nvarchar', 'ntext', 'nchar')
    AND c.collation_name != @collate
    AND o.[type] = 'U'
ORDER BY a

เอาผลลัพธ์ ใน column ที่ 2 ไป execute อีกครั้งก็ใช้ได้

ขอบคุณ Cr: Devart สำหรับคำตอบใน how to change the collate to all the columns of the database?

MySql ต่างกับ sql server

อ่านเจอคำถามนี้ใน facebook

อยากทราบว่าถ้าเราเปลี่ยน database จาก mysql เป็น sql server 2008 นอกจากส่วน connect แล้ว โค๊ดเก่าจากmysql จะสามารถใช้ได้เหมือนเดิมไหมครับ (ผมใช้ codeigniter เขียนครับ)

เลยลองยกตัวอย่างดู โดยใช้ PDO แทน function ของแต่ละ database จะได้เห็นได้ชัดเจน

schema_mysql.php สำหรับ mysql

<?php
$database = 'yii2advanced';

try {
    $dbh = new PDO(
        'mysql:host=localhost;dbname=' . $database,
        'root',
        ''
    );
} catch (PDOException $e) {
    exit('Error!: ' . $e->getMessage());
}
$dbh->query('SET NAMES utf8');

switch ($_GET['op']) {
    case 'columns':{
            $table = $_GET['table'];

            $sql = "SELECT `COLUMN_NAME` AS name
    ,`DATA_TYPE` AS type
    ,IFNULL(`CHARACTER_MAXIMUM_LENGTH`, 0) AS maxlength
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '" . $table . "';";
            $sth = $dbh->prepare($sql);
            $sth->execute();
            $results = [];
            if ($sth->rowCount() > 0) {
                while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
                    array_push($results, $row);
                }
                echo json_encode($results, JSON_NUMERIC_CHECK);
            }
        }break;
    case 'tables':{
            $sql = 'SHOW TABLES IN ' . $database . ';';
            $sth = $dbh->prepare($sql);
            $sth->execute();
            $results = [];
            if ($sth->rowCount() > 0) {
                while ($row = $sth->fetch(PDO::FETCH_COLUMN)) {
                    array_push($results, $row);
                }
            }
            echo json_encode($results);
        }break;
}

และ schema_sqlsrv.php สำหรับ Microsoft sql server

<?php
$database = 'TPA';

try {
    $dbh = new PDO(
        'sqlsrv:Server=MAGI\SQLEXPRESS;Database=' . $database
    );
} catch (PDOException $e) {
    exit('Error!: ' . $e->getMessage());
}
$dbh->query('SET CHARACTER_SET utf8_unicode_ci');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

switch ($_GET['op']) {
    case 'columns':{
            $table = $_GET['table'];

            $sql = "SELECT [COLUMN_NAME] AS name
    ,[DATA_TYPE] AS type
    ,ISNULL([CHARACTER_MAXIMUM_LENGTH], 0) AS maxlength
FROM TPA.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '" . $table . "';";
            $sth = $dbh->prepare($sql, [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);
            $sth->execute();
            $results = [];
            if ($sth->rowCount() > 0) {
                while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
                    array_push($results, $row);
                }
                echo json_encode($results, JSON_NUMERIC_CHECK);
            }
        }break;
    case 'tables':{
            $sql = 'SELECT [TABLE_NAME] FROM ' . $database . '.INFORMATION_SCHEMA.Tables;';
            $sth = $dbh->prepare($sql, [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);
            $sth->execute();
            $results = [];
            if ($sth->rowCount() > 0) {
                while ($row = $sth->fetch(PDO::FETCH_COLUMN)) {
                    array_push($results, $row);
                }
                echo json_encode($results);
            }
        }break;
}

ถึงจะพยามออกแบบ extension ตัวนี้ให้ทำงานได้ทุก database แต่แค่การ connection ก็ต่างกันแล้วแต่ละตัวต่างก็มี ตัวเลือกเพิ่มเติมไม่เหมือนกัน อย่าง การเลือกชุดตัวอักษร SET NAMES utf8 และ SET CHARACTER_SET utf8_unicode_ci ใน sql server (ภาษาไทย ภาษาเดียวก็มีให้เลือกกันเหนื่อยแล้วว่าจะใช้ตัวไหน ลองอ่าน LAB TEST : ความแตกต่างของ SQL THAI Collation แต่ละแบบ) จุดนี้พอจะเข้าใจได้เพราะมาจากคนละบริษัทกัน มาจากคนละความคิด

จุดต่อมา mySql ใช้ ` ` แต่ sql server ใช้ [ ] ในการบอกว่านี่คือชื่อเฉพาะนะ วิธีแก้ก็ง่ายๆ อย่าไปใส่มัน ยกเว้นว่าจะไปใช้ชื่อที่ตรงกับศัพท์สงวน (reserved words) เอาไว้ จำเป็นต้องใช้เพื่อให้ตัว database ไม่สับสน

ถ้าต้องต้องเปลี่ยนฐานข้อมูลก็หลีกเลี่ยงคำพวกนี้ไว้ก่อน

คำสั่งเกี่ยวกับโครงสร้างฐานข้อมูล ต่างเจ้าก็ต่างกัน ทั้งชื่อ type และ key บางครั้งชื่อเดียวกันแต่เก็บข้อมูลได้ไม่เท่ากัน การจะดึงข้อมูลโครงสร้างตารางออกมาก็ใช้คำสั่งต่างกัน ในตัวอย่าง switch case ทั้งสองตัวจะเป็นการ query ดูตารางทั้งหมดในฐานข้อมูลและชนิดของฟิลย์ในตารางทั้งหมด ซึ่งไม่ได้ไกล้เคียงกันเลย (นานๆ จะใช้ที ไม่ต้องไปจำมันก็ได้ ยกเว้นคุณจะเขียน curd ใช้เอง)

การใช้ Abstraction Layers แทนที่จะใช้ Vendor Specific Database Extensions หรือที่เรียกกันว่า native driver ที่เป็นของฐานข้อมูลแต่ละตัว เพราะว่าต้องการที่จะใช้คำสั่งเหมือนๆ กัน ไม่ต้องมาคิดว่าฐานข้อมูลแบบนี้ใช้ function ชื่อนี้ เวลาเปลี่ยน ชนิดฐานข้อมูลก็แค่เปลี่ยนตรง connection ก็พอแล้วไม่ต้องเขียนคำสั่งใหม่ แต่จริงๆ แล้วยังไม่มี Abstraction Layers ตัวไหน หรือของ framework ไหน ไม่ว่าจะเป็น codeigniter, yii, laravel แม้แต่ .net หรือ java ที่แปลง sql query แล้วสามารถทำงานได้เหมือนกันกับทุกๆ ตัว แค่ทำได้ 80% ของทั้งหมดในตัวอย่าง pdo ของ sql server ถ้าไม่ระบุ PDO::ATTR_CURSOR เพิ่มเข้าไป มันก็จะไม่รู้เลยว่า query ออกมามีผลลัพธ์ออกมารึเปล่า และถ้าสังเกตุจะมีคำสั่งที่ใช้ตรวจสอบค่า NULL และแทนด้วยค่าอื่นใน MySQL ใช้ IFNULL แต่ SQL SERVER ใช้ ISNULL การทำงาน โครงสร้างเหมือนกัน แต่เขียนไม่เหมือนกัน แทนกันไม่ได้ (:

เชื่อมต่อพีเฮชพีกับ sql server (sqlsrv)

ถึง mysql จะฟรี ดี รับงานหนักๆ ได้ แต่องค์กรหรือบริษัทใหญ่ๆจะเชื่อถือ microsoft sql server มากกว่าคู่บารมี คู่แท้ php & mysql วันนี้เราจะมาทำให้ php connect กับเอสคิวแอล เซิร์ฟเวอร์กัน โดยใช้ driver ตัวใหม่ sqlsrv แทน mssql ที่โดนยกเลิกไป

  1. เช็ค version ของ php ก่อนโดย run command php -v
  2. download Microsoft Drivers for PHP for SQL Server จากเว็บ http://www.microsoft.com โดยตรง เลือก version ให้ตรงกับ php ที่เราใช้
  3. เปิด ตัว driver ขึ้นมา
    • กด yes ยอมรับข้อตกลงซะ คุณจะได้ไปต่อ
    • เลือกโพลเดอร์ที่จะแตกไฟล์ชั่วคราว ยังไม่ใช่ที่ๆ จะติดตั้งโปรแกรมจริง ๆ
    • หลังจาก สร้างไฟล์ใน folder ที่เราเลือกเสร็จแล้วจะมี alert ขึ้นมา แล้วมันจะปิดตัวเองไม่ต้องตกใจ
  4. เข้าไปดูโฟลเดอร์ที่เลือกไว้ จะเห็นไฟล์ เยอะแยะเลย หาไฟล์ที่เลขตรงกับเวอร์ชั่นเดียวกับ php ของเรา เช่น ใช้ php 5.6 ก็เลือกไฟล์ _56 จะเห็นว่ามี 4 ไฟล์ จะสังเกตุว่า มี _nts และ _ts ความหมายก็คือ
    _nts
    ย่อมาจาก Non Thread Safe สำหรับ php ที่ลงแบบ CGI binary
    _ts
    ย่อมาจาก Thread Safe สำหรับ php ที่ลงแบบ Apache module ถ้าใช้ xampp จะใช้ตัวนี้
  5. เปิดโฟลเดอร์เอ็กเท็นชั่นของพีเอชพี xampp จะอยู่ที่ C:\xampp\php\ext คัดลอกไฟล์ที่เราเลือกมา (แค่ 2 ไฟล์) มาวางเพิ่มเข้าไป
  6. เปิดไฟล์ php.ini xampp อยู่ที่ C:\xampp\php\php.ini ค้นหาบรรทัดที่มี extension=
    แล้วเพิ่ม config ไปตามตัวอย่าง

    extension=php_sqlsrv_56_ts.dll
    extension=php_pdo_sqlsrv_56_ts.dll
    
  7. ถ้าลง sql server ในเครื่องเดียวกับ apache ให้ไปปิด service “SQL Server Reporting Services” ก่อน และเปลี่ยน startup type เป็น manual ก่อนเพราะมันใช้ port 80 เหมือนเว็บเซิร์ฟเวอร์ เป็นสาเหตุที่ทำให้ apache start service ไม่ได้
  8. restart apache ไปดู phpinfo ถ้ามี pdo_sqlsrv และ sqlsrv ขึ้นมาก็แสดงว่าลงเอสคิวแอล เซิร์ฟเวอร์ไดร์เวอร์สำเร็จ

ลบตารางทั้งฐานข้อมูล SQL Server

ช่วงนี้ มีแก้ ลบ โครงสร้าง database บ่อยมาก จะลบตารางออกแล้ว import จากฐานข้อมูลตัว dev มา บางตารางก็มี relation กับตารางอื่นๆ ยุบยับไปหมด ไม่ยอมให้ลบ หาเจอ query ชุดนี้มันใช่เลย สะดวกมากๆ

โปรดใช้จักรยานในการปฏิบัติการ T-T
DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec SP_EXECUTESQL @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END

CLOSE @Cursor DEALLOCATE @Cursor
GO

EXEC sp_MSForEachTable 'DROP TABLE ?'
GO