Пример записи информации в txt файл из SQL запроса

Автор

Сообщение

aav
Администратор

Зарегистрирован: 14.09.2004
Сообщения: 1081
Откуда: Санкт-Петербург

Добавлено: 10.08.2011 20:42 Заголовок сообщения: Пример записи информации в txt файл из SQL запроса
По просьбе одного из участников публикую пример запроса для записи информации в текстовый файл:

Код:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*
Повторная распечатка этикетки по номеру проводки из таблицы INV_TRANSACTION
Запись в текстовый файл для Bar Tender’а

07.04.2006 А.В.Вишняков
08.12.2009 А.А.Васильев

*/
CREATE PROC [dbo].[usr_WriteToTxtFile]
@transaction_no nvarchar(10)=’62183′,—Номер проводки
@CATALOG_NAME varchar(255)=’D:\Collect\WDSCAN\OLD’,—Название каталога, который сканирует Commander
@LabelFileName varchar(255)=’D:\Collect\LABELS_TEST\WOLabel.btw’,—Имя шаблона этикетки
@PrinterName varchar(255)=’KM350_TCA’—Имя принтера, на который будет отправлены данные для печати этикетки
AS
declare @TEXT_FILE_NAME varchar(255)
DECLARE @FS int, @OLEResult int, @FileID int
DECLARE @LineString varchar(8000), @hr int, @source varchar(30), @desc varchar (200)
DECLARE @PATH_AND_FILE varchar(255)
— Зададим уникальное имя файла с расширением .dat
set @TEXT_FILE_NAME=left(NewId(),36)+’.dat’
— ОПРЕДЕЛИМ ИМЯ ФАЙЛА С КАТАЛОГОМ
set @CATALOG_NAME =
case
when len(@CATALOG_NAME)>0 and left(reverse(@CATALOG_NAME),1)<>’\’ then @CATALOG_NAME+’\’
else @CATALOG_NAME
end

set @PATH_AND_FILE = @CATALOG_NAME + @TEXT_FILE_NAME

— CREATE FSO-OBJECT
EXEC @OLEResult = sys.sp_OACreate ‘Scripting.FileSystemObject’, @FS OUTPUT
IF @OLEResult <> 0 GOTO Error_Handler

— CREATE FILE-OBJECT
EXEC @OLEResult = sys.sp_OAMethod @FS,’CreateTextFile’, @FileID OUTPUT, @PATH_AND_FILE
IF @OLEResult <> 0 GOTO Error_Handler

— ПИШЕМ В ТЕКСТОВЫЙ ФАЙЛ
—————————————————————————
— первые 2 строки — командные для Bar Tender Commander’а
set @LineString=’%BTW% /AF="’+@LabelFileName+’" /PRN="’+@PrinterName+’" /P /D="<Trigger File Name>" /DD /R=3 /C=1 /X’
EXEC @OLEResult = sys.sp_OAMethod @FileID, ‘WriteLine’, NULL, @LineString
set @LineString=’%END%’
EXEC @OLEResult = sys.sp_OAMethod @FileID, ‘WriteLine’, NULL, @LineString
— названия полей, используемых в этикетке
set @LineString=’"serial_no","wo_qty","item_without_fac","input_lot_no","location","W_O_NO","BILL_ID","COMPLETE_DATE",
"ITEM_DESCRIPTION","ENTERED_BY","HOLD_STATUS","ITEM_REVISION_LEVEL","LAST_UPDATED_BY","LOT_NO","ORDER_STATUS","ORDER_TYPE",
"QTY","QTY_COMPLETED","QTY_SCRAP","RELEASE_DATE","SCHEDULED_DATE","SCHEDULED_START_DATE","FACILITY","YIELD",
"NEGATIVE_BALANCE","ABC_CODE","ALLOCATION_STRATEGY","ASSEMBLY","BIN_CODE","CHANGE_DATE","CHANGE_USER","COMMENT",
"COST_STRATEGY","COST_STANDARD","COST_AVERAGE","CREATE_DATE","DRAWING_NO","ECN_NO","EXPIRATION_PERIOD","EXPIRATION_U_M",
"HEIGHT","INSPECT","INV_STATUS","INVENTORY_U_M","ISSUE_STRATEGY","ITEM_CLASS","ITEM_DESC","ITEM_LONG_DESC","ITEM_NO",
"ITEM_TYPE","LAST_UPDATE","LEAD_TIME","LENGTH","LONG_ITEM_NO","LOT_CONTROLLED","LOT_CONTROLLED_SIMPLE",
"LOT_EXPIRY_DAYS_SALES","LOT_EXPIRY_DAYS_USAGE","LOT_SIZE","MAX_ORDER_QTY","MIN_ORDER_QTY","PROCUREMENT_CODE",
"PURCHASING_U_M","REORDER_POINT","REORDER_QTY","SAFETY_STOCK","SELLING_U_M","SERIAL_CONTROLLED","SIZE_U_M","STORE_TYPE",
"USER_DEF_1","USER_DEF_2","USER_DEF_3","UPC_CODE","WEIGHT","WEIGHT_CLASS_CODE","WEIGHT_U_M","WIDTH","YIELD_FACTOR",
"OVER_PICK_PERCENTAGE","SERIAL_CONTROLLED_SIMPLE","LOT_CONTROLLED_SHIPPING","SERIAL_CONTROLLED_DATA_COLLECTION",
"LOCATION_CONTROLLED","BREAK_CONTAINER_PERCENTAGE"’
EXEC @OLEResult = sys.sp_OAMethod @FileID, ‘WriteLine’, NULL, @LineString
— содержимое полей (собственно данные, которые будут напечатаны на этикетке)
select @LineString=’,"’+convert(nvarchar(10),to_qty)+’","’+to_item_no+’",,,,,,"’+right(year(it.create_date),2)+right(’0′
+convert(nvarchar,DATEPART(week, it.create_date)),2)+’|’+right(’0000000’+transaction_no,7)+’|’+ left(substring(to_item_no,3,3)+
‘.’+substring(to_item_no,6,3)+’.’+substring(to_item_no,9,2)+space(10),10)+’|’+convert(nvarchar,convert(int,round(to_qty*weight,0)))
+’kg",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"’+item_desc+’",,"’+to_item_no+’",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"0"’
from INV_TRANSACTION it (nolock)
join ITEM_MASTER im (nolock)
on it.to_item_no=im.item_no
where transaction_no=convert(int,left(@transaction_no,7))
EXEC @OLEResult = sys.sp_OAMethod @FileID, ‘WriteLine’, NULL, @LineString
— 2 пустые строки
set @LineString=»
EXEC @OLEResult = sys.sp_OAMethod @FileID, ‘WriteLine’, NULL, @LineString
set @LineString=»
EXEC @OLEResult = sys.sp_OAMethod @FileID, ‘WriteLine’, NULL, @LineString

