Hi Mike,
I've had a look at the white paper on use instances and also stopped using Management Studio, as you suggested. The problem is that I can run my application from VWD quite happily, until for no reason the application can't connect to the database. This happens ramdomly and is very frustrating. I have worked out a way to correct this problem with the following method (I don't know if this is correct but it's the only work around I know.
1) I close down VWD and open Management Studio,
2) I right click to attach a database, and then navigate to the .mdf file for the database, and click to attach it. I then delete the existing icon for that database (which I can't acess anyway, it only shows me the main icon but not the tables)
3) I rename the new instance of the database to the correct name (minus path info etc)
4) I create an SQL script as follows:
EXEC sp_grantlogin 'jon\ASPNET'
(This line executes correctly),
Then I enter these lines:
USE BalloonShop \*name of database \*
EXEC sp_grantdbaccess 'jon\ASPNET'
(At this stage when I execute the query, Management Studio informs me that this name already exists in the database and so doesn't execute)
5) I now close out of Management Studio and re-open VWD, and I can now run my application correctly.
I know I've got some configuration settings wrong somewhere but I find the whole user instance thing confusing. For the record I'm running the appllication through VWD's built in web server, I did originally have it through IIS but I moved it (this will explain the confusion of locations from my first post, I gave you the original location, sorry about that).
Any advice you can give would be gratefully recieved, ie is there an easy way to reconfigure my settings to make everything work as expected or have I got to start the database again from scratch (not a pleasent thought). Also is there an idiots guide to SQL Server Express anywhere that I can read.
Jon
ps I've just realised that I get the 'can't connect to database problem when running the application after I've made a change to the database from VWD ie adding a new stored proceedure, Don't know if this helps, but better you get the full picture.
I havn't read your first post.... what is the summary of what you are trying to acheive.
I have used SQL Express with VWD for a while now and have had no problems moving the database from my VWD environment to a production system. The first thing is that when using the VWD the Databases are created in the APP_Data directory and the connection string used inside the web.config file has a parameter that attaches the database at runtine to the Database Engine it also configures the security so only the Windows Account that has attached the Database can access the Database Files (User Instances). When moving to production systems or attaching it to an existing Engine the first step I would do is to take all of the files (MDF and LDF) for the database and put them in another directory, away from the ASP_Data directory in the root of your web application. Normally this would be the default data directories for the Database server that you are going to use. Next inside the management studio attach the database files and set up the security for the user that you are going to be using.
In my case here I normally create an application pool for the Web site to use in IIS and set the app pool to run under a user, I would then configure this user to have the access needed to run the database system ie Stored Procs and Table access. The last stage is to change the connection string in the web.config file for your application to reflect the new server. Making sure that the attach Database parameters are removed.
Hope this helps.
