February 4, 2009 at 12:53 am
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
February 5, 2009 at 8:53 am
Did you try creating a Linked Server?
December 23, 2009 at 12:02 pm
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)".
February 17, 2010 at 5:42 am
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)".
February 17, 2010 at 5:48 am
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)
August 6, 2010 at 2:49 am
Try this, it will work...
USE master
GO
-- To use named parameters: Add linked server in the source (Local machine - eg: MachineName or LocalSeverLoginName)
sp_addlinkedserver
@server = N'LnkSrv_RemoteServer_TEST',
@srvproduct=N'', -- Leave it blank when its not 'SQL Server'
@provider=N'SQLNCLI', -- see notes
@datasrc=N'RemoteServer',
@provstr=N'UID=sa;PWD=sa;'
--,@catalog = N'MYDATABASE' eg: pubs
GO
/*
Note:
To check provider name use the folling query in the destination server
Select Provider From sys.servers
*/
----------------------------------------------------------------------------------------------------------
-- Optional
--EXEC sp_addlinkedsrvlogin 'LnkSrv_RemoteServer_TEST', 'true' -- (self is true) -- for LocalSeverLoginName
--GO
-- Remote login
sp_addlinkedsrvlogin
@rmtsrvname = 'LnkSrv_RemoteServer_TEST',
@useself = 'False',
@rmtuser = 'sa',
@rmtpassword = 'sa'
GO
-- OR
/*
IF the above add linked server login failed then try in the Linked Server (LnkSrv_RemoteServer_TEST) Property
Select -> Security - > 'For a login not defined in the list above, Connection will:'
Choose - > Be made using this security context
SET Remote login: sa
With password: sa
*/
----------------------------------------------------------------------------------------------------------
-- Test server connection
declare @srvr nvarchar(128), @retval int;
set @srvr = 'LnkSrv_RemoteServer_TEST';
begin try
exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
set @retval = sign(@@error);
end catch;
if @retval <> 0
raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );
-- OR
BEGIN TRY
EXEC sp_testlinkedserver N'LnkSrv_RemoteServer_TEST';
END TRY
BEGIN CATCH
PRINT 'Linked Server not available';
RETURN;
END CATCH
----------------------------------------------------------------------------------------------------------
-- Get access linked server database
SET xact_abort ON
GO
BEGIN TRANSACTION
SELECT * FROM LnkSrv_RemoteServer_TEST.DBName.dbo.tblName
COMMIT TRAN
GO
-- OR
SELECT * FROM OPENQUERY(LnkSrv_RemoteServer_TEST, 'SELECT * FROM DBName.dbo.tblName')
GO
-- OR
SELECT * FROM OPENQUERY(LnkSrv_RemoteServer_TEST, 'SELECT * FROM sys.databases Order by name')
GO
----------------------------------------------------------------------------------------------------------
August 17, 2010 at 3:41 pm
¡ Hi ¡
I have the same problem, when I run this part of your code:
declare @srvr nvarchar(128), @retval int;
set @srvr = 'LnkSrv_RemoteServer_TEST';
begin try
exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
set @retval = sign(@@error);
end catch;
if @retval <> 0
raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );
I receipt the message: Unable to connect to server. This operation will be tried later
I make the connection with odbc, what changes I have to do :crying:?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply