Hi everyone,

I am tryin to query a column using Microsoft's text writer. The column in my query is :

# The daily noon exchange rates for major foreign currencies are published every business day at about

how do I include this column in my query? quotes, brackets.

Thanke for the help

Well, I changed the application so I could connect the linked server to a directory on a local drive, and everything works great from Query Analyzer... but when I run it from the web application, I get a vague error "OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error." I've given full permissions to the directory to everyone, thinking it might be a security access issue, but that didn't help.. any Ideas?
Thanks ...

(original post)
This works great for files on the local machine, thanks for the code. I have a situation where I would like to point to a directory on a different server, so I tried in the data source \\servername\dir\subdir
but havent had any success... have you done this sort of connection before? I have to mess with the security settings to get it to not error out, but even when there are no errors, the Tables section is empty...

if you have done this, could you explain the security settings you used on the Linked Server properties, plus the security settings for the directory you link to...

thanks..

Well, banging my head against the problem long enough finally led me to the solution.

Microsoft articles http://support.microsoft.com/default...b;en-us;814398 and http://support.microsoft.com/default...b;en-us;296711, discuss problems with the Jet engine. My difficulty arose from the vagueness of the error I got, no error number or file locations...

Appearantly, Jet creates a temporary file dsn for the file being accessed through the Linked Server.

Here's the wonderful part, it creates it in the directory referenced by the Environmental Variable "Temp" or "TMP", <em>for the user account that the SQL service runs under</em>...After finding the directory and assigning appropriate rights, the application worked beautifully...

now, after wasting 2 days of running around in circles, I can get to doing some actual work :)

Sam

I'm having the same problem with CSV file. It gets the numerical data and does not get the string data. I wanted to ask if you got any solution working.
thanks

Hi ajimi,

Sounds like there are problems interpreting the data. Try creating a schema.ini to define exactly what is in your text file. Here's a link with an easy way to create one...
http://p2p.wrox.com/topic.asp?TOPIC_ID=25011

hth
Phil

Actually I have an asp page that will read any CSV file, retrieve the data and display on the page and then insert into the SQL database. I cannot use schema.ini as I'm not sure about the format of CSV files to be read. It can have any format. I just have to read "Mailing Address" Fields record by record. Right now the problem is I cannot read zip code field correctly especially if the field has both 5 and 9 digit zip codes. Either it reads a 5 digit or 9 digit zips depending on which one in majority and ignores the other one. Do you have any clue how to resolve this problem?

thanks
ajimi