SQL错误 [英] SQL Errors

问题描述

 Microsoft.SqlServer.Management.Smo.FailedOperationException：还原失败 服务器'   \\\\\ pipe \D50B8647-2E0A-47 \ tsql \query'。 ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException：执行Transact-SQL语句或批处理时发生异常。 ---> System.Data.SqlClient.SqlException：无法获取独占访问权限，因为   使用。 
 RESTORE DATABASE  异常终止。 System.Data.SqlClient.SqlConnection.OnError上的
（SqlException异常， Boolean  breakConnection）
 at System.Data.SqlClient.SqlInternalConnection.OnError（ SqlException异常， Boolean  breakConnection）
 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning（TdsParserStateObject stateObj）
 at System.Data.SqlClient.TdsParser .Run（RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream，BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject stateObj）
 at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds（ String  methodName， 布尔  async ）
 at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery（DbAsyncResult result，< span class =code-sdkkeyword> String  methodName， Boolean  sendToPipe）
 at System.SData.SqlClient.SqlCommand.ExecuteNonQuery（）
 at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery（ String  sqlCommand，ExecutionTypes executionType）
 ---内部异常堆栈跟踪结束--- 
 at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery（ String  sqlCommand，ExecutionTypes executionType）
 at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery（StringCollection sqlCommands，ExecutionTypes executionType）
 at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery（StringCollection queries）
 at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql（服务器服务器，StringCollection查询）
 at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore（Server srv）
 ---内部异常堆栈的结束trace --- 
 at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore（Se rver srv）

解决方案

基本上你试图对一个用户连接到它的数据库执行操作，并且操作必须在单用户模式下执行。要执行还原，必须将其包装在以下语句中：

  alter   database  database_name_goes_here  set  single_user  with   rollback  immediate 
 ...步骤 执行回滚 
  alter   database  database_name_goes_here  set  multi_user 

阅读本文 [ ^ ]

在第一个
，这是
使用此方法终止数据库中的所有连接
< pre lang =cs> public void KillActiveConnection（string DbName）
 {
 try 
 {
 ConnectforMaster （）; //连接字符串到主表
 cmd.CommandText =& quot; SELECT min（spid）from master.dbo.sysprocesses其中dbid = db_id（& quot; + DbName +& quot;）& quot; + 
& quot; &安培; QUOT ;; 
 cmd.CommandType = CommandType.Text; 
 cmd.Connection = cnn; 
 sda.SelectCommand = cmd; 
 sda.Fill（ds，& quot; kill& quot;）; 
 if（ds.Tables [& quot; kill& quot;]。Rows.Count  -  1！= -1）
 {
 for（int i = 0; i& lt ; = ds.Tables [& quot; kill& quot;]。Rows.Count  -  1; i ++）
 {
 cmd.CommandText =& quot; kill& quot; + ds.Tables [& quot; kill& quot;]。行[i] [0] .ToString（）; 
 cmd.CommandType = CommandType.Text; 
 cmd.Connection = cnn; 
 cnn.Open（）; 
 cmd.ExecuteNonQuery（）; 
 cnn.Close（）; 
} 
} 
} 
 catch（异常a）
 {
 SqlErrorShow（& quot; KillActiveConnection函数中的错误：& quot;，a 。信息）; 
} 
最后
 {
 cnn.Close（）; 
 Disconnect（）; 
 cnn.ConnectionString =& quot;& quot ;; 
} 
}< / pre> 
在删除所有可以恢复数据的连接后将数据库名称传递给它

Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server '\\.\pipe\D50B8647-2E0A-47\tsql\query'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Exclusive access could not be obtained because the database is in use.
RESTORE DATABASE is terminating abnormally.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
   at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
   at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)

解决方案

Basically you are attempting to perform an operation on a database which has a user connected to it, and the operation must be performed in single user mode. To perform your restore, you must wrap it in the following statements:

alter database database_name_goes_here set single_user with rollback immediate
...Steps to perform rollback
alter database database_name_goes_here set multi_user

Read This[^]

Hi 
for this at the first
Kill all connection in your database with this method
<pre lang="cs">public void KillActiveConnection(string DbName)
       {
           try
           {
               ConnectforMaster();//connection string to master table
               cmd.CommandText = &quot;SELECT  min(spid) from master.dbo.sysprocesses where dbid = db_id(&quot; + DbName + &quot;) &quot; +
                                   &quot; &quot;;
               cmd.CommandType = CommandType.Text;
               cmd.Connection = cnn;
               sda.SelectCommand = cmd;
               sda.Fill(ds, &quot;kill&quot;);
               if (ds.Tables[&quot;kill&quot;].Rows.Count - 1 != -1)
               {
                   for (int i = 0; i &lt;= ds.Tables[&quot;kill&quot;].Rows.Count - 1; i++)
                   {
                       cmd.CommandText = &quot;kill &quot; + ds.Tables[&quot;kill&quot;].Rows[i][0].ToString();
                       cmd.CommandType = CommandType.Text;
                       cmd.Connection = cnn;
                       cnn.Open();
                       cmd.ExecuteNonQuery();
                       cnn.Close();
                   }
               }
           }
           catch (Exception a)
           {
               SqlErrorShow(&quot;Error in KillActiveConnection function : &quot;, a.Message);
           }
           finally
           {
               cnn.Close();
               Disconnect();
               cnn.ConnectionString = &quot;&quot;;
           }
       }</pre>
pass your Database name to it after killing all connection you can restore your data

这篇关于SQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！