Вставка идентификатора на связанном сервере не выполняется
Я хочу использовать хранимую процедуру для копирования таблицы из моей тестовой базы данных на связанный сервер с тем же ID/Identity, но я не могу заставить ее работать.
Я установил IDENTITY_INSERT в ON, но он все еще жалуется на столбец идентификатора.
Здесь моя процедура:
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
Но когда я запускаю SP, он вызывает следующую ошибку:
Поставщик OLE DB "SQLNCLI10" для связанного сервера.... не смог занести INSERT INTO в таблицу "[LINKEDSERVER]. [ProductManager]. [dbo]. [UIData]" из-за столбца "Id". У пользователя не было разрешения на запись в столбец.
Связанные свойства сервера RPC и RPC out установлены в значение true. Надеюсь, что кто-то может помочь мне здесь?
ОБНОВЛЕНИЕ: Я решил разобраться, сначала я копирую данные с локального сервера на связанный сервер в TEMP_TABLE, где мне не нужно иметь дело с проблемами IDENTITY.
Затем я написал хранимую процедуру на связанном/удаленном сервере, так как я не использую SELECT *, а укажите список столбцов. Скорее всего, это будет работать с локального сервера в SP тоже, но у меня нет времени или интереса, чтобы проверить его еще.
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
Ответы
Ответ 1
IDENTITY_INSERT не работает со связанными серверами AFAIK, если только вы не выполняете динамический SQL, который включает в себя SET IDENTITY_INSERT в пакетном режиме, или на каком-то некотором коде (например, хранимый Proc) на удаленном сервере, который делает это для вас.
IDENTITY_INSERT предназначен для сеанса (см. MSDN), и когда вы используете удаленный сервер, это, вероятно, будет в другом сеанс из вашего оператора, выполняемого через [LINKEDSERVER].tempdb.sys.sp_sqlexec, что приводит к его сбою, как вы видите.
Ответ 2
Вы можете вставить значение удостоверения в таблицу с столбцом идентификации на связанном сервере с трюком "SWITCH TO" .
Если вы не использовали трюк "SWITCH TO" , чтобы добавить и удалить идентификатор в столбце, он очень быстрый, даже на больших таблицах!
Концептуально вы просто создаете новую SCHEMA точно так же, как и таблицу, в которой вы хотите INSERT, без идентификатора. Затем переключите таблицу на эту SCHEMA и выполните INSERT. Затем вернитесь к SCHEMA с определенным идентификатором.
Пример ниже был протестирован на связанном сервере в AZURE.
Все оговорки об использовании "SWITCH TO" применяются (индексы должны быть одинаковыми, отбрасывать и воссоздавать внешние ключи и т.д.)
Чтобы протестировать, вы можете запустить полный script ниже в базе данных Linked Azure SQL Server. Вам нужно будет найти/заменить с помощью [LINKED_SERVER_NAME] и [DATABASE_NAME], заменив ваши значения. В не-Azure DB вам может потребоваться добавить "ON PRIMARY" к созданию таблицы.
--Let setup the example by creating a table with an IDENTITY column on the Linked Server
EXEC('
CREATE TABLE [DATABASE_NAME].[dbo].[Example_Table](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NULL
)
'
) AT [LINKED_SERVER_NAME]
--INSERT some data into the table
INSERT INTO [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] ([Name]) VALUES ('Travis')
INSERT INTO [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] ([Name]) VALUES ('Mike')
-- Looks good
SELECT * FROM [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table]
GO
-- Create a TABLE with an identical schema, without the identity defined
EXEC('
CREATE TABLE [DATABASE_NAME].[dbo].[Example_Table_temp](
[ID] [int] NOT NULL,
[Name] [nchar](10) NULL
)
'
) AT [LINKED_SERVER_NAME]
--Now Use the "SWITCH TO" to move the data to the new table
EXEC('
ALTER TABLE [DATABASE_NAME].[dbo].[Example_Table] SWITCH TO [DATABASE_NAME].[dbo].[Example_Table_temp]
'
) AT [LINKED_SERVER_NAME]
--Drop the old table (It should now be empty, but you may want to verify that if you are unsure here)
EXEC('
DROP TABLE [DATABASE_NAME].[dbo].[Example_Table]
'
) AT [LINKED_SERVER_NAME]
--Rename the new table back to the old table name
-- NOTE the lack of database and owner identifiers in the new name
-- NOTE the use of double single qoutes (ESCAPED single quotes)
EXEC('
EXEC sp_rename ''[DATABASE_NAME].[dbo].Example_Table_temp'',''Example_Table''
'
) AT [LINKED_SERVER_NAME]
-- Now do your IDENTITY INSERTs !!!!
INSERT INTO [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] (ID,[Name]) VALUES (888,'Travis')
INSERT INTO [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] (ID,[Name]) VALUES (999,'Mike')
--Verify they got put in
SELECT * FROM [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table]
--Now let switch it back to our SCHEMA with an IDENTITY
EXEC('
CREATE TABLE [DATABASE_NAME].[dbo].[Example_Table_temp](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NULL
)
ALTER TABLE [DATABASE_NAME].[dbo].[Example_Table] SWITCH TO [DATABASE_NAME].[dbo].[Example_Table_temp]
DROP TABLE [DATABASE_NAME].[dbo].[Example_Table]
EXEC sp_rename ''[DATABASE_NAME].[dbo].Example_Table_temp'',''Example_Table''
'
) AT [LINKED_SERVER_NAME]
--Data is still there
SELECT * FROM [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table]
GO
-- And note you can no longer INSERT the IDENTITY
INSERT INTO [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] (ID,[Name]) VALUES (45,'Travis')
GO
