SQL Server 預存程序呼叫外部 DLL 實作範例

應用場景

資料表異動觸發 Trigger → 呼叫 Master 資料庫擴充預存程序 → 載入外部 DLL → 透過訊息/事件通知應用程式，避免輪詢資料庫。

環境需求

• VC6 需安裝 Platform SDK（含 srv.h、opends60.lib）
• VC.NET 可直接編譯
• DLL 需置於 SQL Server Binn 目錄或 System32，並重啟 SQL 服務

擴充預存程序 (Master)

CREATE PROCEDURE sp_testdll AS
    EXEC sp_addextendedproc 'SetFileName', 'storeproc.dll'
    EXEC sp_addextendedproc 'addLine', 'storeproc.dll'
    DECLARE @file VARCHAR(200), @txt VARCHAR(200), @rt INT
    SELECT @file = 'c:\welcome.txt'
    EXEC @rt = SetFileName @file
    IF @rt = 0 BEGIN
        SELECT @txt = 'welcome 01'; EXEC @rt = addLine @txt
        SELECT @txt = 'welcome 02'; EXEC @rt = addLine @txt
    END
    EXEC sp_dropextendedproc 'SetFileName'
    EXEC sp_dropextendedproc 'addLine'
    DBCC SetFileName(FREE); DBCC addLine(FREE)

DLL 實作 (storeproc.cpp)

#include 
#include            // 必須
#define XP_NOERROR 0
#define XP_ERROR   1

char g_file[MAX_PATH];

void Log(const char* s) {
    #ifdef _DEBUG
    HANDLE h=CreateFile("c:\\storeproc.log", GENERIC_WRITE, FILE_SHARE_READ|FILE_SHARE_WRITE,
                        NULL, OPEN_ALWAYS, 0, NULL);
    if (h!=INVALID_HANDLE_VALUE) {
        SetFilePointer(h,0,NULL,FILE_END);
        DWORD w; WriteFile(h,s,strlen(s),&w,NULL); WriteFile(h,"\r\n",2,&w,NULL);
        CloseHandle(h);
    }
    #endif
}

extern "C" SRVRETCODE WINAPI SetFileName(SRV_PROC* p) {
    Log("SetFileName start");
    if (srv_rpcparams(p)!=1) return XP_ERROR;
    BYTE t; ULONG maxLen, actLen; BOOL isNull;
    srv_paraminfo(p,1,&t,&maxLen,&actLen,NULL,&isNull);
    if (actLen) { ZeroMemory(g_file,MAX_PATH); memcpy(g_file,srv_paramdata(p,1),actLen); Log("Set filename ok"); return XP_NOERROR; }
    return XP_ERROR;
}

extern "C" SRVRETCODE WINAPI addLine(SRV_PROC* p) {
    Log("addLine start");
    if (srv_rpcparams(p)!=1) return XP_ERROR;
    BYTE t; ULONG maxLen, actLen; BOOL isNull;
    srv_paraminfo(p,1,&t,&maxLen,&actLen,NULL,&isNull);
    if (actLen) {
        char* buf=new char[actLen+3]; ZeroMemory(buf,actLen+3);
        memcpy(buf,srv_paramdata(p,1),actLen); memcpy(buf+actLen,"\r\n",3);
        HANDLE h=CreateFile(g_file,GENERIC_WRITE,FILE_SHARE_READ|FILE_SHARE_WRITE,NULL,OPEN_ALWAYS,0,NULL);
        if (h==INVALID_HANDLE_VALUE) { delete[] buf; return XP_ERROR; }
        SetFilePointer(h,0,NULL,FILE_END); DWORD w;
        BOOL ok=WriteFile(h,buf,strlen(buf),&w,NULL) && w==strlen(buf);
        delete[] buf; CloseHandle(h);
        return ok?XP_NOERROR:XP_ERROR;
    }
    return XP_ERROR;
}

BOOL WINAPI DllMain(HINSTANCE, DWORD, LPVOID) { return TRUE; }

測試步驟

1. 編譯 DLL → 複製到 C:\WINNT\System32 或 SQL Binn 目錄
2. 重啟 SQL Server 服務
3. Query Analyzer 執行 EXEC sp_testdll
4. 檢查 c:\welcome.txt 內容與 c:\storeproc.log 除錯訊息

參考

SQL Server 線上叢書：擴充預存程序程式設計參考。