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.
| Author |
Topic |
|
Kimi86
Yak Posting Veteran
79 Posts |
 Posted - 2014-07-24 : 15:48:46
|
| i am trying to copy a table with arounf 5 million rows to another server . I am doing this through SQL server import export utility. The tranfer goes on fine for a few minitues but then the pkg fails with below errors"Communication link failureTCP Provider: An existing connection was forcibly closed by the remote host.DTS_E_INDUCEDTRANSFORMFAILUREONERRORAlso the destination tables remain empty after the error.This is just a one time updateI have also tried to copy the table first into a csv file and then import the csv file into the destination but i gain get similar errors..Is there a way to commit transferred rows while importing through import export tool? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-24 : 15:59:52
|
| I think this is a network glitch between the two servers. The utilities are losing the connection to the destination server.You could try to do it in batches instead that way the data gets saved and doesn't rollback the entire thing.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
Kimi86
Yak Posting Veteran
79 Posts |
Posted - 2014-07-24 : 16:15:38
|
| Thats what I want to do.. I want to do it in batches and commit rows when a batch gets over.. how to do this using import export wizard |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-24 : 16:16:48
|
| I'm not sure that you can with the wizard. You can with bcp (-F and -L switches/parameters) or an SSIS package.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-07-24 : 16:45:15
|
| One thought that comes to mind is to use the "Write a query to specify data to transfer" in the Import/export wizard. It is on the "Specify Table Copy or Query" dialog of the wizard. There, you would write a query that selects only a small number of rows. This can work well if you have a good cluster key that can be used to specify the where clause of the select query. |
|
|
|
Kimi86
Yak Posting Veteran
79 Posts |
Posted - 2014-07-24 : 17:02:36
|
| Yes I am doing that now but everytime a pkg fails I have to check the rows added and change the pkg.. Its pretty time consuming |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-07-24 : 17:27:11
|
| Create a new database on the source server, copy the table into that database, back it up, copy the back up to a USB stick or some such, bring it over to the destination server, restore the database, and copy the data to your target database. |
|
|
|
|
|
|
import export tool for 4 million rows