Need better way to test for existence of file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need better way to test for existence of file

Author

Topic

jjamjatra
Starting Member

13 Posts

Posted - 2008-09-03 : 17:52:04

I have a stored proc which needs to process up to 4 Excel files named Book1.xls, Book2.xls, etc. I am having trouble when, say Book3.xls does not exist. The error I get even stops me at stored procedure compile time if the file is not there! Here is the error text I get trying to issue ALTER PROCEDURE in Query Analyzer:

Server: Msg 7399, Level 16, State 1, Procedure _Job_ConvertRawFHAData_PrepareStep, Line 128
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

Here is a snippet of my technique for which I am seeking an improvement:


SET @FileName = 'Book1.xls'
SET @File = @Path + @FileName
EXEC master..xp_fileexist @File, @i out
IF @i = 1
BEGIN
INSERT INTO devmdw.dbo.JJA_02_Excel
SELECT 
	'PropertyState' 	= [Property State] ,
	'PropertyCounty' 	= [Property County] ,
	'PropertyZip' 		= CONVERT(VARCHAR(255),[Property Zip]) ,
	'OriginatingMortgagee' 	= [Originating Mortgagee] ,
	'LenderID' 		= CONVERT(VARCHAR(255),[Originating Mortgagee Number]) , 
	'SponsorName' 		= [Sponsor Name] ,
	'SponsorID' 		= CONVERT(VARCHAR(255),[Sponosr Number]) , 
	'DownPaymentSource' 	= [Down Payment Source] ,
	'NonProfitTaxID' 	= CONVERT(VARCHAR(255),[Non Profit Number]) , 
	'ProductType' 		= [Product Type] ,
	'LoanPurpose' 		= [Loan Purpose] ,
	'PropertyProductType' 	= [Property/Product Type] ,
	'InterestRate' 		= Convert(float,[Interest Rate]), 
	'MortgageAmount' 	= Convert(int,[Mortgage Amount]) ,
	'SaleYear' 		= Convert(int,[Year]) ,
	'SaleMonth' 		= Convert(int,[Month]) 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\RawData_MDS\Book1.xls;HDR=Yes;IMEX=1', 
'SELECT
	[Property State] ,
	[Property County] ,
	[Property Zip] ,
	[Originating Mortgagee] ,
	[Originating Mortgagee Number] ,
	[Sponsor Name] ,
	[Sponosr Number] ,
	[Down Payment Source] ,
	[Non Profit Number] ,
	[Product Type] ,
	[Loan Purpose] ,
	[Property/Product Type] ,
	[Interest Rate] ,
	[Mortgage Amount] ,
	[Year] ,
	[Month] 
FROM [Sheet1$]')
END
SET @FileName = 'Book2.xls'
SET @File = @Path + @FileName
EXEC master..xp_fileexist @File, @i out

The line number reference in the error message points to the SET @FileName = 'Book3.xls' statement which is the file that does not exist now (but will exist off and on in the future). Thanks for your time on this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 01:47:12

www.sqldba.org/articles/ 36-check-if-file-exists-in-sql-server.aspx

jjamjatra
Starting Member

13 Posts

Posted - 2008-09-04 : 11:02:21

None of the 3 methods of checking for file existence work in my case. Doing more trial and error, I have found that somehow the parsing of the OPENROWSET function is causing the problem. Please note closely how the OPENROWSET function contains a file reference. If that file exists, then there is no problem at compile time nor at execution time. If that file does not exist, then no method of checking will allow the stored procedure to be updated via ALTER and of course it fails immediately at runtime when invoked from my calling client.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 11:04:57

what about this?

http://www.sqldts.com/211.aspx

jjamjatra
Starting Member

13 Posts

Posted - 2008-09-04 : 12:13:43

Another interesting article but still misses my point. I need to be able to check for the existence of each of a series of files. Checking needs to be done inside a stored procedure, thus T-SQL techniques rather than DTS. The problem seems to be with OPENROWSET when the file does NOT exist as discussed in this article:

http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx

As the article states, OPENROWSET will fail if the path is wrong or the file does not exist. Yet that is my whole point in trying to test first if each file exists. The path is correct (I know it has to be relative to where the file is on the server). The issue is implementing the file existence checking in such a way that the compile of the stored procedure is allowed and at runtime the IF logic is allowed to bypass the processing of a given file.

I am thinking of a workaround that would always create a set of files but wherever there was no data, then I'd create a file with zero rows. Modeled as follows:

book1.xls (this file contains n rows)
book2.xls (this file contains m rows)
book3.xls (this file contains zero rows because there is no way to check for a missing file)
book4.xls (this file contains zero rows because there is no way to check for a missing file)

This approach seems crazy but through testing it appears like the only way I can fool OPENROWSET.

P.S. Before all this I was using DTS with a data pump task from Excel but there is some nasty behavior in Excel regarding column conversion. I came up with OPENROWSET which works except for when the file may be missing.

Thanks for hanging in with me on this problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources