적용 대상:
SQL Server
중요합니다
이 기능은 이후 버전의 SQL Server에서 제거됩니다. 새 개발 작업에서 이 기능을 사용하지 말고 현재 이 기능을 사용하는 애플리케이션을 수정할 계획입니다. 대신 CLR 통합 을 사용하세요.
확장 저장 프로시저의 작동 원리
확장 저장 프로시저가 작동하는 과정은 다음과 같습니다:
클라이언트가 확장 저장 프로시저를 실행할 때, 요청은 클라이언트 애플리케이션에서 SQL Server로 테이블 형식의 데이터 스트림(TDS) 또는 단순 객체 접근 프로토콜(SOAP) 형식으로 전송됩니다.
SQL Server는 확장 저장 프로시저와 연관된 DLL을 검색하고, 아직 로드되지 않은 DLL을 불러옵니다.
SQL Server는 요청된 확장 저장 프로시저(DLL 내에서 구현된 함수로 구현됨)를 호출합니다.
확장 저장 프로시저 API를 통해 결과 집합과 매개변수를 서버에 반환합니다.
과거에는 오픈 데이터 서비스가 비SQL Server 데이터베이스 환경으로 가는 게이트웨이와 같은 서버 애플리케이션을 작성하는 데 사용되었습니다. SQL Server는 오픈 데이터 서비스 API의 구식 부분을 지원하지 않습니다. 원래 Open Data Services API에서 SQL Server에서 여전히 지원되는 부분은 확장 저장 프로시저 함수뿐이므로, API는 확장 저장 프로시저 API로 이름이 변경되었습니다.
분산 쿼리와 CLR 통합의 등장으로 확장 저장 절차 API 애플리케이션에 대한 필요성은 대부분 대체되었습니다.
기존 게이트웨이 애플리케이션이 있다면 SQL Server에 기본 제공되는 게이트웨이 opends60.dll 애플리케이션으로 애플리케이션을 실행할 수 없습니다. 게이트웨이 애플리케이션은 더 이상 지원되지 않습니다.
확장 저장 프로시저 vs. CLR 통합
CLR 통합은 Transact-SQL에서 표현하기 어렵거나 불가능했던 서버 측 논리를 작성하는 것보다 더 견고한 대안을 제공합니다. 초기 SQL Server 버전에서는 확장 저장 프로시저(XP)가 데이터베이스 애플리케이션 개발자가 이러한 코드를 작성할 수 있는 유일한 수단이었습니다.
CLR 통합을 통해 이전에 저장 프로시저 형태로 작성되던 논리는 종종 테이블 값 함수로 표현하는 것이 더 적합한데, 이는 함수가 생성한 결과를 절에 FROM 임베드하여 문장에 SELECT 쿼리할 수 있게 합니다.
자세한 내용은 CLR 통합 개요를 참조하세요.
확장 저장 프로시저의 실행 특성
확장 저장 프로시저의 실행은 다음과 같은 특징을 가집니다:
확장 저장 프로시저 함수는 SQL Server의 보안 컨텍스트 하에서 실행됩니다.
확장 저장 프로시저 함수는 SQL 서버의 프로세스 공간에서 실행됩니다.
확장 저장 프로시저 실행에 연관된 스레드는 클라이언트 연결에 사용되는 스레드와 동일합니다.
중요합니다
서버에 확장 저장 프로시저를 추가하고 다른 사용자에게 실행 권한을 부여하기 전에, 시스템 관리자는 각 확장 저장 프로시저에 해롭거나 악의적인 코드가 포함되어 있지 않은지 철저히 검토해야 합니다.
확장 저장 프로시저 DLL이 로드된 후, DLL은 SQL Server가 중지되거나 관리자가 명시적으로 DLL DBCC <DLL_name> (FREE)을 언로드할 때까지 서버의 주소 공간에 로드된 상태로 남아 있습니다.
확장 저장 프로시저는 다음 문장을 사용하여 EXECUTE 저장 프로시저로 Transact-SQL에서 실행할 수 있습니다:
EXECUTE @retval = xp_extendedProcName @param1, @param2 OUTPUT;
매개 변수
@ 리발
수익 가치입니다.
@ 파라멘트 1
입력 매개 변수입니다.
@ 파라마 2
입출력 매개변수입니다.
주의
확장 저장 프로시저는 성능 향상과 SQL 서버 기능을 확장합니다. 하지만 확장 저장 프로시저인 DLL과 SQL Server가 동일한 주소 공간을 공유하기 때문에, 문제 프로시저는 SQL Server 기능에 부정적인 영향을 미칠 수 있습니다. 확장 저장 프로시저 DLL이 던지는 예외는 SQL Server에서 처리하지만, SQL Server 데이터 영역에 손상을 줄 수 있습니다. 보안 예방 차원에서, SQL Server 시스템 관리자만이 SQL Server에 확장 저장 프로시저를 추가할 수 있습니다. 이러한 절차들은 설치 전에 철저히 테스트되어야 합니다.
확장 저장 프로시저 API가 포함된 서버로 결과 집합을 전송하세요
결과 집합을 SQL Server로 보낼 때, 확장 저장 프로시저가 적절한 API를 다음과 같이 호출해야 합니다:
함수는
srv_sendmsg모든 행(있다면)이 와 함께 있기srv_sendrow전이나 후에 어떤 순서로든 호출될 수 있습니다. 완료 상태가 전송srv_senddone되기 전에 모든 메시지는 클라이언트에 전송되어야 합니다.srv_sendrow이 함수는 클라이언트에 전송되는 각 행마다 한 번씩 호출됩니다. 모든 행은 메시지, 상태 값 또는 완료 상태srv_sendmsg가 , 의srv_status인자srv_pfield, 또는srv_senddone로 전송되기 전에 클라이언트에 전송되어야 합니다.모든 열이 정의
srv_describe되지 않은 행을 보내면 애플리케이션이 정보 오류 메시지를 발생시키고 클라이언트로 반환FAIL됩니다. 이 경우에는 그 로우가 전송되지 않습니다.
확장 저장 프로시저 생성
확장 저장 프로시저는 프로토타입을 가진 C/C++ 함수입니다:
SRVRETCODE xp_extendedProcName (SRVPROC *);
접두사 xp_ 사용은 선택 사항입니다. 확장된 저장 프로시저 이름은 서버에 설치된 코드 페이지/정렬 순서와 관계없이 Transact-SQL 문에서 참조될 때 대문자 구분이 가능합니다. DLL을 만들 때:
진입점이 필요하다면, 함수를
DllMain작성하세요.이 함수는 선택 사항입니다. 소스 코드에 제공하지 않으면, 컴파일러가 자체 버전을 링크하는데, 이 버전은 아무 일도 하지 않고 단지 를 반환할
TRUE뿐입니다. 함수를DllMain제공하면, 운영체제는 스레드나 프로세스가 DLL에 연결되거나 분리될 때 이 함수를 호출합니다.DLL 외부에서 호출되는 모든 함수(모든 확장 저장 프로시저 Efunctions)는 내보내야 합니다.
파일 섹션
.def에EXPORTS함수 이름을 나열해 내보낼 수 있고, 소스 코드__declspec(dllexport)에서 함수 이름 앞에 Microsoft 컴파일러 확장자(__declspec()두 개의 밑줄로 시작하는 확장자)를 붙일 수도 있습니다.
이 파일들은 확장 저장 프로시저 DLL을 생성하는 데 필요합니다.
| File | Description |
|---|---|
srv.h |
확장 저장 프로시저 API 헤더 파일 |
opends60.lib |
임포트 라이브러리 opends60.dll |
확장 저장 프로시저 DLL을 생성하려면 Dynamic Link Library 타입의 프로젝트를 생성하세요. DLL 생성에 대한 자세한 내용은 개발 환경 문서를 참조하세요.
모든 확장 저장 프로시저 DLL은 다음 함수를 구현하고 내보내야 합니다:
__declspec(dllexport) ULONG __GetXpVersion()
{
return ODS_VERSION;
}
__declspec(dllexport) 마이크로소프트 전용 컴파일러 확장 프로그램입니다. 컴파일러가 이 지침을 지원하지 않는다면, 해당 섹션 아래 EXPORTS 파일에 이 함수를 DEF 내보내야 합니다.
SQL Server가 trace 플래그 -T260 로 시작되거나 시스템 관리자 권한이 있는 사용자가 실행 DBCC TRACEON (260)되고, 확장 저장 프로시저 DLL이 지원되지 __GetXpVersion()않을 경우, 다음 경고 메시지가 오류 로그에 인쇄됩니다(__GetXpVersion() 는 두 개의 밑줄로 시작합니다).
Error 8131: Extended stored procedure DLL '%' does not export __GetXpVersion().
확장 저장 프로시저 DLL이 내보내지만 __GetXpVersion(), 함수가 반환하는 버전이 서버가 요구하는 버전보다 작으면, 함수가 반환한 버전과 서버가 예상하는 버전을 경고 메시지로 오류 로그에 출력합니다. 이 메시지가 뜨면 에서 잘못된 값을 __GetXpVersion()반환하고 있거나, 오래된 버전의 srv.h.로 컴파일하고 있는 것입니다.
비고
SetErrorMode, Win32 함수는 확장 저장 프로시저에서 호출되어서는 안 됩니다.
장기 실행 확장 저장 프로시저들은 주기적으로 호출 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 사용하여 수행됩니다.
중요합니다
시스템 관리자는 확장된 저장 프로시저를 꼼꼼히 검토하여 서버에 추가하고 다른 사용자에게 실행 권한을 부여하기 전에 해롭거나 악성 코드가 포함되어 있지 않은지 확인해야 합니다. 모든 사용자 입력에 대해 유효성을 검사합니다. 사용자 입력을 검증하기 전에 연결하지 마세요. 유효성 검사가 수행되지 않은 사용자 입력으로부터 생성된 명령은 실행하지 마세요.
첫 번째 매개변수는 sp_addextendedproc 함수의 이름을 지정하고, 두 번째 매개변수는 해당 함수가 존재하는 DLL의 이름을 지정합니다. DLL의 전체 경로를 명시해야 합니다.
비고
완전한 경로로 등록되지 않은 기존 DLL은 SQL Server 2005(9.x) 또는 그 이후 버전으로 업그레이드한 후 작동하지 않습니다. 문제를 sp_dropextendedproc 해결하려면 DLL을 등록 해제한 후 완전한 경로를 지정하여 sp_addextendedproc, 다시 등록하세요.
에서 sp_addextendedproc 지정된 함수의 이름은 경우를 포함해 DLL에서 함수 이름과 정확히 동일해야 합니다. 예를 들어, 이 명령어는 dll xp_hello.dll내에 위치한 함수 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.dll로 등록되어 있음에도 불 xp_Hello 구하고, SQL Server가 나중에 해당 함수를 호출하면 xp_Hello 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 서버에서 확장 저장 프로시저를 제거하세요
사용자 정의 확장 프로시저 DLL에서 각 확장 저장 프로시저 함수를 삭제하려면, SQL Server 시스템 관리자는 해당 함수의 이름과 해당 함수가 위치한 DLL의 이름을 지정하여 시스템 저장 프로시저를 sp_dropextendedproc 실행해야 합니다. 예를 들어, 이 명령어는 SQL Server에서 이름이 붙은 xp_hello.dll, DLL에 위치한 함수 xp_hello를 제거합니다:
sp_dropextendedproc 'xp_hello';
sp_dropextendedproc 시스템 확장 저장 프로시저를 드롭하지 않습니다. 대신 시스템 관리자는 확장 저장 프로시저에 대한 권한을 공개 역할에 거부 EXECUTE 해야 합니다.
확장 저장 프로시저 DLL을 언로드합니다
SQL Server는 DLL의 함수 중 하나에 호출이 이루어지면 확장 저장 프로시저 DLL을 로드합니다. DLL은 서버가 종료되거나 시스템 관리자가 문장을 DBCC 사용해 DLL을 언로드할 때까지 계속 로드됩니다. 예를 들어, 이 명령어는 를 언로드하여 xp_hello.dll시스템 관리자가 서버를 종료하지 않고 이 파일의 최신 버전을 디렉터리에 복사할 수 있게 합니다:
DBCC xp_hello(FREE);