Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0
This is the forum to discuss the Wrox book ASP.NET 2.0 Website Programming: Problem - Design - Solution by Marco Bellinaso; ISBN: 9780764584640
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old May 23rd, 2007, 11:23 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Atlanta, Georgia, USA.
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default Database Deployment to Shared Servers; SQL Script

Many people use shared-hosting sites and they have great difficulty populating a remote database if they arenít in the SA role. I will explain the newest solution from Microsoft shortly, but first I want to discuss the problems with the other options. First, VS cannot generate SQL scripts for data. Second, the free Express version of SQL Server Management Studio doesn't have the import feature to suck data into a target DB. Third, even if you have a full version of SQL Server, the SQL Server Management Studio's Import feature has a nasty bug that prevents it from importing the identity values correctly. You can make your own SSIS package to do the job, but it's not nearly as easy as the import feature.

To work-around these problems I wrote a small console program in C# that can connect to the local and remote DBís and it can copy the records over correctly without corrupting the identities:
http://www.EricEngler.com/downloads/...bleData.cs.txt

Although this program seems to work fine, and many people have used it, it would be far more convenient if we had a tool that could create a huge SQL script that could create the objects AND populate the data tables. There are several commercial tools that can do this, but theyíre expensive and they donít always work as advertised.

Thankfully, Microsoft has now developed a free solution for us that will create a huge SQL script from an existing database! But as you might expect, there are a couple small wrinkles. Iíll explain more in the next message of this thread.
  #2 (permalink)  
Old May 23rd, 2007, 11:32 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Atlanta, Georgia, USA.
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Due to the problems people have in copying data from one server to another, many times the best option is just to create a huge SQL script that contains the data in the source DB, and then you can load it in the target DB. This has many advantages: it can be run on a target DB using any kind of SQL provision offered by a hosting company, it doesn't require a full edition of SQL Server, it allows you to inspect and possibly modify the script first, etc.

The new SQL Server 2005 Database Publishing Wizard can be used to generate this kind of script that can create the objects (procedures/UDFs, etc) and also populate the data. By default, the script you create will first drop all of the existing objects in the target DB, which is usually desirable because you can re-create the whole DB this way after making changes to the source DB.

This article explains the Wizard a bit Ė please read this:
http://aspnet.4guysfromrolla.com/articles/050907-1.aspx

To start the wizard, you can right click on a connection object that's tied to a form in VS 2005, or from a right-click on a database in the server explorer in VS, or you can run it from a command line:

  C:
  CD \Program Files\Microsoft SQL Server\90\Tools\Publishing
  sqlpubwiz

It needs a Connection string in most cases since it can't seem to find your SQL Server even if you have the browser service running (maybe this problem was unique to my system). This is a common connection string used with SQL Server Express - note that the DB name isn't needed here because it will prompt you to select a DB next:

  Data Source=.\SqlExpress;Integrated Security=SSPI;

I don't know if it can connect to a file using an appropriate connection string with a full path to a DBF in your app_data folder (using "AttachDBFilename=" in the connection string), or if you first have to officially connect the DBF to your SQL Server first. In my case, I had already attached my DB to my SQL Server so it showed up as a Database
when viewed in SQL Server Management Studio Express.

  exec sp_attach_db @dbname=N'TheBeerHouse',
       @filename1=N'C:\<PATH>\TheBeerHouse.mdf',
       @filename2=N'C:\<PATH>\TheBeerHouse_Log.ldf'

If you change from an auto-connect in app_data to a connect-by-name using the above stored procedure call, you may have to change your connection string in Web.config also:

 <add name="LocalSqlServer"
  connectionString="Data Source=.\SQLEXPRESS;Integrated Security=True;Initial Catalog=TheBeerHouse;"
  providerName="System.Data.SqlClient"/>

My next message in this thread continues this talk and explains some work-arounds.
  #3 (permalink)  
Old May 23rd, 2007, 11:41 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Atlanta, Georgia, USA.
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

