Locking a database file prior to copying it

[John]

Hi all,

My application updates a sql server 2005 express database prior to copying
it with the result being the "in use by another process" and I cannot copy
it as a result. I've posted the code that updates the database below. Please
could someone let me know how I free up the mdf file properly prior to
copying it.

Otherwise, does someone know how I can free it up programmatically or simply
copy it without receiving the errror?

Regards
John.

SqlCommand cm = null;

SqlConnection cn = null;

bool retcode = true;

try

{

cn = new SqlConnection(connstring);

cn.Open();

cm = new SqlCommand("Delete from tblSys where sys_key = 'exp'");

cm.CommandType = System.Data.CommandType.Text;

cm.Connection = cn;

cm.ExecuteNonQuery();

cm = new SqlCommand("Insert into tblSys (sys_key, sys_val) values('exp', '"
+ newExpDate + "')", cn);

cm.CommandType = System.Data.CommandType.Text;

cm.ExecuteNonQuery();

retcode = true;

}

catch (System.Exception er)

{

retcode = false;

}

finally

{

try

{

if (cm != null)

{

cm.Connection.Close();

cm.Dispose();

}

if (cn != null)

{

if (cn.State == System.Data.ConnectionState.Open)

{

cn.Close();

}

cn.Dispose();

}

}

catch (System.Exception er)

{

retcode = false;

}

}

return retcode;

 

[David Browne]

Hi all,

My application updates a sql server 2005 express database prior to copying
it with the result being the "in use by another process" and I cannot copy
it as a result. I've posted the code that updates the database below.
Please could someone let me know how I free up the mdf file properly prior
to copying it.

Otherwise, does someone know how I can free it up programmatically or
simply copy it without receiving the errror?

Did you attach the db file in your connection string? If so did you turn
off connection pooling in the connection string?

You can always manually detach the database by running sp_detach_db.

David

 

[John]

Hi David,

Thanks a lot for the response - I've tried neither suggestions. I'll give it
a try in a while and get back to you.

Regards
John.

 

[John]

Hi David,

The pooling didn't work for me but the explicit call to detach the db did -
I did, however, have to put in a thread.wait for 10 seconds (actually 5 did
the trick but I want to be sure) to allow a little time for detachment.

Thanks a lot.

Regards
John.