From: M Bourgon on
Here's a puzzler. Not sure why I haven't run into this problem
before.

I'm loading files that may or may not exist. So I do a directory
listing, and if the file exists I import it. However, even if the
file doesn't exist, it looks like the OPENDATASOURCE is run, and it
fails because there's no file to import.

You can run this on your machine, you don't need to create any files
since it's supposed to skip the load if files exist.
I'm running this on SQL Server 2005 SP3 x32, though SQL Server 2008
x64 gives the error that the OLEDB provider hasn't been registered.

Only idea I have is that it wants to check things out for the query
optimizer - if I wrap it in an EXEC ('select *...') then it works as
expected.


USE tempdb

if object_id('tempdb..##load') is not null
drop table ##load
CREATE TABLE ##load (id INT IDENTITY, filelist VARCHAR(255))

insert into ##load EXEC master..xp_cmdshell 'dir /b c:\test
\test*.xls'
DELETE FROM ##load WHERE FILELIST IS NULL OR FILELIST = '' OR FILELIST
LIKE '%NOT FOUND%'


IF (SELECT COUNT(*) FROM ##load WHERE filelist = 'test2.xls') =1
AND (SELECT COUNT(*) FROM ##load WHERE filelist = 'test.xls') = 0
BEGIN
if object_id('tempdb..mytest') is not null
drop table tempdb..mytest

SELECT *
INTO tempdb.dbo.mytest
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test\test.xls";
Extended properties=Excel 8.0')...[Sheet1$]

END