OPENROWSET using Jet.OLDB and a text file

[JayKusch]

Is this code possible? I have inherited an SP that is bombing out w/ an error of:


Server: Msg 7399, Level 16, State 1, Line 2

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

[OLE/DB provider returned message: Unspecified error]

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].


Now I know I can work around this issue w/ a quick code change BUT my REAL questions is if this syntax is correct and permissable.

I thought the datasource had to be a database of some sort.

Here is the code:

select distinct [NoiseWord] = x.[F1]
from   openrowset
(
'Microsoft.Jet.OLEDB.4.0',
'Text;HDR=NO;Database=C:\Program Files\Common Files\System\MSSearch\Data\Config\',
'select * from noise.enu'
  ) as x
where x.[F1] is not NULL

Thanks

J. Kusch

 

[JayKusch]

No takers eh - lol!

Thanks

J. Kusch

 

[vongrunt]

If HDR=NO, then how engine is supposed to know column names? First one becomes F1, second F2 etc?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[JayKusch]

OK ... Here is the jist.

Found that the code above DOES work as intended. Tried it on another server w/ the same configuration and BINGO!

As to vongrunt's input, I have not tested if that makes a difference or not. the file is a single column file so maybe its defaulting to the one and only column. I will add another column as a test to your reply and see if it still works.

Now I have posted a new thread in the SQL Setup & Admin forum because it now looks to be a cluster and/or SQL Server config issue. Here is the link to that new thread ... thread962-1179969


Thanks

J. Kusch