|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, February 05, 2009 8:21 PM
Points: 3,
Visits: 35
|
|
i want to select data from csv file , use following command
select *
from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver (*.csv)};
DBQ=C:\myFolder\',
'select * from myFile')
but it giving following error message
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
how i add referece of MSDASQL ?
how i solve this problem ?
Thanks in advance
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Tuesday, March 09, 2010 12:28 PM
Points: 273,
Visits: 445
|
|
Did you try creating a Linked Server?
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, December 23, 2009 12:00 PM
Points: 1,
Visits: 9
|
|
I'm having a simular issue, it started today.
Up until now this worked fine:
INSERT INTO SDSMTI_STAGING_METRICS_TRANSACTIONS
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt *.csv)}DefaultDir=E:\Uploads','select * from [InvoiceFeed_20091221.csv]')
Now I get:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Friday, February 19, 2010 8:41 AM
Points: 28,
Visits: 16
|
|
The sample SQL you listed contains several syntax errors: a) the .txt / .csv specification, and b) missing comma before the Select statement. See a correct sample statement below:
SELECT *
FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver(*.txt; *.csv)};DEFAULTDIR=D:\HRGgrouper\v40\;',
'SELECT * FROM Hrg4_outputfile_APC_quality.csv')
However, I am getting the same problem as you on our Live server, although the statement works OK on Dev and Test.
I have tried turning on the Ad hoc query on the live server, and rebooted the server to activate it, but this did not resolve the problem.
Anyone any ideas?
The exact error I am getting On Live after reboot 16.2.10 is:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Friday, February 19, 2010 8:41 AM
Points: 28,
Visits: 16
|
|
Sorry, I forgot to mention in my reply a few moments ago that I was referring to the post from mbearlover
.
Also, the SQL server reconfigutation commands I used were:
-- turn on the 'Ad Hoc Distributed Queries' option.
sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE WITH override
GO
After reboot, I rerun sp_configure 'Ad Hoc Distributed Queries' which showed that the configured and the run_value of this setting were both ON (ie. 1)
|
|
|
|