As good as the Database Publishing Wizard is, it has some limitations. It can't handle procedures or UDFs set as ExecuteAsUser (the offending clause in SQL is: WITH EXECUTE AS 'dbo'), and these objects are often found in ASP.NET 2.0 databases that are used with the VS 2005 Diagramming support that lets you make changes to DB objects visually. If youíre willing to give up the diagramming support from the source DB, you can drop the sysdiagrams table, and the related objects from your source DB before you run the wizard:

  sysdiagrams (table)
  fn_diagramobjects (Functions, Scalar Valued)
  sp_alterdiagram (Stored Procedure)
  sp_creatediagram (Stored Procedure)
  sp_dropdiagram (Stored Procedure)
  sp_helpdiagramdefinition (Stored Procedure)
  sp_helpdiagrams (Stored Procedure)
  sp_renamediagrams (Stored Procedure)
  sp_upgraddiagrams (Stored Procedure)


Before creating a SQL script from a source DB you should also delete some of the old records you donít really want to copy. You should empty the aspnet_WebEvent_Events table first, and you may want to remove your anonymous users, and perform other maintenance like that first.

delete from aspnet_WebEvent_Events
go
delete from aspnet_profile
where userid in
 (select userid from aspnet_users where isanonymous = 1)
go
delete from aspnet_users where isanonymous = 1
go

IMPORTANT! Make sure that you have deleted any testing administrator accounts before you deploy to a production server (such as the common admin/admin account). You must have a good admin account with a known and somewhat secure password because the SQL generated by the publishing wizard will delete the entire existing target DB and re-create it using only the data present in the source DB at the time this script was created.

Please try this Wizard - I'm sure you'll like it.
http://www.microsoft.com/downloads/d...displaylang=en

Eric

  #4 (permalink)  
Old May 28th, 2007, 04:40 PM
Authorized User
 
Join Date: May 2007
Location: Oslo, , Norway.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

I would like to try this method, as it appears to be the only alternative when using the wizard. It is best to have all SQL Script that populate tables

What is the consequences of droping the sysdiagrams table and other records on the system? Will it be possible to build it afterward? Does it matter?

Thanks again for valuable advices :)


<<

If youíre willing to give up the diagramming support from the source DB, you can drop the sysdiagrams table, and the related objects from your source DB before you run the wizard:

  sysdiagrams (table)
  fn_diagramobjects (Functions, Scalar Valued)
  sp_alterdiagram (Stored Procedure)
  sp_creatediagram (Stored Procedure)
  sp_dropdiagram (Stored Procedure)
  sp_helpdiagramdefinition (Stored Procedure)
  sp_helpdiagrams (Stored Procedure)
  sp_renamediagrams (Stored Procedure)
  sp_upgraddiagrams (Stored Procedure)

>>




  #5 (permalink)  
Old May 31st, 2007, 08:50 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Atlanta, Georgia, USA.
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I never use the diagram method of modifying tables, so it wouldn't hurt me to lose this, but you can copy the DB and do some testing. It's pretty easy to make a local copy of the whole DB (you can just copy the MDF if you want). I'm guessing it will just recreate these objects the next time you create a DB diagram as explained in the book.

Eric

  #6 (permalink)  
Old February 10th, 2008, 08:17 AM
Registered User
 
Join Date: Jan 2008
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to vegaliming
Default

hi£¨englere £¨you are so hard working~~~~

be careful for your health.

so THX U for your help~~~~~

vega lee

  #7 (permalink)  
Old May 17th, 2008, 08:00 AM
Authorized User
 
Join Date: Jun 2006
Location: Lahore, , Pakistan.
Posts: 46
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,
What do I need to do if I want to change the name of the database from "ASPNETDB" to something else like "MyDB" if I'm using ASP.NET's Membership and my other tables also exist in the same db of ASPNETDB ?
(I'm asking this for the deployment time on shared server)

Thanks.



 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Script to populate TBH database motemape BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 May 25th, 2007 05:42 AM
sql script that gets the 2 different database vaneza SQL Server 2000 1 August 3rd, 2005 03:55 AM
Different Servers - Same database itHighway Classic ASP Basics 1 April 19th, 2005 04:27 PM
database copy acorss servers help needed thebmwz4 SQL Server 2000 3 November 25th, 2004 02:49 AM
sql server database backup by script mateenmohd SQL Server 2000 4 July 6th, 2004 01:32 PM



All times are GMT -4. The time now is 12:50 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.