View Single Post
  #2 (permalink)  
Old May 23rd, 2007, 10:32 PM
englere englere is offline
Friend of Wrox
Join Date: Jun 2003
Location: Atlanta, Georgia, USA.
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts

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:

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:

  CD \Program Files\Microsoft SQL Server\90\Tools\Publishing

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',

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;"

My next message in this thread continues this talk and explains some work-arounds.