The only caveat to that is when you are tying to automate a process completely from SQ Server and do not wish to involve any other programs.
It covers some tips and tricks but also goes over how you must turn on the permissions in SQL Server for this to work. You must be an admin.
Here is the code to turn on permissions for Ole Automation:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
SQL SERVER COURSE - 70% OFF FOR OUR READERS
For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.
Normally this course is $59.00, but for a limited time it is available through this offer for only $18.00!
|  |
Here is a procedure that I wrote to reuse the functionality without having to kep re-writing all of the code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[WriteToFile]
@File VARCHAR(2000),
@Text VARCHAR(2000)
AS
BEGIN
DECLARE @OLE INT
DECLARE @FileID INT
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @File, 8, 1
EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @Text
EXECUTE sp_OADestroy @FileID
EXECUTE sp_OADestroy @OLE
END
| Of course, this does you absolutely no good unless you can invoke the functionality in an easily accessible way. This next three code snippets will go over how to harness the functionality both quickly and easily.
The first example is about as dumbed down as you could make it. The second and third code snippets will be progressively more sophisticated and robust. |
So, here are the three different code examples that you can use to invoke this SQL functionality:
EXEC WriteToFile 'C:\Users\Mertman\Documents\ACME\Log Files\Log.txt','Did it work?'
Or...
DECLARE @Path VARCHAR(2000)
DECLARE @Txt VARCHAR(2000)
SET @Path = 'C:\Users\Mertman\Documents\ACME\Log Files\Log.txt'
SET @Txt = 'Did it work the next time?'
EXEC WriteToFile @Path, @Txt
Or...
DECLARE @Path VARCHAR(2000)
DECLARE @Txt VARCHAR(2000)
DECLARE @YYYY INT
DECLARE @MM INT
DECLARE @DD INT
DECLARE @YYYY_Txt VARCHAR(2000)
DECLARE @MM_Txt VARCHAR(2000)
DECLARE @DD_Txt VARCHAR(2000)
DECLARE @DateStamp VARCHAR(2000)
SET @YYYY = DATEPART(yy,GETDATE())
SET @YYYY_Txt = CAST(@YYYY AS VARCHAR(2000))
SET @MM = DATEPART(mm,GETDATE())
SET @MM_Txt = CAST(@MM AS VARCHAR(2000))
SET @DD = DATEPART(dd,GETDATE())
SET @DD_Txt = CAST(@DD AS VARCHAR(2000))
SET @DateStamp = @YYYY_Txt + '_' + @MM_Txt + '_' + @DD_Txt
SET @Path = 'C:\Users\Mertman\Documents\ACME\Log Files\Log_' + @DateStamp + '.txt'
SET @Txt = 'Did it work?'
EXEC WriteToFile @Path, @Txt
The video is here:
More questions? Check out the following related article How To Write To a Text File from SQL Server
SQL SERVER COURSE - 70% OFF FOR OUR READERS
For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.
Normally this course is $59.00, but for a limited time it is available through this offer for only $18.00!
| |
Thanks for the code, it run successfully but it didn't create the file?
ReplyDeleteHi Aya,
DeleteDo you have write permissions at the write location?
Also, try the directory directly above the one where you expect the file... that can happen if you forget the trailing "\" on the folder name...
DeleteHi Aya Wael,
ReplyDeleteDo you have write permission on the folder where you are trying to write the file? A lot of the time it is a permissions issue.
Hey - I also couldn't write to a file? I outputted to my local machine 'C:\Users\\Documents\Log.txt'
ReplyDeleteChange: 'C:\Users\\Documents\Log.txt'
Deleteto: 'C:\Users\Documents\Log.txt'
You might also want to try and write the file to a different location than a documents folder (or any folder that might be using environmental variables).
DeleteCan you try writing to the root? C:\Log.txt ?
Even i am facing the same issue unable to write to a file...
ReplyDeleteDo you have write permissions? Some versions of Windows have really difficult security issues. Try writing to C:\
DeleteIt started working after i added a "GO" statement at the end of the Procedure(WriteToFile)...
ReplyDeleteThat was all it took? Good to know, I will add this into my demo code from now on, thanks Sravs!
DeleteVery useful article..Thank you so much..
ReplyDeleteCan you have same Sp for Read from file..
It Saves my time lot..
Post your code:
ReplyDeleteI am geeting below error.please help me.
ReplyDeleteMsg 217, Level 16, State 1, Procedure WriteToFile, Line 86
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
You might be using nested triggers. Temporarily turn off the triggers on the tables and the error will go away.
ReplyDeleteALTER TABLE table_name DISABLE TRIGGER trigger_name
I have every permission and code is right then also file is not creating.. Please help
ReplyDeleteHi Jaimin, here are some questions:
Delete1. What version of SQL Server are you using?
2. Are you an admin in sql?
3. How are you logging on to SSMS? (SQL authentication or NT?)
3a. If NT, then is that user account an admin in Windows?
4. Can you go to the directory in question and create a file right now in windows?
5. What is network architecture? Are you trying to write the file to a location that is not on the same machine as either you or the server?
Note: If you are trying to write to a mapped drive then you might need to use the machines internal ip address
This comment has been removed by the author.
ReplyDeleteHow can one read errors of sp_OAMethod?
ReplyDeletetried this
EXECUTE @OLEResult = sp_OAMethod @FS,'CreateTextFile',@FileID OUTPUT, @file
if @OLERESULT<>0 EXEC sp_OAGetErrorInfo @FS, @source OUT, @desc OUT
but @source and @desc are empty
Hi Man I have tried your code several times but does't work
ReplyDeletethe procedure is been created yet noting is in my folder
Solution ERROR database 'mssqlsystemresource', schema 'sys'.
ReplyDeleteuse [master]
GO
GRANT EXECUTE ON [sys].[sp_OASetProperty] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OAMethod] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OAGetErrorInfo] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OADestroy] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OAStop] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OACreate] TO [public]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OAGetProperty] TO [public]
GO
sp_configure 'show advanced options', 1
GO
reconfigure
go
exec sp_configure
go
exec sp_configure 'Ole Automation Procedures', 1
-- Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install.
go
reconfigure
go