May 13, 2002 at 9:35 am
Hello, I´m new at this and I´m not sure about if I´ll do it correctly.
I have this problem:
1) I had an old server with Windows NT Server and SQL Server 7.0.
I changed for another one with W2k Server, and I restored all
the databases, users and permissions.
2) In the same domain, I have a PC with Windows NT, which has an
application under Visual Basic 6.0 that use one of the databases
and makes INSERT every 2 or 3 minutes (more or less).
3) I use objects ADODB.Connection to make an Execute with the
INSERT sentence. Sometimes, I have to repeat three or four times
the same sentence until it has succes.
4) If I run the SQL Analyzer in the server, this error never
happens.
5) If I change the client, and I put another PC with W2k Prof., the
problem happens with less frequency, and never if the SQL Analyzer
is running.
¿What's wrong in this system?
¿Can I make something to take information about the error without
use the SQL Analyzer?
Please it´s very urgent, and I´ll be thank you to everybody that
answer me.
Thank you so much.
May 13, 2002 at 9:44 am
What is you error message when it fails and you have to retry?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 13, 2002 at 9:56 am
quote:
What is you error message when it fails and you have to retry?"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
I don´t have error message when it fails.
The user of the application has orders to try twice INSERT.
If the first INSERT fail, he could make another one without
error message. If the second INSERT fails too, he has to repeat
until receive an error message.
May 13, 2002 at 10:41 am
Try then doing a trace with profiler to see if you can catch what is going on or if the problem is trying to connection to the DB.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 15, 2002 at 3:13 am
I use a public (and global) ADODB.Connection in the application, which
is always open.
Looking for a help, I found the "ExecuteComplete" event, and I inserted
the sentences into the code. I could check that:
- After the Execute, the event ExecuteComplete is called, but always
return the status adStatusOK, even the Insert fails.
- I don´t find the error code in the SQL server Analyzer, cause when the
trace is running, that error never occurs.
I think that the problem could be cause the different priorities under
SQL engine make the insert wait in the background until it´s missing.
Is it possible?
I use msado21, cause all the others computers in the system use that
version. Could it be causing errors between W2k and SQL 7.0?
May 15, 2002 at 9:45 am
Not sure where to go from here, cannot find anything reported like this before and cannot think of anything specific. Can you post a section of relevant code maybe something will jump out.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 15, 2002 at 10:44 am
Of course!!! Thank you so much for your time 😉
Inside global module:
Global DBasePara As New ADODB.Connection
...
DBasePara.ConnectionTimeout = 60
DBasePara.CommandTimeout = 0
DBasePara.Provider = "sqloledb"
DBasePara.Properties("Data Source").Value = Servidor_
DBasePara.Properties("Initial Catalog").Value = "Almacen"
DBasePara.Properties("Integrated Security").Value = "SSPI"
DBasePara.Open ' Without problems or errors
Another form:
...
Dim WithEvents conAux As ADODB.Connection
...
Set conAux = DBasePara
DBasePara.Execute "INSERT INTO TABLA (Fecha, Linea, HoraProd) VALUES ('" + _ fecAux_ + "', " + CStr(linea_) + "," + CStr(hora_) + ")"
Set conAux = Nothing
...
Private Sub conAux_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.error, _
adStatus As ADODB.EventStatusEnum, _
ByVal pCommand As ADODB.Command, _
ByVal pRecordset As ADODB.Recordset, _
ByVal pConnection As ADODB.Connection)
If (adStatus <> adStatusOK) Then
pantallas_.Saca_ventana pError.Description, QuietoParao
End If
End Sub
May 17, 2002 at 6:02 am
What ADO object Library are you using and have you tried to install the current MDAC to see if this makes a difference. I see no issue with the code right off so I am thinking may be an issue. Also do you have the VB 6 compiler on the NT machine? If so and you did not create on that box check your references may be using a different ADO object library.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 17, 2002 at 8:45 am
Just a thought, does this table have a trigger on it?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 20, 2002 at 10:27 am
Hi again!
I think I´ve find the fail: All it´s cause the new server is faster and better than old, and when the Analyzer is running, makes the system more slowly.
I have three triggers in this table (one for each action), and the code in the Insert trigger makes another insert in the historical table. That table has two fields like index (Actual date and a number that can be repeat). The code is similar to:
MyTable INSERT TRIGGER:
...
Insert ... HISTORICO ... (1)
Exec MyStoreProc
...
MyStoreProc:
...
Update ... MyTable ...
...
MyTable UPDATE TRIGGER:
...
Insert ... HISTORICO ... (2)
...
If the system is fast, the second Insert can have the same index than first one, and causes a fail.
Can the SQL makes a rollback by defect? Is it possible?
May 20, 2002 at 11:51 am
Rollback do occurr by default. You may nee to look a bit at your logic to see if you can combine your triggers and SP into one action.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply