Зачем может потребоваться писать в текстовые файлы из хранимых процедур? Например, банально, для лога работы (хотя его разумнее было бы организовать таблицей в базе). В моем случае необходимо было выгружать по запросу пользователя текстовый файл с результатами огромного количества запросов. Гнать данные на сторону ASP.NET, затем распарсивать, затем писать в файл стредствами дотнета – мне показалось черезчур сложным. Поэтому был найден вариант создания файла напрямую из БД.
Перво-наперво, необходимо сконфигурировать SQL Server на работу с OLE-объектами.
[cc lang="tsql"]
EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures’, 1;
GO
RECONFIGURE;
GO
– и проверим, что опция config_value установилась в 1
EXEC sp_configure ‘Ole Automation Procedures’;
GO
[/cc]
Результаты работы скрипта:
Configuration option ‘show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘Ole Automation Procedures’ changed from 1 to 1. Run the RECONFIGURE statement to install.
Ура. Первый шаг сделан. Теперь в базе master создадим пользовательскую функцию для записи в файл, выполняем скрипт ниже:
[cc lang="tsql"]
USE [master]
GO
CREATE FUNCTION [dbo].[fn_WriteToTextFile]
(
@FileName varchar(1024)
, @Text varchar(MAX)
)
RETURNS int
AS
BEGIN
DECLARE @Fso int, @Err int, @FileID int, @Result int
– инициализируем FSO
EXECUTE @Err = sp_OACreate ‘Scripting.FileSystemObject’, @Fso OUT
IF @Err <> 0 BEGIN SET @Result = -1 GOTO final END
– открываем новый текстовый файл на запись
EXECUTE @Err = sp_OAMethod @Fso, ‘CreateTextFile’, @FileID OUT, @FileName
IF @Err <> 0 BEGIN SET @Result = -1 GOTO final END
–пишем текст
EXECUTE @Err = sp_OAMethod @FileID, ‘Write’, Null, @Text
IF @Err <> 0 BEGIN SET @Result = -1 GOTO final END
– закрываем файл
SET @Result = 0
final:
EXECUTE @Err = sp_OADestroy @FileID
EXECUTE @Err = sp_OADestroy @Fso
RETURN @Result
END
GO
[/cc]
И используем ее следующим образом:
[cc lang="tsql"]
SELECT master.dbo.fn_WriteToTextFile(‘c:\myfile.txt’, ‘Содержимое файла.’);
[/cc]
В случае ошибки функция вернет значение -1.
AppendToFile
Другой вариант той же функции (менее полезный, но вдруг пригодится), добавляющий строку к текстовому файлу – вот вам и простейший лог, кстати говоря.
[cc lang="tsql"]
USE [master]
GO
CREATE FUNCTION [dbo].[fn_AppendToTextFile]
(
@FileName varchar(1024)
, @Text varchar(MAX)
)
RETURNS int
AS
BEGIN
DECLARE @Fso int, @Err int, @FileID int, @Result int
– инициализируем FSO
EXECUTE @Err = sp_OACreate ‘Scripting.FileSystemObject’, @Fso OUT
IF @Err <> 0 BEGIN SET @Result = -1 GOTO final END
– открываем новый текстовый файл на запись
EXECUTE @Err = sp_OAMethod @Fso, ‘OpenTextFile’, @FileID OUT, @FileName, 8, 1
IF @Err <> 0 BEGIN SET @Result = -1 GOTO final END
–пишем текст
EXECUTE @Err = sp_OAMethod @FileID, ‘WriteLine’, Null, @Text
IF @Err <> 0 BEGIN SET @Result = -1 GOTO final END
– закрываем файл
SET @Result = 0
final:
EXECUTE @Err = sp_OADestroy @FileID
EXECUTE @Err = sp_OADestroy @Fso
RETURN @Result
END
GO
[/cc]
Отличие – в вызове методов OpenTextFile вместо CreateTextFile, а также метода WriteLine вместо Write.