c# - 创建后无法连接到 Microsoft SQL Server 数据库 ("The system cannot find the file specified")

Question

我有一个脚本可以为需要数据库连接的测试运行准备环境(创建一些表写入一些值等)

我有一个通过以下方式创建 DB_NAME 的 PowerShell 脚本

& sqllocaldb create DB_NAME
& sqllocaldb start DB_NAME
& sqllocaldb info DB_NAME

输出是:

LocalDB instance "DB_NAME" created with version 13.1.4001.0.
LocalDB instance "DB_NAME" started.

Name:               DB_NAME
Version:            13.1.4001.0

Shared name:        

Owner:              fv-az604\VssAdministrator
Auto-create:        No
State:              Running

Last start time:    11/7/2019 11:47:43 PM

Instance pipe name: np:\\.\pipe\LOCALDB#24C8D765\tsql\query

连接是通过下面的代码进行的

using (SqlCommand cmd = new SqlCommand(sql))
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        cmd.Connection = connection;
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
    }
}

连接字符串如下所示:

Application Name=MyApp;Connect Timeout=60;Data Source=localhost;Initial Catalog=master;Integrated Security=sspi;Connection Reset=false;Min Pool Size=1;Max Pool Size=200;Pooling=true;MultipleActiveResultSets=true;Enlist=false

数据库名称:DB_NAME

完整的堆栈跟踪是:

[Error]: 
Type: System.ComponentModel.Win32Exception
Message: The system cannot find the file specified
Source: 
Stack:

Type: System.Data.SqlClient.SqlException
Message: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Source: .Net SqlClient Data ProviderHelpLink.ProdName: Microsoft SQL Server
HelpLink.EvtSrc: MSSQLServer
HelpLink.EvtID: 2
HelpLink.BaseHelpUrl: http://go.microsoft.com/fwlink
HelpLink.LinkId: 20476

Stack:
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at MyClass.MyFuncABC(String connectionString, String databaseName) in D:\a\1\s\App\MyClass.cs:line 333
   at MyClass.MyFuncAB() in D:\a\1\s\App\MyClass.cs:line 444
   at MyClass.MyFuncA() in D:\a\1\s\App\MyClass.cs:line 555
   at App.Main(String[] args) in D:\a\1\s\App\Main.cs:line 1337

Answer 1

关于丢失文件的第一个错误消息只是水中的泥浆。真正的是第二个，这是每个 SQL Server DBA 都熟知的:

Message: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

正如消息所说，无法连接到 SQL Server。客户端库尝试通过 TCP/IP、共享内存和命名管道进行连接。由于管道是最后一个选项，它也失败了，因此错误包含来自管道的消息。

本地数据库 has it's own , 相当 peculiar syntax , 对于 connection strings .您使用的是连接到真正的 SQL Server。

也许是这样

Server=np:\\.\pipe\LOCALDB#24C8D765\tsql\query

会更好。另外，find out你的 localdb 的实例名称并尝试类似的东西

Server=(localdb)\MyInstance;Integrated Security=true;