확장 저장 프로시저를 데이터베이스 엔진 프로그래밍
적용 대상: 
SQL Server
Important
SQL Server의 이후 버전에서는 이 기능이 제거됩니다. 새 개발 작업에서는 이 기능을 사용하지 않도록 하고, 현재 이 기능을 사용하는 애플리케이션은 수정하세요. 대신 CLR 통합을 사용합니다.
확장 저장 프로시저의 작동 방식
확장 저장 프로시저가 작동하는 프로세스는 다음과 같습니다.
클라이언트가 확장 저장 프로시저를 실행하면 클라이언트 애플리케이션에서 SQL Server로 TDS(테이블 형식 데이터 스트림) 또는 SOAP(Simple Object Access Protocol) 형식으로 요청이 전송됩니다.
SQL Server는 확장 저장 프로시저와 연결된 DLL을 검색하고 아직 로드되지 않은 경우 DLL을 로드합니다.
SQL Server는 요청된 확장 저장 프로시저를 호출합니다(DLL 내에서 함수로 구현됨).
확장 저장 프로시저는 확장 저장 프로시저 API를 통해 결과 집합을 전달하고 매개 변수를 다시 서버로 반환합니다.
과거에 Open Data Services는 비 SQL Server 데이터베이스 환경에 게이트웨이와 같은 서버 애플리케이션을 작성하는 데 사용되었습니다. SQL Server는 Open Data Services API의 사용되지 않는 부분을 지원하지 않습니다. SQL Server에서 계속 지원되는 원래 Open Data Services API의 유일한 부분은 확장 저장 프로시저 함수이므로 API의 이름이 확장 저장 프로시저 API로 바뀌었습니다.
분산 쿼리 및 CLR 통합이 등장하면서 확장 저장 프로시저 API 애플리케이션의 필요성이 크게 대체되었습니다.
기존 게이트웨이 애플리케이션이 있는 경우 SQL Server와 함께 제공되는 해당 애플리케이션을 사용하여 opends60.dll 애플리케이션을 실행할 수 없습니다. 게이트웨이 애플리케이션은 더 이상 지원되지 않습니다.
확장 저장 프로시저 및 CLR 통합
CLR 통합은 표현하기 어렵거나 Transact-SQL에서 작성할 수 없었던 서버 쪽 논리를 작성하는 보다 강력한 대안을 제공합니다. SQL Server의 이전 릴리스에서 XP(확장 저장 프로시저)는 데이터베이스 애플리케이션 개발자가 이러한 코드를 작성할 수 있는 유일한 메커니즘을 제공했습니다.
CLR 통합을 사용하면 저장 프로시저 형식으로 작성된 논리가 테이블 반환 함수로 더 잘 표현되는 경우가 많으며, 이를 통해 함수에 의해 생성된 결과를 문에 포함시켜 문에 SELECT FROM 쿼리할 수 있습니다.
자세한 내용은 CLR 통합 개요를 참조하세요.
확장 저장 프로시저의 실행 특성
확장 저장 프로시저 실행에는 다음과 같은 세 가지 특징이 있습니다.
확장 저장 프로시저 함수는 SQL Server의 보안 컨텍스트에서 실행됩니다.
확장 저장 프로시저 함수는 SQL Server의 프로세스 공간에서 실행됩니다.
확장 저장 프로시저의 실행과 연결된 스레드는 클라이언트 연결에 사용되는 스레드와 동일합니다.
Important
서버에 확장 저장 프로시저를 추가하고 다른 사용자에게 실행 권한을 부여하기 전에 시스템 관리자는 각 확장 저장 프로시저를 철저히 검토하여 유해하거나 악의적인 코드가 포함되지 않았는지 확인해야 합니다.
확장 저장 프로시저 DLL이 로드된 후에는 SQL Server가 중지되거나 관리자가 DLL을 사용하여 DBCC <DLL_name> (FREE)DLL을 명시적으로 언로드할 때까지 DLL이 서버의 주소 공간에 로드된 상태로 유지됩니다.
다음 문을 사용하여 EXECUTE Transact-SQL에서 확장 저장 프로시저를 저장 프로시저로 실행할 수 있습니다.
EXECUTE @retval = xp_extendedProcName @param1, @param2 OUTPUT;
매개 변수
@ retval
반환 값입니다.
@ param1
입력 매개 변수입니다.
@ param2
입력/출력 매개 변수입니다.
주의
확장 저장 프로시저는 성능 향상을 제공하고 SQL Server 기능을 확장합니다. 그러나 확장 저장 프로시저 DLL과 SQL Server는 동일한 주소 공간을 공유하므로 문제 프로시저가 SQL Server 작동에 부정적인 영향을 줄 수 있습니다. 확장 저장 프로시저 DLL에서 throw된 예외는 SQL Server에서 처리되지만 SQL Server 데이터 영역이 손상될 수 있습니다. 보안 예방 조치로 SQL Server 시스템 관리자만 SQL Server에 확장 저장 프로시저를 추가할 수 있습니다. 이러한 프로시저는 설치되기 전에 철저히 테스트해야 합니다.
확장 저장 프로시저 API를 사용하여 서버에 결과 집합 보내기
SQL Server에 결과 집합을 보낼 때 확장 저장 프로시저는 다음과 같이 적절한 API를 호출해야 합니다.
함수는
srv_sendmsg모든 행(있는 경우)이 있는srv_sendrow이전 또는 이후의 순서로 호출될 수 있습니다. 완료 상태가 함께 전송되기 전에 모든 메시지를 클라이언트로srv_senddone보내야 합니다.이
srv_sendrow함수는 클라이언트로 전송된 각 행에 대해 한 번 호출됩니다. 메시지, 상태 값 또는 완료 상태가 함께 전송되기 전에 모든 행을 클라이언트로srv_sendmsgsrv_statussrv_pfieldsrv_senddone보내야 합니다.정의된 열
srv_describe이 모두 없는 행을 보내면 애플리케이션이 정보 오류 메시지를 발생시키고 클라이언트로 돌아갑니다FAIL. 이 경우 행이 전송되지 않습니다.
확장 저장 프로시저 만들기
확장 저장 프로시저는 프로토타입을 사용하는 C/C++ 함수입니다.
SRVRETCODE xp_extendedProcName (SRVPROC *);
접두 xp_ 사 사용은 선택 사항입니다. 확장 저장 프로시저 이름은 서버에 설치된 코드 페이지/정렬 순서에 관계없이 Transact-SQL 문에서 참조할 때 대/소문자를 구분합니다. DLL을 빌드하는 경우:
진입점이 필요한 경우 함수를 작성합니다
DllMain.이 함수는 선택 사항입니다. 소스 코드에서 제공하지 않으면 컴파일러는 고유한 버전을 연결합니다. 이 버전은 반환
TRUE만 수행합니다. 함수를DllMain제공하는 경우 스레드 또는 프로세스가 DLL에 연결되거나 DLL에서 분리되면 운영 체제에서 이 함수를 호출합니다.DLL 외부에서 호출된 모든 함수(모든 확장 저장 프로시저 Efunction)는 내보내야 합니다.
파일 섹션에 해당 이름을
EXPORTS나열하여 함수를 내보낼 수도 있고, 소스 코드__declspec(dllexport)의.def함수 이름 앞에 Microsoft 컴파일러 확장(__declspec()두 개의 밑줄로 시작)을 접두사로 지정할 수도 있습니다.
확장 저장 프로시저 DLL을 만드는 데 필요한 파일은 다음과 같습니다.
| 파일 | 설명 |
|---|---|
srv.h |
확장 저장 프로시저 API 헤더 파일 |
opends60.lib |
에 대한 라이브러리 가져오기 opends60.dll |
확장 저장 프로시저 DLL을 만들려면 동적 링크 라이브러리 형식의 프로젝트를 만듭니다. DLL을 만드는 방법에 대한 자세한 내용은 개발 환경 설명서를 참조하세요.
모든 확장 저장 프로시저 DLL은 다음 함수를 구현하고 내보내야 합니다.
__declspec(dllexport) ULONG __GetXpVersion()
{
return ODS_VERSION;
}
__declspec(dllexport) 는 Microsoft 관련 컴파일러 확장입니다. 컴파일러가 이 지시문을 지원하지 않는 경우 이 함수를 파일의 DEF 섹션 아래에 EXPORTS 내보내야 합니다.
SQL Server가 추적 플래그 -T260 로 시작되거나 시스템 관리자 권한이 있는 사용자가 실행 DBCC TRACEON (260)되고 확장 저장 프로시저 DLL이 지원 __GetXpVersion()되지 않는 경우 다음 경고 메시지가 오류 로그에 출력됩니다(__GetXpVersion() 두 개의 밑줄로 시작).
Error 8131: Extended stored procedure DLL '%' does not export __GetXpVersion().
확장 저장 프로시저 DLL이 내보내 __GetXpVersion()지만 함수에서 반환된 버전이 서버에 필요한 버전보다 작으면 함수에서 반환된 버전과 서버에서 예상하는 버전을 나타내는 경고 메시지가 오류 로그에 출력됩니다. 이 메시지가 표시되면 잘못된 값을 __GetXpVersion()반환하거나 이전 버전의 srv.h.
참고 항목
SetErrorModeWin32 함수는 확장 저장 프로시저에서 호출하면 안 됩니다.
장기 실행 확장 저장 프로시저는 주기적으로 호출 srv_got_attention 되어야 하므로 연결이 종료되거나 일괄 처리가 중단된 경우 프로시저가 자체적으로 종료될 수 있습니다.
확장 저장 프로시저 DLL을 디버그하려면 SQL Server \Binn 디렉터리에 복사합니다. 디버깅 세션에 대한 실행 파일을 지정하려면 SQL Server 실행 파일의 경로 및 파일 이름(예: C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn\sqlservr.exe)을 입력합니다. 인수에 대한 sqlservr 자세한 내용은 sqlservr Application을 참조 하세요.
SQL Server에 확장 저장 프로시저 추가
확장 저장 프로시저 함수를 포함하는 DLL은 SQL Server에 대한 확장 역할을 합니다. DLL을 설치하려면 표준 SQL Server DLL 파일이 포함된 디렉터리(C:\Program Files\Microsoft SQL Server\MSSQL16.0.<x>\MSSQL\Binn 기본적으로)와 같은 디렉터리에 파일을 복사합니다.
확장 저장 프로시저 DLL을 서버에 복사한 후 SQL Server 시스템 관리자는 DLL의 각 확장 저장 프로시저 함수를 SQL Server에 등록해야 합니다. 이 작업은 시스템 저장 프로시저를 sp_addextendedproc 사용하여 수행됩니다.
Important
시스템 관리자는 확장 저장 프로시저를 철저히 검토하여 서버에 추가하고 다른 사용자에게 실행 권한을 부여하기 전에 유해하거나 악성 코드가 포함되지 않도록 해야 합니다. 모든 사용자 입력에 대해 유효성을 검사합니다. 유효성을 검사하기 전에 사용자 입력을 연결하지 마세요. 유효성 검사가 수행되지 않은 사용자 입력으로부터 생성된 명령은 실행하지 마세요.
첫 sp_addextendedproc 번째 매개 변수는 함수의 이름을 지정하고 두 번째 매개 변수는 해당 함수가 상주하는 DLL의 이름을 지정합니다. DLL의 전체 경로를 지정해야 합니다.
참고 항목
SQL Server 2005(9.x) 이상 버전으로 업그레이드한 후에는 전체 경로에 등록되지 않은 기존 DLL이 작동하지 않습니다. 문제를 sp_dropextendedproc 해결하려면 DLL의 등록을 취소한 다음 전체 경로를 지정하여 다시 등록 sp_addextendedproc, 합니다.
지정된 sp_addextendedproc 함수의 이름은 DLL의 함수 이름과 대/소문자를 포함하여 정확히 동일해야 합니다. 예를 들어 이 명령은 이름이 xp_hello.dlldll에 있는 함수 xp_hello, 를 SQL Server 확장 저장 프로시저로 등록합니다.
sp_addextendedproc 'xp_hello', 'c:\Program Files\Microsoft SQL Server\MSSQL13.0.MSSQLSERVER\MSSQL\Binn\xp_hello.dll';
지정된 sp_addextendedproc 함수의 이름이 DLL의 함수 이름과 정확히 일치하지 않으면 새 이름이 SQL Server에 등록되지만 이름을 사용할 수 없습니다. 예를 들어 SQL Server 확장 저장 프로시저로 등록되어 있지만 xp_Hello xp_hello.dll나중에 함수를 호출하는 데 사용하는 xp_Hello 경우 SQL Server는 DLL에서 함수를 찾을 수 없습니다.
-- Register the function (xp_hello) with an initial upper case
sp_addextendedproc 'xp_Hello', 'c:\xp_hello.dll';
-- Use the newly registered name to call the function
DECLARE @txt VARCHAR(33);
EXEC xp_Hello @txt OUTPUT;
오류 메시지는 다음과 같습니다.
Server: Msg 17750, Level 16, State 1, Procedure xp_Hello, Line 1
Could not load the DLL xp_hello.dll, or one of the DLLs it references. Reason: 127(The specified procedure could not be found.).
지정된 sp_addextendedproc 함수의 이름이 DLL의 함수 이름과 정확히 일치하고 SQL Server 인스턴스의 데이터 정렬이 대/소문자를 구분하지 않는 경우 사용자는 이름의 소문자와 대문자 문자를 조합하여 확장 저장 프로시저를 호출할 수 있습니다.
-- Register the function (xp_hello)
sp_addextendedproc 'xp_hello', 'c:\xp_hello.dll';
-- The following example succeeds in calling xp_hello
DECLARE @txt VARCHAR(33);
EXEC xp_Hello @txt OUTPUT;
DECLARE @txt VARCHAR(33);
EXEC xp_HelLO @txt OUTPUT;
DECLARE @txt VARCHAR(33);
EXEC xp_HELLO @txt OUTPUT;
SQL Server 인스턴스의 데이터 정렬이 대/소문자를 구분하는 경우 프로시저가 다른 경우 호출되는 경우 SQL Server는 확장 저장 프로시저를 호출할 수 없습니다. DLL의 함수와 정확히 동일한 이름 및 데이터 정렬로 등록된 경우에도 마찬가지입니다.
-- Register the function (xp_hello)
sp_addextendedproc 'xp_hello', 'c:\xp_hello.dll';
-- The following example results in an error
DECLARE @txt VARCHAR(33);
EXEC xp_HELLO @txt OUTPUT;
오류 메시지는 다음과 같습니다.
Server: Msg 2812, Level 16, State 62, Line 1
SQL Server를 중지하고 다시 시작할 필요가 없습니다.
SQL Server에 설치된 확장 저장 프로시저 쿼리
SQL Server 인증된 사용자는 시스템 프로시저를 실행 sp_helpextendedproc 하여 현재 정의된 확장 저장 프로시저와 각 프로시저가 속한 DLL의 이름을 표시할 수 있습니다. 예를 들어 다음 예제에서는 속한 DLL을 xp_hello 반환합니다.
sp_helpextendedproc 'xp_hello';
확장 저장 프로시저를 지정하지 않고 실행되는 경우 sp_helpextendedproc 모든 확장 저장 프로시저와 해당 DLL이 표시됩니다.
SQL Server에서 확장 저장 프로시저 제거
사용자 정의 확장 저장 프로시저 DLL에서 각 확장 저장 프로시저 함수를 삭제하려면 SQL Server 시스템 관리자가 시스템 저장 프로시저를 실행 sp_dropextendedproc 하여 함수의 이름과 해당 함수가 상주하는 DLL의 이름을 지정해야 합니다. 예를 들어 이 명령은 SQL Server에서 명명 xp_hello.dll, 된 DLL에 있는 함수xp_hello를 제거합니다.
sp_dropextendedproc 'xp_hello';
sp_dropextendedproc 는 시스템 확장 저장 프로시저를 삭제하지 않습니다. 대신 시스템 관리자는 퍼블릭 역할에 대한 확장 저장 프로시저에 대한 사용 권한을 거부 EXECUTE 해야 합니다.
확장 저장 프로시저 DLL 언로드
SQL Server는 DLL의 함수 중 하나를 호출하는 즉시 확장 저장 프로시저 DLL을 로드합니다. DLL은 서버가 종료되거나 시스템 관리자가 문을 사용하여 DBCC 언로드할 때까지 로드된 상태로 유지됩니다. 예를 들어 이 명령은 시스템 관리자가 서버를 종료하지 않고 이 파일의 최신 버전을 디렉터리에 복사할 수 있도록 언로드 xp_hello.dll합니다.
DBCC xp_hello(FREE);