I have an application that was developed under Visual Basic .NET 2005 Standard that I keep continuously running to monitor and gather data and store that data in a SQL Server Express database. This is all done on a single computer that's not networked to anything. The trouble is, I'd like to look at the data in the database from time to time without shutting down the application, but I'm not able to do so. If I try to copy the database files, named lamination.mdf and lamination.ldf, to another location, I get the following error message;
"Cannot copy lamination: There has been a sharing violation. The source or destination file may be in use."
I would like to be able to copy the file to my office computer where it would be convenient to use Management Studio Express to look at the database tables. I can't even use Management Studio Express on the computer where the application is running, as I get the following message when I try to attach the database:
"CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file 'C:\Documents and Settings\tbarnard\My Documents\Visual Studio 2005\Projects\Lamination Phase I\LamDataBase\Lamination.mdf'. (Microsoft SQL Server, Error: 5123)"
Here is the connection string in the VB application:
"Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\tbarnard\My Documents\Visual Studio 2005\Projects\Lamination Phase I\LamDataBase\Lamination.mdf";Integrated Security=True;User Instance=True"
In the VB app, connections to the database are made only every few minutes, and the connections are immediately closed.
Is there anyway I can either copy the database files, or at least look at the database tables with Management Studio Express without stopping the VB application?
Thanks,
Tim
Multiple connections to user instances are not possible. Attach the database to the Express instances to make that possible.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||
Jens, I'm confused. You say that multiple connections to user instances are not possible, but then you say to attach the database to the Express intances to make that possible. Is it possible or not? And if it is possible, could you go into more detail to explain how?
Thanks,
Tim
|||You'd need to shut down SQL before you can copy the files.|||I would suggest reading something about the priciples of user instance and regular SQl Server instances. SQL Server instances are permanatly attached to the SQL Server service, while user instances are only attached during the usage of any application. If one application gains access to the file, it is opened exclusivly for the user, therefore cannot be opened through another user instance connection. The Server permanently attached file on the other site will allow though the service to handle multiple user requests.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||I would be careful which such assumptions. Sure the files can be copied during the usage, but shutting down SQL Server will make the datafile be consistent. If you do not shut down the server service, the information of datafile and logfile can differ making the database after a restore inaccessible. So the better way would be to shutdown the service and therefore close the files.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||
Jens,
I found one way to connect to, and examine, a database with Sequel Server Management Studio Express, while it is being used by another application with a user instance. It is described at the following link, under the heading "Connecting to a SQL Server Express user instance":
Jens,
I found one way to connect to, and examine, a database with Sequel Server Management Studio Express, while it is being used by another application with a user instance. It is described at the following link, under the heading "Connecting to a SQL Server Express user instance":
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlexprbol/html/0bd6143a-ca9d-4f36-ae23-bd35a2d22d70.htm
Basically, the application needs to be running first, and then you connect to the user instance with a named pipe protocol. However, the Select statement they use in the example didn't work for me as is;
Select owning_principle_name, instance_pipe_name from sys.dm_os_child_instances
I had to drop the field names and use this
Select * from sys.dm_os_child_instances
Then, from the new connection listed in the object explorer I drilled down to the folder containing the user instance database (the folder name having the same path as my application's database) and was able to "manage objects related to your user instance" as they put it.
Tim
