StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTrying to detach attach database - can not acces db after
text
Body
copied!<p>I try to do the following:</p> <ul> <li><p>Detach the database</p></li> <li><p>Copy files to a temporary folder</p></li> <li><p>Attach the database again</p></li> </ul> <p>This works the first time, but when I try to run this method a second time from the same process I get an error. I can always access the databse that was attached again from other clients, but not from within my application.</p> <p>The error is:</p> <p>"Transport level error while trying to send the request to the server.(provider: Shared Memory-Provider, error: 0 - no process at the other end of the pipe.)", when I try to read data from sys.database_files of the newly attached db.</p> <p>The error is translated from german "Fehler auf Übertragungsebene beim Senden der Anforderung an den Server. "</p> <p>It happens after "cmdGetDBFileName.ExecuteReader". I can still open the connection but querying sys.database_files failes.</p> <p>The source is pretty long, but I guess you can skip the part in the beginning where I get the filenames of the db to detach. Do you see my error or have any ideas what I could check?</p> <pre><code>public bool DetachB2CPrepare() { _log.Debug("DetachB2CPrepare"); SqlConnection prepareDBConnection = null; SqlConnection prepareMasterDBConnection = null; SqlDataReader readerDbFiles = null; bool result = true; try { //rc_b2c_product_prepare.mdf string prepareDBPysicalFileName = ""; //rc_b2c_product_prepare string prepareDBFileName = ""; //D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\rc_b2c_product_prepare.mdf string prepareDBFileNameComplete = ""; //rc_b2c_product_prepare_1.ldf string prepareTransactionLogPhysicalFileName = ""; //rc_b2c_product_prepare_log string prepareTransactionLogFileName = ""; //D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\rc_b2c_product_prepare_1.ldf string prepareTransactionLogFileNameComplete = ""; _log.DebugFormat("Try to open B2CPrepare"); prepareDBConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["B2CPrepare"].ConnectionString); prepareDBConnection.Open(); //Get the file names of DB SqlCommand cmdGetDBFileName = new SqlCommand("select name , physical_name, type from sys.database_files where type= 0"); cmdGetDBFileName.Connection = prepareDBConnection; readerDbFiles = cmdGetDBFileName.ExecuteReader(); if (readerDbFiles.Read()) { prepareDBFileName = (string)readerDbFiles["name"]; prepareDBFileNameComplete = (string)readerDbFiles["physical_name"]; int lastSlash = prepareDBFileNameComplete.LastIndexOf(@"\"); prepareDBPysicalFileName = prepareDBFileNameComplete.Substring(lastSlash + 1, prepareDBFileNameComplete.Length - lastSlash - 1); readerDbFiles.Close(); } else{ return false; } cmdGetDBFileName.CommandText = "select name , physical_name, type from sys.database_files where type= 1"; readerDbFiles = cmdGetDBFileName.ExecuteReader(); if (readerDbFiles.Read()) { prepareTransactionLogFileName = (string)readerDbFiles["name"]; prepareTransactionLogFileNameComplete = (string)readerDbFiles["physical_name"]; int lastSlash = prepareTransactionLogFileNameComplete.LastIndexOf(@"\"); prepareTransactionLogPhysicalFileName = prepareTransactionLogFileNameComplete.Substring(lastSlash + 1, prepareTransactionLogFileNameComplete.Length - lastSlash - 1); readerDbFiles.Close(); } else { return false; } _log.DebugFormat("shrink transactionlog {0}", prepareTransactionLogFileName); SqlCommand cmdShrinkPrepare = new SqlCommand(string.Format(@"DBCC Shrinkfile('{0}',100) ", prepareTransactionLogFileName)); cmdShrinkPrepare.Connection = prepareDBConnection; cmdShrinkPrepare.ExecuteNonQuery(); //master auf MyProductName prepareMasterDBConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyProductNameMaster"].ConnectionString); prepareMasterDBConnection.Open(); _log.Debug("cmdOffline"); //Datenbank verbindunge löschen SqlCommand cmdOffline = new SqlCommand(@"ALTER DATABASE rc_b2c_product_prepare SET SINGLE_USER WITH ROLLBACK IMMEDIATE"); cmdOffline.Connection = prepareMasterDBConnection; cmdOffline.ExecuteNonQuery(); _log.Debug("cmdDetach: rc_b2c_product_prepare" ); SqlCommand cmdDetach = new SqlCommand(@"dbo.sp_detach_db @dbname = N'rc_b2c_product_prepare',@keepfulltextindexfile = N'false'"); cmdDetach.Connection = prepareMasterDBConnection; cmdDetach.ExecuteNonQuery(); string pathForCopies = MyProductName.Backend.settings.B2CPrepareDBBackupPath; //copy files to temp folder string tempFileDB = pathForCopies + "\\" + prepareDBPysicalFileName; string tempFileLog = pathForCopies + "\\" + prepareTransactionLogPhysicalFileName; _log.DebugFormat("Copy: {0} TO: {1}", prepareDBFileNameComplete, tempFileDB); System.IO.File.Copy(prepareDBFileNameComplete, tempFileDB, true); _log.DebugFormat("Copy: {0} TO: {1}", prepareTransactionLogFileNameComplete, tempFileLog); System.IO.File.Copy(prepareTransactionLogFileNameComplete, tempFileLog, true); _log.DebugFormat("cmdAttach: db {0} log {1}", prepareDBFileNameComplete, prepareTransactionLogFileNameComplete); SqlCommand cmdAttach = new SqlCommand( string.Format(@" CREATE DATABASE rc_b2c_product_prepare ON ( FILENAME = N'{0}' ), ( FILENAME = N'{1}' ) FOR ATTACH", prepareDBFileNameComplete, prepareTransactionLogFileNameComplete)); cmdAttach.Connection = prepareMasterDBConnection; cmdAttach.ExecuteNonQuery(); _log.Debug("ALTER DATABASE rc_b2c_product_prepare SET MULTI_USER "); //set multi user SqlCommand cmdOnline = new SqlCommand(@"ALTER DATABASE rc_b2c_product_prepare SET MULTI_USER WITH ROLLBACK IMMEDIATE"); cmdOnline.Connection = prepareMasterDBConnection; cmdOnline.ExecuteNonQuery(); return result; } catch (Exception e) { _log.Error(e); return false; } finally { if (prepareDBConnection != null) { prepareDBConnection.Close(); } if (prepareMasterDBConnection != null) { prepareMasterDBConnection.Close(); } if (readerDbFiles != null) { readerDbFiles.Close(); } } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload