indoaryaan
IS-IT--Management
I am using OpenDatasource function to retrieve data(filename) from a #table ( using cursor, one row(filename) at a time) and process the contents of the text file..say n rows in a file. If the file has no contents(no rows), i get an error. How do i work around this?? I would like to insert the processed contents of the file into a new table. hope the code below helps..if the file has no contents then i get an error :
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=CPInvoiceOutPut2002_07_23_6_2#txt'].
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CPInvoiceOutPut2002_07_23_6_2#txt'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
code:
drop table #testFTP
--get a list of filenames.txt from another file.txt
SELECT IDENTITY(int,1,1) as RowID, FILENAME, GETDATE() AS CREATED_ON into #testFTP FROM OPENDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source = "D:\BuildAddOns\Scripts\InvoiceError";User ID=;Password=;Extended properties=Text')...filelist#txt where FILENAME LIKE 'CPInvoiceOutPut%'
Declare @RunSQL varchar(1000)
DECLARE @error_Insert int
DECLARE @error_select int
DECLARE @ROWID int
DECLARE @FILENAME Varchar(255)
DECLARE @DATETIME DATETIME
DECLARE CUR_testFTP CURSOR
FOR
Select t.RowID, t.FILENAME, t.CREATED_ON
from #testFTP t left outer join TESTFTP F
on t.FILENAME = F.FILENAME
where F.FILENAME is NULL
OPEN CUR_testFTP
FETCH NEXT FROM CUR_testFTP INTO @ROWID, @FILENAME, @DATETIME
--if ( @@FETCH_STATUS = 0) OR ( @@FETCH_STATUS <> 0 )
CREATE TABLE #TESTFTPCOLUMNS ( col001 ntext )
WHILE @@FETCH_STATUS = 0
BEGIN
--process the contents of file...the error occurs in this ---statement below.
Select @RunSQL = 'INSERT INTO #TESTFTPCOLUMNS ' +
'SELECT * FROM OPENDataSource(''Microsoft.Jet.OLEDB.4.0'',
''Data Source = "D:\BuildAddOns\Scripts\InvoiceError";User ID=;Password=;Extended properties=Text'')...' +
Replace(FileName ,'.','#') From #testFTP Where RowID = CONVERT( VARCHAR(10),@ROWID)
SELECT @RunSQL
Exec(@RunSQL)
Select @error_Insert = @@ERROR
IF @error_Insert <> 0
Begin
Print 'Insert Error'
declare @xFilename varchar(100)
set @xFilename = 'The ' + @FILENAME + 'insert error has occured.'
EXEC master.dbo.xp_startmail
EXEC master.dbo.xp_sendmail @recipients = 'xyz@hotmail.com',
@message = @xFilename,
@subject = 'FTP Process Error'
EXEC master.dbo.xp_stopmail
End
INSERT TESTFTPCOLUMNS
Select Distinct
left(CAST(col001 AS VARCHAR (1000)),1) InvoiceOrCreditNote ,rTrim(SUBSTRING(CAST(col001 AS VARCHAR (1000)),2,15)) + '-' +
case SUBSTRING(CAST(col001 AS VARCHAR (1000)),17,1)
When '0' Then SUBSTRING(CAST(col001 AS VARCHAR (1000)),18,1)
Else SUBSTRING(CAST(col001 AS VARCHAR (1000)),17,2)
End
As ReferralNumber,
case rTrim(SUBSTRING(CAST(col001 AS VARCHAR (1000)),19,1))
When 'C' Then rTrim(SUBSTRING(CAST(col001 AS VARCHAR (1000)),20,19))
Else rTrim(SUBSTRING(CAST(col001 AS VARCHAR (1000)),19,20))
End
as InvoiceNumber,
GETDATE() AS CREATEDON
from dbo.#TESTFTPCOLUMNS
Where left(CAST(col001 AS VARCHAR (1000)),1)in ('I','C')
Order By InvoiceOrCreditNote,ReferralNumber
Select @error_select = @@ERROR
if @error_select <> 0
begin
Print 'file' + @FILENAME + 'Error'
declare @yFilename varchar(100)
set @yFilename = 'The ' + @FILENAME + 'process error has occured.'
EXEC master.dbo.xp_startmail
EXEC master.dbo.xp_sendmail @recipients = 'xyz@hotmail.com',
@message = @yFilename,
@subject = 'FTP Process Error'
EXEC master.dbo.xp_stopmail
end
INSERT INTO TESTFTP VALUES ( @FILENAME, @DATETIME )
FETCH NEXT FROM CUR_testFTP INTO @ROWID, @FILENAME, @DATETIME
TRUNCATE TABLE #TESTFTPCOLUMNS
END
DROP table #TESTFTPCOLUMNS
CLOSE CUR_testFTP
DEALLOCATE CUR_testFTP
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=CPInvoiceOutPut2002_07_23_6_2#txt'].
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CPInvoiceOutPut2002_07_23_6_2#txt'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
code:
drop table #testFTP
--get a list of filenames.txt from another file.txt
SELECT IDENTITY(int,1,1) as RowID, FILENAME, GETDATE() AS CREATED_ON into #testFTP FROM OPENDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source = "D:\BuildAddOns\Scripts\InvoiceError";User ID=;Password=;Extended properties=Text')...filelist#txt where FILENAME LIKE 'CPInvoiceOutPut%'
Declare @RunSQL varchar(1000)
DECLARE @error_Insert int
DECLARE @error_select int
DECLARE @ROWID int
DECLARE @FILENAME Varchar(255)
DECLARE @DATETIME DATETIME
DECLARE CUR_testFTP CURSOR
FOR
Select t.RowID, t.FILENAME, t.CREATED_ON
from #testFTP t left outer join TESTFTP F
on t.FILENAME = F.FILENAME
where F.FILENAME is NULL
OPEN CUR_testFTP
FETCH NEXT FROM CUR_testFTP INTO @ROWID, @FILENAME, @DATETIME
--if ( @@FETCH_STATUS = 0) OR ( @@FETCH_STATUS <> 0 )
CREATE TABLE #TESTFTPCOLUMNS ( col001 ntext )
WHILE @@FETCH_STATUS = 0
BEGIN
--process the contents of file...the error occurs in this ---statement below.
Select @RunSQL = 'INSERT INTO #TESTFTPCOLUMNS ' +
'SELECT * FROM OPENDataSource(''Microsoft.Jet.OLEDB.4.0'',
''Data Source = "D:\BuildAddOns\Scripts\InvoiceError";User ID=;Password=;Extended properties=Text'')...' +
Replace(FileName ,'.','#') From #testFTP Where RowID = CONVERT( VARCHAR(10),@ROWID)
SELECT @RunSQL
Exec(@RunSQL)
Select @error_Insert = @@ERROR
IF @error_Insert <> 0
Begin
Print 'Insert Error'
declare @xFilename varchar(100)
set @xFilename = 'The ' + @FILENAME + 'insert error has occured.'
EXEC master.dbo.xp_startmail
EXEC master.dbo.xp_sendmail @recipients = 'xyz@hotmail.com',
@message = @xFilename,
@subject = 'FTP Process Error'
EXEC master.dbo.xp_stopmail
End
INSERT TESTFTPCOLUMNS
Select Distinct
left(CAST(col001 AS VARCHAR (1000)),1) InvoiceOrCreditNote ,rTrim(SUBSTRING(CAST(col001 AS VARCHAR (1000)),2,15)) + '-' +
case SUBSTRING(CAST(col001 AS VARCHAR (1000)),17,1)
When '0' Then SUBSTRING(CAST(col001 AS VARCHAR (1000)),18,1)
Else SUBSTRING(CAST(col001 AS VARCHAR (1000)),17,2)
End
As ReferralNumber,
case rTrim(SUBSTRING(CAST(col001 AS VARCHAR (1000)),19,1))
When 'C' Then rTrim(SUBSTRING(CAST(col001 AS VARCHAR (1000)),20,19))
Else rTrim(SUBSTRING(CAST(col001 AS VARCHAR (1000)),19,20))
End
as InvoiceNumber,
GETDATE() AS CREATEDON
from dbo.#TESTFTPCOLUMNS
Where left(CAST(col001 AS VARCHAR (1000)),1)in ('I','C')
Order By InvoiceOrCreditNote,ReferralNumber
Select @error_select = @@ERROR
if @error_select <> 0
begin
Print 'file' + @FILENAME + 'Error'
declare @yFilename varchar(100)
set @yFilename = 'The ' + @FILENAME + 'process error has occured.'
EXEC master.dbo.xp_startmail
EXEC master.dbo.xp_sendmail @recipients = 'xyz@hotmail.com',
@message = @yFilename,
@subject = 'FTP Process Error'
EXEC master.dbo.xp_stopmail
end
INSERT INTO TESTFTP VALUES ( @FILENAME, @DATETIME )
FETCH NEXT FROM CUR_testFTP INTO @ROWID, @FILENAME, @DATETIME
TRUNCATE TABLE #TESTFTPCOLUMNS
END
DROP table #TESTFTPCOLUMNS
CLOSE CUR_testFTP
DEALLOCATE CUR_testFTP