Solved

Export data Identity Column Problem

Posted on 2007-11-17
8
606 Views
Last Modified: 2010-05-18
Hi,

I want to export data from my remote server to my local machine.  I want an exact copy.  The problem is that I can export data from remote dbQIIHL to local dbQIIHL but since I have identity colums the export doesn't override the identity column constraint on my local machine, hence my sql queries do not work on the local machine after the export process.

Is there a way to turn off the identity on a database, do the export, and then turn it on again so that the db behaves normally?
0
Comment
Question by:vstack
8 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20305134
... export ... So I assume you are doing something with insert statements to fill the local copy of your database ?

In that case, use this function (from BOL):
SET IDENTITY_INSERT [database_name.[schema_name].] table {ON|OFF}

Turn the setting on, do the import, and turn it off again. It is off by default ... as you experienced :-)

for more info, look here:
http://msdn2.microsoft.com/en-us/library/ms188059.aspx

Hope this helps ...
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 20305569
I use SQL Delta from the www.sqldelta.com which has a database difference and copying tool.  It automates the process.
0
 

Author Comment

by:vstack
ID: 20306285
I am using the export wizard in SQL Server 2005
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 18

Expert Comment

by:Yveau
ID: 20306988
... the export wizard creates an SSIS package for you.
Either add two SQL Tasks in the SSIS package, or manually run the SET IDENTITY statement prior to the SSIS package and afterwards.

Hope this helps ...
0
 

Author Comment

by:vstack
ID: 20307139
Yveau, with your help, I am close to the solution.  I managed tio export all data but I had to manually click the allow Identity insert for EVERY table.  Even this did not work until I deselected the Optimize for many tables.

Is their a way to do this so that I don't have to check the 'Allow Identity Insert' for every table?
0
 
LVL 18

Accepted Solution

by:
Yveau earned 500 total points
ID: 20309126
Sure, you can loop through every table with the cursor included in the code snippet ...

Hope this helps ...

declare @cTbl varchar(50)
declare @cSQL varchar(max)
 
declare Yveau cursor
for
select  name
from    dbo.sysobjects
where   type = 'U'
-- make sure to add enough clauses here to ONLY list the tables with IDENTITY columns !!!
 
open    Yveau
fetch   Yveau
into    @cTbl
 
while   (@@Error = 0)
and     (@@Fetch_Status = 0)
begin
        set     @cSQL = 'SET IDENTITY_INSERT '+@cTbl+' ON'
        exec    (@cSQL)
 
        fetch   Yveau
        into    @cTbl
end
 
close   Yveau
deallocate Yveau

Open in new window

0
 

Author Closing Comment

by:vstack
ID: 31409738
This works with some extra variation for my problem
0
 

Expert Comment

by:ejohnson4211
ID: 21550735
This is clearly a bug, yet I don't see it on Microsoft's KnowledgeBase as yet.  Does someone from Microsoft monitor this forum?  How do we escalate this issue to them?

Thanks!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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