DSNless SQL Server 2012连接字符串的问题;司机不行 [英] Issues with DSNless SQL Server 2012 Connection String; driver does not work

问题描述

我大部分时间都使用无dsn连接字符串。我遇到了sql server 2012驱动程序的问题，我无法解决这个问题。

为了讨论，我将在 引用函数 AttachDSNLessTable（） ;


https://support.microsoft.com/en-gb/help/892490/how-to-create-a-dsn-少连接到sql-server-for-linked-tables-in

作为测试连接字符串的方法，因为我使用的代码涉及更多

该函数中用于受信任身份验证的驱动程序规范是"SQL Server"

 '//使用受信任的身份验证不提供stUsername。
 stConnect =" ODBC; DRIVER = SQL Server; SERVER =" &安培; stServer&英寸; DATABASE =" &安培; stDatabase& " ;; Trusted_Connection = Yes"  

我发现如果我使用以后的驱动程序直到sql server 2008连接正常。但是对于sql server 2012的驱动程序（显然是sql server 2014，安装程序出现时会有各种相互矛盾的消息），它将无法连接。特定的
驱动程序是

用于SQL Server的Microsoft ODBC驱动程序11版本12.0.5543.11 \ x64

我指定的数据库测试是sql server 2012.

Debug显示这些连接字符串，具体取决于我调整代码的方式：

ODBC; DRIVER = {SQL Server Native Client 11.0}; DATABASE = mydb; SERVER = 192.168.3.5; Trusted_Connection = Yes;
$
ODBC; DRIVER = SQL Server Native Client 11.0; DATABASE = mydb; SERVER = 192.168.3.5; Trusted_Connection = Yes ;

有谁知道这是什么问题？

解决方案

嗯，必须在每台客户端计算机上安装本机11或更高版本的驱动程序。如果您使用默认和"长时间遗留SQL服务器，则默认安装它们。

作为一般规则，我使用旧版驱动程序，因为它们总是安装在任何最新版本的Windows上 - 因此不需要额外安装软件。

但是，如果有问题的表使用任何"较新的"datetime2格式列，则使用本机11驱动程序的一个"大"原因。如果您使用旧版驱动程序，则Access会将这些列视为字符串，而不是日期时间。因此，如果表格使用较新的
日期时间，那么您真的必须采用本机驱动程序。

我注意到你的一个连接周围有{}，而另一个连接没有 - 确保在创建连接字符串时不需要它们。我想你可以尝试两者，看看会发生什么。但你必须真的，但真的很小心。如果
你在任何时候连接一个已知的工作字符串，那么使用INCORRECT格式的其他连接也会起作用！ （这是因为Access缓存已知的工作连接 - 一旦发生，那么Access将使用这些替代连接（
这意味着你必须（我不能强调这必须）退出Access并在测试时重新输入如果在任何时间点，您通过任何方式连接了一个新的连接字符串，并且在该会话期间通过工作连接进行连接。

我不知道为什么你的某些字符串不起作用，但最好在这些情况下测试使用Access来启动ODBC面板来创建新连接。请记住，从Access创建的任何和所有连接（文件
DSN）默认为DSN-less。DSN（文件dsn）仅在链接时使用 - 之后它们是DSN-less因此您可以将应用程序分发到其他工作站而无需DSN。

因此，根本不确定您发布的是 - 但如果您使用的是本机驱动程序，然后必须安装在每个工作站上。如果你偶然连接了一个工作连接，那么额外的测试尝试是空的
并且由于连接缓存问题而无法确定它们是否正常工作 - 在测试期间需要非常小心。连接后，尝试连接不正确也会有效。

问候，

Albert D. Kallal（访问MVP，2003-2017）

加拿大艾伯塔省埃德蒙顿

I use dsn-less connection strings most of the time. I have run into an issue with the drivers for sql server 2012 which I have not been able to resolve.

For discussion I will reference the function AttachDSNLessTable() at 

https://support.microsoft.com/en-gb/help/892490/how-to-create-a-dsn-less-connection-to-sql-server-for-linked-tables-in

as a method to test connection strings, because the code I use is more involved

Tthe driver spec'd in that function for trusted authentication is "SQL Server"

'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"

I find that if I use a later driver up to sql server 2008 is connects fine. However with the driver for sql server 2012 (and apparently sql server 2014, there are various contradictory messages as the installer goes along) it won't connect. The specific driver is

Microsoft ODBC Driver 11 for SQL Server ver 12.0.5543.11\x64

The database I spec in the test is sql server 2012.

Debug shows connection strings like these, depending on how I tweak the code:

ODBC;DRIVER={SQL Server Native Client 11.0};DATABASE=mydb;SERVER=192.168.3.5;Trusted_Connection=Yes;
ODBC;DRIVER=SQL Server Native Client 11.0;DATABASE=mydb;SERVER=192.168.3.5;Trusted_Connection=Yes;

Does anyone know what the issue is?

解决方案

Well, the native 11 or later drivers have to be installed on each client computer. If you use the default and "long time legacy SQL server, then they are installed by default.

As a general rule I use the legacy drivers since they always will be installed on any recent version of windows – so no additional installing of software is required.

However, one "big" reason to use the native 11 drivers if the tables in question use any "newer" datetime2 format columns. If you use the legacy drivers, then Access sees these columns as string, and not date time. So if tables user newer date time, then you REALLY have to quite much adopt the native drivers.

I note your one connection has {} around it, and the other does not – quite sure they not required when creating the connection string. I suppose you could try both and see what occurs. But you have to be REALLY, but REALLY careful. If you at ANY time connect with a known working string, then additional connections with INCORRECT formats will ALSO work! (this is because Access caches known working connections – and once that occurs, then Access will use these alternative connections (what this means is that you MUST (and I cannot stress this MUST) exit Access and re-enter when testing if a new connection string works if at ANY point in time you connected by any means during that session with a working connection.

I don’t know why some of your strings don’t work, but you would be best for testing in those cases to use Access to launch the ODBC panel to create a new connection. Keep in mind that any and all connections created from Access (a file DSN) are by default DSN-less. The DSN (a file dsn) are ONLY used at link time – after that they are DSN-less and thus you can distribute the application to other workstations without the DSN being required.

So not at all sure what you issue is – but if you are using native drivers, then they have to be installed on each workstation. And if you by accident connected with a working connection, then additional testing attempts are null and void and cannot be certain they worked due to the connection caching issue – so much caution is required during testing. Once you connection, then attempts with incorrect connections will also work.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

这篇关于DSNless SQL Server 2012连接字符串的问题;司机不行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！