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

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

1. Jugulator

   20.04.2012 at 15:52

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

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

     15.10.2012 at 18:30

     Наконец-то дошли руки да и появилась реальная надобность это попробовать. С удивлением обнаружил, что приведенные автором (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 at 21:27

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

   Попробовал воспользоваться функцией 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 at 15:36

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

   

Comments are closed.