procedure :

------------------------

CREATE Proc [dbo].[ap_mtn_Sync_Table_data]

@EndDate DATETIME,

@prefix VARCHAR(100)

AS

SET NOCOUNT OFF

SET XACT_ABORT ON

CREATE TABLE #tmp_table(table_name VARCHAR(50),id INT IDENTITY)

DECLARE @SQLcmd NVARCHAR(400),@TableName NVARCHAR(50), @StartDate DATETIME

DECLARE @i INT ,@max INT

SELECT @i = 1, @StartDate = last_used_date FROM vbr_db.dbo.HQ_to_IN_LU_CONTROLLER

SET @SQLCmd = 'INSERT INTO #tmp_table (table_name) SELECT OBJECT_NAME(OBJECT_ID) FROM sys.dm_db_index_usage_stats ' +

' WHERE database_id = (SELECT database_id FROM sys.databases WHERE NAME = "vbr_db")' +

' AND index_id in (0,1) ' +

' AND last_user_update BETWEEN ''' + CONVERT(VARCHAR(25),@StartDate,121) + ''' AND ''' + CONVERT(VARCHAR(25),@EndDate,121) + ''' AND OBJECT_NAME(OBJECT_ID) LIKE ''%' + @prefix + '%'''

EXEC(@SQLCmd)

CREATE INDEX IX_#tmp_table_table_name ON #tmp_table(table_name)

SELECT @TableName = table_name, @i = @i + 1 FROM #tmp_table WHERE @i = ID

WHILE @@rowcount <> 0

BEGIN

SET @SQLCmd = 'DELETE FROM vbr_db.dbo.' + @TableName

EXEC(@SQLCmd) AT srvr2

SET @SQLCmd = 'INSERT INTO [vbr_db].dbo.' + @TableName + ' SELECT * from srvr1.vbr_db.dbo.' + @TableName + ' WITH (NOLOCK)'

EXEC(@SQLCmd) AT srvr2

SELECT @TableName = table_name, @i = @i + 1 FROM #tmp_table WHERE @i = ID

END

here , two query are to be get executed at linked server (srvr2)

domain\sqlman is the service account user who has admin rights...on both servers

the strange thing is that if i run manually it works fine

please reply