Ch12 P406 & 423

ken evans · October 19th, 2010, 03:29 PM

Help!

I have spent most of the day struggling with the SQL Server connection.
Amongst other things, I have uninstalled and re-installed SQL Server (2008 R2 Express) - takes ages!

All that I am looking for is a reliable and foolproof way to link a new ASP.NET 4 website to an existing database and then to develop the website against the database.

Now I know how to design and create databases and then work with them in SQL Server Management Studio. (been doing that for years)

So what I'm having trouble with is linkiing a new website to an existing database.

Lets start with the P 406 Try-it-out
Step 4: When I doublle click I get the error message:
"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user interface."

In addition the Database Explorer appears and in it is:
Data Connections> PlanetWrox.mdf

Then I right-click on "PlanetWrox.mdf" and choose "modify connections" in the popup dialog box.

Then, in the "Change Data Source" dialog box I change the Data source
From: Microsoft SQL Server Database File (SqlClient)
To: Microsoft SQL Server

The next dialog box is called "Modify Connection"
In the "Server Name" textbox I click on the down arrow at the right.
After 20 seconds or so I see the text "D1525\SQLEXPRESS"
(D1525 is my computer name)

I choose the radio button next to "Use Windows Authentication".
(Because that is how I set it up when I installed SQL Server)

If I already had a database, then I would "Select or enter a database name" [Note: This actually works with one of my own databases but I don't know how to get the database to appear in the App_Data folder - But do I need this?]
(This shows the databases that are registered with the SQL Server .
Note that the "master, model msdb and temp" options are system databases and not to be touched.)

Since this is a new database, I choose "Attach a database file" then click the browse button and go to C:\BegASPNET\DatabaseTest\App_Data.
Then highlight the PlanetWrox.mdf db that is in this folder is in this folder.(From Step 3) then click "open"

BUT when I click on "Test Connection" or "OK" I get a complicated error message that reads: "Unable to open the physical file......Operating System Error 5 ....

What next?

Ken

PS: Here is some data that I hope will be helpful:

1. SQL server version:
SQL Server 2008 R2

2. SQL Instance:
D1525\SQLEXPRESS

3. What happens when you create a brand new web site, add App_Data and a SQL Server database and open that. Does that work?
* Same problem.

4. Are you trying this out with your own database, or with one from the download?
* Both

5. Can you log in using SQL Server Management Studio?
* Yes

Imar · October 19th, 2010, 03:51 PM

Hi Ken

Looks like you already saw this post: Ch12 pg 406-407 (Try it Out)access db within VWD but did you try emptying the folder that is mentioned there and restart your machine?

Also, looks like you're mixing attached databases and the App_Data folder. Here's what I would try instead:

1. Use SQL Server Management Studio and create a new database. SQL Server will store it in ts default data folder (not App_Data of your site).

2. Use VWD's Database Panel and connect to the SQL Server instance and choose your database.

Eventually, using a local database in App_Data is much easier during development than an attached database, so I hope you can get that to work. Sounds like a SQL Server error to me (see thread I linked to) but there could be many other reasons: http://www.google.com/search?hl=en&s...l=&oq=&gs_rfai=

Hope this helps,

Imar

ken evans · October 19th, 2010, 04:52 PM

"Looks like you already saw this post: [URL]http://p2p.wrox.com/book-beginning-asp-net-4-c-vb/80620-ch12-pg-406-407-try-out-access-db-within-vwd.html

Yes , I spent a lot of time rummaging around.

but did you try emptying the folder that is mentioned there and restart your machine?

Well - I couldn't find the folder...

Also, looks like you're mixing attached databases and the App_Data folder.
Yes - I sort of realised that

Here's what I would try instead:

1. Use SQL Server Management Studio and create a new database. SQL Server will store it in ts default data folder (not App_Data of your site).

2. Use VWD's Database Panel and connect to the SQL Server instance and choose your database.

When I do that, the database shows up immediatley in the Database Explorer and I can browse the tables OK.

Eventually, using a local database in App_Data is much easier during development than an attached database, so I hope you can get that to work.

Well - my intention is to use a "proper" SQL Server database. (SQL Server 2008 or 2008 R2)

What I don't understand, is:

(a) how to code against a "real" database (not an mdf)
(b) do I need the "App_Data" folder if I'm coding against a "proper" database?
(c) Is it possible to get a "proper" database to appear in the App_Data folder?
(d) If so, what are the benefits?

Sounds like a SQL Server error to me (see thread I linked to) but there could be many other reasons.

Yup - Where is Sherlock?

Ken

Imar · October 20th, 2010, 02:58 AM

Quote:

Well - I couldn't find the folder...

Try turning on the display of hidden and system folders in Windows Explorer and then search for folders called SQLEXPRESS.

Quote:

When I do that, the database shows up immediatley in the Database Explorer and I can browse the tables OK.

Well, isn't that good news and exactly what you want? It means you're working against SQL Server with an attached database, without attaching it on the fly. This is as close as it gets to working with a "real" SQL Server.

Quote:

Well - my intention is to use a "proper" SQL Server database. (SQL Server 2008 or 2008 R2)

SQL Express is pretty much just as "proper" as the others; for a large part it just comes down to licensing. For standard database stuff (tables, stored procedures, views, CRUD operations and so on), the two are identical.

Quote:

(a) how to code against a "real" database (not an mdf)

That's what you're doing when you create a new database in SQL Server (Express or not) and then connect to it using VWD.

Quote:

(b) do I need the "App_Data" folder if I'm coding against a "proper" database?

In both cases, you have a "proper" database. The only difference between one in App_Data and one attached to SQL Server is the fact that when you use the App_Data folder, the database is attached for you on the fly. In both cases, an MDF file is used to store the data.

Quote:

(c) Is it possible to get a "proper" database to appear in the App_Data folder?

You probably don't want that as you'll run into conflicts. Databases located in App_Data are attached on the fly when you double-click them in VWD. When they are already attached to SQL Server, you get file blocking issues. My advice is to use App_Data if you want to quickly hook up databases using VWD in a web application. If you want to use a "proper" setup (quotes are yours; IMO, both options are proper), move your MDF to a different location (such as C:\Databases), attach it to SQL Server (Express or a commercial version; that doesn't matter) and connect to it from within VWD.

Quote:

(d) If so, what are the benefits?

None. App_Data is for databases attached on the fly; for databases you want to explicitly attach to SQL Server, choose a different location.

Hope this helps,

Imar

ken evans · October 20th, 2010, 03:54 AM

Hi Imar,
Thanks - that is really very very helfpul.
It has cleared up my confusion about:
"To use App_Data or Not to use App_Data? - That is the Question."

Cheers
Ken

ken evans · October 20th, 2010, 05:45 AM

Ref: Ch12 P406 & 423 - Can't see SQL Server

After my problems with accessing the PlanetWrox.mdf from the App_Data folder, I thought that I would be "smart"

and attach PlanetWrox.mdf to Sql Server and work from there.

So I went into SQL Server Management Studio, right clicked "databases" and chose "Attach".
In the "Attach Databases" dialog box I:
* Clicked on "Add" button
* Navigated to the downloaded PlanetWrox.mdf file - then clicked "OK".

On the next dialog box, I clicked "OK" and received the following error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------
An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.
------------------------------

After clicking on the hyperlink, I got the next error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'D1525\SQLEXPRESS'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Unable to open the physical file "C:\BegASPNET\Resources\Chapter 12\PlanetWrox.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)". (Microsoft SQL Server, Error: 5120)

For help, click: http://go.microsoft.com/fwlink?ProdN...0&LinkId=20476
------------------------------

Now I noticed that in the "Attach Databases" dialog box, in the "Planet Wrox" database details, there are two file names. The second one is "PlanetWrox_Log.LDF".
This file has a "Current File Path" of " D:\Imar Install\2008\PlanetWrox_log.LDF" and a message "Not Found".

I'm wondering if this is the cause of the failure?

Or is there some other way to attach the .mdf file within SQL Server?

Thanks
Ken

Imar · October 20th, 2010, 05:58 AM

Hi ken,

Here's what I would do:

1. Copy the PlanetWrox.mdf file to a new location such as C:\Databases. It's a lot easier to manage your DB files when you centralize them in a single location

2. Attach the database as you described, then select the log file (the one that generates the error) and click Remove or Delete (not sure which one).

When you then attach the database, SQL Server creates a log file for you.

Cheers,

Imar

ken evans · October 20th, 2010, 06:08 AM

OK, I tried that.
Unfortunately, I get exactly the same error messages.
And the link at: For help, click: http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476
just says:

"We're sorry
There is no additional information about this issue in the Error and Event Log Messages or Knowledge Base databases at this time. You can use the links in the Support area to determine whether any additional information might be available elsewhere."

Imar · October 20th, 2010, 06:14 AM

But were you able to remove the log file from the list? Did it mention "Not found" in the Message column?

Also, what permissions did you assign to C:\Databases?

Finally, have you read Chapter 19 and Appendix B? They deal with stuff like this.

Imar

ken evans · October 20th, 2010, 06:25 AM

"were you able to remove the log file from the list?"
Yes -just clicked on the "Remove" button

"Also, what permissions did you assign to C:\Databases?"

And that was the problem!!
Such a lot of fiddly details....

Anyway, the PlanetWrox database is now attached in SQL Server Management Studio. Hurray!

Thanks very much for sticking with me on this and for your super-fast responses.

Best wishes
Ken