Hello, first of all I want to say I am at the end of my rope. So ANY helpful information is appreciated. I am familiar with SQL, but by no means proficient. With that being said, lets get into it.
So every day a user runs a Program in Access, this access database interacts with sql. However since 03/20 the program has failed constantly, always in the same place. Below I will show the vba, and the SQL side of things.
VBA side
'Mark new AR on list as new
'Took out code for non-existent AR
'If IsDate(txtLastDate.Value) Then
'DoCmd.RunSQL "UPDATE ADData SET ADData.New = True WHERE (((ADData.ADGroup) Not In (SELECT ADData.ADGroup FROM ADData WHERE (((ADData.Date)=#" & txtLastDate.Value & "#));)) AND ((ADData.Date)=#" & Cal.Value & "#) And ((ADData.SystemType)=""AR""));"
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "sp_ARUpdateNewAsNew"
.CommandType = adCmdStoredProc
'Parameters.refresh
.Parameters("@lastdate") = txtLastDate.Value
.Parameters("@cal") = Cal.Value
End With
cmd.Execute
The process fails on cmd.execute. I gives error "Run-Time error'2147217871' Timeout Expired.
Although the SQL statement is commented above, I am pulling this directly from the SQL management studio this is the Stored Procedure.
USE [Trouble_List]
GO
/****** Object: StoredProcedure [dbo].[sp_ARUpdateNewAsNew] Script Date: 06/25/2012 10:00:32 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_ARUpdateNewAsNew](
@lastdate datetime,
@cal datetime
)
AS
UPDATE dbo.ADData
SET New = 1
WHERE (ADGroup NOT IN
(SELECT ADData.ADGroup
FROM ADData
WHERE (((ADData.Date) = @LastDate)))) AND (Date = @Cal) AND (SystemType = 'AR') AND (Importing = 1)
What I find interesting is this process has been failing since 3/20. There was a database restore done on 3/19 at 3am. I find it interesting, but don't know if it is related.
Timeout Expired
Posted - 06/25/2012 : 11:34:06
