VB 2008 Express and SQL

[CabinFever]

All Done Local on computer that has both VB2008 Express and SQL 2008 Express installed on it.

If my connection string looks like the following it WORKS.

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\SSPE_InterCOMM.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

Why can't I share the containing folder and map it. For example as Y?

Data Source=.\SQLEXPRESS;AttachDbFilename="Y:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\SSPE_InterCOMM.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

I get "This connection cannot be tested because the specified database file does not exist."

I was hoping to be able to set it up this way so that this application can be ran on remote computers all with a mapped drive back to mine.

 

[gmmastros]

That is certainly not the best way to accomplish this. Let me explain...

In order for this to work, every computer would have to have the SQL Express database engine installed on it. Instead, if you install SQL Express on one computer and then connect to it from the remote computers, it will be a lot less resource intensive.

When SQL Connects to a database, it get's an exclusive lock on the file. This means that you cannot (under any circumstances) have the same database attached to multiple instances of SQL Server.

Even if you were able to get this to work, you would likely have a lot more network traffic than you actually need. For example, if you run a query against a large table that returns just a small number of rows, all of the data would have to travel across the network so that the SQL Engine would be able to discard it. Certainly not ideal.

In some ways, I am disappointed with microsoft for allowing user instances with the AttachDBFilename functionality. This really was meant for occasional database use on a single computer. For an application this extends beyond the "non-trivial", this really isn't a good option to use.

Perhaps a good use of the user instance would be in a remote data collection environment where you send people out in the field with laptops to collect certain bits of information that they synchronize with a central database.

Anyway... I suggest you attach the database to an instance of SQL Server on a computer, and then have the clients attach to the the SQL Instance on that one computer. This is the way the database engine was intended to be used, and where it really shines.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CabinFever]

Thanks for responding so quickly!

Hope I am not getting out of bounds on this forum by asking more about sql.
This is all new to me...I am trying to have 5-6 computers looking at the same data tables in real time (5 min auto-refresh). Each being able to add and update as needed.

I have searched and nothing is clear on this, to me anyway.

"I suggest you attach the database to an instance of SQL Server on a computer, and then have the clients attach to the the SQL Instance on that one computer."

Still leaves me scratching my head. Please can you explain more, or point me to a good starting point. forum, book, ,person etc.

Thanks again your your input on this! ! !

 

[gmmastros]

I don't have SQL2008 installed, but I suspect the "clicks" to attach a database are very similar to SQL 2005.

First, open SQL Server Management Studio (SSMS).
Log in with a system administrator account (usually windows authentication will do the trick if you are running SSMS on the same computer as the database engine.
After logging in, you should see an object explorer window on the left side of the screen. If you don't, click View -> Object Explorer.

The object explorer looks similar to Windows Explorer folder view. The top level will be the instance name (usually your computer name followed by \SQLExpress).
Below that, you will see Databases. Below Databases, you will see "System Database" (don't mess with these) and any user databases that are attached to the sql instance. If your user database is not listed, then you will need to attach it.

To attach a database...
Right click databases in the object explorer window.
Click "Attach..."

An "Attach databases" window will appear. Click the "Add" button. Browse to your database (the MDF file). Click OK. Click OK again.

There. Done. The database is now attached.

Before remote users can connect to the database, there's a little more you will need to do. Specifically... security. I find that it's usually easier to use SQL Authentication. So...

In object explorer, expand security, right click "Logins", click "New Login". Add your login names, select SQL Server Authentication, give it a password. Click "User Mapping" on the "Select a page" section and select your database from the list. To make things easier on yourself, also select db_owner in the box labeled "Database role membership".

Most of the time, that's all you need to do. Of course, you will need to change the connection string

http://connectionstrings.com

SQL Server usually connects (from client computer to database server) using TCP/IP. If you have a firewall running on your computer, you will probably need to make an exception in it to allow for SQL Server to operate. Google for "SQL Server firewall exception" to see how this is done.






-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CabinFever]

Thank you once again for your time...I will dive in once more tomorrow.

The database table is bound to my datagridview. Once I built it and installed on remote computer, SQL server installed on it as well(prerequisite). My thinking was that it(sql) was needed to enable my remoteVB app to interface with the original database. Application ran great with all data present. Until I made changes. So there must be a local copy of data now on each computer instead of both looking at the same file.

 

[CabinFever]

RESOLVED:

After turning off the Firewall on my PC (running sqlserver) I got a new error 'the SELECT permission was denied...' added my application to the exceptions list. Turned the firewall back on giving the same error...OK . Then went into my created database properties and added 'guest' to the permissions properties...It is working now.

I knew that this had to be something that I had missed, being the first time doing this. I concentrated on it being a SQLserver setting and didn't even think about each database that I created.

Live and learn...And...Hopefully remember!