Timeout Expired - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Timeout Expired

Author

Topic

redbull
Starting Member

2 Posts

Posted - 2012-06-25 : 11:34:06

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.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-25 : 11:48:00

How long dodes it take to fail?
Run the SP frrom management studio and see if it succeeds and how long it takes would probably be the first step.
Could be a lot of things causing the issue.
Next most likely I guess would be a permissions problem.

WOuld be surprised if this failure and the restore were not related. Maybe the restore was done because someone was doing something that failed and the result is a lost index or corrupt table.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

redbull
Starting Member

2 Posts

Posted - 2012-06-25 : 11:53:33

Alright, I will do that and relay my findings. I appreciate your time and help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources