MSSQL2008에서 XP_CMDSHELL 활성화 시키기

2013년 08월 12일 golgol [DB]MSSQL Server 0


-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

참조
MS-SQL Server 2000 에서 사용하던 xp_cmdshell 을 MS-SQL Server 2005, 2008 에서 사용하고 싶을때는

SELECT * FROM sys.configurations WHERE name = 'xp_cmdshell'

을 사용하여 xp_cmdshell 항목의 value 값을 체크한다.
value 값이 0으로 되어 있을 경우 이것을 1로 변경하면 사용이 가능하다.

sys.configurations 값을 변경할 때는 sp_configure 라는 시스템 저장프로시저를
이용하여 값을 변경한다.

EXEC sp_configure 'show advanced options', 1
go
RECONFIGURE
go
EXEC sp_configure 'xp_cmdshell', 1
go
RECONFIGURE
go

위와 같이 변경하면 사용이 가능하다.

간혹, 위 스크립트 실행시 다음과 같은 에러가 출력된다면

‘시스템 카탈로그에 대한 임의 업데이트는 지원되지 않습니다.’

이 문제는 sp_configure 저장 프로시저의 업데이트 허용 매개 변수가 1로 설정된 경우 발생한다. 이 문제를 해결하려면 업데이트 허용 매개 변수를 0으로 설정한다.

SELECT * FROM sys.configurations WHERE name = 'allow updates'

allow updates 의 설정값이 1로 되어있을 경우 아래의 스크립트를 실행하여 0으로 변경한다.

EXEC sp_configure 'allow updates', '0'
go
RECONFIGURE WITH OVERRIDE
go

위와 같이 실행 후 다시 sp_configure 값을 변경하면 된다.

참고 )
http://msdn.microsoft.com/ko-kr/library/ms190693(SQL.100).aspx
http://technet.microsoft.com/ko-kr/library/ff487039.aspx
[출처] MS-SQL Server 2008 에서 xp_cmdshell 사용 설정|작성자 winc21

=>오류

구성 옵션 ‘show advanced options’이(가) 1에서 1(으)로 변경되었습니다. RECONFIGURE 문을 실행하여 설치하십시오.
메시지 5845, 수준 16, 상태 1, 줄 2
AWE(Address Windowing Extensions)에는 현재 프로세스의 액세스 토큰에 없는 ‘메모리의 페이지 잠금’ 권한이 있어야 합니다.
구성 옵션 ‘xp_cmdshell’이(가) 0에서 0(으)로 변경되었습니다. RECONFIGURE 문을 실행하여 설치하십시오.
메시지 5845, 수준 16, 상태 1, 줄 2
AWE(Address Windowing Extensions)에는 현재 프로세스의 액세스 토큰에 없는 ‘메모리의 페이지 잠금’ 권한이 있어야 합니다.

샘플

USE [DataDownload]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    	<허>
-- Create date: <2013-08-27>
-- Description:	<다운로드>
-- AirDataDownload
-- =============================================
ALTER PROCEDURE [dbo].[AirDataDownload]
	-- Add the parameters for the stored procedure here

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

Declare @iWeek int --요일 : 1:일요일 2:월요일 3:화요일 4:수요일 5:목요일 6:금요일 7:토요일
Declare @NowDate DateTime -- 오늘날짜

Declare @MON int --월
Declare @TUE int --화
Declare @WED int --수 
Declare @THU int --목
Declare @FRI int --금
Declare @SAT int --토
Declare @SUN int --일

Set @MON = 0
Set @TUE = 0
Set @WED = 0
Set @THU = 0
Set @FRI = 0
Set @SAT = 0
Set @SUN = 0 
Set @NowDate = CONVERT(CHAR(10),GETDATE(), 23) -- fomat: 03 30 2012 12:00AM
-- 오늘 요일 구하기 --
Set @iWeek = (select datepart(dw,@NowDate))

--test
--Set @iWeek =2

if @iWeek=1  --일요일
begin
	Set @SUN = 1 
end
else if @iWeek=2 --월요일
begin
   Set @MON = 1
end
else if @iWeek=3 --화요일
begin
   Set @TUE = 1
end
else if @iWeek=3 --수요일
begin
   Set @WED = 1
end
else if @iWeek=4 --목요일
begin
   Set @THU = 1
end
else if @iWeek=5 --금요일
begin
   Set @SAT = 1
end
else if @iWeek=6 --토요일
begin
   Set @SUN = 1
end

Declare @SID varchar(3)
Declare @Airline varchar(2)
Declare @OnlyUpdateair int 

--
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1

-- To update the currently configured value for advanced options.
RECONFIGURE

-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1

-- To update the currently configured value for this feature.
RECONFIGURE
--

  Declare con_cursor cursor for SELECT TOP 1000 [Agency].[SID]
  FROM [DataDownload].[dbo].[Agency]
  where [Agency].Active =1 and [Agency].OnlyUpdateair=1 
        and MON>=@MON and TUE>=@TUE and WED>=@WED and THU>=@THU and FRI>=@FRI and SAT>=@SAT and SUN>=@SUN

	open con_cursor --커스를 연다.

	fetch next from con_cursor into @SID

	while (@@fetch_status <>-1)
	begin
	if (@@fetch_status = -2)
	continue

	begin
	-- 처리
    --print @SID
    --print @Airline
    -- # 시작 #
    DECLARE @FileName varchar(50), 
		@bcpCommand varchar(8000) 

SET @FileName = 'D:\DataDownload\'+@SID+'\Delete_'+ CONVERT(char(10),GETDATE(),120)+'.csv'

SET @bcpCommand = 'SELECT [SerialNumber] ' 
      + ',[airCode] ' 
      + ',[AgentCode] ' 
      + ',[Airlinecode] ' 
      + ',[AreaCode] ' 
      + ',[DeleteDate] ' 
  + 'FROM [Finix].[dbo].[HistorySalesairDelete] '
  + 'Where DeleteDate>GetDate()-1'
		
SET @bcpCommand = 'bcp "' + @bcpCommand + '" queryout "'+ @FileName + '" -t, -T -S -U -P -c'
EXEC master..xp_cmdshell @bcpCommand 

--print @FileName
--print @bcpCommand
--EXEC(@bcpCommand)
    
    -- # 끝 #
	end

	fetch next from con_cursor into @SID
	end

	close con_cursor
	Deallocate con_cursor


-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1

-- To update the currently configured value for advanced options.
RECONFIGURE

-- To disable the feature.
EXEC sp_configure 'xp_cmdshell', 0

-- To update the currently configured value for this feature.
RECONFIGURE


END