Export from 2000 to 2008 - Indentity column

I did an export of data from 2000 to 2008, all the tables and data copied 
over with the exception of the Identity attribute.  (which is the auto number 
feature).
This is not good, I tried to manually do it and it says that I need to drop 
and recreate table. This poses an issue given all data I have in the table 
and that needs to be imported over.

What am I missing?
0
Utf
1/27/2010 4:41:01 PM
sqlserver.server 1327 articles. 0 followers. Follow

6 Replies
423 Views

Similar Articles

[PageSpeed] 53

MOCK (MOCK@discussions.microsoft.com) writes:
> I did an export of data from 2000 to 2008, all the tables and data
> copied over with the exception of the Identity attribute.  (which is the
> auto number feature). 

How did you perform the export? It's difficult to tell why IDENTITY
did not make it to other side, if I don't know how you did it.

> This is not good, I tried to manually do it and it says that I need to
> drop and recreate table. This poses an issue given all data I have in
> the table and that needs to be imported over. 

Yes, there is no ALTER command to add/remove IDENTITY. Certainly a 
missed feature.
 

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
1/27/2010 11:00:38 PM
Within SQL 2008 SQL Server Management Studio, I did a right click on the 
database and Tasks, then either Import or Export works to bring the stuff 
over...but the Identity attribute does not carry over.

"Erland Sommarskog" wrote:

> MOCK (MOCK@discussions.microsoft.com) writes:
> > I did an export of data from 2000 to 2008, all the tables and data
> > copied over with the exception of the Identity attribute.  (which is the
> > auto number feature). 
> 
> How did you perform the export? It's difficult to tell why IDENTITY
> did not make it to other side, if I don't know how you did it.
> 
> > This is not good, I tried to manually do it and it says that I need to
> > drop and recreate table. This poses an issue given all data I have in
> > the table and that needs to be imported over. 
> 
> Yes, there is no ALTER command to add/remove IDENTITY. Certainly a 
> missed feature.
>  
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 
> .
> 
0
Utf
1/28/2010 1:51:01 PM
MOCK (MOCK@discussions.microsoft.com) writes:
> Within SQL 2008 SQL Server Management Studio, I did a right click on the 
> database and Tasks, then either Import or Export works to bring the stuff 
> over...but the Identity attribute does not carry over.

Never user that one before. I guess the problem is that it can export
to all sorts of sources, and all may not support IDENTITY.

One option is the Copy Database Wizard, and select only one table.
But I have found CDW to buggy and unreliable.

Another option is simply to create the table before you export. Preferrably
this should be done from scripts kept under version control, but you
can also script the table in Mgmt Studio.
 


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
1/28/2010 11:17:50 PM
Hi,

I don't know these wizards either, but it sounds like you try to insert a 
value into a column with the Identity proterty set and this will not work.

If you have a table with an Identity column and you want to explicitly 
insert a value, then you'll have to disable the IDENTITY property first. 
This is done with the SET IDENTITY_INSERT TableName ON command.

It sounds like this is what it missing in your case and that you script 
tries to insert a value in the column with the Identity property enabled.


-- 
Regards
Steen Schl�ter Persson (DK)


"MOCK" <MOCK@discussions.microsoft.com> wrote in message 
news:2C7730C7-8BFB-4C6A-AD67-869C90ED430A@microsoft.com...
> Within SQL 2008 SQL Server Management Studio, I did a right click on the
> database and Tasks, then either Import or Export works to bring the stuff
> over...but the Identity attribute does not carry over.
>
> "Erland Sommarskog" wrote:
>
>> MOCK (MOCK@discussions.microsoft.com) writes:
>> > I did an export of data from 2000 to 2008, all the tables and data
>> > copied over with the exception of the Identity attribute.  (which is 
>> > the
>> > auto number feature).
>>
>> How did you perform the export? It's difficult to tell why IDENTITY
>> did not make it to other side, if I don't know how you did it.
>>
>> > This is not good, I tried to manually do it and it says that I need to
>> > drop and recreate table. This poses an issue given all data I have in
>> > the table and that needs to be imported over.
>>
>> Yes, there is no ALTER command to add/remove IDENTITY. Certainly a
>> missed feature.
>>
>>
>> -- 
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000: 
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>> .
>> 


0
Steen
1/29/2010 8:39:00 AM
Erland Sommarskog (esquel@sommarskog.se) writes:
> MOCK (MOCK@discussions.microsoft.com) writes:
>> Within SQL 2008 SQL Server Management Studio, I did a right click on the 
>> database and Tasks, then either Import or Export works to bring the stuff 
>> over...but the Identity attribute does not carry over.
> 
> Never user that one before. I guess the problem is that it can export
> to all sorts of sources, and all may not support IDENTITY.
> 
> One option is the Copy Database Wizard, and select only one table.
> But I have found CDW to buggy and unreliable.
> 
> Another option is simply to create the table before you export.
> Preferrably this should be done from scripts kept under version control,
> but you can also script the table in Mgmt Studio. 
  
I looked a little more into the wizard, and there are good news and 
there are bad news.

The good news is that if all you want is IDENTITY, you can click Edit SQL
and add the IDENTITY property manually to the script.

The bad news is that the wizard is basically useless for the task.
Not only does it fail to include the IDENTITY property. It does not 
carry over keys, constraints or indexes. I tested with a table with
a computed column and it came over as a non-computed column.




-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
1/29/2010 10:41:35 PM
Steen Schl�ter Persson (steen@REMOVETHISasavaenget.dk) writes:
> If you have a table with an Identity column and you want to explicitly 
> insert a value, then you'll have to disable the IDENTITY property first. 
> This is done with the SET IDENTITY_INSERT TableName ON command.

There is a button for this in the Edit Mappings dialog. But that was
not MOCK's problem. The proble was that the IDENTITY propery is by 
default not copied. You can only achieve this by editing the CREATE
TABLE script.
 


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
1/29/2010 10:42:50 PM
Reply:

Similar Artilces:

Contacts export display X400 not SMTP
Hi all, I'm not sure if this behavior is by default or not, but when my users try to export their contacts to an Excel file, any internal (domain) contacts show the X400 address rather than the SMTP address. Is there a way to change this? I've read some about VB scripts with CDO or something, but hoping there is an alternate way. Thanks Mike -- what version Exchange? -- Susan Conkey [MVP] "Mike W." <wallmr_snowpam@gmailDOTC0M> wrote in message news:iQ72h.553254$Pi2.240974@fe08.news.easynews.com... > Hi all, > > I'm not sure if this behavio...

viewing contacts crashes outlook 2000 #2
From: "Shane Lieberg" <cool1two@hotmail.com> Subject: viewing contacts crashes outlook 2000 Date: Monday, December 08, 2003 10:59 AM problem, when viewing contacts (pst with outlook address book service) using the address book viewer "hit the address book on tool bar", and scroll though the contacts. i will crash at some random point. if you view the same contact list in folder view. "left side of screen under contacts", you can view all of the contacts and the info without issue. ...

Export to XML exports single row only
What I did: On XML source panel of Excel 2007 added XML map, mapped to columns using ranges (i. e. A1:A1825), selected “Export” from “Developer” tab in the ribbon. Result: Output XML file contains only one record (first row from the worksheet) I’ve done this numerous times in Excel 2003 with no problem. What happens?? Please help! Thanks, Eugene ...

Export / Import Security
Ok, so I have setup my test environment again. Now I have to create security all over from scratch to define what will be used in production. Is there a way to export from the test environment and then import it into the newly upgraded installation? In both cases security has been/will be completely removed from the 8.0 install (restored on new server) before being upgraded to version 10. Dear, Security will be upgraded upon upgrading GP from GP 8.0 SP5 to GP 10.0. Converting the Security structure from GP 8.0 to 10.0 will not be an easy task. Regards, -- Mohammad R. Daoud MCP, MCBM...

need help with outlook 2000
I have client with microsoft outlook 2000 running on a XP machine. recently outlook closes on its own. I have tried the detect and repair option under help and tried a fresh install but the problem still persist. What other options are there to try? ...

Exporting Sent Email Addresses from Entourage
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Hello! I'm trying to export email addresses from an employee's Entourage, but would like to include email addresses in his "Sent Items" folder. Is there a way to export these email addresses aside from exporting the "MBOX" file, which has all the email content as well? Thanks in advance! On 3/12/08 8:49 AM, in article ee9381d.-1@webcrossing.caR9absDaxw, "Quinlisk@officeformac.com" <Quinlisk@officeformac.com> wrote: > I'm trying to export email addresses from an emplo...

I can't type in the third column of my landscape document.
I set up a legal-size, landscaped document in 3 columns. I have typed in the first two, but I can't type anything in the third. It's as if the document ends. When I look at the page in Print Preview, it looks perfect: the first two sections are there, but the third one is blank. Messing around with the margins hasn't helped me. Have you filled the first two columns? They should automatically spill over to the third, or you can insert a column break (Ctrl+Shfit+Enter). -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://w...

