Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Using Import/Export Wizard vs Generate Scripts

Author  Topic 

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2010-02-26 : 17:49:55
Hi,

I have a development SQL Server and a production SQL Server both with SQL Server 2008. I have noticed that when I use the Import/Export wizard to copy data from my development server to my production server I get "Foreign Key Constraints" errors. I do choose to enable identity insert in the mappings during the wizard.

However, if I ude the "Generate Scripts..." method (right-click --> Task --> Generate Scripts) and I generate, say only the data, and then run the script on the production server it works without problems.

Are there other settings I'm not properly setting in the wizard? If I use the wizard do I have to remove the constraints and then add them back? ...ugh...I hope not!

Thanks for your help!

- RoLY roLLs

http://www.buysellmotors.com
http://www.tikiloveroom.com
http://www.rolyrolls.com
http://iphone.rolyrolls.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-26 : 23:44:37
Why are you moving data from development to production? usually its other way around. If you've foreign key relationships in your table you need to make sure you first transfer data from parent table before going with data transfer of your table which references it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2010-02-27 : 00:13:30
I'm moving data like the initial user (admin) and other initial data required.

I'm aware of having to move the parent data before the child data, but my question is wouldn't the wizard be able to do this? I mean, I remember SQL 2000's wizard worked just fine. I'm currently importing one table at a time (parents first, then children and then their children, etc). This seems a bit like retogression if the wizard doesn't do do it itself. And like I said, the Generate scripts DOES script the parent table data first and then it's children.

- RoLY roLLs

http://www.buysellmotors.com
http://www.tikiloveroom.com
http://www.rolyrolls.com
http://iphone.rolyrolls.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-27 : 00:29:48
and you're sure that you've set keep identity property for parent tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2010-02-27 : 00:40:33
yup. I just found an interesting problem. For the heck of it, I tried Using the import wizard for just one table, a table name User. This table has one primary key, UserID. It has other fields, none of which are foreign keys. Upon clicking finish, I get the following message:

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Error)
Messages
* Error 0xc002f210: Preparation SQL Task 1: Executing the query "TRUNCATE TABLE [dbo].[ipa_User]
" failed with the following error: "Cannot truncate table 'dbo.ipa_User' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)


- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Success)

- Copying to [dbo].[ipa_User] (Stopped)

- Post-execute (Stopped)


here is the table script:


