[SQL SERVER][TSQL]匯出Store Procedure內容並存成檔案
前幾天同事問我在SQL2005中有沒有辦法使用TSQL匯出Store Procedure內容並存成檔案，
由於之前一直沒時間測試，所以趕快利用今天下班時間終於完成該需求，自己記錄一下。
注意: Sql Service Account也需要有相關系統存取權限
1.Enable Ole Automation Procedures
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
2.Create usp_WriteContentToFile(寫入檔案)
create PROCEDURE dbo.usp_WriteContentToFile
(
@Content ntext,
@Path NVARCHAR(255),
@Filename NVARCHAR(100)
)
AS
DECLARE @objFileSystem int,
@objTextStream int,
@objErrorObject int,
@strErrorMessage nvarchar(1000),
@Command nvarchar(1000),
@hr int,
@fileAndPath nvarchar(100)
set nocount on
select @strErrorMessage='開啟檔案系統物件'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT
Select @fileAndPath=@Path+'\'+@Filename
if @hr=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='檔案建立路徑: '+@fileAndPath+''
if @hr=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @fileAndPath,2,True
if @hr=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='檔案寫入路徑: '+@fileAndPath+''
if @hr=0 execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @Content
if @hr=0 Select @objErrorObject=@objTextStream, @strErrorMessage='檔案關閉: '+@fileAndPath+''
if @hr=0 execute @hr = sp_OAMethod @objTextStream, 'Close'
if @hr<>0
begin
Declare
@Source varchar(255),
@Description varchar(255),
@Helpfile varchar(255),
@HelpID int
EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'執行發生錯誤')
+', '+coalesce(@Description,'')
raiserror (@strErrorMessage,16,1)
end
EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream
執行
結果
參考
Reading and Writing Files in SQL Server using T-SQL