How to disable Identity with Linked Server?

Asked

Viewed 265 times

0

How to disable Identity in Linked Server to not generate increment? Server table must equal local base.

SET IDENTITY_INSERT [ServidorLincado].[BD_TESTE].dbo.Produto ON --Desabilita o IDENTITY

INSERT [ServidorLincado].[BD_TESTE].dbo.Produto (
 [Codigo]  
,[Nome]  
,[Valor]  
,[ID]   -- esse campo é identity
)
SELECT 
 [Codigo]
,[Nome]
,[Valor]
,[ID]  
FROM  dbo.Produto

SET IDENTITY_INSERT [ServidorLincado].[BD_TESTE].dbo.Produto OFF --Habilita o IDENTITY

I get the error below:

Msg 117, Level 15, State 1, Line 2 The Object name contains more than the Maximum number of prefixes. The Maximum is 2.

sql-server sql-server-2008 sql-server-express

  • In the article Bulk import of data you can find examples similar to the case you cite: https://portosql.wordpress.com/articles/

1 answer

0

The IDENTITY_INSERT instruction must be executed in the instance of the database. It is not possible to define for other instances.

-- código #1
SET IDENTITY_INSERT [BD_TESTE].dbo.Produto ON;

INSERT [BD_TESTE].dbo.Produto (Codigo, Nome, Valor, ID) 
  SELECT Codigo, Nome, Valor, ID
    from outrainstancia.bdorigem.dbo.Produto;

SET IDENTITY_INSERT [BD_TESTE].dbo.Produto OFF;

Browser other questions tagged sql-server sql-server-2008 sql-server-express

You are not signed in. Login or sign up in order to post.