/****** Object:  Table [dbo].[ipa_User]    Script Date: 02/27/2010 00:39:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ipa_User](
	[ipa_UserID] [int] IDENTITY(1,1) NOT NULL,
	[ipa_UserUID] [uniqueidentifier] NOT NULL,
	[ipa_UserName] [varchar](255) NOT NULL,
	[ipa_UserPassword] [varchar](16) NULL,
	[ipa_UserEmail] [varchar](255) NULL,
	[ipa_UserEmailVerify] [bit] NOT NULL,
	[ipa_UserEmailVerifyCode] [uniqueidentifier] NULL,
	[ipa_UserIgnore] [bit] NOT NULL,
	[ipa_UserEnteredDate] [smalldatetime] NOT NULL,
	[ipa_UserLastModifiedDate] [smalldatetime] NOT NULL,
	[ipa_UserLastModifiedBy] [int] NULL,
 CONSTRAINT [PK_ipa_User] PRIMARY KEY CLUSTERED 
(
	[ipa_UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[ipa_User] ADD  CONSTRAINT [DF_ipa_User_ipa_UserUID]  DEFAULT (newid()) FOR [ipa_UserUID]
GO

ALTER TABLE [dbo].[ipa_User] ADD  CONSTRAINT [DF_ipa_User_ipa_UserEmailVerify_1]  DEFAULT ((0)) FOR [ipa_UserEmailVerify]
GO

ALTER TABLE [dbo].[ipa_User] ADD  CONSTRAINT [DF_ipa_User_ipa_UserEmailVerify]  DEFAULT (newid()) FOR [ipa_UserEmailVerifyCode]
GO

ALTER TABLE [dbo].[ipa_User] ADD  CONSTRAINT [DF_ipa_User_ipa_UserIgnore]  DEFAULT ((0)) FOR [ipa_UserIgnore]
GO

ALTER TABLE [dbo].[ipa_User] ADD  CONSTRAINT [DF_ipa_User_ipa_UserEntered]  DEFAULT (getdate()) FOR [ipa_UserEnteredDate]
GO

ALTER TABLE [dbo].[ipa_User] ADD  CONSTRAINT [DF_ipa_User_ipa_UserLastModified]  DEFAULT (getdate()) FOR [ipa_UserLastModifiedDate]
GO




quote:
Originally posted by visakh16

and you're sure that you've set keep identity property for parent tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




- RoLY roLLs

http://www.buysellmotors.com
http://www.tikiloveroom.com
http://www.rolyrolls.com
http://iphone.rolyrolls.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-27 : 00:48:40
thats understandable, for tables with fk constraints you can do TRUNCATE. you need to use DELETE FROM table instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2010-02-27 : 00:53:27
So the issue is where? In the wizard? Or an option I need to select/unselect?

- RoLY roLLs

http://www.buysellmotors.com
http://www.tikiloveroom.com
http://www.rolyrolls.com
http://iphone.rolyrolls.com
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-27 : 00:58:53
I only ever do it with scripts ... I feel more in control, and the scripts (over time) get things in comments like the syntax for disabling FKeys, and reactivating them, where we have (at some time found) circular relationships, resetting Identity seed, and so on.

Not the answer to your original question though ...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-27 : 01:05:11
quote:
Originally posted by RoLYroLLs

So the issue is where? In the wizard? Or an option I need to select/unselect?

- RoLY roLLs

http://www.buysellmotors.com
http://www.tikiloveroom.com
http://www.rolyrolls.com
http://iphone.rolyrolls.com

Nope its not problem with wizard.
even otherwise you cant do truncate if it has fk relationship

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2010-02-27 : 01:13:04
quote:
Originally posted by Kristen

I only ever do it with scripts ... I feel more in control, and the scripts (over time) get things in comments like the syntax for disabling FKeys, and reactivating them, where we have (at some time found) circular relationships, resetting Identity seed, and so on.

Not the answer to your original question though ...


yeah I feel more inclined to do it with scripts. except there is one table with about 300k records, and we always get a not enough memory error. the wizard doesn't complain probably since it copies by batches.

I have successfully scripted a table's data into a file, but have not been able to open it, so that I can run it. I also haven't found how to run the file without opening it. Not that I need to know it for what I'm doing now, but the knowledge will come in handy one day.

Thanks!

- RoLY roLLs

http://www.buysellmotors.com
http://www.tikiloveroom.com
http://www.rolyrolls.com
http://iphone.rolyrolls.com
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2010-02-27 : 01:15:38
quote:
Originally posted by visakh16


Nope its not problem with wizard.
even otherwise you cant do truncate if it has fk relationship


Right...I'm still not sure how to fix this issue, if I wanted to use the wizard though. As my previous post, I'm not going to use the wizard for this, but what should I do or look for, etc. if I have this issue arise again AND prefer to use the wizard?

Thanks for your replies.

- RoLY roLLs

http://www.buysellmotors.com
http://www.tikiloveroom.com
http://www.rolyrolls.com
http://iphone.rolyrolls.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-27 : 01:23:12
quote:
Originally posted by RoLYroLLs

quote:
Originally posted by visakh16


Nope its not problem with wizard.
even otherwise you cant do truncate if it has fk relationship


Right...I'm still not sure how to fix this issue, if I wanted to use the wizard though. As my previous post, I'm not going to use the wizard for this, but what should I do or look for, etc. if I have this issue arise again AND prefer to use the wizard?

Thanks for your replies.

- RoLY roLLs

http://www.buysellmotors.com
http://www.tikiloveroom.com
http://www.rolyrolls.com
http://iphone.rolyrolls.com

why cant you use DELETE table option?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-27 : 02:41:42
If you are going from DEV to PRODUCTION why not drop the Fkeys, truncate the tables, push the data up (using Wizard if you like, for the benefit of its batching methods), and the recreate the FKeys?
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

255 Posts
Posted - 2010-02-27 : 03:01:15
I guess I was just trying to avoid *extra* work. FKeys and DB role permission are set and I didn't want to have to undo them to add data to later redo them.

- RoLY roLLs

http://www.buysellmotors.com
http://www.tikiloveroom.com
http://www.rolyrolls.com
http://iphone.rolyrolls.com
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-27 : 05:22:16
OK, Plan C then!!

Export the files using BCP in Native Format AND Order By Clustered PK

... copy them to target server ...

Truncate / Delete target tables (there is a script on SQL Team that will work out which to do, and in what order)

Import in Parent/Child order using BCP with the "Ordered" hint (you can specify batch size too, although using the "Ordered" hint may be enough for 300K rows)

(Not sure if this is a one-off or something you do, for example, whenever you install you application for a client. If "often-ish" I think this route would be the best because you can lock-down the exported files, for a given version, and the script approach is easily repeatable - whereas the Wizard approach is "Hope I tick exactly the same boxes as last time"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site