June 21, 2005 at 3:50 pm
Hi,
I have the following error on XP Pro machines (all updates) using Access to run the following procedures: The aim of the routine is to access a table on SQL 2000 lock it and increment the next number. The procedues runs fine on Win2K machines but stops at about the 3969 mark in XP for some unknown reason. Anyone shed any light - It'd be much appreciated.
TIA Richard
Public Sub MakeSORS()
Dim i, j As Long
Dim T As Double
Dim MOrd As Long
Debug.Print "running...."
T = Timer
For i = 1 To 100000
MOrd = getnext1("lastsor")
Debug.Print i
Next i
T = Timer - T
Debug.Print "done..time to add 100000 records = " & T
End Sub
Public Function getnext1(MType As String)
Dim CMD As New ADODB.Command
Dim CONN As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim prmout As ADODB.Parameter
Dim prmin As ADODB.Parameter
CONN.Open "DSN=MWDATA;"
CMD.ActiveConnection = CONN
CMD.CommandText = "getnext"
CMD.CommandType = adCmdStoredProc
Set prmin = CMD.CreateParameter("MLast", adChar, adParamInput, 12)
CMD.Parameters.Append prmin
prmin.Value = MType
Set prmout = CMD.CreateParameter("LASTNUM", adInteger, adParamOutput)
CMD.Parameters.Append prmout
CMD.Execute
getnext1 = prmout.Value
CONN.Close
Set rst = Nothing
Set CMD = Nothing
Set CONN = Nothing
Set prmout = Nothing
Set prmin = Nothing
End Function
CREATE PROCEDURE getnext (@MLast char(12) , @MLastNum int OUTPUT) AS
--Declare @LastNum int
set nocount on
begin transaction
if @MLast='LASTSOR'
begin
Select @MLastNum=LASTSOR From parmnum with (tablockx)
Select @MLastNum = @MlastNum + 1
Update parmnum Set LASTSOR = @MLastNum
select LASTSOR from parmnum
end
commit transaction
GO
June 21, 2005 at 10:51 pm
Error Message:
Run-Time Error '-2147467259 (80004005)'
[Microsoft][ODBC SQL Serverver Driver][TCP/IP Sockets]
SQL Server does not exist or Access Denied.
This occus after it has entered just short of 4000 entries. It only happens
on an XP machine - Not Win2K. Win2K machines are stress testing this App at
same time and no problem with Logs etc. All service packs applied.
Thanks Richard
June 25, 2005 at 4:46 am
Problem solved - amazed with 93 views no replies received.
The solution is that when a port is opened and closed quickly it remains out of use for 240 seconds. Also ODBC uses ports 10xx-5000 so under 4000 ports available. When stress testing you can easily use up the 4000 ports within the timescale for release. Solution is to expand ports available and /or reduce Wait time on used ports.
I learnt a bit in finding the solution.
Richard
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply