Click here to Skip to main content
15,892,059 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
How to rectify Issues of Identity insert during insertion on using Linked Server in Sql?
Posted
Comments
CHill60 10-Feb-15 3:20am    
You will need to give us more details - what is the issue?
Kandiya 10-Feb-15 8:19am    
I am doing an Archival of data based on Archival date from original DB to another DB for Archival. The Archival server is Linked Server.The Issue comes on insertion on tables with identity insert ON.
Archival Script is as follows:

-- exec [DIB_ARCHIVE] 'DIBARCHIVETEST','DIBWPS_ARCHIVE_DB_ITT','05-10-2010'
CREATE PROCEDURE [dbo].[DIB_ARCHIVE1]
(
@LIVEDB NVarchar(100)
, @ARCHIVEDB Varchar(100)
, @ArchiveDate VARCHAR(10)
, @ArchiveAgeDate VARCHAR(10)
)
AS

DECLARE @TableName Varchar(200)
Declare @DateField Varchar(50)
Declare @TableType char(1)
Declare @STRIDENTITY NVarchar(4000)
Declare @STRINSERT NVarchar(4000)
Declare @STRDELETE NVarchar(4000)
declare @ColumnList varchar(MAX)
declare @STRUSE Nvarchar(100)
declare @DISABLECONSTRAINT Nvarchar(1000) declare @ENABLECONSTRAINT Nvarchar(1000) Declare @Archive_LogID INT Declare @NumRowsChanged INT

SET @DISABLECONSTRAINT=@LIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"'
EXECUTE sp_executesql @DISABLECONSTRAINT SET @DISABLECONSTRAINT=@ARCHIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"'
EXECUTE sp_executesql @DISABLECONSTRAINT
SET XACT_ABORT ON

set @STRUSE='USE '+@LIVEDB
EXECUTE sp_executesql @STRUSE


---Archive LOg-------------
INSERT INTO Archive_Log (LiveDBName,ArchivalDBName,Archival_Date,ArchivalStartTime)
VALUES (@LIVEDB,@ARCHIVEDB,getdate(),getdate())
SET @Archive_LogID= SCOPE_IDENTITY() ---Archive LOg-------------

BEGIN TRY
BEGIN TRAN

IF CURSOR_STATUS('global','ARCHIVE')>=-1
BEGIN
DEALLOCATE ARCHIVE
END

DECLARE ARCHIVE CURSOR FOR SELECT TableName,DateField,TableType FROM dbo.Archive_config OPEN ARCHIVE fetch next from ARCHIVE into @TableName, @DateField,@TableType

WHILE @@FETCH_STATUS = 0
BEGIN

SET @STRIDENTITY=''
set @ColumnList=''
set @STRINSERT=''

--**********SQL to SET IDENTITY_INSERT ON**********--
IF Exists (select 1 from sys.all_columns c INNER JOIN sys.tables t ON t.object_id = c.object_id
WHERE is_identity = 1 AND t.type = 'U' and Object_name(t.object_id)=@TableName)
SET @STRIDENTITY='SET IDENTITY_INSERT '+ @ARCHIVEDB +'.dbo.'+@TableName + ' ON '
--**********SQL to SET IDENTITY_INSERT ON**********--

IF(@TableType='M') --Master Table
BEGIN
--**********Delete all rows from Master/Meta Table**********--
SET @STRDELETE='delete from '+ @ARCHIVEDB +'.dbo.'+@TableName
EXECUTE sp_executesql @STRDELETE
--**********Delete all rows from Master/Meta Table**********--


---Archive LOg-------------

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900