VBA ConnectionTimeout problems

[Matt.]

Hi all!

I'm experiencing a very frustrating Timeout problem. The SQL Server is very
busy, getting data delivered to it from 20 die cast machine PLCs using
RSLinx. But it has over 2 GB of RAM, so it should be able to handle this.
As you'll note, I've tried editing the ConnectionTimeout property, but cell
J1 always reports 30 or 31 seconds as th elapsed time.

Server is MS SQL Server 2000
Compaq Proliant ML 320

Client is Excel 2000.

Network is standard TCP/IP over ethernet.

Any ideas greatly appreciated.

cheers,
Matt.


Sub Main()
''''''''''''''''''''''''''''''''''''''''
' Author: Matt Benvenuti
' Date delivered: 2004/09/02
' Primary user: Razia Dinath for recording Production information
'
''''''''''''''''''''''''''''''''''''''''
' This function is called from a button on DCMData worksheet.
'
' The purpose of this function is to return data from the MS SQL Server
related to
' DCM shot counts and run time, as measured by the tools in place to
perform these tasks. The
' function works as follows:
'
' 1) open a connection to the SQL Server and populate a recordset for
' a production date the user specifies;
' 2) copy that data to a worksheet called DCMData
' 3) close the workbook, and end execution
'''''''''''''''''''''''''''''''''''''''''
On Error GoTo Err_Sub

Dim strConnection As String
Dim strSQL As String
Dim oWorkBook As Workbook 'workbook connection
Dim connDB As New ADODB.Connection 'connection to SQL Server
database
Dim rsDCMDataDays As ADODB.Recordset 'recordset for DCM data
Dim rsDCMDataNights As ADODB.Recordset 'recordset for DCM data
Dim rsDCMTimes As ADODB.Recordset

Dim ExcelDate As String
Dim ExcelDateNight As Date
Dim strTimeDay As String
Dim strTimeAft As String
Dim strTimeNight As String
Dim strShiftProductionStart As String
Dim strShiftProductionEnd As String
Dim intHeaderCol As Integer

Dim intDCM As Integer
Dim intShiftCount As Integer

Dim intDowntimeInterval As Integer

Dim strMsg As String 'error message variables
Dim datStart As Date
Dim intElapse As Integer
'''''''''''''''''''''''''''''''''''''''''
' set downtime interval to 4 minutes (240 seconds)
'''''''''''''''''''''''''''''''''''''''''
intDowntimeInterval = 240

'''''''''''''''''''''''''''''''''''''''''
' set shift times
'''''''''''''''''''''''''''''''''''''''''
strTimeDay = "07:00:00"
strTimeAft = "15:00:00"
strTimeNight = "23:00:00"

ExcelDate = Format(Date - 1, "yyyy-mm-dd")
ExcelDate = InputBox("Enter the Production date: ", "Production Date
box", ExcelDate)
If ExcelDate = "" Then ' user pressed Cancel button
GoTo Exit_Sub
End If
' ExcelDateNight = DateAdd("d", -1, ExcelDate) 'night shift starts day
before "production date"
ExcelDateNight = DateAdd("d", 1, ExcelDate) 'night shift starts day
after "production date"

'''''''''''''''''''''''''''''''''''''''''
' open the connection to the SQL Server
'''''''''''''''''''''''''''''''''''''''''
datStart = Now()
connDB.ConnectionString = "Provider=sqloledb;Data Source=H2242-PLC;" _
& "Initial Catalog=*****;User Id=*****;Password=*****"
connDB.ConnectionTimeout = 120
connDB.Open

intShiftCount = 1
Sheets("DCMData").Range("A1:I30").ClearContents
Sheets("DCMData").Range("A1:I30").ClearFormats

While intShiftCount <= 3
Select Case intShiftCount
Case 1
strShiftProductionStart = Format(ExcelDate & " " &
strTimeDay, "yyyy-mm-dd hh:mm:ss")
strShiftProductionEnd = Format(ExcelDate & " " & strTimeAft,
"yyyy-mm-dd hh:mm:ss")
intHeaderCol = 1
Case 2
strShiftProductionStart = Format(ExcelDate & " " &
strTimeAft, "yyyy-mm-dd hh:mm:ss")
strShiftProductionEnd = Format(ExcelDate & " " &
strTimeNight, "yyyy-mm-dd hh:mm:ss")
intHeaderCol = 4
Case 3
strShiftProductionStart = Format(ExcelDate & " " &
strTimeNight, "yyyy-mm-dd hh:mm:ss")
strShiftProductionEnd = Format(ExcelDateNight & " " &
strTimeDay, "yyyy-mm-dd hh:mm:ss")
intHeaderCol = 7
End Select

Call ShiftInfo(strShiftProductionStart, strShiftProductionEnd,
intDowntimeInterval, intHeaderCol, connDB, ExcelDate)
intShiftCount = intShiftCount + 1
Wend

Exit_Sub:
Set connDB = Nothing
Set oWorkBook = Nothing
Exit Sub

Err_Sub:
intElapse = (Now() - datStart) * 24 * 60 * 60
Cells(1, 10).Value = intElapse
strMsg = "Error # " & Str(Err.Number) & " was generated by " &
Err.Source & Chr(13) & Err.Description
MsgBox strMsg, , "Error", Err.HelpFile, Err.HelpContext
' MsgBox Err.Number & Err.Description
Resume Exit_Sub

End Sub

 

[keepITcool]

Matt,

Why not create a simple (parameter) QueryTable?
your only input is the production date, and the output goes to a fixed
range is fixed anyway.

Define an inputcell, put some data validation on it to accpet valid
dates..
Create a dataquery with the productiondate as parameter,
assign the cell to the parameter.. tell the query to autoupdate on a
change of the cell and ..

Done!




Further: the ShiftInfo procedure is missing.. and several variables
(the recordsets) appear to be unused.

but more importantly you do not close the connection, which is
definitely not recommended...

before setting the oConnDB to nothing you should close it.
(unless you do so in the missing function)


Select Case oconnDB.State
Case 0 'it's closed
Case 1 'it's open
oconDB.close
Case 2 'it's trying to connect...
msgbox "Houston?.. We have a problem!"
End Select




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Matt. wrote :

 

[Matt.]

Matt,

Why not create a simple (parameter) QueryTable?
your only input is the production date, and the output goes to a fixed
range is fixed anyway.

Define an inputcell, put some data validation on it to accpet valid
dates..
Create a dataquery with the productiondate as parameter,
assign the cell to the parameter.. tell the query to autoupdate on a
change of the cell and ..

Done!




Further: the ShiftInfo procedure is missing.. and several variables
(the recordsets) appear to be unused.

but more importantly you do not close the connection, which is
definitely not recommended...

before setting the oConnDB to nothing you should close it.
(unless you do so in the missing function)


Select Case oconnDB.State
Case 0 'it's closed
Case 1 'it's open
oconDB.close
Case 2 'it's trying to connect...
msgbox "Houston?.. We have a problem!"
End Select

~~~~~~~~~~~`snip~~~~~~~~~~~~

I've never used a QueryTable before. But I'll look into it. I suspect my
timeout problem may still occur, though.

Thanks for your suggestion about checking the state, I hadn't noticed it.
I've implemented. I didn't include the other function because I didn't
think it was relevant to the problem, and I didn't want to hang out ALL of
my dirty laundry

The number of rows returned can vary, depending on if the DCM was down for
an entire shift and therefore didn't put any data into the database for the
period in question.

And I still don't know why the ConnectionTimeout is not functioning
properly.

cheers,
Matt.

 

[Matt.]

Hi all!

I'm experiencing a very frustrating Timeout problem. The SQL Server is very
busy, getting data delivered to it from 20 die cast machine PLCs using
RSLinx. But it has over 2 GB of RAM, so it should be able to handle this.
As you'll note, I've tried editing the ConnectionTimeout property, but cell
J1 always reports 30 or 31 seconds as th elapsed time.

Server is MS SQL Server 2000
Compaq Proliant ML 320

Client is Excel 2000.

Network is standard TCP/IP over ethernet.

Any ideas greatly appreciated.

cheers,
Matt.

~~~~~~~~~~~~~~~~~~~~~~snip~~~~~~~~~~~~~~~~~~~~~~~~~~~

Another point of information.

I've and ASP Web Page that connects to the same database (different table)
on the server, and it connects fine. Refreshes fine. No issues. So I know
the server is responsive.

cheers,
Matt.

 

[Jamie Collins]

I didn't include the other function because I didn't
think it was relevant to the problem, and I didn't want to hang out ALL of
my dirty laundry

Sorry to appear dense but I haven't grasped the problem: what happens
where and when? Does the connection timeout? Do you get an error? Are
you actually using the connection? I think you may have snipped too
much code.

Jamie.

--

 

[Matt.]

Hi Jamie!

The error is a Timeout error (-2147217871) at the line "connDB.Open". So
the connection is never established. And as I mentioned in a "more info"
post, an ASP page that hits the same database connects with no problem.

cheers,
Matt.

 

[Jamie Collins]

The error is a Timeout error (-2147217871) at the line "connDB.Open". So
the connection is never established. And as I mentioned in a "more info"
post, an ASP page that hits the same database connects with no problem.

I that case, I don't think there is anything in your code except the
connection string that will affect the ability to connect. Apart from
querying a different table, what is your ASP connection code doing
that your VBA code is not?

Jamie.

--

 

[Matt.]

I that case, I don't think there is anything in your code except the
connection string that will affect the ability to connect. Apart from
querying a different table, what is your ASP connection code doing
that your VBA code is not?

Jamie.

--

Hi Jamie!

That's why I'm so confused. Nothing is different (code is below). I've
rewritten the VBA so that it bangs the server 10 times before I allow it to
actually fail. That seems to work but is painfully slow. Any other advice
is greatly appreciated.

cheers,
Matt.

~~~~~~~~~~~~~~~~~~~~~~~~
ASP Code snippet:
<%
set connDB = Server.CreateObject("ADODB.Connection")

strConnection = "Provider=sqloledb;Data Source=H2242-PLC;Initial
Catalog=Amcan;User Id=******;Password=******"

connDB.Open strConnection


strSQL = "select distinct [CASTER].[MACHINE_NUMBER], " _

& "[CASTER].[SERIAL_NUMBER], [CASTER].[DAILY_PART_COUNT], " _

& "[CASTER].[EVENT_TIME], [CASTER].[ACTUAL_SLOW_SHOT_VELOCITY], " _

& "[CASTER].[SLOW_SHOT_VELOCITY_START_MEASURE],
[CASTER].[SLOW_SHOT_VELOCITY_END_MEASURE], " _

& "[CASTER].[ACTUAL_INTERMEDIATE_SHOT_VELOCITY], " _

& "[CASTER].[INTERMEDIATE_SHOT_VELOCITY_START_MEASURE], " _

& "[CASTER].[INTERMEDIATE_SHOT_VELOCITY_END_MEASURE], " _

& "[CASTER].[ACTUAL_FAST_SHOT_VELOCITY],
[CASTER].[FAST_SHOT_VELOCITY_START_MEASURE], " _

& "[CASTER].[FAST_SHOT_VELOCITY_END_MEASURE],
[CASTER].[FINAL_INTENSIFIER_PRESSURE], " _

& "[CASTER].[INTENSIFIER_PRESSURE_LOWER_TIME_WINDOW],
[CASTER].[INTENSIFIER_PRESSURE_UPPER_TIME_WINDOW], " _

& "[CASTER].[INTENSIFIER_PRESSURE_RISE_TIME],
[CASTER].[INTENSIFIER_PRESSURE_TO_RISE_TO], " _

& "[CASTER].[DWELL_TIMER_PRESET], " _

& "[CASTER].[TIE_BAR_#1_MECHANICAL_TONNAGE],
[CASTER].[TIE_BAR_#2_MECHANICAL_TONNAGE], " _

& "[CASTER].[TIE_BAR_#3_MECHANICAL_TONNAGE],
[CASTER].[TIE_BAR_#4_MECHANICAL_TONNAGE], " _

& "[CASTER].[FURNACE_TEMPERATURE], " _

& "[CASTER].[BISCUIT_LENGTH_LOW], [CASTER].[BISCUIT_LENGTH_GOOD] , " _

& "[CASTER].[BISCUIT_LENGTH_HIGH] " _

& "from [CASTER] " _

& "where [CASTER].[EVENT_TIME]>= dateadd(day, -1, getdate()) and
[CASTER].[MACHINE_NUMBER]>'24' " _

& "order by [CASTER].[MACHINE_NUMBER], [CASTER].[SERIAL_NUMBER],
[CASTER].[DAILY_PART_COUNT], " _

& "[CASTER].[EVENT_TIME]"

' Response.Write(strSQL)

' Response.End

Set rsIndustrial = connDB.Execute(strSQL)

%>

 

[Jamie Collins]

That's why I'm so confused. Nothing is different (code is below).

strConnection = "Provider=sqloledb;Data Source=H2242-PLC;Initial
Catalog=Amcan;User Id=******;Password=******"

Initial catalog is the same? Anything else going on e.g.
connection/resource pooling, threading, etc?

Jamie.

--

 

[Matt.]

Initial catalog is the same? Anything else going on e.g.
connection/resource pooling, threading, etc?

Jamie.

--

Thanks for your time, Jamie. I really appreciate it.

The Server is receiving data from 20 die cast machines PLCs, via 2 different
RSLinx boxes. However, I've checked CPU usage, and it hovers at around 20 -
30% (with an occasional spike to 90+% - 3 to 6 times per "window" for lack
of a better term). There is no other application requesting data from the
server. And nothing esle communicating at all with it.

Sorry I can't help with the problem solving more than I am.

cheers,
Matt.

 

[Matt.]

Initial catalog is the same? Anything else going on e.g.
connection/resource pooling, threading, etc?

Jamie.

--

Hi Jamie!

I did reply to this question, but it seems to have gotten lost.

There is an RSLinx connection to 20 PLCs controlling die casting machines
that fire on average 40 - 60 times an hour.

That is the only other activity on the server.

cheers,
Matt.

 

[Jamie Collins]

I did reply to this question, but it seems to have gotten lost.

There is an RSLinx connection to 20 PLCs controlling die casting machines
that fire on average 40 - 60 times an hour.

That is the only other activity on the server.

Sorry, I can't think of anything further :-(

Jamie.

--