April 27, 2013 at 12:00 am
hello all.
i use this T-Sql for reading txt file:
select * from OpenDataSource( 'Microsoft.ACE.OLEDB.12.0',
'Data Source="f:\rahnama.txt";Extended properties="Text;hdr=no"')...rahnama#txt
but gets error:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "'f:\rahnama.txt' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
Msg 7303, Level 16, State 1, Line 4
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
what is the problem?what do you do?
April 27, 2013 at 3:21 am
elham_azizi_62 (4/27/2013)
hello all.i use this T-Sql for reading txt file:
select * from OpenDataSource( 'Microsoft.ACE.OLEDB.12.0',
'Data Source="f:\rahnama.txt";Extended properties="Text;hdr=no"')...rahnama#txt
but gets error:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "'f:\rahnama.txt' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
Msg 7303, Level 16, State 1, Line 4
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
what is the problem?what do you do?
The "F:\" drive is no doubt your drive and not the drive sqlserver sees. The following will list you the drives that sqlserver can see on its own hosting OS.
exec xp_fixeddrives
If the F: drive is visible then Reconfigure SqlServer to enable xp_cmdshell (Sorry Opc3) and then run
exec xp_cmdshell 'dir f:\'
exec xp_cmdshell 'type f:\rahnama.txt'
The first ought to allow you to see what sqlserver sees as the f: drive, probable nothing.
The second command will if the file exists will stream the file rahnama.txt to you sql session.
Alternatively use the full unc path or a share that sql server can see with its account.
April 27, 2013 at 4:12 am
thanks for your reply.
I run this scripts and sql server can to see drive f and rahnama.txt but gets the same error again.
April 27, 2013 at 4:13 am
what is this?:...rahnama#txt
April 27, 2013 at 5:09 am
It looks like the driver you are using is for Excel/Access/FoxPro and not for text files.
When I have had to query csv files I have used the openrowset command or used bcp or used SSIS to load the file in once.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply