November 27, 2001 at 2:23 pm
SELECT *
FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\;','SELECT * FROM [Details#txt]') AS t
I have this script that works when I reference a text file defined by a drive path i.e. c:\ but, when using UNC, i.e. \\mymachine\c$ it doesn't work. I am positive that it was working in the past but, it clearly doesn't now. Has anyone had any similar experience with using OPENROWSET?
I know that I can BCP the data into a temp table as well however, curiousity is killing me and I want to know why. Any thoughts anyone has would be greatly appreciated.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 27, 2001 at 4:11 pm
Works fine on my machine, using this statement:
SELECT *
FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=\\eg\c$;','SELECT * FROM [Details#txt]') AS t
Perhaps its a permission error?
Andy
November 28, 2001 at 6:56 am
Andy,
Thanks Andy! However, no on the permissions as I have Domain Admin privs throughout. The error I recieve is as follows;
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Text Driver] '(unknown)' is not a valid path.
If I map the drive it works fine. However, that is annoying. Additionally, I know that it worked in the past. My guess is that something changed causing the UNC to not be recognized. Just hoping someone else has stumbled across this. Weird.
Interested in any thoughts that anyone has on this. Thanks in advance.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 28, 2001 at 12:21 pm
Is it not working for any machine which you specify using UNC or just a specific one?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
November 28, 2001 at 12:43 pm
If you run this, keep in mind it is running from the server, not your workstation. Is the file on the server?
Steve Jones
November 28, 2001 at 12:47 pm
Well, it gets more interesting and your question made me try something different. It appears that I can select from the UNC, \\ServerA\c$, from some machines using SQL QA but not from all (hope that is not too confusing).
All machines that I am using are Windows 2000 Pro or Server running SQL 2000. My client machine is the machine that I first started noticing this on.
I think I will start checking versions on the msdasql.dll between these machines. I will keep you posted on what I find.
Thanks Brian and Andy!!!!
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 28, 2001 at 4:37 pm
August 16, 2002 at 8:30 am
Re-opening an old topic here, but I've just found this post as I've been looking for a way of using OPENROWSET to open a text file.
I have been trying to get further details on the parameters reqd for 'MSDASQL' but this is now regarded by Microsoft as a 'Deprecated Component' - suggesting that we use an OLE DB replacement wherever possible.
Can someone tell me which OLE DB Provider that I should be using for accessing Text files.
Many thanks,
August 16, 2002 at 8:56 am
I believe you should be using the JET engine. Works for most file formats like Excel, Access, plain text, CSV, ...
I haven't really used it. I always used the ODBC driver for Text, and referencing a DSN.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply