Creating Writing Reading Saving a File Using Sql server
Introduction:
In this article, i am going to explain about how to create,write,save a file using sql server.
Main:
The below predefined stored procedure allows you to write a file under sql server filesystem.Just give the filename as input(File you wants to create under file system).
Create Procedure [dbo].[NetProgrammingHelp_SaveFile](@text as NVarchar(Max),@Filename Varchar(200))
AS
Begin
declare @Object int,
@rc int, -- the return code from sp_OA procedures
@FileID Int
EXEC @rc = sp_OACreate 'Scripting.FileSystemObject', @Object OUT
EXEC @rc = sp_OAMethod @Object , 'OpenTextFile' , @FileID OUT , @Filename , 2 , 1
Set @text = Replace(Replace(Replace(@text,'&','&'),'<' ,'<'),'>','>')
EXEC @rc = sp_OAMethod @FileID , 'WriteLine' , Null , @text
Exec @rc = master.dbo.sp_OADestroy @FileID
Declare @Append bit
Select @Append = 0
If @rc <> 0
Begin
Exec @rc = master.dbo.sp_OAMethod @Object, 'SaveFile',null,@text ,@Filename,@Append
End
Exec @rc = master.dbo.sp_OADestroy @Object
End
EXEC NetProgramming_SaveFile 'Microsoft SQL Server 2008', 'C:MSSQL.txt'
Create Procedure [dbo].[NetProgrammingHelp_SaveFile](@text as NVarchar(Max),@Filename Varchar(200)) AS Begin declare @Object int, @rc int, -- the return code from sp_OA procedures @FileID Int EXEC @rc = sp_OACreate 'Scripting.FileSystemObject', @Object OUT EXEC @rc = sp_OAMethod @Object , 'OpenTextFile' , @FileID OUT , @Filename , 2 , 1 Set @text = Replace(Replace(Replace(@text,'&','&'),'<' ,'<'),'>','>') EXEC @rc = sp_OAMethod @FileID , 'WriteLine' , Null , @text Exec @rc = master.dbo.sp_OADestroy @FileID Declare @Append bit Select @Append = 0 If @rc <> 0 Begin Exec @rc = master.dbo.sp_OAMethod @Object, 'SaveFile',null,@text ,@Filename,@Append End Exec @rc = master.dbo.sp_OADestroy @Object End EXEC NetProgramming_SaveFile 'Microsoft SQL Server 2008', 'C:MSSQL.txt' |
Conclusion:
Hope this helps.
Happy Coding.