Re: Problem with program hanging - SQLServer issue?


"Martin" <martinvalley@xxxxxxxxxxx> wrote in message
news:vji1a194b9sso9sjgj0qjjak5mlsfjlo5g@xxxxxxxxxx
> I'm having a problem in my VB6 program in that it will "stall" or
> "hang" (like it's in an endless loop) from time to time, seemingly at
> random. I am highly suspicious that the problem is with some async
> queries the program makes to an SQL Server (2000) database.
>
> I'm using the following code in various places in the program:
>
> Public WithEvents SQLServerCN As ADODB.Connection
>
> CS$ = "Provider=SQLOLEDB.1; Persist Security Info=False;"
> CS$ = CS$ & "UID=MyUserID; pwd=MyPassword;"
> CS$ = CS$ & "Initial Catalog=MyDatabaseName;"
> CS$ = CS$ & "Data Source=TheServerName"
>
> Set SQLServerCN = New ADODB.Connection
> SQLServerCN.CursorLocation = adUseClient
> SQLServerCN.ConnectionTimeout = 3
> SQLServerCN.Open CS$, , , adAsyncConnect
>
>
> I execute some stored procedures like so:
> Dim rsX As New ADODB.Recordset
> rsX.Open "LookupBarcode 12345678", SQLServerCN, adOpenStatic,
> adLockReadOnly, adAsyncExecute
>
> And, like this:
> SP$ = "CheckOrder @OrderNumber=" & CON$ & ",@Status=0"
> SQLServerCN.Execute SP$, , adCmdText
>
> And a couple of SQL statements like so:
> SQL$ = "UPDATE MyTable SET lStatus=110, lDestinationID=123 WHERE
> strBarcode='12345678';"
> SQLServerCN.Execute SQL$, , adCmdText + adExecuteNoRecords
>
> In the SQLServerCN_ExecuteComplete event, I check for errors and do
> various other things, depending on which stored procedure or statement
> was executed.
>
> It's all pretty straight-forward and 99.9% of the time it seems to
> work ok.
>
> Do any of you have any ideas that I might investigate as to what might
> be happening that would cause my program to literally stop executing
> for a few seconds? I thought the fact that I was doing things
> asynchronously would allow me to keep running even if a problem was
> encountered on the server side - is this not true?
>


SQLServerCN.Execute is not asynchronous.


-Mark


.