--------------------------------------------------
--  USAGE :
--  Select line from
--  Dbo.uftReadfileAsTable('MyPath','MyFileName')
--------------------------------------------------
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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 TABLE [dbo].[Load_RNFile](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[EverythingElse] [varchar](500) NULL,
 CONSTRAINT [PK_Load_RN] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
) ON [PRIMARY]

GO