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!
| |
