IHi

I am on page 532, chapter 12 Deployment, trying to import data to remote SQL server. Now I have 2 problems:

1)The book instructs to disabling constraints on the local database first. Do I have to script all the table manually?

2)Then how am I going to put back the constraints on those constrain disabled tables?

Greatly appreciate your reply!

More on the above problems
3) I only have: Detach, Shrink, Backup, Restore, Generate script on the context menu after clicking "Task"

Is it because I am using the SSMS express version?



Thank you

He showed you an example of a SQL Script to do this. I made 2 scripts: one to enable constraints and one to disable constraints. It may help you to see how I actually kick this off first. I use a batch file on my PC to migrate data from my local database to my remote database (I'm not sure if the formatting of this message will wrap the lines but I hope you can see where each line should end):
-----
c:
cd "\scripts"
sqlcmd -S 111.222.333.444 -d DBname -U DBUserName -P DBUserPwd -i DisableConstraints.sql
sqlcmd -S 111.222.333.444 -d DBname -U DBUserName -P DBUserPwd -i EmptyData.sql
dtexec /f "tbhv3.dtsx"
sqlcmd -S 111.222.333.444 -d DBname -U DBUserName -P DBUserPwd -i EnableConstraints.sql
sqlcmd -S 111.222.333.444 -d DBname -U DBUserName -P DBUserPwd -Q "select count(*) from tbh_forums"
pause
----
This should be easy to understand: disable constraints on target DB, empty tables on target DB, run the DTS package made by SSMS, enable constraints on target DB, and get a row count of records in the tbh_forums table. The row count just tells me if this worked or not.

I'll post the SQL scripts in separate messages - keep reading.

DisableConstraints.sql:
----
ALTER TABLE aspnet_Applications NOCHECK CONSTRAINT ALL
ALTER TABLE aspnet_Membership NOCHECK CONSTRAINT ALL
ALTER TABLE aspnet_Paths NOCHECK CONSTRAINT ALL
ALTER TABLE aspnet_PersonalizationAllUsers NOCHECK CONSTRAINT ALL
ALTER TABLE aspnet_PersonalizationPerUser NOCHECK CONSTRAINT ALL
ALTER TABLE aspnet_Profile NOCHECK CONSTRAINT ALL
ALTER TABLE aspnet_Roles NOCHECK CONSTRAINT ALL
ALTER TABLE aspnet_SchemaVersions NOCHECK CONSTRAINT ALL
ALTER TABLE aspnet_Users NOCHECK CONSTRAINT ALL
ALTER TABLE aspnet_UsersInRoles NOCHECK CONSTRAINT ALL
ALTER TABLE aspnet_WebEvent_Events NOCHECK CONSTRAINT ALL
ALTER TABLE sysdiagrams NOCHECK CONSTRAINT ALL
ALTER TABLE tbh_Articles NOCHECK CONSTRAINT ALL
ALTER TABLE tbh_Categories NOCHECK CONSTRAINT ALL
ALTER TABLE tbh_Comments NOCHECK CONSTRAINT ALL
ALTER TABLE tbh_Departments NOCHECK CONSTRAINT ALL
ALTER TABLE tbh_Forums NOCHECK CONSTRAINT ALL
ALTER TABLE tbh_Newsletters NOCHECK CONSTRAINT ALL
ALTER TABLE tbh_OrderItems NOCHECK CONSTRAINT ALL
ALTER TABLE tbh_Orders NOCHECK CONSTRAINT ALL
ALTER TABLE tbh_OrderStatuses NOCHECK CONSTRAINT ALL
ALTER TABLE tbh_PollOptions NOCHECK CONSTRAINT ALL
ALTER TABLE tbh_Polls NOCHECK CONSTRAINT ALL
ALTER TABLE tbh_Posts NOCHECK CONSTRAINT ALL
ALTER TABLE tbh_Products NOCHECK CONSTRAINT ALL
ALTER TABLE tbh_ShippingMethods NOCHECK CONSTRAINT ALL

EmptyData.sql:
---
delete from aspnet_Applications
delete from aspnet_Membership
delete from aspnet_Paths
delete from aspnet_PersonalizationAllUsers
delete from aspnet_PersonalizationPerUser
delete from aspnet_Profile
delete from aspnet_Roles
delete from aspnet_SchemaVersions
delete from aspnet_Users
delete from aspnet_UsersInRoles
delete from aspnet_WebEvent_Events
delete from sysdiagrams
delete from tbh_Articles
delete from tbh_Categories
delete from tbh_Comments
delete from tbh_Departments
delete from tbh_Forums
delete from tbh_Newsletters
delete from tbh_OrderItems
delete from tbh_Orders
delete from tbh_OrderStatuses
delete from tbh_PollOptions
delete from tbh_Polls
delete from tbh_Posts
delete from tbh_Products
delete from tbh_ShippingMethods
go

EnableConstraints.sql:
---
ALTER TABLE aspnet_Applications CHECK CONSTRAINT ALL
ALTER TABLE aspnet_Membership CHECK CONSTRAINT ALL
ALTER TABLE aspnet_Paths CHECK CONSTRAINT ALL
ALTER TABLE aspnet_PersonalizationAllUsers CHECK CONSTRAINT ALL
ALTER TABLE aspnet_PersonalizationPerUser CHECK CONSTRAINT ALL
ALTER TABLE aspnet_Profile CHECK CONSTRAINT ALL
ALTER TABLE aspnet_Roles CHECK CONSTRAINT ALL
ALTER TABLE aspnet_SchemaVersions CHECK CONSTRAINT ALL
ALTER TABLE aspnet_Users CHECK CONSTRAINT ALL
ALTER TABLE aspnet_UsersInRoles CHECK CONSTRAINT ALL
ALTER TABLE aspnet_WebEvent_Events CHECK CONSTRAINT ALL
ALTER TABLE sysdiagrams CHECK CONSTRAINT ALL
ALTER TABLE tbh_Articles CHECK CONSTRAINT ALL
ALTER TABLE tbh_Categories CHECK CONSTRAINT ALL
ALTER TABLE tbh_Comments CHECK CONSTRAINT ALL
ALTER TABLE tbh_Departments CHECK CONSTRAINT ALL
ALTER TABLE tbh_Forums CHECK CONSTRAINT ALL
ALTER TABLE tbh_Newsletters CHECK CONSTRAINT ALL
ALTER TABLE tbh_OrderItems CHECK CONSTRAINT ALL
ALTER TABLE tbh_Orders CHECK CONSTRAINT ALL
ALTER TABLE tbh_OrderStatuses CHECK CONSTRAINT ALL
ALTER TABLE tbh_PollOptions CHECK CONSTRAINT ALL
ALTER TABLE tbh_Polls CHECK CONSTRAINT ALL
ALTER TABLE tbh_Posts CHECK CONSTRAINT ALL
ALTER TABLE tbh_Products CHECK CONSTRAINT ALL
ALTER TABLE tbh_ShippingMethods CHECK CONSTRAINT ALL

This worked fine, except for one small problem that was a bug in my version of SSMS. There's a screen where you have to Edit Column mappings for each table. In that screen you're supposed to check the little box that says "Enable Identity Insert", and this has to done separately for every table. There's also a "Delete rows in destination table" checkbox here, but I use a script for that.

The problem I had (which I hope has been fixed in SSMS, but I haven't checked the latest version) is that the Identity Insert doesn't work. Even after you go to all the trouble to check this box for every table (which is a pain because you can't do it all from one screen), it still renumbered all my identities in my target tables - YUCK! The reason this is a problem is that it breaks the refrrential integrity - the idenities are foreign keys in other tables, and when SSMS renumbers the identities they will no longer match the tables that have them as foreign keys.

Thank you for your answer.

After deleting and enable back the constrain.
In my case, there 's still a problem with the idenity, even though I had enabled the idenity for every table.

Anyway, I'll figure it out.
Thank you.

I just noticed that Marco put some scripts like these in the app_data folder along with the DB.

SMO (SQL Management Objects) are used by SSMS to perform the data import function, so this isn't workable with the Express version of SSMS.

However, you're probably lucky that you can't use it, because of the nasty identity bug. Even worse, if you inspect the record counts carefully you'll see that some of the records didn't get copied, and there are NO ERRORS during the procedure! There's nothing worse that a database tool that silently swallows records!

I wrote my own SMO program, and thankfully I tested it inside a VMware session because it somehow corrupted by SQL Server and I couldn't log into it anymore. Luckily I made a snapshot backup of the VM session and I was back in business soon.

I also downloaded SP1 of SQL 2005, but it didn't help with these problems.

I'll write a reliable table copy program and post it for people to download. Thankfully, most of .NET isn't this poor! A simple DataAdapter and DataTable design should work fine for our purposes.

I made some progress on this. It turns out that the cascading deletes/updates are causing the lost records. This occurs when I fix the identities, so the table copy is not the source of the lost record problem (but it is the source of the identity problem). Maybe I can disable the cascading until after I fix the identities.

Now's the time to ask me if I'm having fun yet!