Inserting Excel Data into Word Columns
Hi, I am fairly new to VBA and Excel/Word and have been trying to automat a sheet we have, I have created the Excel Sheet and unfortunately th Word doc was created sometime ago and cannot be changed. I have searched this forum and found this code: Code ------------------- Sub CreateNewWordDoc() ' to test this code, paste it into an Excel module ' add a reference to the Word-library ' create a new folder named C:\Foldername or edit the filnames in the code Dim wrdApp As Word.Application Dim wrdDoc As Word.Document Dim i As Integer Set wrdApp = CreateObject(&qu...

Run Query from a Column Value
Hi Let if I have a tb1 with col1 CREATE TABLE tbl1 ( Col1 VARCHAR(500) ) INSERT INTO tbl1 VALUES('SELECT * FROM tbl1') INSERT INTO tbl1 VALUES('SELECT Query FROM tbl1') In col1 I have inseted a query as a string. How can I run the query stored in col1. Regards, Muhammad Bilal You have to use dynamic SQL: http://www.sommarskog.se/dynamic_sql.html -- Plamen Ratchev http://www.SQLStudio.com On Fri, 30 Apr 2010 15:34:01 -0700, Muhammad Bilal <MuhammadBilal@discussions.microsoft.com> wrote: >Hi > >Let if I have...

Exchange 2000 User not created
When I add a user in AD, mail enable the user, the user is not showing up as a mailbox in Exchange 2000 malbox store nor is in the GAL. In AD, after user creation, the user has all Exchange tabs with the correect info and the addresses from RUS are in the Exchange addresses. Have tried to access mailbox by setting up Outlook for the user, but username is not resolving. Help please!!! Thanks Hi Jerry, mail-enable and mailbox-enable are two different things. Make sure you're performing mailbox-enable. Also the domain controller that you're making this change may be different tha...

CRM 4.0 entity view column properties
Has anyone run into this? Using the Web Client, in customizations open an entity and one of the views. Click on a column heading and try to change its properties (the width of the column). I get an alert that says "Select the column whose properties you want to change". Clearly not recognizing that I've clicked on a column. Using the Outlook Client this works fine. Sort of strange... Hi Bob, You can know if a column is selected if it has green border surrounding it. Can you see this before you click on "Change Properties"? When you launch any View page (s...

calendar in publisher 2000
Hi all. Is it possible to have a yearly calendar in publisher 2000 go from say March 2005 to March 2006 as opposed to it always going from January to December? Also, when doing a monthly calendar, is it possible to show more than one month, or would I need to do two monthly calendars on the same page? The bottom month is always grayed out. I would like to show for example July and August on the same page. Thanks for any help. Eric You can do it, but you will have to manually adjust your pages. Select the calendar you want to use in the wizard. Click dates first, change your rang...

Export item ID?
Hi there. Having resolved my previous EMS Data import issues, I have now moved on to more complicated things (importing supplier lists, aliases etc). However, these operations require the item ID for each item - something I don't have. Is there some way I can export these from the database so I know what item ID corresponds to each item. I have run a query in Administrator (select itemlookupcode, ID from item) which tells me this, but I haven't worked out how to export this out to a spreadsheet. Any help would be appreciated, cheers After running your query goto File menu and Export....

Exporting email in Outlook Express
I am unable to export all emails in Outlook Express. First it had an error message mentioning MAPI so i followed the instructions on Microsoft Knowledge Base to install Windows Messaging - now when i try to export the following error message is: MAPISP32 caused an invalid page fault in module KERNEL32.DLL at 0187:bff7b9a6. Does anyone know what this means? tania jackson <tania@realtime-bs.com> wrote: > I am unable to export all emails in Outlook Express. Ask in an Outlook Express newsgroup. -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapid...

Determining SQL Server 2008 Instance Name?
I'm building a "Setup and Deployment" project to distribute our application. We have a database called dbMain that runs on SQL Server 2008. I'm connecting to the database from our WPF Application via the app.config file, and here are the particulars: --------------------------------------------------------------------------------------------------------------------------------------------------------------- <?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> </configSections> <conne...

