L'identité insérer sur le serveur lié échoue
Je veux utiliser une procédure stockée pour copier une table de ma base de données de test à un serveur lié avec le même IDENTIFIANT et /ou de l'Identité, mais je ne peux pas le faire fonctionner..
J'ai réglé le IDENTITY_INSERT
à ON
mais il se plaint toujours sur la colonne ID.
Voici ma procédure:
CREATE PROCEDURE [dbo].[TEST2PROD_CopyUIDataSServer]
AS Begin
declare @sql nvarchar(max)
-- First truncate target table
set @sql = 'EXEC [LINKEDSERVER].tempdb.sys.sp_sqlexec' + char(39)+ 'TRUNCATE Table [ProductManager].dbo.[UIData]' + char(39)+ ';'
---- SET IDENTITY_INSERT ON
set @sql = @sql + 'EXEC [LINKEDSERVER].tempdb.sys.sp_sqlexec' + char(39)+ 'SET IDENTITY_INSERT [ProductManager].[dbo].[UIData] ON' + char(39)+ ';'
---- INSERT UIDATA records from DB1 into linked server DB2
set @sql = @sql + 'WITH TestData as (SELECT * from ProductManager.dbo.UIData UID)' + NCHAR(13)+ 'INSERT INTO [LINKEDSERVER].[ProductManager].[dbo].[UIData]' + NCHAR(13) + 'select * from TestData;'
print @sql
exec (@sql)
end
Mais quand j'execute le PS, il me donne l'erreur suivante:
Le fournisseur OLE DB "SQLNCLI10" du serveur lié .... ne pouvait pas INSÉRER DANS la table "[LINKEDSERVER].[ProductManager].[dbo].[UIData]" en raison de la colonne "Id". L'utilisateur n'a pas l'autorisation d'écrire dans la colonne.
Lié propriétés du serveur RPC RPC et sont mis à true. J'espère someboy peut m'aider ici?
Mise à JOUR: j'ai décidé de tirer les choses en dehors, j'ai d'abord copier les données à partir du serveur local vers le serveur lié dans un TEMP_TABLE
où je n'ai pas à traiter avec IDENTITY
questions.
Ensuite, j'ai écrit une procédure stockée sur le lien /serveur à distance, depuis que je ne suis pas en utilisant SELECT *
mais de spécifier la liste des colonnes. Les Chances sont ce à partir du serveur local dans un SP aussi, mais je n'ai pas le temps ou l'intérêt de le vérifier encore..
USE [ProductManager]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TEST2PROD_CopyBaseTables]
AS BEGIN
DECLARE @DestTable VARCHAR(50)
DECLARE @DestPath VARCHAR(50)
DECLARE @SrceTable VARCHAR(255)
declare @sql nvarchar(max)
DECLARE @columnList varchar(max)
DECLARE @err int
Begin TRY
declare @comma_delimited_list varchar(4000)
--- FIRST TRY WITH ONE TABLE, EXTENDABLE...
set @comma_delimited_list = 'UIData'
declare @cursor cursor
set @cursor = cursor static for
select * from dbo.Split(@comma_delimited_list,',') a
declare @naam varchar(50)
open @cursor
while 1=1 begin
fetch next from @cursor into @DestTable
if @@fetch_status <> 0 break
--Create tablenames
SET @SrceTable = '[ProductManager].[dbo].TEMP_' + @DestTable
SET @DestPath = '[ProductManager].[dbo].'+ @DestTable
print @srceTable;
print @DestTable;
--Truncate target table
set @sql ='TRUNCATE TABLE '+ @DestPath + ';'
--Insert statement needs column names
set @columnList =''
SELECT @columnList = coalesce(@columnList + '[' + name + '],','') FROM sys.columns Where OBJECT_NAME(OBJECT_ID) = @DestTable
if RIGHT(RTRIM(@columnList),1) = ','
begin
SET @columnList = LEFT(@columnList, LEN(@columnList) - 1)
end
--Transfer data from source table 2 destination
set @sql = @sql + ' SET IDENTITY_INSERT ' + @DestPath + ' ON;' + ' INSERT INTO ' + @DestPath + '(' + @columnList + ') SELECT ' + @columnList + ' FROM ' + @SrceTable
print @sql;
exec (@sql)
end
-- not strictly necessary w/cursor variables since the will go out of scope like a normal var
close @cursor
deallocate @cursor
End Try
Begin Catch
declare @ErrorMsg nvarchar(MAX);
select @ErrorMsg = ERROR_MESSAGE();
SELECT @err = @@error IF @err <> 0 Return @err
end Catch
END
OriginalL'auteur Mike Dole | 2012-09-12
Vous devez vous connecter pour publier un commentaire.
IDENTITY_INSERT
ne fonctionne pas avec des serveurs liés autant que je sache, sauf si vous exécutez SQL dynamique qui inclut laSET IDENTITY_INSERT
dans le lot ou avoir un peu de code (procédure Stockée par exemple) sur le serveur distant qui le fait pour vous.La
IDENTITY_INSERT
est par session (voir MSDN) et lorsque vous utilisez le serveur distant ce sera probablement dans une autre session à partir de votre instruction exécutée via[LINKEDSERVER].tempdb.sys.sp_sqlexec
, ce qui provoque l'échec comme vous le voyez qu'il se passe.l'ensemble du code (réglage de l'identité insérer sur, en insérant les données, l'établissement de l'identité insérer off) aurait besoin d'être dans une SP. E. g. vous pouvez nourrir les éléments simples pour être inséré dans la SP, ou si vous souhaitez effectuer l'ensemble des opérations que vous devez passer dans une TVP ou d'avoir le serveur lié utiliser le serveur en tant que serveur lié).
FYI. Il ne fait pas comme par magie de travail en l'enveloppant dans du sql dynamique. Vous obtenez toujours cette erreur: Le nom de l'objet 'LINKED_SERVER.DestinationDatabase.dbo.DestinationTable " contient plus que le nombre maximum de préfixes. Le maximum est de 2.
La seule façon que je pourrais l'obtenir pour fonctionner est de set IDENTITY_INSERT à l'intérieur d'une procédure stockée sur la destination du serveur lié.
OriginalL'auteur
Vous pouvez insérer une identité, d'une valeur dans une table avec un colonne d'identité sur un serveur lié avec le bouton "passer À" tromper.
Si vous n'avez pas utilisé le "SWITCH" astuce pour ajouter et supprimer identité sur une colonne, c'est très rapide, même sur de grandes tables!
Conceptuellement, il vous suffit de créer un nouveau SCHÉMA exactement comme la table que vous êtes désireux de
INSERT
à sans l'identité définie. Ensuite, passez la table pour que le SCHÉMA et faire votreINSERT
. Puis revenez dans le SCHÉMA avec l'identité définie.L'exemple ci-dessous a été testé sur un serveur lié dans AZURE.
Toutes les mises en garde de l'utilisation de "passer À" appliquer (les index doivent être les mêmes, de supprimer et recréer les clés étrangères, etc)
Pour tester, vous pouvez exécuter le script intégral ci-dessous sur l'une Liée Azure SQL Server base de données. Vous aurez besoin de faire un rechercher/remplacer avec
[LINKED_SERVER_NAME]
et[DATABASE_NAME]
, en remplacement avec vos valeurs. Sur un non-Azur DB vous devrez peut-être ajouter des "PRIMAIRES" à la table créations.OriginalL'auteur
Vous avez besoin pour exécuter la requête dynamique Exemple :
Si une colonne de définir l'identité de l', vous devez définir le
SET IDENTITY_INSERT TargetTable ON
et la nécessité de spécifié le nom de la colonne. Exemple:INSERT ... SELECT
peut travailler?OriginalL'auteur