GOTO Done — EXIT PROCEDURE

Error_Handler:
EXEC @hr = sys.sp_OAGetErrorInfo null, @source OUT, @desc OUT
SELECT hr = CONVERT (binary(4), @hr), source = @source, description = @desc

Done:
EXEC @OLEResult = sys.sp_OADestroy @FileID
EXEC @OLEResult = sys.sp_OADestroy @FS

RETURN @@error + @OLEResult

Последний раз редактировалось: aav (15.10.2012 18:15), всего редактировалось 1 раз

Jugulator
Главный форумщик

Зарегистрирован: 08.10.2004
Сообщения: 428

Добавлено: 20.04.2012 15:52 Заголовок сообщения: Чтение и запись текстовых файлов в SQL с использованием FSO.
Есть статья Фила Фактора (Phil Factor) "Чтение и запись файлов в SQL Server с использованием T-SQL" ("Reading and Writing Files in SQL Server using T-SQL"). В статье как раз используется ‘Scripting.FileSystemObject’, как и в коде выше: для записи файлов процедура spWriteStringToFile, а для чтения ufsReadfileAsString.

aav
Администратор

Зарегистрирован: 14.09.2004
Сообщения: 1081
Откуда: Санкт-Петербург

Добавлено: 15.10.2012 18:30 Заголовок сообщения: Re: Чтение и запись текстовых файлов в SQL с использованием

Jugulator писал(а):

Есть статья Фила Фактора (Phil Factor) "Чтение и запись файлов в SQL Server с использованием T-SQL"

Наконец-то дошли руки да и появилась реальная надобность это попробовать. С удивлением обнаружил, что приведенные автором функции не работают: у него, по всей видимости, collations не case sensitive, а у меня CS и поэтому у меня неряшливый стиль программирования, когда в одном месте указано @hr, а в другом — @HR, не проходит. В общем, публикую исправленный вариант:

Код:

CREATE FUNCTION [dbo].[uftReadfileAsTable]
(
@path VARCHAR(255),
@filename VARCHAR(100)
)
RETURNS
@File TABLE
(
[LineNo] int identity(1,1),
line varchar(8000))

AS
BEGIN

DECLARE @objFileSystem int
,@objTextStream int,
   @objErrorObject int,
   @strErrorMessage Varchar(1000),
   @command varchar(1000),
   @hr int,
   @String VARCHAR(8000),
   @YesOrNo INT

select @strErrorMessage=’opening the File System Object’
EXECUTE @hr = sp_OACreate ‘Scripting.FileSystemObject’ , @objFileSystem OUT

