Re: copying data from one server to another

(imorxr@[EMAIL PROTECTED]
) writes:
> That command executes without error, but the problem is that I cannot
> perform the actual insert, because it is not within the execute
> statement. In other words, the following doesn't work:
> 
> EXECUTE [remoteservername].Library2005.dbo.sp_executesql N'SET
> IDENTITY_INSERT dbo.tblLanguages ON'

This is because the effect of a SET command is reverted when the
scope in which the SET command is executed exists. You would
need to have the SET command and the INSERT statement in the 
same batch.

Overall, this is a good example how IDENTITY can buy you a load of 
troubles. Had you rolled your own, you would have copied the data
long ago.

-- 
Erland Sommarskog, SQL Server MVP, esquel@[EMAIL PROTECTED]
 Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx