Timed Out when calling from VBA function

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Timed Out when calling from VBA function

Timed Out when calling from VBA function

Rate Topic

Display Mode

Topic Options

Author

Message

mramey

Posted Wednesday, December 28, 2011 10:50 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 10:04 AM
Points: 6, Visits: 59

I have a vba function below. I call a stored procedure but it times out. Even if I set the timed out to zero it will hang forever. This function worked fine for months up until yesterday. Nothing has changed in any environment that I can tell. If I run the stored procedure directly in SSMS and set the variables as static it runs in less than a second. Can anyone tell me what might be happening in my function?

Thanks

Code:

Public Function AddNewJob(JOB As String) 'This Function will Import the New Job data direct from Hagen Tables.
Dim CN As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set CN = New ADODB.Connection
With CN
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = DLookup("Source", "tbl_Connection")
.Properties("User ID").Value = DLookup("UserID", "tbl_Connection")
.Properties("Password").Value = DLookup("Password", "tbl_Connection")
.Properties("Initial Catalog").Value = DLookup("Catalog", "tbl_Connection")
.Open
End With

strSQL = "Exec ImportHagenJobInfoToOneBase '" & JOB & "'"
CN.Execute strSQL
CN.Close
Set CN = Nothing

Here's the Stored Procedure being called:

USE [OneBase]
GO
/****** Object: StoredProcedure [dbo].[ImportHagenJobInfoToOneBase] Script Date: 12/27/2011 17:04:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Michael Ramey>
-- Create date: <Create Date,08/30/2011,>
-- Description: <Description,This Procedure Imports Hagen Job Data to JOT Tables,>
-- =============================================
ALTER PROCEDURE [dbo].[ImportHagenJobInfoToOneBase](@Job VARCHAR(16))

AS
DECLARE
@strQuery NVARCHAR(4000),
@Sys VARCHAR(4)

SET @Sys = 'R1'

BEGIN
SET @strQuery = 'INSERT INTO tbl_Job_Information(JobNbr, JobName, CustomerID, Drop_Date, Sales_Rep_ID, CSR, UpdateDate, CreatedBy, UpdatedBy, CreateDate, UpdateTime, HQN)
SELECT TOP 1 * FROM OPENQUERY([HAGEN], ''SELECT "Job-ID","Job-Desc","Cust-ID-Ordered-by", "Date-Promised", "Sales-Rep-ID", "CSR-ID",
"Update-date", "Created-By", "Update-by", "Created-Date", "Update-Time","Last-Estimate-ID"
FROM PUB.Job
WHERE "System-ID" = '''''+ @Sys +''''' AND "Job-ID" =''''' + @Job + ''')'

EXEC sp_executesql @strQuery

END

Post #1227475

toddasd

Posted Wednesday, December 28, 2011 1:24 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:42 PM
Points: 477, Visits: 3,649

Something to try: place SET NOCOUNT ON; after procedure declaration.

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

Post #1227541

Brian Kukowski

Posted Thursday, January 05, 2012 5:01 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:52 AM
Points: 421, Visits: 963

If you haven't already done so, I'd recommend some basic checks

(1) Verify that the code is actually connecting to SQL Server. Have you tried stepping through the code, and seen it get past the entire With/End With block of code?

(2) If a connection is being made, see if the code is being blocked by another SQL Server process.

There's various ways to check for (2). I use the sp_whoisactive stored procedure, written by Adam Machanic, but you can simply execute the built-in sp_who2 stored procedure.

Brian Kukowski

Post #1230635

stephen99999

Posted Wednesday, January 11, 2012 11:15 AM

Valued Member

Group: General Forum Members
Last Login: Sunday, November 18, 2012 10:58 PM
Points: 63, Visits: 216

Not a VB dev, but in C# we set the SqlCommand.Timeout. Most likely it is the connection or command in your VB code that is actually Timing out, not the stored procedure.

Also, I would think you wouldn't set your Timeout = 0, but actually increase it since usually everywhere this is used, Timeout represents the length in time (seconds or milliseconds) a process will run for until it closes.

Stephen

Post #1234227

mramey

Posted Wednesday, January 11, 2012 11:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 10:04 AM
Points: 6, Visits: 59

Thanks for everyone's suggestions. I found the problem to be my connection string. I tweeked my connection credentials and now it runs well.

Mike

Post #1234242

« Prev Topic | Next Topic »

Permissions