Publisher 2000 cannot save, not enough memory.
After creating a 5 to 30 page project with 2 photos on each page with captions for each photo, when trying to save it will say not enough memory try another drive, but it will end up crashing without saving. This happens in Win 98 and Win XP opening across a network with a server drive with 20gigs free and 25 gigs free on the local drive. -- Message posted via http://www.officekb.com Are you saving to the local drive? Publisher does not do well saving to a network drive. You should save locally and copy over to the network. PUB2000: Cannot Save Publication After You Lose Network Connectio...

Export Image Package
I have an access application that has stored .JPG images within the database as a "Package". How can I export the image back to a normal .jpg file so that it can be read by a normal (non ole server) imaging application? ...

Excel 2000 Security patch crashes Excel
I recently upgraded my computer, although I am still using Excel 2000. As part of the upgrade, I decided to be good and updated all the available service patches for Excel 2000. I later tried opening a file that I have created and work with extensively - and Excel crashed. Something that never happened before. I tried opening the file on a different computer - and it worked just fine. So I began debugging to find what the problem was - I stripped out all the VBA, and then I stripped out the worksheets one-by-one, until I came upon the root of the problem. I had a picture inserted, with a hyper...

Outlook 2000 Won't Stop Polling
Not sure where the setting is, but every time I close and exit out of Outlook, I continue to get notifications that I have new email in my inbox. How do I get Outlook to stop doing that? I like to keep a copy on the server until I'm ready to read it, but I'm not always on my home computer when checking it ... so I like the feature of removing it from the server once downloaded, I just need to figure out how to get it to stop polling. Thanks, --Jim ...

aufgaben im Entourage 2008 Personen zuordnen
Hallo, Hat jemand nen Tipp wie ich erstellte Aufgaben im Entourage 2008 Personen zuordnen kann? Dann m�sste dieses ja als Aufgabe �ber den Ausgang an die Person versandt werden und diese m�sste eine zu oder Absage senden k�nnen Ich bin wohl immer noch Outlook gebrandet...*lachen* Teddy Auto Translation from Word 2008: Has someone nen tap like I provided tasks in the Entourage 2008 persons to assign can? Then this would have as task over the exit to those Person and these would have to be dispatched be able to send one too or refusal I am probably still Outlook broken� *lachen* Answer:...

Exporting to Powerpoint
I have a small table in Excel that I am trying to export to PowerPoint, for some inexplicable reason one column was always missing even though it was specified in the range which the Macro is supposed to export. My manager is the only was able to see the value in that column and no other can replicate his success. Do you know any reason for this? We are all looking at the exact same file, why could he see something that the rest of us cannot? Tao, Why don't you simply copy and paste the whole table into a Powerpoin slide? Joh -- Message posted from http://www.ExcelForum.com...

Outlook 2000 does not close on exit
Hi, Has anyone else had this problem. A client used a trial version of Outlook 2003 and later uninstalled it from the Add/Remove programs. Then reinstalled Outlook 2000. Now when they close outlook, the window saying Outlook is now closing remains running on the screen indefinitely. You have to shutdown the application through the taskmanager (and it is very reluctant to close then). Any help would be appreciated. Have they tried Detect and Repair? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) ...

Export registry key
Hi, Is there an API to Export and Extract a registry key in a text format ? I need to simulate the operation that is done by the user of exporting a key (and creating a .reg file in a text format). I do not want to use RegSaveKey/RegRestorekey which work in a binary format. Yoav. I believe you will have to do this yourself. There is no Automation or exported function interface to RegEdit, and that .Reg file concept is a feature of that program. However, it shouldn't be terribly difficult to do this, using RegEnumKeyEx and RegEnumValue or the CRegKey class, use a CStdioFile object...

Export from Excel to Word
I have a 20 page Excel document that has a LOT of formating involved, it is text not data. Is there a way that I get it into Word without having to redo all the formatting? It is 11x17, landscape. Yes: Select the area you wish to copy. While holding the SHIFT key pull-down: Edit > Copy Picture... In the Word document, just paste. Because you have pasted a picture, the formatting will be preserved. Also because its a picture, yo can re-size it as you choose. -- Gary's Student "kath0178" wrote: > I have a 20 page Excel document that has a LOT of formating inv...

Reverse of export to database
Ave, I would like to generate a Visio drawing starting from an Access database which contains the results of an 'export to database' of an existing drawing. I found import possibilities from Excel, csv, txt files, but not from databases. I'm using Visio 2002 Professional. Greetings, Are you trying to import the data from the database tables or the database schema? If it is the database schema, run the Database modeling template (File \ New \ Database \ Database model diagram) If you want to get the data, try the "Link to Database" dialog (Tools \ Macros \ Visio Extras...