[IF loop] OPENDATASOURCE reads file even if conditions not met?
in [Programming SQL]
|
Prev: Partitioning - adding data to 1 table grows the NDFs of all other
Next: OPENDATASOURCE reads file even if conditions not met?
From: M Bourgon on 20 Jul 2010 14:51 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 |