I have posted a question like this before but is seems that I did not explain the whole story clear. So I made this one to explain it in details. Here's the stored procedure
USE [xxxx]
GO
/****** Object: StoredProcedure [dbo].[MARK_ACTIVE] Script Date: 4/20/2015 5:55:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[xxxx]
AS
SET ANSI_NULLS ON
INSERT INTO [123.456.7.890].[DBASE1].[dbo].[TABLE] (smsfr,smsmsg,smsdt,dbdt,devid,status,status2,IsHEX)
SELECT TOP 3 smsfr,smsmsg,smsdt,dbdt,devid,status,status2,IsHEX
FROM [098.765.4.321].[[DBASE2].[dbo].[TABLE2] cr
WHERE
NOT EXISTS (SELECT id,smsfr,smsmsg,smsdt,dbdt,devid,status,status2,IsHEX FROM [123.456.7.890].[DBASE1].[dbo].[TABLE] c
WHERE cr.smsdt = c.smsdt)
and here's my job settings
My General
My Steps
My advance steps
My Scheduler
When I run the stored proc it executes for less than 10 seconds but when i try to call that stored proc using the job this is what happen
it just keep on in progress state but nothing happens, looks like it got stuck in that state.
Best Answer
If you are using Windows Authentication in the linked server setup it will not work using the SQL Server agent even if you have Kerberos correctly setup. You can configure the linked server to use SQL authentication for those connections but I would recommend to create a SSIS package for this as the authentication will be easier to configure.
Can you please post the linked server configuration for further information?