Error when trying to create SQL database for VWD website

[Debbie]

I am trying to deploy my website, which is basically a modification of the VWD personal web site. I have read the excellent article "Beginner's Guide to uploading a WVD 2005 Express website" and tried to follow the instructions but my website isn't working.

What I did was
1) created a SQL database using Helm and I called this "personal".
2) Using the Helm database manager I created a user to manage the database. I called this user "Debbie"
3) I then used myLittleAdmin for SQL Server 2005 to populate the database. Using the Tools I ran a query copied from personal-add.sql (supplied by MS). This did create the tables Album and Photos and stored procedures, but the tables seem to be empty and the stored procedures are prefixed by the user name.
4) So when I tried to run the website I now get the error
" Server Error in '/' Applicaton
Invalid object name 'SiteContent'"

5) I am connecting to the database with the following in web.config

<add name="personal" connectionString="
Data Source=ALUMINIUM;
Initial Catalog=personal.mdf;
Persist Security Info=True;
User ID=Debbie;
Password=*******;
Trusted_Connection=False"
providerName="System.Data.SqlClient" />
<remove name="LocalSqlServer" />

<add name="LocalSqlServer" connectionString="
Data Source=ALUMINIUM;
Initial Catalog=personal.mdf;
Persist Security Info=True;
User ID=Debbie;
Password=*******;
Trusted_Connection=False" providerName="System.Data.SqlClient" />

I'm sure this and all the other errors I've had are due to the fact that my database isn't set up correctly. But can anyone tell me what I did wrong when setting up my database?

[RFH Reseller: dv8host]

Hi Debbie,

I'm not a database developer so not completely sure, but I think it might work if you take off the .mdf after the name of the database. Whenever I setup one I only have to enter the database name (without the .mdf extension).

Hope that helps.

Chris

[Debbie]

.. as I created the database with the name "personal". I think the extension gets added on by the creating app. I've tried connecting without the ".mdf" in the xml web.config file and that produced errors too!

[RFH Reseller: Sol]

The error message you posted implies that the database should contain a 'SiteContent' table or view.

Unfortunately this is not so easy to diagnose without really having a look at things. However, in the meantime check what tables exist in the development copy of the database and those on the server (in your personal db).

[Debbie]

Two tables exist, Albums and Photos, these also appear in my development environment. Sadly though, the tables in the RF database are empty (0 rows) whereas the tables should contain 2 and 47 rows respectively. I could add the stuff by hand as I know what to add, trouble is I can't see how to do this using myLittleAdmin.

If I read the posting correctly in the Beginners Guide... I should only have had to run the script to create the tables etc and hey presto it should work. Only it doesn't.

[RFH Reseller: Sol]

OK, this narrows things down a bit, since it is quite clear that the script generated only created the database tables. Unfortunately Databases have never really been easy to work with, and I would suggest you have two options.

1). Take a backup of the local development database and email RFH support asking if they can 'restore' this on the production server. RFH may not be willing to do this / may charge a small administration fee.

2). Have a look at the 'Database Publishing Wizard'. You should be able to use this to generate a SQL script which you can execute via myLittleAdmin, however if the script is too large you will need to look at creating a simple ASPX page to execute the script (there is a sample and tutorial here). When using the DPW you should pay attention to the known issues, in particular the notes about collations.

If the resulting script does generate any errors feel free to post here and I'll see if I can help you solve them.

[RFH Customer: Ourpcman]

1). Take a backup of the local development database and email RFH support asking if they can 'restore' this on the production server. RFH may not be willing to do this / may charge a small administration fee.

I think in the FAQ's they say they do this for £15.

[RFH Reseller: Sol]

I think in the FAQ's they say they do this for £15.

I thought I had seen a fee for this somewhere but wasn't 100% sure, so thanks for clearing that up. I would add that this fee is quite reasonable since it does invollve a couple of steps and is not just as straight forward as simply restoring the database (for those curious, it would involve ensuring objcet permissions are set correctly as well as changing the paths to the files that make up the database).

[Debbie]

OK, I'll look into creating a script that works and let you know how I get on. Thanks for the information. Otherwise, it seems I have a fall back position, £15 to RFH doesn't sound like a lot after you've spent days getting no where!

[Staff: Warren Ashcroft]

Dont forget about the MyLittleBackup software (accessible via the link given in Helm on your database) - this will allow you to upload and restore database backups.