问题描述
我有一个SQL Server数据库，我需要通过vbscript将数据推送到其中，以及将数据提取到Excel中.我发现了多个连接字符串，但是没有存储库可以比较它们的性能和功能优势.到目前为止，我发现的驱动程序选项(Provider=)是:
I have an SQL Server database, and I need to push data into it through vbscript, as well as pull data into Excel. I have found multiple connection strings, but no repository for the benefits of performance and functionality comparing them. The driver options (Provider=) I have found so far are:
- {SQL Server}(ODBC)
- SQLOLEDB(比ODBC更新，但已被弃用?)
- SQLOLEDB.1(单击获取外部数据"时Excel 2016会使用什么，但在connectionstrings.com上甚至没有提及...我假定上述版本的较新版本，但仍不推荐使用技术?)
- SQLNCLI11(本地客户端，OLE DB)
- {SQL Server本机客户端11.0}(本机客户端，ODBC)
- {SQL Server} (ODBC)
- SQLOLEDB (newer than ODBC, but being deprecated?)
- SQLOLEDB.1 (what Excel 2016 uses when clicking 'Get External Data', but not even mentioned on connectionstrings.com... I assume a newer version of the above, but still the deprecated technology?)
- SQLNCLI11 (native client, OLE DB)
- {SQL Server Native Client 11.0} (native client, ODBC)
我读过的很多东西都说ODBC更好，因为它存在的时间更长了. OLE DB已经存在了很长的时间，才具有相同的优势. OLE DB可以与某个公司的应用程序一起使用. ODBC是由同一家公司生产的. OLE DB可以更好地与不同类型的应用程序进行连接. ODBC与数据库更好地配合使用.而且Native是... Native，所以必须更好...因为名字?
Different things I read say that ODBC is better because it has been around longer. And that OLE DB has been around long enough to have the same advantages. And OLE DB was made to work with a certain company's applications. And ODBC was made by the same company. And OLE DB can connect to and from different kinds of applications better. And ODBC works better with databases. And Native is...Native, so must be better... because of the name?
我在SO上发现了多个问题，这些问题四处徘徊，没有答案或部分答案，或者有多个评论声称答案已过时.那么，到目前为止，这些不同驱动程序之间的具体区别是什么?他们在不同情况下有不同的表现吗?它们有不同的功能吗?我是否需要进行性能分析以确定我的特定用例的最佳性能和可靠性，或者 Microsoft或某些知名专家是否推荐了标准的最佳实践"?还是他们基本上都在做同样的东西，只要它安装在目标系统上就没关系了吗?
I find multiple questions here on SO floating around with no or partial answers, or having multiple comments claiming the answers are out of date. So, as of now, what the specific differences between these different drivers? Do they have different performance in different circumstances? Do they have different features? Do I need to do profiling to determine the best performance and reliability for my particular use case, or is there a standard "best practice" recommended by Microsoft or some recognized expert? Or are they all basically doing the same thing and as long as it's installed on the target system it doesn't really matter?
推荐答案
ODBC-它用于连接到关系数据库.但是，OLE DB可以访问关系数据库以及非关系数据库.
ODBC-it is designed for connecting to relational databases.However, OLE DB can access relational databases as well as nonrelational databases.
邮件服务器，目录服务，电子表格和文本文件中有数据. OLE DB允许SQL Server链接到这些非关系数据库系统.例如，如果要通过SQL Server查询域控制器上的Active Directory，则无法使用ODBC进行此操作，因为它不是关系数据库.但是，您可以使用OLE DB提供程序来完成此操作.
There is data in your mail servers, directory services, spreadsheets, and text files. OLE DB allows SQL Server to link to these nonrelational database systems. For instance, if you want to query, through SQL Server, the Active Directory on the domain controller, you couldn't do this with ODBC, because it's not a relational database. However, you could use an OLE DB provider to accomplish that.
http://www.sqlservercentral.com/Forums/Topic537592-338- 1.aspx
这篇关于连接到SQL Server时，OLEDB/ODBC驱动程序之间有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！