OPENDATASOURCE reads file even if conditions not met?

Prev: [IF loop] OPENDATASOURCE reads file even if conditions not met?
Next: Combine many to one record

From: Eric Isaacs on 20 Jul 2010 16:05

---

> 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

From what I'm seeing above, it looks to see if test2.xls exists and if
test.xls does not exist and if both cases are true, then it opens the
one that doesn't exist. (text.xls).

-Eric Isaacs

From: John Bell on 20 Jul 2010 17:25

---

On Tue, 20 Jul 2010 11:51:17 -0700 (PDT), M Bourgon
<bourgon(a)gmail.com> wrote:

>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

If test.xls does not exists then (SELECT COUNT(*) FROM ##load WHERE
filelist = 'test.xls') = 0 so why are you trying to load it?

You may want to try:

IF EXISTS (SELECT * FROM ##load WHERE filelist = 'test2.xls')
AND NOT EXISTS (SELECT * FROM ##load WHERE filelist = 'test.xls')
BEGIN
SELECT 'test.xls does not exist by test2.xls does'
END



John

From: Erland Sommarskog on 20 Jul 2010 17:51

---

M Bourgon (bourgon(a)gmail.com) writes:
> 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.

Yes, OPENDATASOURCE has to be executed at compile time, since else
SQL Server cannot determine what data types etc SELECT statement
will return.

Although admittedly, this is a little inconsistent, since if you
in place of OPENDATASOURCE would put "nonexistingtable" SQL Server
would keep its mouth shut because of deferred name resolution. (Which
is a big misfeature in my opinion. Deferred name resolution.)

You can work around the problem by putting the select in EXEC().

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: M Bourgon on 21 Jul 2010 17:14

---

On Jul 20, 4:51 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> Yes, OPENDATASOURCE has to be executed at compile time, since else
> SQL Server cannot determine what data types etc SELECT statement
> will return.

> You can work around the problem by putting the select in EXEC().

That's what I thought, but it's nice to know why and confirm the
workaround. Unexpected behavior on SQL's part.
Thanks, sir.

 | 
Pages: 1
Prev: [IF loop] OPENDATASOURCE reads file even if conditions not met?
Next: Combine many to one record