无法立即连接到新创建的SQL Server数据库到新、数据库、SQL、Server

2023-09-03 03:50:55 作者：风过了无痕

我要创建使用 SQL Server管理数据库对象。

我写了下面的方法来生成一个数据库：

I wrote the following method to generate a database:

public static void CreateClientDatabase(string serverName, string databaseName)
{
    using (var connection = new SqlConnection(GetClientSqlConnectionString(serverName, String.Empty)))
    {
        var server = new Server(new ServerConnection(connection));
        var clientDatabase = new Database(server, databaseName);

        clientDatabase.Create();
        server.ConnectionContext.Disconnect();
    }
}

此后不久，我调用另一个方法来执行一个SQL脚本生成的表，等：

Shortly thereafter, I call another method to execute a SQL script to generate tables, etc.:

public static void CreateClientDatabaseObjects(string createDatabaseObjectsScriptPath, string serverName, string databaseName)
{
    using (var connection = new SqlConnection(GetClientSqlConnectionString(serverName, databaseName)))
    {
        string createDatabaseObjectsScript = new FileInfo(createDatabaseObjectsScriptPath).OpenText().ReadToEnd();
        var server = new Server(new ServerConnection(connection));

        server.ConnectionContext.ExecuteNonQuery(createDatabaseObjectsScript);
        server.ConnectionContext.Disconnect();
    }
}

语句 server.ConnectionContext.ExecuteNonQuery（createDatabaseObjectsScript）抛出一个 SQLEXCEPTION 与消息的无法打开登录所请求的数据库数据库名称。登录失败。用户登录失败'用户'。的

The statement server.ConnectionContext.ExecuteNonQuery(createDatabaseObjectsScript) throws a SqlException with the message Cannot open database "The database name" requested by the login. The login failed. Login failed for user 'the user'.

如果我试图通过在调试器中陈述步进，此异常永远发生和脚本执行罚款。

If I try stepping through the statements in the debugger, this exception never happens and the script executes fine.

我唯一的猜测是，服务器需要一些时间来初始化数据库就可以打开了。这是准确的？有没有办法判断一个数据库已经准备好以外尝试和失败连接？

My only guess is that the server needs some time to initialize the database before it can be opened. Is this accurate? Is there a way to tell if a database is ready other than trying and failing to connect?

编辑：我要指出，该数据库是绝对被在所有情况下创建

I should mention that the database is definitely being created in all cases.

编辑2：之前创建数据库，我称之为的 EntityFramework.dll的System.Data.Entity.Database.Exists 的方法来检查，如果该数据库已经存在。如果我删除电话，一切似乎都正常工作。这几乎就像如果调用缓存的结果，看到新的数据库搞乱了后续连接（无论他们是否使用实体框架）。

Edit 2: Prior to creating the database, I call the System.Data.Entity.Database.Exists method of EntityFramework.dll to check if the database already exists. If I remove that call, everything seems to work as expected. It's almost as if that call is caching the result and messing up the subsequent connections from seeing the new database (regardless of whether or not they use Entity Framework).

修改3：我更换了的EntityFramework的 Database.Exists 方法与使用SMO为基础的方法 Server.Databases 。载（数据库名称）。我在数据库中不能立即产生后，打开得到了同样的问题。同样，如果我不检查数据库是否存在创建之前，我可以马上创建后打开它，但我希望能够在创建之前检查是否存在，所以我不尝试创建一个现有的数据库。

Edit 3: I replaced the EntityFramework's Database.Exists method with an SMO-based approach using Server.Databases.Contains(databaseName). I get the same issue in that the database cannot be opened immediately after creating. Again, if I don't check if a database exists prior to creation I can immediately open it after creating it but I'd like to be able to check for existence prior to creation so I don't try to create an existing database.

这两个的EntityFramework的 Database.Exists 和SMO的 Server.Databases.Contains 都只有执行的SQL查找在master.sys.databases数据库名。我不明白为什么/如何干扰了数据库连接。

Both the EntityFramework's Database.Exists and SMO's Server.Databases.Contains both just execute SQL that looks for the database name in master.sys.databases. I don't understand why/how this is interfering with database connections.

无法立即连接到新创建的SQL Server数据库到新、数据库、SQL、Server

推荐答案