分类:
2008-10-13 17:07:10
CREATE PROCEDURE sp_testdll AS exec sp_addextendedproc 'SetFileName', 'storeproc.dll' --声明函数 exec sp_addextendedproc 'addLine', 'storeproc.dll' declare @szFileName varchar(200) declare @szText varchar(200) declare @rt int Select @szFileName = 'c:\welcome.txt' EXEC @rt = SetFileName @szFileName --调用SetFileName函数,参数为--szFileName; if @rt = 0 begin select @szText = 'welcome 01' Exec @rt = addLine @szText --调用addLine select @szText = 'welcome 02' Exec @rt = addLine @szText end exec sp_dropextendedproc 'SetFileName' exec sp_dropextendedproc 'addLine' dbcc SetFileName(free) dbcc addLine(free)动态连接库的实现:这种动态连接库和普通的有所不同。该动态连接库要放入SQL的执行目录下,或直接放到Window的System32目录下,并重起SQL-Server
#include编译完成后,把动态链接库放到WINNT/System32目录下,启动SQL Server。我们可以打开SQL Server Query Analyzer调用存储过程sp_testdll以测试其运行是否正确。#include //要加入这个.h文件 #define XP_NOERROR 0 #define XP_ERROR 1 #ifndef _DEBUG #define _DEBUG #endif char szFileName[MAX_PATH+1]; void WriteInfo(const char * str); extern "C" SRVRETCODE WINAPI SetFileName(SRV_PROC* pSrvProc) { WriteInfo("SetFileName start"); int paramCount = srv_rpcparams(pSrvProc); if (paramCount != 1){ WriteInfo("Param Err start"); return XP_ERROR; } BYTE bType; unsigned long cbMaxLen; unsigned long cbActualLen; BOOL fNull; int ret = srv_paraminfo(pSrvProc, 1, &bType, &cbMaxLen, &cbActualLen, NULL, &fNull); if (cbActualLen){ ZeroMemory(szFileName, MAX_PATH+1); memcpy(szFileName, srv_paramdata(pSrvProc, 1), cbActualLen); WriteInfo("Set filename ok"); return (XP_NOERROR); } else { WriteInfo("Set filename param failed"); return XP_ERROR; } } extern "C" SRVRETCODE WINAPI addLine(SRV_PROC* pSrvProc) { WriteInfo("addline start"); int paramCount = srv_rpcparams(pSrvProc); if (paramCount != 1){ WriteInfo("addline param err"); return XP_ERROR; } BYTE bType; unsigned long cbMaxLen; unsigned long cbActualLen; BOOL fNull; bool rt = false; int ret = srv_paraminfo(pSrvProc, 1, &bType, &cbMaxLen, &cbActualLen, NULL, &fNull); if (cbActualLen){ int n; char srt[3] = {0x0d, 0x0a, 0}; char * c = new char[cbActualLen + 3]; if (!c)return XP_ERROR; ZeroMemory(c, cbActualLen + 3); memcpy(c, srv_paramdata(pSrvProc, 1), cbActualLen); memcpy(c+cbActualLen, srt, 3); HANDLE hf = CreateFile(szFileName, GENERIC_WRITE, FILE_SHARE_WRITE|FILE_SHARE_READ, NULL, OPEN_ALWAYS, 0, NULL); if (hf == INVALID_HANDLE_VALUE){ WriteInfo("addline create file err "); delete []c; return XP_ERROR; } WriteInfo("addline create file ok "); DWORD dwWt; n = strlen(c); SetFilePointer(hf, 0, NULL, FILE_END); if (WriteFile(hf, c, n, &dwWt, NULL) && dwWt == n) { WriteInfo("addline write file ok "); rt = true; } delete []c; CloseHandle(hf); } return rt ? XP_NOERROR:XP_ERROR; } inline void WriteInfo(const char * str){ #ifdef _DEBUG char srt[3] = {0x0d, 0x0a, 0}; HANDLE hf = CreateFile("c:\\storeproc.log", GENERIC_WRITE, FILE_SHARE_WRITE|FILE_SHARE_READ, NULL, OPEN_ALWAYS, 0, NULL); if (hf != INVALID_HANDLE_VALUE){ SetFilePointer(hf, 0, NULL, FILE_END); DWORD dwWt; WriteFile(hf, str, strlen(str), &dwWt, NULL); WriteFile(hf, srt, strlen(srt), &dwWt, NULL); CloseHandle(hf); } else { MessageBox(NULL, "Write info err", "Message", MB_OK|MB_ICONINFORMATION); } #endif } BOOL WINAPI DllMain(HINSTANCE hinstDLL,DWORD fdwReason,LPVOID lpReserved) { return TRUE; }