error help

I am trying to select data from an excel spreadsheet.. so i use

select *
FROM OpenRowSet('MSDASQL','Driver=Microsoft Excel Driver (*.xls); DBQ=c:dt.xls', 'SELECT * FROM [Sheet1$] ' )

and the error i got is

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

any suggestions?Not too sure about the syntax here, so I could be blowing smoke. Apologies, if I am.

If the spreadsheet is named dt.xls, should it be DBQ=C:\dt.xls?

If that is not it, then I would check to see that Sheet1 is in the spreadsheet, and make sure no one has gone and renamed it.

Hope this helps.|||Originally posted by MCrowley
Not too sure about the syntax here, so I could be blowing smoke. Apologies, if I am.

If the spreadsheet is named dt.xls, should it be DBQ=C:\dt.xls?

If that is not it, then I would check to see that Sheet1 is in the spreadsheet, and make sure no one has gone and renamed it.

Hope this helps.

Fixed the syntax, and checked sheet1 was there,but still no good, any other suggestions?

Error Handling with openrowset

I am using openrowset to interact with some fox pro tables.
I want to be able do error handling if the openrowset quiery fails.
When the quiery gets and error it terminates and does not seem to return and
error status.
Here is a code sample.
Update openrowset('MSDASQL',
'Driver={Microsoft Visual FoxPro
Driver};UID=;PWD=;SourceDB=\\dataserver\prod\apps\ tele\;SourceType=DBF;Exclusive=No;Background
Fetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes',
'select PROMO_CODE from prospect_conf
where area_code+home_phone in (select parea_code+phome_phon from
prospext_conf
where ctod(misc5) = date())')
set promo_code = LEft(promo_code,3)+'0'
Thanks in advance
Regarding Trapping the error from an OpenRowset failure.
I have had similar problems using OpenRowset with FoxPro tables, and found the workaround to be as follows.
1. Build the Openrowset query inside a stored procedure.
2. Call that procedure from inside a parameterised DTS package (Exec usp_Procname ?,?,?)
3. Call the DTS package from another stored procedure.
The DTS package will return a trapable error, so if you are trying to do some type of batch processing your process can continue on.
Admittedly it's a bit convoluted, but it works.

error handling OPENROWSET

If, for example, i have inside a procedure the next statement:
EXEC ('SELECT TOP 0 id1 as id1, *
FROM OPENROWSET(''MSDASQL'', ''Driver={Microsoft Visual FoxPro Driver};
UID=;PWD=;SourceDB=' + @.path + ';
SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;
Collate=Machine;Null=Yes;Deleted=Yes;'', ''
SELECT * FROM ' + @.file + '
'')')

in some ocasions it will generate errors
- if no file or path,
- if no column, ...
how can i handle those errors and continue the procedure without generating an error.Error for "no column" when does that occur ? Only way i cna think of is if the file doe not exist.

To check if the file or path exists

declare @.Path varchar(128)
declare @.FileName varchar(10)

--i used 10 chars because foxpro2.6 allows only length of 10 for a filename and i am not sure what visual fp uses (also fp2.6 doesn't like spaces in path if that matters in your situation)

select @.Path = 'C:\', @.FileName = 'myfile.dbf'
declare @.i int
declare @.File varchar(1000)

select @.File = @.Path + @.FileName
exec master..xp_fileexist @.File, @.i out
if @.i = 1
EXEC ('SELECT TOP 0 id1 as id1, *
FROM OPENROWSET(''MSDASQL'', ''Driver={Microsoft Visual FoxPro Driver};
UID=;PWD=;SourceDB=' + @.path + ';
SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;
Collate=Machine;Null=Yes;Deleted=Yes;'', ''
SELECT * FROM ' + @.file + ''')')
else
print 'no file'