пользователям программных продуктов Scala 5.1, iScala 2.1, iScala 2.2, iScala 2.3, iScala 3.0, iScala 3.1, iScala 3.2, iScala 3.3 (и так далее)

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

Create Text FileПо просьбе одного из участников публикую пример запроса для записи информации в текстовый файл.
Эта хранимая процедура сначала записывает во временную таблицу содержимое будущего XML файла, а затем пишет построчно это содержимое в файл. Файл дальше обрабатывается механизмом Epicor Service Connect.
Вы можете весь кусок, начиная с

exec('declare @RqNo nchar(10)
select @RqNo=PCR2001 from PCR2'+@CC+'00 (nolock)

и по

INSERT INTO #XMLCONTENT
exec(@SQL)

выкинуть, а ниже создаваемый курсор

declare @LINETEXT nvarchar(max)
declare text_lines cursor
for
select *
from #XMLCONTENT (nolock)

заменить на свой 🙂
Ниже полный текст хранимой процедуры:

CREATE procedure [dbo].[usr_CopyRequisitionLines]
@CC nchar(2)='TR',--Код компании
@ReqNo nchar(10)='0000011990',--Номер требования
@CATALOG_NAME varchar(255)='D:\TEMP'--Название каталога
as
declare @bFolder int
DECLARE @FS int, @OLEResult int, @FileID int 
DECLARE @hr int, @source varchar(30), @desc varchar (200)
declare @TEXT_FILE_NAME varchar(255)
DECLARE @PATH_AND_FILE varchar(255)
-- Зададим имя файла с расширением .xml
set @TEXT_FILE_NAME='CopyRequisitionLines.xml'
-- ОПРЕДЕЛИМ ИМЯ ФАЙЛА С КАТАЛОГОМ
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
print @PATH_AND_FILE
-- CREATE FSO-OBJECT
EXEC @OLEResult = sys.sp_OACreate 'Scripting.FileSystemObject', @FS OUTPUT
IF @OLEResult <> 0 GOTO Error_Handler

--проверить - существует ли заданная директория, для этого вызовем функцию 'FolderExists'
--ранее созданного OLE объекта--------------------------------------------------------
execute @OLEResult = sp_OAMethod @FS,'FolderExists',@bFolder OUT, @CATALOG_NAME
IF @OLEResult <> 0 Or @bFolder = 0
BEGIN
  --а если не существует - то создать её--------------------------------------------
  execute @OLEResult = sp_OAMethod @FS,'CreateFolder',@bFolder OUT, @CATALOG_NAME
  IF @OLEResult <> 0 And @bFolder = 0
  BEGIN
    GOTO Error_Handler    
  END
END

-- CREATE FILE-OBJECT
EXEC @OLEResult = sys.sp_OAMethod @FS,'CreateTextFile', @FileID OUTPUT, @PATH_AND_FILE, true, true
IF @OLEResult <> 0 GOTO Error_Handler
exec('declare @RqNo nchar(10)
select @RqNo=PCR2001 from PCR2'+@CC+'00 (nolock)
where PCR2001='''+@ReqNo+''' and PCR2037=''1''')
IF @@ROWCOUNT<>0
begin
create table #XMLCONTENT (
	[LINETEXT] [nvarchar] (max) NOT NULL
)

declare @SQL nvarchar(max)
set @SQL='select ''<?xml version="1.0"?>
<msg:msg xsi:schemaLocation="http://Epicor.com/InternalMessage/1.1 http://scshost/schemas/Epicor/ScaInternalMsg.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:msg="http://Epicor.com/InternalMessage/1.1">
	<msg:req tag="Requisition In">
		<msg:dta>
			<dta:Requisition xmlns:dta="http://www.scala.net/Requisition/1.1">
				<dta:RqsnNum>''+convert(nchar(3),convert(int,isnull([MaxPrefix],''899''))+1)+substring(OLD.PCR1001,4,7)+''</dta:RqsnNum>
				<dta:DeptCode>C''+replace(OLD.PCR1003,''C'','''')+''</dta:DeptCode>
				<dta:WhCode>''+OLD.PCR1006+''</dta:WhCode>
				<dta:RqsnType>''+OLD.PCR1007+''</dta:RqsnType>
				<dta:InitiateApprovalProcess>1</dta:InitiateApprovalProcess>
				<dta:RqsnLines>'' as Text
from PCR1'+@CC+'00 (nolock) OLD
LEFT JOIN (select max(left(PCR1001,3)) as MaxPrefix, substring(PCR1001,4,7) as Suffix from PCR1'+@CC+'00 (nolock) where left(PCR1001,1)=''9'' group by substring(PCR1001,4,7)) PRFX ON substring(PCR1001,4,7)=Suffix
where OLD.PCR1001='''+@ReqNo+'''
union all
select ''
					<dta:RqsnLine>
						<dta:LineNum>''+OLD.PCR2002+''</dta:LineNum>
						<dta:StockCode>''+replace(replace(replace(OLD.PCR2007,char(185),''1''),''&'',''&amp;''),''"'',''&quot;'')+''</dta:StockCode>
						<dta:StockDescr>''+replace(replace(replace(OLD.PCR2008,char(185),''1''),''&'',''&amp;''),''"'',''&quot;'')+''</dta:StockDescr>
						<dta:StockDescr2>''+replace(replace(replace(OLD.PCR2024,char(185),''1''),''&'',''&amp;''),''"'',''&quot;'')+''</dta:StockDescr2>
						<dta:QtyOrdered unitName="''+rtrim(SCUN003)+''">''+convert(nvarchar,OLD.PCR2009)+''</dta:QtyOrdered>
					</dta:RqsnLine>'' as Text
from PCR2'+@CC+'00 (nolock) OLD
join SCUN'+@CC+'00 (nolock)
on SCUN001=OLD.PCR2020 and SCUN002=''*''
where OLD.PCR2001='''+@ReqNo+''' and OLD.PCR2037=''1''
union all
select ''
				</dta:RqsnLines>
			</dta:Requisition>
		</msg:dta>
		<msg:ctx>
			<SrvLocation/>
			<UserName>ServiceConnect</UserName>
			<UserPwd>******</UserPwd>
			<CompanyCode>'+@CC+'</CompanyCode>
			<FiscalYear>2018</FiscalYear>
			<AllowRaisingBusinessEvents>1</AllowRaisingBusinessEvents>
			<Options>
				<!-- RqsnTemplateMode: defines if incoming data is processed as requisitions or requisition templates
					0 = requisition mode (default)
					1 = requisition template mode -->
				<RqsnTemplateMode>0</RqsnTemplateMode>
				<!-- AllowRqsnAdd: defines if adding new requisitions (requisition templates) is allowed
					1 = allowed (default)
					0 = not allowed -->
				<AllowRqsnAdd>1</AllowRqsnAdd>
				<!-- AllowRqsnChange: defines if changing existent requisitions (requisition templates) is allowed
					1 = allowed (default)
					0 = not allowed -->
				<AllowRqsnChange>1</AllowRqsnChange>
				<!-- AllowRqsnDelete: defines if deleting existent requisitions (requisition templates) is allowed
					1 = allowed (default)
					0 = not allowed -->
				<AllowRqsnDelete>1</AllowRqsnDelete>
			</Options>
		</msg:ctx>
	</msg:req>
</msg:msg>'' as Text'
INSERT INTO #XMLCONTENT
exec(@SQL)
declare @LINETEXT nvarchar(max)
declare text_lines cursor
for
select *
from #XMLCONTENT (nolock)
open text_lines
FETCH NEXT FROM text_lines
INTO @LINETEXT

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @OLEResult = sys.sp_OAMethod @FileID, 'Write', NULL, @LINETEXT
--print @str
FETCH NEXT FROM text_lines
INTO @LINETEXT
end
CLOSE text_lines
DEALLOCATE text_lines
select 'Lines are copied' as Text
end
else
begin
select 'No lines to copy' as Text
end
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

Не забудьте посмотреть заметку «SQL Server: OLE Automation Procedures», а также комментарии ниже списка опубликованных процедур.

Список опубликованных процедур:

5 комментариев

  1. Jugulator Jugulator
    20.04.2012    

    Есть статья Фила Фактора (Phil Factor) «Чтение и запись файлов в SQL Server с использованием T-SQL» («Reading and Writing Files in SQL Server using T-SQL»). В статье как раз используется ‘Scripting.FileSystemObject’, как и в коде выше: для записи файлов процедура spWriteStringToFile, а для чтения ufsReadfileAsString.

    • Алексей Васильев Алексей Васильев
      15.10.2012    

      Наконец-то дошли руки да и появилась реальная надобность это попробовать. С удивлением обнаружил, что приведенные автором (Phil Factor) функции не работают: у него, по всей видимости, 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 @YesOrNo<>0  break 
          if @hr=0 Select @objErrorObject=@objTextStream, 
             @strErrorMessage='reading from the output file "'+@filename+'"' 
          if @hr=0 execute @hr = sp_OAMethod  @objTextStream, 'Readline', @String OUTPUT 
          INSERT INTO @File(line) SELECT @String 
          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,'') 
          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 
      
       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 @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
  2. Алексей Васильев Алексей Васильев
    24.10.2012    

    Недавно столкнулся с интересной проблемой. Опишу все подробно и по порядку, может кому-то пригодится.
    Итак, возникла задача, одновременно с распечаткой бумажной накладной создавать её электронную копию с записью в 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. После выполнения запроса всё заработало 🙂

  3. Алексей Васильев Алексей Васильев
    06.12.2012    

    Попробовал воспользоваться функцией uftReadfileAsTable, опубликованной выше. Ругался разными словами 🙁
    Не читался файл содержащий кириллические символы.
    Никак не мог понять назначение трёх последних параметров в выражении

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

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

  4. Алексей Васильев Алексей Васильев
    14.10.2013    

    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, ей, как водится, отвечают что-то типа "почитай документацию", человек потом пишет: "спасибо, разобралась" и всё. А рассказать другим… это, видимо, лишнее. 🙂