Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: Would this code cause this linked server error?

    Do I need to introduce a delay between execution of code? or would I have better luck using: sp_executesql in both cases, let me try that r.q.

    Code:
    Code:
    	SET @sql = 
    	'DELETE LINK01.MYDB.dbo.'+@stTable+'
    	WHERE ID >= '+CAST(@MyId AS VARCHAR(20))+';
    	'
    	EXEC (@sql)			
    	
    	set @nsql = N'set identity_insert MYDB.dbo.'+@stTable+' on ;insert into MYDB.dbo.'+@stTable+' (id, tDate, tTime, tVolume, iOpen, iHigh, iLow, iClose) 
    	SELECT id, tDate, tTime, tVolume, iOpen, iHigh, iLow, iClose
    	FROM LINK02.MYDB.dbo.'+@stTable+' ;set identity_insert MYDB.dbo.'+@stTable+' off ;'			
    	
    	exec LINK01.MYDB.dbo.sp_executesql @nsql
    	PRINT '[I-many]'
    Error:
    Executed as user: NT AUTHORITY\SYSTEM. Cannot get the data of the row from the OLE DB provider "SQLNCLI10" for linked server "LINK01". [SQLSTATE 42000] (Error 7346) OLE DB provider "SQLNCLI10" for linked server "LINK01" returned message "Row handle referred to a deleted row or a row marked for deletion.". [SQLSTATE 01000] (Error 7412). The step failed.

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    still got the error making them both sp_executsql -- Actually modified the debug version of the code, this might have fixed it.

    going to try adding in an absurbdly long delay of 5 seconds, to make sure I have isolated the code.

    If I have the correct code, will try packaging the two together into a stored procedure on local server, I think that may fix it.
    Last edited by Gagnon; 02-15-12 at 11:09.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just to amuse me, try using:
    Code:
    	SET @sql = 
    	'DELETE LINK01.MYDB.dbo.'+@stTable+'
    	WHERE ID >= '+CAST(@MyId AS VARCHAR(20))+';
    	'
    --	EXEC (@sql)			
    	
    	set @nsql = @sql + ' ' + N'set identity_insert MYDB.dbo.'+@stTable+' on ;insert into MYDB.dbo.'+@stTable+' (id, tDate, tTime, tVolume, iOpen, iHigh, iLow, iClose) 
    	SELECT id, tDate, tTime, tVolume, iOpen, iHigh, iLow, iClose
    	FROM LINK02.MYDB.dbo.'+@stTable+' ;set identity_insert MYDB.dbo.'+@stTable+' off ;'			
    	
    	exec LINK01.MYDB.dbo.sp_executesql @nsql
    	PRINT '[I-many]'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •