attached in vb8 vs vb6 incompatability

I have two programs which access sql server 2005. One was written in vb6 the other in vb8 (aka vb.net 2005).

vb6 works well with this connection string:

Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Database=MyDbName;Data Source=.\MyServerInstanceName

vb8 (in vs2005) generated the following connection string which works well in the vb8 application:

Data Source=.\MyServerInstanceName;AttachDbFilename='C:\MyDbPath\MyDbName.mdf';Integrated Security=True

Within their own programs each connection string works well. However, if I use one program and then the other (e.g., vb8 program then vb6), I get an error message:

Unable to open the physical file "C:\MyDbPath\MyDbName.mdf". Operating system error 32: "32(The process cannot acces the file because it is being used by another process.)".

If I detach the database using SSMS, I can then access it with the other program. Obviously, this is not an ideal situation and not something I would want the end user to have to do.

Also, when using the vb6 connection string, SSMS lists the database in Object Explorer as: MyDbName. When using the vb8, the database is listed as C:\MyDbPath\MyDbName.mdf. BTW, the database is set for multi-user.

How can I get the two programs to place nice together?

YOu can either use the database file with one process or attach the database to a SQL Server (Express) instance to make it accessible for more than one application.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

When you use the attach command in the connection string the application that is running the command has exclusive access to the files, If you want the database to be used by multiple applications you will need to first make sure that the database will allow remote connections then attach the database to the database engine using the management tools or the command line tools. Once this is done you would then just connect using the following string

"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"

The above connection string was taken from www.connectionstrings.com

|||

The database absolutely must be used by more than one program. SQL Server Express is not an option because of its 4gb limit. Perhaps I'm not understanding something here. It sounds as tho you are saying an SQL Server database cannot be used by more than one program at the same time unless the database is so small that it is unusable for all except the simplest of applications.

|||

Again, perhaps I am missing something. Are you saying SQL Server 2005 cannot be used by multiple users unless they are all using the same program, or they have enough database management experience to use tools such as SSMS, or even more shocking still: they have to use command line tools? This is starting sound like Linux and MySQL.

It is shocking to imagine that everyday users would have to have such extensive skills just to use database enabled Windows XP? applications.

|||Yes, you are right, you got me wrong :-)

OK, a database file which is not attached can be attached as a user instance with a SQL Server Express instance. As a user instance, only the application which opened the connection to the user instance is able to access the database. If you attach the database file to a server instance, you can access the data through the SQL Server Express Service as you already know it from the common SQL Server database access.

Hope that clarifies things.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Sorry, but no. I don't understand why you are talking about SQL Server Express. I'm not using the Express version; it is totally unusable for the project I'm working on.|||I was talking about Express because you are using the user instance feature.|||

Where am I using the user instance feature?

|||Data Source=.\MyServerInstanceName;AttachDbFilename='C:\MyDbPath\MyDbName.mdf';Integrated Security=True|||

Now, I am confused ! The .\MyServerInstanceName refers to an instance of SQL Server 2005 and not .\sqlexpress . Also, in VS2005, if I try to change the connection string to User Instance = True (it is currently set to False), I get the following error message:

The user instance login flag is not supported on this version of SQL Server. The connection will be closed.

|||I resolved the problem by starting both vb6 & vb8 programs with a check for whether or not the database is attached. If it is not, I use sp_attach_single_file_db to attach it. Then I changed the part of the vb8 connection AttachDbFilename= to Database=.