Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

OpenDataSource Error

Status
Not open for further replies.

indoaryaan

IS-IT--Management
Nov 19, 2003
26
US
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top