SQL 错误：链接服务器“(null)”的“OLE DB 提供程序“MSDASQL”返回消息“[Microsoft][ODBC 驱动程序管理器] 未找到数据源名称...”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5011226/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error: "OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found ..."
提问by Contango
If I execute the following command:
如果我执行以下命令：
select
*
from
OpenRowset (
'MSDASQL',
'Driver={Microsoft Text Driver (*.txt;*.csv)};DefaultDir=C:\;',
'select top 10 * from C:\x.csv'
)
... then Microsoft SQL Server Management Studio responds with:
...然后 Microsoft SQL Server Management Studio 响应：
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
[Microsoft][ODBC 驱动程序管理器] 未找到数据源名称且未指定默认驱动程序。
I'm running Microsoft SQL Server 2008 R2 on Win 7 x64. I've also tried it on Windows Vista x32, same error.
我在 Win 7 x64 上运行 Microsoft SQL Server 2008 R2。我也在 Windows Vista x32 上试过，同样的错误。
Questions:
问题：
- Has anyone successfully run this command on Win 7 x64?
- Do any of you know what could be causing the T-SQL command to fail like this?
- 有没有人在 Win 7 x64 上成功运行过这个命令？
- 你们中有人知道是什么导致 T-SQL 命令像这样失败吗？
Update 1:
更新 1：
If you get an error that mentions "ad hoc queries", run the following to eliminate it:
如果您收到提及“即席查询”的错误，请运行以下命令以消除它：
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Update 2:
更新 2：
It works on an out-of-the-box Win7 x64 machine, but I still can't fix this error on my machine. I'll go with "bulk insert" (see my comments below).
它适用于开箱即用的 Win7 x64 机器，但我仍然无法在我的机器上修复此错误。我将使用“批量插入”（请参阅下面的评论）。
采纳答案by Mark Wilkins
I just tried it on x64 Win7 and made it work. I think there are a couple problems.
我刚刚在 x64 Win7 上试了一下，让它工作了。我认为有几个问题。
- I believe you have to add a space
between
*.txt;and*.csv - Don't include the path with the file name
- 我相信你必须在
*.txt;和之间添加一个空格*.csv - 不要在文件名中包含路径
This worked:
这有效：
select * from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=c:\;',
'select top 10 * from x.csv')
回答by Magee
Solution: I've just wrestled with this issue for several hours on a Win7 x64 machine, and it is so difficult to find a good answer online that I thought I'd contribute one to this thread belatedly.
解决方案：我刚刚在 Win7 x64 机器上与这个问题搏斗了几个小时，而且很难在网上找到一个好的答案，我想我会为这个帖子贡献一个迟到的。
On my machine (Win7, x64, SQL Server 2008 R2), Administrative Tools > Data Sources (ODBC) > Drivers, shows no driver called "Microsoft Text Driver". But there is a driver labeled "Microsoft Access Text Driver (*.txt, *.csv)"
在我的机器（Win7、x64、SQL Server 2008 R2）上，管理工具 > 数据源 (ODBC) > 驱动程序，没有显示名为“Microsoft Text Driver”的驱动程序。但是有一个驱动程序标记为“Microsoft Access Text Driver (*.txt, *.csv)”
I was able to change the driver name in code similar to the original questioner's INCLUDING the parentheses (*.txt, *.csv) WITH a comma and a space, not a semicolon. And it worked.
我能够在类似于原始提问者的代码中更改驱动程序名称，包括带有逗号和空格而不是分号的括号 (*.txt, *.csv)。它奏效了。
select
*
from
OpenRowset (
'MSDASQL',
'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=C:\;',
'select top 10 * from C:\x.csv'
)
Note that the syntax in specifying the drive must be exactly the same. I can vouch for that because I went through several wrong iterations.
请注意，指定驱动器的语法必须完全相同。我可以保证这一点，因为我经历了几次错误的迭代。