if @hr=0 Select @objErrorObject=@objFileSystem, @strErrorMessage=’Opening file "’+@path+’\’+@filename+’"’,@command=@path+’\’+@filename

if @hr=0 execute @hr = sp_OAMethod @objFileSystem , ‘OpenTextFile’
, @objTextStream OUT, @command,1,false,0—for reading, FormatASCII

WHILE @hr=0
BEGIN
if @hr=0 Select @objErrorObject=@objTextStream,
@strErrorMessage=’finding out if there is more to read in "’+@filename+’"’
if @hr=0 execute @hr = sp_OAGetProperty @objTextStream, ‘AtEndOfStream’, @YesOrNo OUTPUT

if @hr=0 Select @objErrorObject=@objTextStream,
@strErrorMessage=’closing the output file "’+@filename+’"’
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,’doing something’)
      +’, ‘+coalesce(@Description,»)
insert into @File(line) select @strErrorMessage
end
EXECUTE sp_OADestroy @objTextStream
— Fill the table variable with the rows for your result set

RETURN
END

и ещё:

Код:

CREATE FUNCTION [dbo].[ufsReadfileAsString]
(
@path VARCHAR(255),
@filename VARCHAR(100)
)
RETURNS
Varchar(max)
AS
BEGIN

DECLARE @objFileSystem int
,@objTextStream int,
   @objErrorObject int,
   @strErrorMessage Varchar(1000),
   @command varchar(1000),
   @chunk Varchar(8000),
   @string varchar(max),
   @hr int,
   @YesOrNo int

Select @string=»
select @strErrorMessage=’opening the File System Object’
EXECUTE @hr = sp_OACreate ‘Scripting.FileSystemObject’ , @objFileSystem OUT

if @hr=0 Select @objErrorObject=@objFileSystem, @strErrorMessage=’Opening file "’+@path+’\’+@filename+’"’,@command=@path+’\’+@filename

if @hr=0 execute @hr = sp_OAMethod @objFileSystem , ‘OpenTextFile’
, @objTextStream OUT, @command,1,false,0—for reading, FormatASCII

IF @YesOrNo<>0 break
if @hr=0 Select @objErrorObject=@objTextStream,
@strErrorMessage=’reading from the output file "’+@filename+’"’
if @hr=0 execute @hr = sp_OAMethod @objTextStream, ‘Read’, @chunk OUTPUT,4000
SELECT @string=@string+@chunk
end
if @hr=0 Select @objErrorObject=@objTextStream,
@strErrorMessage=’closing the output file "’+@filename+’"’
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,’doing something’)
      +’, ‘+coalesce(@Description,»)
select @string=@strErrorMessage
end
EXECUTE sp_OADestroy @objTextStream
— Fill the table variable with the rows for your result set

RETURN @string
END

aav
Администратор

Зарегистрирован: 14.09.2004
Сообщения: 1081
Откуда: Санкт-Петербург

Добавлено: 24.10.2012 21:27 Заголовок сообщения: Re: Пример записи информации в txt файл из SQL запроса
Недавно столкнулся с интересной проблемой. Опишу все подробно и по порядку, может кому-то пригодится.
Итак, возникла задача, одновременно с распечаткой бумажной накладной создавать её электронную копию с записью в EDI папку.
Предложил использовать хранимую процедуру примерно следующего содержания:

Код:

CREATE PROCEDURE [dbo].[ASN_REP]
@NumOrd AS VARCHAR(10), — № ЗП
@NumLorry AS VARCHAR(17)
AS
——————————————————————————————————————————————
SET NOCOUNT ON
SET DATEFORMAT mdy
——————————————————————————————————————————————
exec ASN @NumOrd, @NumLorry
exec ASN_WriteToFile ‘С:\iScala 2.3\EDI_OUT’, ‘DESADV’
select * from ASNPS00 (nolock)

Эта хранимая процедура сначала вызывает другую хранимую процедуру, которая заполняет требуемую таблицу данными.
Затем она вызывает вторую хранимую процедуру, которая записывает данные из этой таблицы в файловую систему.
Далее она выводит содержимое заполненной таблицы.
Хранимая процедура вызывается из кристалловского отчёта.
Когда проверяли этот отчёт всё работало.
Затем отчёт был добавлен в меню Скалы и… перестал работать. Точнее, файл перестал создаваться.
Стали разбираться. Я спросил, от имени какого пользователя работает Скала. Оказалось, что этот пользователь обладает расширенными правами, но не является db owner’ом. То есть, когда мы вызывали отчёт "напрямую" он обращался к SQL серверу от имени пользователя, его запустившего (с административными правами), а когда включили в меню Скалы, он стал вызываться от имени пользователя, через которого идёт обращение к SQL серверу со стороны Скалы. Я попросил подсоедититься к SQL серверу под этим пользователем и выполнить последовательно все 3 действия, описанных выше. Выяснилось, что всё работает, кроме хранимой процедуру, пишущей информацию в файловую структуру.
При попытке выполнить хранимую процедуру получили сообщения примерно такого содержания:

Код:

Сообщение 229, уровень 14, состояние 5, процедура sp_OACreate, строка 1
Запрещено разрешение "EXECUTE" на объект "sp_OACreate" базы данных "mssqlsystemresource", схемы "sys".
Сообщение 229, уровень 14, состояние 5, процедура sp_OAMethod, строка 1
Запрещено разрешение "EXECUTE" на объект "sp_OAMethod" базы данных "mssqlsystemresource", схемы "sys".
Сообщение 229, уровень 14, состояние 5, процедура sp_OADestroy, строка 1
Запрещено разрешение "EXECUTE" на объект "sp_OADestroy" базы данных "mssqlsystemresource", схемы "sys".

Стало всё понятно. Я предложил выполнить следующий запрос примерно такого содержания:

Код:

use master
grant exec on sp_OACreate to ScalaUser
grant exec on sp_OAMethod to ScalaUser
grant exec on sp_OADestroy to ScalaUser

Хочу добавиль, что пользователь ScalaUser должен существовать в базе данных master. После выполнения запроса всё заработало Very Happy

aav
Администратор

Зарегистрирован: 14.09.2004
Сообщения: 1081
Откуда: Санкт-Петербург

Добавлено: 06.12.2012 21:54 Заголовок сообщения: Re: Чтение и запись текстовых файлов в SQL с использованием
Попробовал воспользоваться функцией uftReadfileAsTable, опубликованной выше. Ругался разными словами !@#$%^&*

Не читался файл содержащий кириллические символы.
Никак не мог понять назначение трёх последних параметров в выражении

Код:

execute @hr = sp_OAMethod @objFileSystem , ‘OpenTextFile’, @objTextStream OUT, @command,1,false,0

В заблуждение ввело то, что подобное выражение используется не только для чтения файла, но и для записи. Потом-таки разобрался:
1 означает ForReading (Открывает файл только для чтения),
false означает должен ли создаваться новый файл, если файл filename не существует (не должен. Именно наличие этого параметра и вводило в заблуждение, т.к. он требуется в случае открытия файла на запись, если он существует, или создания нового, если файла с таким именем нет, а мы собираемся только читать информацию из файла и в этом случае этот параметр не имеет смысла),
0 означает, что файл открывается как ASCII
А мне нужен был Unicode.
Заменил на -1 (-1 Открывает файл в кодировке Unicode, -2 Открывает файл, используя системную кодировку)
Наконец заработало Very Happy

aav
Администратор

Зарегистрирован: 14.09.2004
Сообщения: 1081
Откуда: Санкт-Петербург

Добавлено: 14.10.2013 15:36 Заголовок сообщения: Re: Чтение и запись текстовых файлов в SQL с использованием

http://msdn.microsoft.com/en-us/library/aa265018(v=vs.60).aspx писал(а):

CreateTextFile Method

Description

Creates a specified file name and returns a TextStream object that can be used to read from or write to the file.

Syntax

object.CreateTextFile(filename[, overwrite[, unicode]])

The CreateTextFile method has these parts:

Part: object
Description: Required. Always the name of a FileSystemObject or Folder object.
Part: filename
Description: Required. String expression that identifies the file to create.
Part: overwrite
Description: Optional. Boolean value that indicates if an existing file can be overwritten. The value is True if the file can be overwritten; False if it can’t be overwritten. If omitted, existing files are not overwritten.
Part: unicode
Description: Optional. Boolean value that indicates whether the file is created as a Unicode or ASCII file. The value is True if the file is created as a Unicode file; False if it’s created as an ASCII file. If omitted, an ASCII file is assumed.

Таким образом, код

Код:

EXEC @OLEResult = sys.sp_OAMethod @FS,’CreateTextFile’, @FileID OUTPUT, @PATH_AND_FILE

предполагает, что будет создан файл только, если его не существует, в кодировке ASCII, а если нам нужно создать файл в формате Unicode, это нужно указать явно, например, таким образом:

Код:

EXEC @OLEResult = sys.sp_OAMethod @FS,’CreateTextFile’, @FileID OUTPUT, @PATH_AND_FILE, true, true

Иногда полезно документировать подобные вещи хотя бы для себя, иначе приходится искать то, что уже когда-то было использовано, но забылось. Особенно при этом меня поражают вопросы на различных форумах: человек спрашивает, как создать файл в кодировке Unicode, ей, как водится, отвечают что-то типа "почитай документацию", человек потом пишет: "спасибо, разобралась" и всё. А рассказать другим… это, видимо, лишнее. Фигушки, самому мало...