title: SQL Server Read and Load Text File using function, Error whilst Opening file
body: <p>I am using the below function but it is giving me an error 
  Error whilst Opening file "C:\MyData\TEST.txt", </p>
  
  <pre><code>CREATE FUNCTION [dbo].[ReadfileAsTable]
  (
  @Path VARCHAR(1000),
  @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&lt;&gt;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&lt;&gt;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
  </code></pre>
  
  <p>My effort is to load the data from text file into SQL Server temp table. I can use this temp table for further query based on path,file name passed to this function.</p>
  
  <p>I executed the above using below commands</p>
  
  <pre><code>Select line from
   dbo.[ReadfileAsTable]('C:\MyData','TEST.txt')
  where line not like'#%' 
  --where line doesnt begin with a hash
  </code></pre>
  
  <p>Please advice.</p>