December 28, 2011 at 10:50 am
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
December 28, 2011 at 1:24 pm
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.
January 5, 2012 at 5:01 am
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.
January 11, 2012 at 11:16 am
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
January 11, 2012 at 11:33 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply