?
Solved

Primary key lost when exporting the data

Posted on 2002-03-08
8
Medium Priority
?
621 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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 60 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

801 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