Solved

Primary key lost when exporting the data

Posted on 2002-03-08
8
617 Views
Last Modified: 2011-09-20
Hi Experts

I have a database on my local server and an another database on my isp side. To run my web sites i have to upload the data to my host isp. I have only the export and import rights. The problem is that i have primary keys in my tables on my local database. The movement i export the data to my isp server all the primary keys are lost. I tried the similar thing by exporting data to an another local server. The result is same. Is this a big bug in the sql server or there is any way to solve the same. Remember i donot have the rights to take the backup of the database or restore it.

With Regards

Loveneesh Bansal
0
Comment
Question by:loveneesh_bansal
  • 4
  • 3
8 Comments
 
LVL 4

Expert Comment

by:ruperts
ID: 6852454
I haven't tried this, but I assume that you have the IDENTITY property on for the primary key field.

You'll have to turn it off for the insert on the target machine, and then afterwards (if needed) put it back on again.
0
 
LVL 2

Expert Comment

by:UncleMatt
ID: 6856231
how are you importing the data?

if you are using DTS there is a set identity insert on option when you select the tables i believe.

if you are using linked servers and doing it that way then use:

set identity_insert on

Matt.
0
 
LVL 1

Author Comment

by:loveneesh_bansal
ID: 6859625
dear UncleMatt i have tried the same but still the result is same. I suppose it is a big bug in sql server .

loveneesh
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 2

Expert Comment

by:UncleMatt
ID: 6861621
i've done this same thing myself, so it's not a bug in the server, what versions of SQL Server are you using, are they both the same.  

If you are using DTS what options are you setting.

let us know more information and i might be able to help.

Matt.
0
 
LVL 1

Author Comment

by:loveneesh_bansal
ID: 6864118
Dear Unlcematt

I am using sql server 2000. To test the things i had created two database. In the first database name as test i had made a table name as one. Under this table i have only one field name as id. This is the primary key. Now hen i used the enterprise edition of sql server and from the second database i import this table. When i saw the design from this database the primary key was not there.

Please help me for the same.

loveneesh
0
 
LVL 2

Accepted Solution

by:
UncleMatt earned 15 total points
ID: 6865295
when you setup the dts package, there is a choice that i can't remember all of, but the bottom of the three cohoices is something like transfer objects between sql servers.

If you make sure you use this selection, and then in options make sure indexes is selected, it should transfer all the data like you want it to.

if not, post a step through of exactly what you do in each step of DTS and i can check to see if there is something you are missing.

Matt.
0
 
LVL 1

Author Comment

by:loveneesh_bansal
ID: 6867416
Thanks unclematt.



0
 
LVL 1

Author Comment

by:loveneesh_bansal
ID: 6933648
Sorry Uncle matt

I am accepting your answer very late. Thanks for your kind support
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server - Find Parent/Child relationship tree between numeric range. 7 41
Get the latest status 8 30
insert wont work in SQL 14 20
Update a text value in another table 10 39
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question