Trouble with Importing Data

Last Post 10 Apr 2007 10:43 AM by Web4Deb. 9 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
BlakeK
New Member
New Member

--
18 Jan 2007 11:08 AM
We used to have a SQL 2000 server with a production database and a development copy of that database.
Whenever we wanted some new data in the development copy, I would go to that database and choose "Import" and then follow the wizard to import the tables I wanted from the production database. Everything worked fine with this setup.
We have since moved to SQL 2005 server and for the first time today, I attempted to copy tables from our production database into a new development database.
I went to the development database and choose "Import Data" and followed the prompts for the "SQL Server Import and Export Wizard".
Everything appeared to work fine, and when the process completed, I saw that the appropriate tables had been copied to the development database.
But, upon further inspection, I have found that none of the tables in the development database have primary keys, indexes, etc.
Am I missing something I need to do in the wizard, or do I need to change any settings, in order for all the aspects of the various tables to be included, such as primary keys, indexes, foreign keys, and such?
Any help would be much appreciated.

Thanks.
BlakeK
New Member
New Member

--
19 Jan 2007 07:03 AM
The whole DB?!
That sucks. Is there no way to just copy a subset of tables, and include their primary keys, indexes, foreign keys, etc.?
Our entire DB is enormous. It takes up over 330GB of disk space, has over 1000 tables, and several tables that are over 1 billion rows.
I only need about 50 of the tables copied to the development DB for the work we need to do.
I don't have the disk space to create a full duplicate.
I can't believe that I could easily do this in 2K, but it can't be done at all in 2K5. Why would MS take away such functionality?
If anyone can help me, or point me to articles to read on how to do this, I would appreciate it.
Markus_SQL
New Member
New Member

--
19 Jan 2007 09:27 AM
Unless I am missing something the import/export wizard in SS2000 never transfered pri. keys or secondary indexes to begin with. It would only create the table and transfer the rows just like 2005 does. You need to either script out the create all and create the objects in your target and then use import/export wizard to simply tranfer the data. Or manually create the pri. keys and secondary indexes.
BlakeK
New Member
New Member

--
19 Jan 2007 10:12 AM
quote:

Originally posted by: Markus_SQL
Unless I am missing something the import/export wizard in SS2000 never transfered pri. keys or secondary indexes to begin with. It would only create the table and transfer the rows just like 2005 does. You need to either script out the create all and create the objects in your target and then use import/export wizard to simply tranfer the data. Or manually create the pri. keys and secondary indexes.


OK, bear with me while I write out exactly what I used to do on 2K and what I am doing now on 2K5.
This should help me get a grasp on the changes, and hopefully be more clear on providing you all with the information needed to evaluate my situation.

We still have our 2K machine, so I tested this to make sure it works exactly as I will describe...

SQL 2000
Enterprise Manager
Expand the "Databases" tree.
Right-Click on the destination database where I want to copy the data to.
Choose "All Tasks", then "Import Data".
This opens a window that says "Data Transformation Services Import and Export Wizard".
Click "Next".
On the next page, choose the source database information, then click "Next".
On the next page, choose the destination database information, then click "Next".
Now, this is the key page to pay attention to!
There are 3 options on this page:
- Copy tables and views from the source database
- Use a query to specify the data to transfer
- Copy objects and data between SQL Server databases

If you choose the first one, then you will be able to transfer tables and their data, but you will NOT get the creation of primary keys, indexes, etc.
We want to choose the last option (Copy objects and data), then click "Next".
On the next screen, at the top check the options to:
- Create destination objects
- Include all dependent objects
- Include extended properties
In the next section, choose:
- Copy data
- - Replace existing data
In the next section, choose:
Uncheck the "Copy All Objects" box, and then click the "Select Objects" button.
This will allow you to select individual tables.
After you choose your objects to copy, click "OK" to close that window, then click the "Next" button on the original window.
On this window, choose "Run Immediately", then click "Next".
On the final window, choose "Finish".
This will result in the tables and their data being copied to the destination database, INCLUDING all their primary keys, indexes, etc.

Now, this is what I am doing in 2K5, and the problems that result...

SQL 2005
Management Studio
Expand the "Databases" tree.
Right-Click on the destination database where I want to copy the data to.
Choose "Tasks", then "Import Data".
This opens a window that says "SQL Server Import and Export Wizard".
Click "Next".
On the next page, choose the source database information, then click "Next".
On the next page, choose the destination database information, then click "Next".
Now, this is the key page to pay attention to!
There are only 2 options on this page:
- Copy data from one or more tables or views
- Write a query to specify the data to transfer

