connect to SQL server 2005 with more then 1 user?

[leeshadmi]

Hi,
I using VB.Net 2008 to connect to SQL server 2005 express edition database file using this connection string:

Data Source=.\SQLEXPRESS;AttachDbFilename=" & mCurrentDB & ";Integrated Security=True;Connect Timeout=30;User Instance=True

When

mCurrentDB

is the full path to the database file.
But this works only for one user so if i want to run the application from 2 computers and to connect it to one same database file i just cant since the second application will not connect to it.

Is this something that can be fixed in my connection string?
Do i need to install any aditional files\options in the SQL server setup?
Please help me to understand it...
Thanks

[viperbyte]

Try this:

ALTER DATABASE yourDatabase
SET SINGLE_USER
GO
EXEC sp_dbcmptlevel develop, 90;
GO
ALTER DATABASE develop
SET MULTI_USER
GO

I haven't tried this with Express but it works for Enterprise. First try just using the Second go block. If that doesn't go well then try the whole block.

[leeshadmi]

Thanks but i dont understand is this the full connection string or i need to combine it with what i have?
if yes then how? what is the right way\place to add this text?

[viperbyte]

The code executes a stored procdeure or two depending how much of it you use. In the mentioned code I accidently left my database name "develop" in the code. Instead of the word develop you use your database name. The code has nothing to do with your connection string. You leave that the way it is. This code affects the database to make it single or multi-user. You execute the script in Query Analyzer. Query Analyzer is in SQL Server.

In Query Analyzer put in

GO
ALTER DATABASE yourDatabaseName
SET MULTI_USER
GO

and run the query. If you get an error message then try the complete code:

ALTER DATABASE yourDatabase
SET SINGLE_USER
GO
EXEC sp_dbcmptlevel yourDatabaseName, 90;
GO
ALTER DATABASE develop
SET MULTI_USER
GO

Substitue the word yourDatabaseName with your database name. I just took a peek at my instance of SQL Server Express and don't see a query analyzer. I don't know how one would execute Stored Procedures in SQL Server Express as-is. I know that there's a graphical tool download for it. And maybe once you have that downloaded you will be able to execute the script. AND maybe you can execute the script without the download but I don't know how. I don't use Express. AND finally I'm not even sure if Express offers multi-user capabilities. But I would be surprised if it didn't. Poke around this forum or the internet on how to execute Stored Procedures on Express.

[viperbyte]

Again I left the word "develop" in part of the script. That's My database name. You will need to change that.

[leeshadmi]

Where do i find the Query Analyzer tool?
I dont see it in the start menu items or in the server explorer options.
Thanks

[leeshadmi]

ok i found it but do i need to run in at customer computer?
Do i need to run it on any customer computer or just on the template database i have?

I created a template database that i copy to the path the user select and only then i connect to this database so i need to understand how it works and if i need to run this command on each copied database or if i run it on the template database and the settings remain for all the copied databases (Can be with deferent names & locations).
Thanks

[viperbyte]

The code references the database.

GO
ALTER DATABASE yourDatabaseName
SET MULTI_USER
GO

Therefore if the database is already in place on various machines then the code would have to be run on every computer that is running the instance of SQL Server Express with your database. If you run the code against your template database then when you copy any future databases from that template your newly created databases will be fine and you won't have to run the code against the newly created databases. Just distribute them.

[leeshadmi]

Is this the only way i can define a database to support multiply connections?

When try this the computer that i ran this commands can connect to the database but other computer can't even if its the only running computer.
(=1 connection)
Cant i find a way to set the connection string to support multiply connections?

[TechGnome]

HAng on... first off... you need to make sure your database is on a SQL Server or a SQL Express install that others can get to. By using AttachDbFilename and User Instance=True you're actually creating a USER Instance of your database. Meaning you and only you will be able to get to it.

What you'll need to do is 1) Re-build the database in a proper SQL Server instance on a server somewhere, 2) recreate the database on an instance of SQL Server Express that has been opened to allow Remote Connections, or 3) Take your MDF file and issue an Attach Database.

Some MSDN Documentation on Attaching Databases
http://msdn.microsoft.com/en-us/library/ms190209.aspx
http://msdn.microsoft.com/en-us/libr...9(SQL.80).aspx
http://msdn.microsoft.com/en-us/library/ms165673.aspx
http://msdn.microsoft.com/en-us/libr...1(SQL.80).aspx

-tg

[dglienna]

Unless you want all users to use the SAME database. Then you could change the .\ to machinename\sqlexpress (leaving OFF the \\machinename\ slashes

[leeshadmi]

I want to have one database file that will be in a network drive path and that all users will use the same file so in case that user 1 add record user 2 will see it after refresh his serach results.
Can you give me an idea or sample to a solution?
Thanks