[2k8] SQL Server에서 프로시저로 CSV 파일 생성

/********************************************************************************************
-- Title : [2k8] SQL Server에서 프로시저로 CSV 파일 생성
-- Key word : export csv txt text
********************************************************************************************/

/**********************
-- SQLCMD 이용
**********************/
-- 서버 trusted 연결
exec master..xp_cmdshell
'sqlcmd -S ServerName -d sqldb -E -o "c:\MyData.csv" -Q "select userid, num from buytbl" -W -w 999 -s"," ';

-- 서버 계정으로 연결
exec master..xp_cmdshell
'sqlcmd -S ServerName -d sqldb -U User -P password -o "c:\MyData.csv" -Q "select ''1'', userid, num from buytbl" -W -w 999 -s"," ';

-- 쿼리를 파일로 호출
-- 파일에 set nocount on; 넣으면 "(개 행이 적용됨)" 메시지 출력 안함
exec master..xp_cmdshell
'sqlcmd -S ServerName -d sqldb -U User -P password -o "c:\MyData.csv" -i c:\query.sql';

-- 머리글 제거(-h-1) 및 컬럼 구분 없앰(-s 제거시)
exec master..xp_cmdshell
'sqlcmd -S 최명환 -d sqldb -U User -P password -o "c:\MyData.csv" -i c:\query.sql -W -h-1';

/**********************
-- BCP 이용
**********************/
exec master..xp_cmdshell
'bcp "select userid, num from sqldb.dbo.buytbl" queryout "c:\MyData2.csv" -c -t, -U sa -P elqlfkd2008'

/**********************
-- OA 개체 이용
-- http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/
**********************/
CREATE PROCEDURE [dbo].[up_write_to_csv]
 (
 @String nvarchar(max), --8000 in SQL Server 2000
 @Path nvarchar(500),
 @Filename sysname
)
AS

set nocount on

declare
 @objFileSystem int
 , @objTextStream int
 , @objErrorObject int
 , @strErrorMessage nvarchar(1000)
    , @Command nvarchar(1000)
    , @hr int
 , @fileAndPath nvarchar(1000)

select @strErrorMessage='opening the File System Object'

EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT

Select @FileAndPath = @path + '\' + @filename

if @HR=0 select @objErrorObject = @objFileSystem , @strErrorMessage = 'Creating file "' + @FileAndPath + '"'
if @HR=0 execute @hr = sp_OAMethod   @objFileSystem   , 'CreateTextFile'
 , @objTextStream OUT, @FileAndPath,2,True

if @HR=0 select @objErrorObject=@objTextStream,
 @strErrorMessage='writing to the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Write', Null, @String

if @HR=0 select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Close'

if @hr<>0
 begin
 declare
  @Source nvarchar(255),
  @Description nvarchar(255),
  @Helpfile nvarchar(255),
  @HelpID int
 
 EXECUTE sp_OAGetErrorInfo  @objErrorObject,
  @source output,@Description output,@Helpfile output,@HelpID output
 select @strErrorMessage='Error while '
   +coalesce(@strErrorMessage,'doing something')
   +', '+coalesce(@Description,'')
 raiserror (@strErrorMessage,16,1)
 end

EXECUTE sp_OADestroy @objTextStream
-- EXECUTE sp_OADestroy @objTextStream
-- 원문에 에러였음.. 여러개 파일 만들때 에러발생함. objFileSystem을 닫아주어야 함
EXECUTE sp_OADestroy @objFileSystem