To make this a little more concrete, here is the connection string I use... "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\testdata\DVDCollectionDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True'" so in that case what would be the dbname?
Note that I need to do this through API (in a .NET application)
HTH, Jens K. Suessmeyer.
As I said, I need (I think) to use sp_detach_db, but I am unsure what the name of the database is to use on that call, indeed, now I think of it, how do I invoke a stored procedure on a closed connection?
HTH, Jens K. Suessmeyer.
I have had the same problem. Once i open a sql express 2005 file based DB using .net i cannot close it, even when setting connection pooling to false in the connection string. Am i doing something wrong here ?.
heres my con string:
@"Data Source=.\SQLEXPRESS;AttachDbFilename="
+ _dir + ";Integrated Security=True;User Instance=True;Pooling=false;MultipleActiveResultSets=True;Connect Timeout=0;"I believe that the default time-out for a connection pool is five (5) minutes. That is five minutes after the last client access.
Once a pool has been established, changine the settings, e.g., Pooling=false, will affect new connections, it doens't remove the existing pool objects. They will evenually 'time out'. I guess you could explicitly kill off the pool objects, calling the garbage collector, but that could be 'messy'.
thanks Arnie for the explanation,
I managed to work around the problem with an 'overkill' solution;
1) I first close the System.Data.SqlClient.SqlConnection,
2) then using the 'System.ServerviceProcess.ServiceController' libary i programmatically stop the "mssql$sqlexpress" service (ie the sql 2005 express service),
3) this unlocks the sql server express .mdf file after about 2 seconds
4) then i programmatically copy the mdf file and restart the sql 2005 express service using the System.ServerviceProcess.ServiceController again.
In hindsight maybe calling the garbage collector might have been better, however this solution works programmatically and is not too many lines of code, + it is a rarely used component.
Cheers
