Stored Procedure with Openrowset stops working.

Good Afternoon,

I have two stored procedures that are executed via SQL Server Agent.
These jobs run fine for weeks at a time then stop working. When
working the stored procedures execute in 1 to 3 seconds each.
Yesterday when running the stored procedures manually they never
complete from the query window. No errors were generated.

If anyone has any ideas on how to resolve this it would be greatly
appreciated!

Thanks, Jody

(Below are the stored procedures being executed.)

Stored Procedure 1

ALTER PROCEDURE [dbo].[spPsExtractAgentGroups]
AS
BEGIN
BEGIN TRANSACTION
/* Create Temp Table */
DECLARE @Temp TABLE
(acd_num int
,group_name varchar(50)
,agent_id int
)

/* Select Data from the CMS and Insert into Temp Table */
INSERT @Temp
(acd_num
,group_name
,agent_id)
acd_no,TRIM(item_name) AS item_name,
value IS NOT NULL')

SELECT *
FROM OPENROWSET('MSDASQL',
'DSN=ATL_PRIMARY;UID=symvista;PWD=vista1',
'select "1" AS acd_no,TRIM(item_name) AS item_name,
TRIM(value) AS value from agroups where item_type="group" AND value
IS NOT NULL')
/* Delete all data from tbl_agt_groups */
TRUNCATE TABLE tbl_agt_groups

/* Select data from Temp Table and insert into tbl_agt_groups */
INSERT tbl_agt_groups
(thekey
,acd_num
,group_name
,agent_id
,add_date)
SELECT DISTINCT CAST(acd_num AS varchar(2)) + '' + group_name + '' +
CAST(agent_id AS varchar(7)) AS thekey,
acd_num,group_name,agent_id,CAST(DATEPART(mm,GETDATE()) AS
varchar(2)) + '/' +
CAST(DATEPART(dd,GETDATE()) AS varchar(2)) + '/' +
CAST(DATEPART(yy,GETDATE()) AS varchar(4)) AS row_date FROM @Temp
/*WHERE (LEN(agent_id) = 7)*/

IF @@ERROR <>0
BEGIN
Rollback Transaction
RAISERROR ('Stored procedure spPsExtractAgentGroups failed,
Transaction rolled back..',16,1)WITH LOG
END
ELSE
Commit Transaction
END


Stored Procedure 2

ALTER PROCEDURE [dbo].[spPsExtractSynonyms]

AS
BEGIN

SET NOCOUNT ON;

MERGE dbo.cms_synonyms AS sym
USING (Select * from OPENQUERY(ATL_PRIMARY,'Select * from
root.synonyms WHERE item_name Not Like ''zz%'' and item_type in
(''agname'',''split'',''tkgrp'')')) AS cms
(acd_no, item_type,item_name, value, descr, standard)
ON (cms.item_name = sym.item_name and cms.item_type = sym.item_type
and cms.value = sym.value)
WHEN MATCHED THEN UPDATE SET sym.value = cms.value, sym.descr cms.descr, sym.standard = cms.standard, sym.update_date GetDate()
WHEN NOT MATCHED THEN
INSERT(acd_no, item_type,item_name, value, descr,
[standard],insert_date, update_date)
VALUES(cms.acd_no, cms.item_type, cms.item_name, cms.value,
cms.descr, cms.[standard],getdate(), getdate());


DELETE FROM dbo.cms_synonyms
where update_date < (Select Max(update_date) from cms_synonyms)

END