c++ - 无法通过ODBC连接到SQL Server

Question

过去，我曾使用ADO来访问SQL Server，ADO连接对象的连接字符串为:

Provider=sqloledb;Data Source=MYPC;Integrated Security=SSPI;

其中MYPC是我的计算机的名称，而SQL Server作为默认实例安装在我的计算机上。

上面的连接字符串效果很好。

但是，现在据说ADO已过时，Microsoft再次推荐ODBC(请参阅 https://blogs.msdn.microsoft.com/sqlnativeclient/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access/)，因此我必须修改代码以改为使用ODBC。

因此，我将与SQL Server的连接更改为以下代码:

    CDatabase Database;

    //  Provider=sqloledb;Data Source=MYPC;Integrated Security=SSPI;
    Database.OpenEx(_T("Driver = {SQL Native Client}; Server = MYPC; Trusted_Connection = yes;"), CDatabase::noOdbcDialog);

    Database.ExecuteSQL(_T("create database [MyDB2019] on primary (name=[MyDB2019_File],filename='F:\\MyDB2019.mdf')"));

    Database.Close();

但是，此代码不起作用。执行 Database.OpenEx之后，将出现CDBException，指示

Data source name not found and no default driver specified.

为什么？

注意:我正在使用Visual C++ 2008和ADO

Answer 1

错误/异常指出了问题所在(尽管乍一看似乎有点通用)。

“找不到数据源名称，并且未指定默认驱动程序”

Data source name not found --> You did not specify a DSN, you do not want to use a DSN, ignore this part

。

and no default driver specified --> You intend to use a driver, so this part of the error most likely applies to you.

The connection string appears syntactically correct: "Driver={Driver Name}...", so the next step is to check whether the driver you try to use, named SQL Native Client, exists on your machine.

“SQL Native Client”是/是SQL Server 2005的驱动程序名称。
从SQL2008开始，驱动程序 名称获得了 版本描述符。

Driver={SQL Native Client}             ,sql2005   
Driver={SQL Server Native Client 10.0} ,sql2008  
Driver={SQL Server Native Client 11.0} ,sql2012 and later  
Driver={ODBC Driver 11 for SQL Server} ,sql2012 and later, odbc appears in the name
Driver={ODBC Driver 13 for SQL Server}  
Driver={ODBC Driver 17 for SQL Server}

在计算机上查找已安装的“SQL Native”和“SQL Server ODBC驱动程序”驱动程序的简单方法是运行“ODBC数据源管理器”。在搜索框中键入Odbc数据源，或在命令/地址栏中键入odbcad32.exe(对于64位:％windir％\ system32 \ odbcad32.exe)

在ODBC数据源管理器中，切换到“驱动程序”选项卡，您将在这里找到所有可用/已安装的驱动程序。

这是一个Powershell脚本，可使用odbc打开与localhost的连接。根据您安装的驱动程序进行相应调整

cls
$conn = new-object system.data.odbc.odbcconnection 
$conn.connectionstring = 
# all these installed on my pc and working
#"Driver={SQL Server Native Client 11.0};Server=localhost; Database=master;Trusted_Connection=yes;" 
#"Driver={SQL Server};Server=localhost; Database=master;Trusted_Connection=yes;" 
#"Driver={ODBC Driver 11 for SQL Server};Server=localhost; Database=master;Trusted_Connection=yes;" 
"Driver={ODBC Driver 17 for SQL Server};Server=localhost; Database=master;Trusted_Connection=yes;" 

$conn.Open()
$conn.State
$conn.Close();

..和mfc按钮

void CMFCDBTestDlg::OnBnClickedButton1()
{
    // TODO: Add your control notification handler code here
    CDatabase database;
    CString connectionstring = _T("Driver={SQL Server Native Client 10.0};Server=localhost;Trusted_Connection=yes;");
    CString messagetext;

    TRY{
        //database.Open(NULL, FALSE, FALSE, connectionstring, FALSE); //ok
        database.OpenEx(connectionstring, CDatabase::noOdbcDialog); //ok

        if (database.IsOpen()){
            messagetext = _T("open, database:") + database.GetDatabaseName();
            database.Close();
        }
   }CATCH(CDBException, e) {
       messagetext = _T("Database error: ")+e->m_strError;
   }
   END_CATCH;

   AfxMessageBox(messagetext, 0, 0);
}