Unlike this screen under SQL2K DTS, there is no 3rd option to "copy data and objects".
If I choose the first option, then just like the first option in 2K, you will only get the tables and their data. You will not get the keys, indexes, etc.

Since there is no 3rd option, like there was in 2K, I need to know if there is another way the same goal can be accomplished easily, or whether there is an update to 2K5 that I need to download in order to have this option?
We are running SQL 2005 Standard Edition (64-bit) version - 9.00.1399.06

I hope that this provides a better explanation of what I was able
SQLUSA
New Member
New Member

--
23 Jan 2007 03:41 PM
You have to build an SSIS package.

Use Transfer SQL Server Object Task.

It transfers object and data.

Kalman Toth
SQLUSA: http://www.sqlusa.com
BlakeK
New Member
New Member

--
08 Feb 2007 12:36 PM
OK, here what I have tried...

I opened the SQL Server Business Intelligence Development Studio
I then created a new project.
On the package design screen, I placed a "Transfer SQL Server Objects" object from the ToolBox.
I double-clicked on this object, and it opened the "Task Editor" window where I defined the following:
Under "Connection"
- My source connection
- My source database
- My destination connection
- My destination database

Under "Destination"
- DropObjectsFirst = True
- IncludeExtendedProperties = True
- CopyData = True
- ExistingData = Replace
- CopySchema = False
- UseCollation = False
- IncludeDependentObjects = False

Under "Destination Copy Options"
- CopyAllObjects = False
- ObjectsToCopy = I created a TablesList collection and chose 3 small tables to test with

Under "Security"
- CopyDatabaseUsers = True
- CopyDatabaseRoles = True
- CopySQLServerLogins = True
- CopyObjectLevelPermissions = True

Under "Table Options"
- CopyIndexes = True
- CopyTriggers = True
- CopyFullTextIndexes = True
- CopyPrimaryKeys = True
- Copy ForeignKeys = True
- GenerateScriptsInUnicode = False

When I was done with this, I saved the package and then executed it.
The execution results were as follows:

- Validation has started
- Validation has completed
- Information - There are no Rules to transfer
- Warning - Table: manufacturer_def already exists at destination
- Warning - Table: model_def already exists at destination
- Warning - Table: item_def already exists at destination
- Information - There are no Views to transfer
*** Then about 10 more Information lines for other objects I chose not to transfer ***
- Information - There are no XmlSchemaCollections to transfer
- Error - Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null...
Task Transfer SQL Server Objects Task Failed
Validation is completed
Warning: The execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1), resulting in failure.

I went and checked the destination database, and the 3 tables I tried to copy still had old data in them, so they obviously did not copy over correctly. I'm not sure why there are warnings about them already existing, since I choose to "DropObjectFirst" in the options. But, I also did another test where I manually dropped one of the tables, and then reran it. In that case I got warnings for the 2 tables that still existed, and the package still had the failure message at the end. The 2 existing tables were not updated, and the 1 table I had dropped was not recreated in the destination database.

As for the error at the end, I have no idea what this means, or what needs done to correct it.
If anyone has advice on what this error means and how to correct it, or if there is anything in my package definition listed above that is not correct for what I wish to accomplish, please let me know.
Web4Deb
New Member
New Member

--
10 Apr 2007 10:43 AM
Did you ever find any answers to your question? I'm trying to to the exact same thing.

-Rob T.
Bittela
New Member
New Member

--
02 May 2007 04:28 AM
I'm not able to copy objects with the 'transfer SQL server object Task'. I opened a call as a Microsoft Premier-support customer. No chance to run a package without errors. Microsoft said, they know the errors but they will be corrected in SQL2k8.

thank you Microsoft. You got some millions each year from our enterprise. Great support
SQLUSA
New Member
New Member

--
02 May 2007 05:50 PM
Bitella,

Really, the next edition is SQL Server 2008 ?

Hmmm...I thought it will be SQL Server 2010!

Kalman Toth, Database Architect
SQL Server Training - http://www.sqlusa.com/ordertripcrown/
SwePeso
New Member
New Member

--
06 May 2007 11:16 PM
You are not authorized to post a reply.

Acceptable Use Policy