Difficult VB/MTS/SQL Error
| Author |
Message |
|
Jamie Schatt
#1 / 12
|
 Difficult VB/MTS/SQL Error Sorry for cross-posting, but I this applies to VB, MTS, and SQL Server. I have a VB6 EXE that calls a VB6 ActiveX DLL that is registered in MTS (and
is set as "requires transaction"). The DLL has a method (code listed below)
that starts a transaction, loops through an array of strings using an ADO
Command object to insert each string into a table in the database, and ends
the transaction. If a duplicate string is encountered, the database rejects
the string, an error is raised, and the method continues with the next
string in the array using a "Resume Next" statement.
My test data includes an array of about 1200 strings, and everything works
great, EXCEPT when I try to insert the same array more than once (which will
result in every string being a duplicate). As expected, each row is rejected
(SQL Server error code=2627), and the method completes successfully (even
though no rows are successfully inserted--this is a perfectly OK scenario).
However, after the transaction is completed, an error is received by the EXE
from the method call, the database log files fill up and the SQL Server must
be restated. FYI, when I debug the whole thing, there are no errors anywhere
in the method--everything goes OK. But upon returning from the method call,
the generic "Method ~ of object ~ failed." error message is received.
I am not sure what's happening here. It seems that the insert is freaking
out the application and the database when the insert results in many
duplicates (maybe some kind of error overhead). My next course of action is
to remove the component from MTS, followed by reducing the number of lines
in the test data.
Thanks in advance!
Jamie Schatte
Millennia Systems
www.millenniasystems.com
****************************************
Public Sub DSMMeterReadCSVFile(ByVal ConnectionString As String, _
ByRef CSVLines() As String)
' --------------------------------------------------------------------------
-
' This prcedure processes an entire CSV file as an array of file lines.
' --------------------------------------------------------------------------
-
On Error GoTo HandleError
Dim adoConnection As ADODB.Connection
Dim adoCmdRegisterDataAdd As ADODB.Command
Dim dsadoRegisterDataRec As DSADO.RegisterDataRec
Dim mtsContext As ObjectContext
Dim AlarmCount As Integer
Dim Alarms As String
Dim Alarm As String
Dim CSVLine As String
Dim I As Integer
' Continue the current transaction
Set mtsContext = GetObjectContext
' Setup the database connection
Set adoConnection = New ADODB.Connection
adoConnection.Open ConnectionString
' Setup the command to add records to the REGISTER_DATA table
Set adoCmdRegisterDataAdd = New ADODB.Command
With adoCmdRegisterDataAdd
Set .ActiveConnection = adoConnection
.CommandType = adCmdText
.CommandText = " INSERT INTO dbo.REGISTER_DATA " & _
" (MeterID, " & _
" ChannelID, " & _
" TimeStamp, " & _
" RegisterValue, " & _
" ReadSource, " & _
" ReadType, " & _
" AlarmFlag) " & _
"VALUES (?, " & _
" ?, " & _
" ?, " & _
" ?, " & _
" ?, " & _
" ?, " & _
" ?) "
.CreateParameter , adInteger
.CreateParameter , adInteger
.CreateParameter , adChar, , 30
.CreateParameter , adDouble
.CreateParameter , adChar, , 1
.CreateParameter , adChar, , 1
.CreateParameter , adChar, , 1
End With
' Parse the file lines
For I = LBound(CSVLines) To UBound(CSVLines)
' Get the next line in the file
CSVLine = CSVLines(I)
' Process the meter reading information for the meter
With dsadoRegisterDataRec
' Format the meter reading data structure
.MeterID = Val(DSCParseLine(CSVLine, 1))
.ChannelID = Val(DSCParseLine(CSVLine, 2))
.TimeStamp = DSCFormatTimeStamp(DSCParseLine(CSVLine, 3))
.ReadSource = DSCParseLine(CSVLine, 4)
.RegisterValue = Val(DSCParseLine(CSVLine, 5))
.ReadType = ""
Alarms = DSCParseLine(CSVLine, 6)
If Len(Alarms) = 0 Then
.AlarmFlag = "N"
Else
.AlarmFlag = "Y"
End If
' Add the meter reading to the REGISTER_DATA table
adoCmdRegisterDataAdd.Parameters(0).Value = .MeterID
adoCmdRegisterDataAdd.Parameters(1).Value = .ChannelID
adoCmdRegisterDataAdd.Parameters(2).Value = .TimeStamp
adoCmdRegisterDataAdd.Parameters(3).Value = .RegisterValue
adoCmdRegisterDataAdd.Parameters(4).Value = .ReadSource
adoCmdRegisterDataAdd.Parameters(5).Value = .ReadType
adoCmdRegisterDataAdd.Parameters(6).Value = .AlarmFlag
adoCmdRegisterDataAdd.Execute
End With
Next I
' Free any objects that were allocated during the function
If adoConnection.State <> adStateClosed Then adoConnection.Close
If Not adoConnection Is Nothing Then Set adoConnection = Nothing
If Not adoCmdRegisterDataAdd Is Nothing Then Set adoCmdRegisterDataAdd =
Nothing
' Continue the current the transaction
mtsContext.SetComplete
If Not mtsContext Is Nothing Then Set mtsContext = Nothing
Exit Sub
HandleError:
' Continue if the error is a duplicate row
If adoConnection.Errors.Count > 0 Then
If adoConnection.Errors(0).NativeError = 2627 Then Resume Next
End If
' Free any objects that were allocated during the function
If adoConnection.State <> adStateClosed Then adoConnection.Close
If Not adoConnection Is Nothing Then Set adoConnection = Nothing
If Not adoCmdRegisterDataAdd Is Nothing Then Set adoCmdRegisterDataAdd =
Nothing
' Abort the transaction and raise the error to the client
mtsContext.SetAbort
If Not mtsContext Is Nothing Then Set mtsContext = Nothing
' Raise the error to the client
Err.Raise Err.Number, STR_CLASSNAME & Err.Source, Err.Description
End Sub
|
| Sat, 17 Nov 2001 03:00:00 GMT |
|
 |
|
#2 / 12
|
Difficult VB/MTS/SQL Error
|
| Fri, 19 Jun 1992 00:00:00 GMT |
|
|
|
Jamie Schatt
#3 / 12
|
Difficult VB/MTS/SQL Error When I remove the component from Transaction Server, everything works fine. I can insert the array of strings many times, and each time after the first, the records are rejected and everything works great. Now, why is this so? This component REALLY needs to be in MTS. Is there some
sort of overhead that MTS is incurring each time I insert a duplicate row
and keep going? Do I need to break the transaction into smaller pieces--like
after every 100 rows? If so, why do the duplicate rows cause this when good
ones do not?
Thanks again!
Jamie Schatte
Millennia Systems
www.millenniasystems.com
|
| Sat, 17 Nov 2001 03:00:00 GMT |
|
|
|
#4 / 12
|
Difficult VB/MTS/SQL Error
|
| Fri, 19 Jun 1992 00:00:00 GMT |
|
|
|
Cláudio A Rodrigue
#5 / 12
|
Difficult VB/MTS/SQL Error This was happening to me when I was calling a method of component that belonged to a transaction and where another component had previously called the SetAbort method. To solve this problem I had to put a Err.Raise method after calling SetAbort on every components. --
Microsoft Certified Professional
Significado do Seu Nome: http://www.c-avolio.com/nomes
Quote: > Sorry for cross-posting, but I this applies to VB, MTS, and SQL Server. > I have a VB6 EXE that calls a VB6 ActiveX DLL that is registered in MTS
(and
> is set as "requires transaction"). The DLL has a method (code listed
below)
> that starts a transaction, loops through an array of strings using an ADO
> Command object to insert each string into a table in the database, and
ends
> the transaction. If a duplicate string is encountered, the database
rejects
> the string, an error is raised, and the method continues with the next
> string in the array using a "Resume Next" statement.
> My test data includes an array of about 1200 strings, and everything works
> great, EXCEPT when I try to insert the same array more than once (which
will
> result in every string being a duplicate). As expected, each row is
rejected
> (SQL Server error code=2627), and the method completes successfully (even
> though no rows are successfully inserted--this is a perfectly OK
scenario).
> However, after the transaction is completed, an error is received by the
EXE
> from the method call, the database log files fill up and the SQL Server
must
> be restated. FYI, when I debug the whole thing, there are no errors
anywhere
> in the method--everything goes OK. But upon returning from the method
call,
> the generic "Method ~ of object ~ failed." error message is received.
> I am not sure what's happening here. It seems that the insert is freaking
> out the application and the database when the insert results in many
> duplicates (maybe some kind of error overhead). My next course of action
is
> to remove the component from MTS, followed by reducing the number of lines
> in the test data.
> Thanks in advance!
> Jamie Schatte
> Millennia Systems
> www.millenniasystems.com
> ****************************************
> Public Sub DSMMeterReadCSVFile(ByVal ConnectionString As String, _
> ByRef CSVLines() As String)
' -------------------------------------------------------------------------- Quote: > -
> ' This prcedure processes an entire CSV file as an array of file
lines.
' -------------------------------------------------------------------------- Quote: > - > On Error GoTo HandleError
> Dim adoConnection As ADODB.Connection
> Dim adoCmdRegisterDataAdd As ADODB.Command
> Dim dsadoRegisterDataRec As DSADO.RegisterDataRec
> Dim mtsContext As ObjectContext
> Dim AlarmCount As Integer
> Dim Alarms As String
> Dim Alarm As String
> Dim CSVLine As String
> Dim I As Integer
> ' Continue the current transaction
> Set mtsContext = GetObjectContext
> ' Setup the database connection
> Set adoConnection = New ADODB.Connection
> adoConnection.Open ConnectionString
> ' Setup the command to add records to the REGISTER_DATA table
> Set adoCmdRegisterDataAdd = New ADODB.Command
> With adoCmdRegisterDataAdd
> Set .ActiveConnection = adoConnection
> .CommandType = adCmdText
> .CommandText = " INSERT INTO dbo.REGISTER_DATA " & _
> " (MeterID, " & _
> " ChannelID, " & _
> " TimeStamp, " & _
> " RegisterValue, " & _
> " ReadSource, " & _
> " ReadType, " & _
> " AlarmFlag) " & _
> "VALUES (?, " & _
> " ?, " & _
> " ?, " & _
> " ?, " & _
> " ?, " & _
> " ?, " & _
> " ?) "
> .CreateParameter , adInteger
> .CreateParameter , adInteger
> .CreateParameter , adChar, , 30
> .CreateParameter , adDouble
> .CreateParameter , adChar, , 1
> .CreateParameter , adChar, , 1
> .CreateParameter , adChar, , 1
> End With
> ' Parse the file lines
> For I = LBound(CSVLines) To UBound(CSVLines)
> ' Get the next line in the file
> CSVLine = CSVLines(I)
> ' Process the meter reading information for the meter
> With dsadoRegisterDataRec
> ' Format the meter reading data structure
> .MeterID = Val(DSCParseLine(CSVLine, 1))
> .ChannelID = Val(DSCParseLine(CSVLine, 2))
> .TimeStamp = DSCFormatTimeStamp(DSCParseLine(CSVLine, 3))
> .ReadSource = DSCParseLine(CSVLine, 4)
> .RegisterValue = Val(DSCParseLine(CSVLine, 5))
> .ReadType = ""
> Alarms = DSCParseLine(CSVLine, 6)
> If Len(Alarms) = 0 Then
> .AlarmFlag = "N"
> Else
> .AlarmFlag = "Y"
> End If
> ' Add the meter reading to the REGISTER_DATA table
> adoCmdRegisterDataAdd.Parameters(0).Value = .MeterID
> adoCmdRegisterDataAdd.Parameters(1).Value = .ChannelID
> adoCmdRegisterDataAdd.Parameters(2).Value = .TimeStamp
> adoCmdRegisterDataAdd.Parameters(3).Value = .RegisterValue
> adoCmdRegisterDataAdd.Parameters(4).Value = .ReadSource
> adoCmdRegisterDataAdd.Parameters(5).Value = .ReadType
> adoCmdRegisterDataAdd.Parameters(6).Value = .AlarmFlag
> adoCmdRegisterDataAdd.Execute
> End With
> Next I
> ' Free any objects that were allocated during the function
> If adoConnection.State <> adStateClosed Then adoConnection.Close
> If Not adoConnection Is Nothing Then Set adoConnection = Nothing
> If Not adoCmdRegisterDataAdd Is Nothing Then Set adoCmdRegisterDataAdd
=
> Nothing
> ' Continue the current the transaction
> mtsContext.SetComplete
> If Not mtsContext Is Nothing Then Set mtsContext = Nothing
> Exit Sub
> HandleError:
> ' Continue if the error is a duplicate row
> If adoConnection.Errors.Count > 0 Then
> If adoConnection.Errors(0).NativeError = 2627 Then Resume Next
> End If
> ' Free any objects that were allocated during the function
> If adoConnection.State <> adStateClosed Then adoConnection.Close
> If Not adoConnection Is Nothing Then Set adoConnection = Nothing
> If Not adoCmdRegisterDataAdd Is Nothing Then Set adoCmdRegisterDataAdd
=
> Nothing
> ' Abort the transaction and raise the error to the client
> mtsContext.SetAbort
> If Not mtsContext Is Nothing Then Set mtsContext = Nothing
> ' Raise the error to the client
> Err.Raise Err.Number, STR_CLASSNAME & Err.Source, Err.Description
> End Sub
|
| Sat, 17 Nov 2001 03:00:00 GMT |
|
|
|
jlboot
#6 / 12
|
Difficult VB/MTS/SQL Error one point & one question 1) i always raise the error on entry to the error handler, followed by
releasing resources then set abort. i have never experienced the "Method ~
of object ~ failed." may be just luck or ....
2) have you considered using a straight connection/execute/insert?
Quote:
>Sorry for cross-posting, but I this applies to VB, MTS, and SQL Server. >I have a VB6 EXE that calls a VB6 ActiveX DLL that is registered in MTS
(and
>is set as "requires transaction"). The DLL has a method (code listed below)
>that starts a transaction, loops through an array of strings using an ADO
>Command object to insert each string into a table in the database, and ends
>the transaction. If a duplicate string is encountered, the database rejects
>the string, an error is raised, and the method continues with the next
>string in the array using a "Resume Next" statement.
>My test data includes an array of about 1200 strings, and everything works
>great, EXCEPT when I try to insert the same array more than once (which
will
>result in every string being a duplicate). As expected, each row is
rejected
>(SQL Server error code=2627), and the method completes successfully (even
>though no rows are successfully inserted--this is a perfectly OK scenario).
>However, after the transaction is completed, an error is received by the
EXE
>from the method call, the database log files fill up and the SQL Server
must
>be restated. FYI, when I debug the whole thing, there are no errors
anywhere
>in the method--everything goes OK. But upon returning from the method call,
>the generic "Method ~ of object ~ failed." error message is received.
>I am not sure what's happening here. It seems that the insert is freaking
>out the application and the database when the insert results in many
>duplicates (maybe some kind of error overhead). My next course of action is
>to remove the component from MTS, followed by reducing the number of lines
>in the test data.
>Thanks in advance!
>Jamie Schatte
>Millennia Systems
>www.millenniasystems.com
>****************************************
>Public Sub DSMMeterReadCSVFile(ByVal ConnectionString As String, _
> ByRef CSVLines() As String)
>' -------------------------------------------------------------------------
-
>-
> ' This prcedure processes an entire CSV file as an array of file lines.
>' -------------------------------------------------------------------------
-
>-
> On Error GoTo HandleError
> Dim adoConnection As ADODB.Connection
> Dim adoCmdRegisterDataAdd As ADODB.Command
> Dim dsadoRegisterDataRec As DSADO.RegisterDataRec
> Dim mtsContext As ObjectContext
> Dim AlarmCount As Integer
> Dim Alarms As String
> Dim Alarm As String
> Dim CSVLine As String
> Dim I As Integer
> ' Continue the current transaction
> Set mtsContext = GetObjectContext
> ' Setup the database connection
> Set adoConnection = New ADODB.Connection
> adoConnection.Open ConnectionString
> ' Setup the command to add records to the REGISTER_DATA table
> Set adoCmdRegisterDataAdd = New ADODB.Command
> With adoCmdRegisterDataAdd
> Set .ActiveConnection = adoConnection
> .CommandType = adCmdText
> .CommandText = " INSERT INTO dbo.REGISTER_DATA " & _
> " (MeterID, " & _
> " ChannelID, " & _
> " TimeStamp, " & _
> " RegisterValue, " & _
> " ReadSource, " & _
> " ReadType, " & _
> " AlarmFlag) " & _
> "VALUES (?, " & _
> " ?, " & _
> " ?, " & _
> " ?, " & _
> " ?, " & _
> " ?, " & _
> " ?) "
> .CreateParameter , adInteger
> .CreateParameter , adInteger
> .CreateParameter , adChar, , 30
> .CreateParameter , adDouble
> .CreateParameter , adChar, , 1
> .CreateParameter , adChar, , 1
> .CreateParameter , adChar, , 1
> End With
> ' Parse the file lines
> For I = LBound(CSVLines) To UBound(CSVLines)
> ' Get the next line in the file
> CSVLine = CSVLines(I)
> ' Process the meter reading information for the meter
> With dsadoRegisterDataRec
> ' Format the meter reading data structure
> .MeterID = Val(DSCParseLine(CSVLine, 1))
> .ChannelID = Val(DSCParseLine(CSVLine, 2))
> .TimeStamp = DSCFormatTimeStamp(DSCParseLine(CSVLine, 3))
> .ReadSource = DSCParseLine(CSVLine, 4)
> .RegisterValue = Val(DSCParseLine(CSVLine, 5))
> .ReadType = ""
> Alarms = DSCParseLine(CSVLine, 6)
> If Len(Alarms) = 0 Then
> .AlarmFlag = "N"
> Else
> .AlarmFlag = "Y"
> End If
> ' Add the meter reading to the REGISTER_DATA table
> adoCmdRegisterDataAdd.Parameters(0).Value = .MeterID
> adoCmdRegisterDataAdd.Parameters(1).Value = .ChannelID
> adoCmdRegisterDataAdd.Parameters(2).Value = .TimeStamp
> adoCmdRegisterDataAdd.Parameters(3).Value = .RegisterValue
> adoCmdRegisterDataAdd.Parameters(4).Value = .ReadSource
> adoCmdRegisterDataAdd.Parameters(5).Value = .ReadType
> adoCmdRegisterDataAdd.Parameters(6).Value = .AlarmFlag
> adoCmdRegisterDataAdd.Execute
> End With
> Next I
> ' Free any objects that were allocated during the function
> If adoConnection.State <> adStateClosed Then adoConnection.Close
> If Not adoConnection Is Nothing Then Set adoConnection = Nothing
> If Not adoCmdRegisterDataAdd Is Nothing Then Set adoCmdRegisterDataAdd
=
>Nothing
> ' Continue the current the transaction
> mtsContext.SetComplete
> If Not mtsContext Is Nothing Then Set mtsContext = Nothing
> Exit Sub
>HandleError:
> ' Continue if the error is a duplicate row
> If adoConnection.Errors.Count > 0 Then
> If adoConnection.Errors(0).NativeError = 2627 Then Resume Next
> End If
> ' Free any objects that were allocated during the function
> If adoConnection.State <> adStateClosed Then adoConnection.Close
> If Not adoConnection Is Nothing Then Set adoConnection = Nothing
> If Not adoCmdRegisterDataAdd Is Nothing Then Set adoCmdRegisterDataAdd
=
>Nothing
> ' Abort the transaction and raise the error to the client
> mtsContext.SetAbort
> If Not mtsContext Is Nothing Then Set mtsContext = Nothing
> ' Raise the error to the client
> Err.Raise Err.Number, STR_CLASSNAME & Err.Source, Err.Description
>End Sub
|
| Sat, 17 Nov 2001 03:00:00 GMT |
|
|
|
Mike Ansti
#7 / 12
|
Difficult VB/MTS/SQL Error MTS is not my speciality but you are committing the first transaction in your DLL before exiting? I think there's a limit on nested transactions in ADO which might cause your problem. Quote:
>This was happening to me when I was calling a method of component that >belonged to a transaction and where another component had previously called >the SetAbort method. To solve this problem I had to put a Err.Raise method >after calling SetAbort on every components. >--
>Microsoft Certified Professional
>Significado do Seu Nome: http://www.c-avolio.com/nomes
>> Sorry for cross-posting, but I this applies to VB, MTS, and SQL Server.
>> I have a VB6 EXE that calls a VB6 ActiveX DLL that is registered in MTS
>(and
>> is set as "requires transaction"). The DLL has a method (code listed
>below)
>> that starts a transaction, loops through an array of strings using an ADO
>> Command object to insert each string into a table in the database, and
>ends
>> the transaction. If a duplicate string is encountered, the database
>rejects
>> the string, an error is raised, and the method continues with the next
>> string in the array using a "Resume Next" statement.
>> My test data includes an array of about 1200 strings, and everything
works
>> great, EXCEPT when I try to insert the same array more than once (which
>will
>> result in every string being a duplicate). As expected, each row is
>rejected
>> (SQL Server error code=2627), and the method completes successfully (even
>> though no rows are successfully inserted--this is a perfectly OK
>scenario).
>> However, after the transaction is completed, an error is received by the
>EXE
>> from the method call, the database log files fill up and the SQL Server
>must
>> be restated. FYI, when I debug the whole thing, there are no errors
>anywhere
>> in the method--everything goes OK. But upon returning from the method
>call,
>> the generic "Method ~ of object ~ failed." error message is received.
>> I am not sure what's happening here. It seems that the insert is freaking
>> out the application and the database when the insert results in many
>> duplicates (maybe some kind of error overhead). My next course of action
>is
>> to remove the component from MTS, followed by reducing the number of
lines
>> in the test data.
>> Thanks in advance!
>> Jamie Schatte
>> Millennia Systems
>> www.millenniasystems.com
>> ****************************************
>> Public Sub DSMMeterReadCSVFile(ByVal ConnectionString As String, _
>> ByRef CSVLines() As String)
>' -------------------------------------------------------------------------
-
>> -
>> ' This prcedure processes an entire CSV file as an array of file
>lines.
>' -------------------------------------------------------------------------
-
>> -
>> On Error GoTo HandleError
>> Dim adoConnection As ADODB.Connection
>> Dim adoCmdRegisterDataAdd As ADODB.Command
>> Dim dsadoRegisterDataRec As DSADO.RegisterDataRec
>> Dim mtsContext As ObjectContext
>> Dim AlarmCount As Integer
>> Dim Alarms As String
>> Dim Alarm As String
>> Dim CSVLine As String
>> Dim I As Integer
>> ' Continue the current transaction
>> Set mtsContext = GetObjectContext
>> ' Setup the database connection
>> Set adoConnection = New ADODB.Connection
>> adoConnection.Open ConnectionString
>> ' Setup the command to add records to the REGISTER_DATA table
>> Set adoCmdRegisterDataAdd = New ADODB.Command
>> With adoCmdRegisterDataAdd
>> Set .ActiveConnection = adoConnection
>> .CommandType = adCmdText
>> .CommandText = " INSERT INTO dbo.REGISTER_DATA " & _
>> " (MeterID, " & _
>> " ChannelID, " & _
>> " TimeStamp, " & _
>> " RegisterValue, " & _
>> " ReadSource, " & _
>> " ReadType, " & _
>> " AlarmFlag) " & _
>> "VALUES (?, " & _
>> " ?, " & _
>> " ?, " & _
>> " ?, " & _
>> " ?, " & _
>> " ?, " & _
>> " ?) "
>> .CreateParameter , adInteger
>> .CreateParameter , adInteger
>> .CreateParameter , adChar, , 30
>> .CreateParameter , adDouble
>> .CreateParameter , adChar, , 1
>> .CreateParameter , adChar, , 1
>> .CreateParameter , adChar, , 1
>> End With
>> ' Parse the file lines
>> For I = LBound(CSVLines) To UBound(CSVLines)
>> ' Get the next line in the file
>> CSVLine = CSVLines(I)
>> ' Process the meter reading information for the meter
>> With dsadoRegisterDataRec
>> ' Format the meter reading data structure
>> .MeterID = Val(DSCParseLine(CSVLine, 1))
>> .ChannelID = Val(DSCParseLine(CSVLine, 2))
>> .TimeStamp = DSCFormatTimeStamp(DSCParseLine(CSVLine, 3))
>> .ReadSource = DSCParseLine(CSVLine, 4)
>> .RegisterValue = Val(DSCParseLine(CSVLine, 5))
>> .ReadType = ""
>> Alarms = DSCParseLine(CSVLine, 6)
>> If Len(Alarms) = 0 Then
>> .AlarmFlag = "N"
>> Else
>> .AlarmFlag = "Y"
>> End If
>> ' Add the meter reading to the REGISTER_DATA table
>> adoCmdRegisterDataAdd.Parameters(0).Value = .MeterID
>> adoCmdRegisterDataAdd.Parameters(1).Value = .ChannelID
>> adoCmdRegisterDataAdd.Parameters(2).Value = .TimeStamp
>> adoCmdRegisterDataAdd.Parameters(3).Value = .RegisterValue
>> adoCmdRegisterDataAdd.Parameters(4).Value = .ReadSource
>> adoCmdRegisterDataAdd.Parameters(5).Value = .ReadType
>> adoCmdRegisterDataAdd.Parameters(6).Value = .AlarmFlag
>> adoCmdRegisterDataAdd.Execute
>> End With
>> Next I
>> ' Free any objects that were allocated during the function
>> If adoConnection.State <> adStateClosed Then adoConnection.Close
>> If Not adoConnection Is Nothing Then Set adoConnection = Nothing
>> If Not adoCmdRegisterDataAdd Is Nothing Then Set
adoCmdRegisterDataAdd Quote: >= >> Nothing >> ' Continue the current the transaction
>> mtsContext.SetComplete
>> If Not mtsContext Is Nothing Then Set mtsContext = Nothing
>> Exit Sub
>> HandleError:
>> ' Continue if the error is a duplicate row
>> If adoConnection.Errors.Count > 0 Then
>> If adoConnection.Errors(0).NativeError = 2627 Then Resume Next
>> End If
>> ' Free any objects that were allocated during the function
>> If adoConnection.State <> adStateClosed Then adoConnection.Close
>> If Not adoConnection Is Nothing Then Set adoConnection = Nothing
>> If Not adoCmdRegisterDataAdd Is Nothing Then Set
adoCmdRegisterDataAdd Quote: >= >> Nothing >> ' Abort the transaction and raise the error to the client
>> mtsContext.SetAbort
>> If Not mtsContext Is Nothing Then Set mtsContext = Nothing
>> ' Raise the error to the client
>> Err.Raise Err.Number, STR_CLASSNAME & Err.Source, Err.Description
>> End Sub
|
| Sun, 18 Nov 2001 03:00:00 GMT |
|
|
|
Dale Ree
#8 / 12
|
Difficult VB/MTS/SQL Error Jamie, your code looks fine, some things you might want to try in order to narrow the problem are down are: 1. Send the CSVLines as ByVal, I can't see where you're changing them, so
there shouldn't be any reason for you to have to retain a link to the
original array.
2. I don't normally set the mtsobjectcontext variable to nothing after
performing an abort/complete, but I don't know if this affects anything.
3. Reduce the time the component remains in memory to 1 minute, then wait
a few minutes before calling it again with the same array. It could be
something to do with the fact it is still present in memory. If this then
works, I don't know how you fix it, but at least you'll have narrowed it
down to the problem being caused by the DLL still residing in memory! This
seems a likely suspect, as you say the program works when it is non-mts.
4. Definitely ignore people saying you should raise the error first, as
soon as an error is raised the caller receives focus back. You must perform
all setaborts and = nothing before raising the error.
5. Try it without the WITH statement. I use them myself, but I've heard
a few people discuss problems with them to do with memory leakage. Can't
hurt to try it anyway!
As an additional point, as you are processing so many records, you may want
to consider creating a stored procedure instead of creating a manual insert
statement. Stored procedures are pre-optimised and compiled, and tend to
run quite a bit quicker than running 'ad hoc' queries as you are doing. For
inserts I've found it cuts the time down by about 25-30% (obviously this'll
be different for each application, so don't quote me!). It depends whether
or not this is important to you, but you never know you may find as a fluke
that this also sorts out your problem!
Quote: > Sorry for cross-posting, but I this applies to VB, MTS, and SQL Server. > I have a VB6 EXE that calls a VB6 ActiveX DLL that is registered in MTS
(and
> is set as "requires transaction"). The DLL has a method (code listed
below)
> that starts a transaction, loops through an array of strings using an ADO
> Command object to insert each string into a table in the database, and
ends
> the transaction. If a duplicate string is encountered, the database
rejects
> the string, an error is raised, and the method continues with the next
> string in the array using a "Resume Next" statement.
> My test data includes an array of about 1200 strings, and everything works
> great, EXCEPT when I try to insert the same array more than once (which
will
> result in every string being a duplicate). As expected, each row is
rejected
> (SQL Server error code=2627), and the method completes successfully (even
> though no rows are successfully inserted--this is a perfectly OK
scenario).
> However, after the transaction is completed, an error is received by the
EXE
> from the method call, the database log files fill up and the SQL Server
must
> be restated. FYI, when I debug the whole thing, there are no errors
anywhere
> in the method--everything goes OK. But upon returning from the method
call,
> the generic "Method ~ of object ~ failed." error message is received.
> I am not sure what's happening here. It seems that the insert is freaking
> out the application and the database when the insert results in many
> duplicates (maybe some kind of error overhead). My next course of action
is
> to remove the component from MTS, followed by reducing the number of lines
> in the test data.
> Thanks in advance!
> Jamie Schatte
> Millennia Systems
> www.millenniasystems.com
> ****************************************
> Public Sub DSMMeterReadCSVFile(ByVal ConnectionString As String, _
> ByRef CSVLines() As String)
' -------------------------------------------------------------------------- Quote: > -
> ' This prcedure processes an entire CSV file as an array of file
lines.
' -------------------------------------------------------------------------- Quote: > - > On Error GoTo HandleError
> Dim adoConnection As ADODB.Connection
> Dim adoCmdRegisterDataAdd As ADODB.Command
> Dim dsadoRegisterDataRec As DSADO.RegisterDataRec
> Dim mtsContext As ObjectContext
> Dim AlarmCount As Integer
> Dim Alarms As String
> Dim Alarm As String
> Dim CSVLine As String
> Dim I As Integer
> ' Continue the current transaction
> Set mtsContext = GetObjectContext
> ' Setup the database connection
> Set adoConnection = New ADODB.Connection
> adoConnection.Open ConnectionString
> ' Setup the command to add records to the REGISTER_DATA table
> Set adoCmdRegisterDataAdd = New ADODB.Command
> With adoCmdRegisterDataAdd
> Set .ActiveConnection = adoConnection
> .CommandType = adCmdText
> .CommandText = " INSERT INTO dbo.REGISTER_DATA " & _
> " (MeterID, " & _
> " ChannelID, " & _
> " TimeStamp, " & _
> " RegisterValue, " & _
> " ReadSource, " & _
> " ReadType, " & _
> " AlarmFlag) " & _
> "VALUES (?, " & _
> " ?, " & _
> " ?, " & _
> " ?, " & _
> " ?, " & _
> " ?, " & _
> " ?) "
> .CreateParameter , adInteger
> .CreateParameter , adInteger
> .CreateParameter , adChar, , 30
> .CreateParameter , adDouble
> .CreateParameter , adChar, , 1
> .CreateParameter , adChar, , 1
> .CreateParameter , adChar, , 1
> End With
> ' Parse the file lines
> For I = LBound(CSVLines) To UBound(CSVLines)
> ' Get the next line in the file
> CSVLine = CSVLines(I)
> ' Process the meter reading information for the meter
> With dsadoRegisterDataRec
> ' Format the meter reading data structure
> .MeterID = Val(DSCParseLine(CSVLine, 1))
> .ChannelID = Val(DSCParseLine(CSVLine, 2))
> .TimeStamp = DSCFormatTimeStamp(DSCParseLine(CSVLine, 3))
> .ReadSource = DSCParseLine(CSVLine, 4)
> .RegisterValue = Val(DSCParseLine(CSVLine, 5))
> .ReadType = ""
> Alarms = DSCParseLine(CSVLine, 6)
> If Len(Alarms) = 0 Then
> .AlarmFlag = "N"
> Else
> .AlarmFlag = "Y"
> End If
> ' Add the meter reading to the REGISTER_DATA table
> adoCmdRegisterDataAdd.Parameters(0).Value = .MeterID
> adoCmdRegisterDataAdd.Parameters(1).Value = .ChannelID
> adoCmdRegisterDataAdd.Parameters(2).Value = .TimeStamp
> adoCmdRegisterDataAdd.Parameters(3).Value = .RegisterValue
> adoCmdRegisterDataAdd.Parameters(4).Value = .ReadSource
> adoCmdRegisterDataAdd.Parameters(5).Value = .ReadType
> adoCmdRegisterDataAdd.Parameters(6).Value = .AlarmFlag
> adoCmdRegisterDataAdd.Execute
> End With
> Next I
> ' Free any objects that were allocated during the function
> If adoConnection.State <> adStateClosed Then adoConnection.Close
> If Not adoConnection Is Nothing Then Set adoConnection = Nothing
> If Not adoCmdRegisterDataAdd Is Nothing Then Set adoCmdRegisterDataAdd
=
> Nothing
> ' Continue the current the transaction
> mtsContext.SetComplete
> If Not mtsContext Is Nothing Then Set mtsContext = Nothing
> Exit Sub
> HandleError:
> ' Continue if the error is a duplicate row
> If adoConnection.Errors.Count > 0 Then
> If adoConnection.Errors(0).NativeError = 2627 Then Resume Next
> End If
> ' Free any objects that were allocated during the function
> If adoConnection.State <> adStateClosed Then adoConnection.Close
> If Not adoConnection Is Nothing Then Set adoConnection = Nothing
> If Not adoCmdRegisterDataAdd Is Nothing Then Set adoCmdRegisterDataAdd
=
> Nothing
> ' Abort the transaction and raise the error to the client
> mtsContext.SetAbort
> If Not mtsContext Is Nothing Then Set mtsContext = Nothing
> ' Raise the error to the client
> Err.Raise Err.Number, STR_CLASSNAME & Err.Source, Err.Description
> End Sub
|
| Sun, 18 Nov 2001 03:00:00 GMT |
|
|
|
#9 / 12
|
Difficult VB/MTS/SQL Error
|
| Fri, 19 Jun 1992 00:00:00 GMT |
|
|
|
Jamie Schatt
#10 / 12
|
Difficult VB/MTS/SQL Error Thanks for the help, everyone! I moved the insert statements into stored procedures, and this not only solved the problem but also made things run MUCH quicker. The now code works great in MTS as either a server (out-of-process) or a library (in-process) package. Thanks again!
Jamie Schatte
Millennia Systems
www.millenniasystems.com
|
| Sun, 18 Nov 2001 03:00:00 GMT |
|
|
|
#11 / 12
|
Difficult VB/MTS/SQL Error
|
| Fri, 19 Jun 1992 00:00:00 GMT |
|
|
|
Ernesto Fernande
#12 / 12
|
Difficult VB/MTS/SQL Error I Had the same problem, and i solved it by starting the MSDTC service on the SQL Server box, i also upgrade to the SP3 for visual studio 6 and it gave me more help on how to resolve this. Ernesto Fernandez
C&A Mexico
Quote: > Sorry for cross-posting, but I this applies to VB, MTS, and SQL Server. > I have a VB6 EXE that calls a VB6 ActiveX DLL that is registered in MTS
(and
> is set as "requires transaction"). The DLL has a method (code listed
below)
> that starts a transaction, loops through an array of strings using an ADO
> Command object to insert each string into a table in the database, and
ends
> the transaction. If a duplicate string is encountered, the database
rejects
> the string, an error is raised, and the method continues with the next
> string in the array using a "Resume Next" statement.
> My test data includes an array of about 1200 strings, and everything works
> great, EXCEPT when I try to insert the same array more than once (which
will
> result in every string being a duplicate). As expected, each row is
rejected
> (SQL Server error code=2627), and the method completes successfully (even
> though no rows are successfully inserted--this is a perfectly OK
scenario).
> However, after the transaction is completed, an error is received by the
EXE
> from the method call, the database log files fill up and the SQL Server
must
> be restated. FYI, when I debug the whole thing, there are no errors
anywhere
> in the method--everything goes OK. But upon returning from the method
call,
> the generic "Method ~ of object ~ failed." error message is received.
> I am not sure what's happening here. It seems that the insert is freaking
> out the application and the database when the insert results in many
> duplicates (maybe some kind of error overhead). My next course of action
is
> to remove the component from MTS, followed by reducing the number of lines
> in the test data.
> Thanks in advance!
> Jamie Schatte
> Millennia Systems
> www.millenniasystems.com
> ****************************************
> Public Sub DSMMeterReadCSVFile(ByVal ConnectionString As String, _
> ByRef CSVLines() As String)
' -------------------------------------------------------------------------- Quote: > -
> ' This prcedure processes an entire CSV file as an array of file
lines.
' -------------------------------------------------------------------------- Quote: > - > On Error GoTo HandleError
> Dim adoConnection As ADODB.Connection
> Dim adoCmdRegisterDataAdd As ADODB.Command
> Dim dsadoRegisterDataRec As DSADO.RegisterDataRec
> Dim mtsContext As ObjectContext
> Dim AlarmCount As Integer
> Dim Alarms As String
> Dim Alarm As String
> Dim CSVLine As String
> Dim I As Integer
> ' Continue the current transaction
> Set mtsContext = GetObjectContext
> ' Setup the database connection
> Set adoConnection = New ADODB.Connection
> adoConnection.Open ConnectionString
> ' Setup the command to add records to the REGISTER_DATA table
> Set adoCmdRegisterDataAdd = New ADODB.Command
> With adoCmdRegisterDataAdd
> Set .ActiveConnection = adoConnection
> .CommandType = adCmdText
> .CommandText = " INSERT INTO dbo.REGISTER_DATA " & _
> " (MeterID, " & _
> " ChannelID, " & _
> " TimeStamp, " & _
> " RegisterValue, " & _
> " ReadSource, " & _
> " ReadType, " & _
> " AlarmFlag) " & _
> "VALUES (?, " & _
> " ?, " & _
> " ?, " & _
> " ?, " & _
> " ?, " & _
> " ?, " & _
> " ?) "
> .CreateParameter , adInteger
> .CreateParameter , adInteger
> .CreateParameter , adChar, , 30
> .CreateParameter , adDouble
> .CreateParameter , adChar, , 1
> .CreateParameter , adChar, , 1
> .CreateParameter , adChar, , 1
> End With
> ' Parse the file lines
> For I = LBound(CSVLines) To UBound(CSVLines)
> ' Get the next line in the file
> CSVLine = CSVLines(I)
> ' Process the meter reading information for the meter
> With dsadoRegisterDataRec
> ' Format the meter reading data structure
> .MeterID = Val(DSCParseLine(CSVLine, 1))
> .ChannelID = Val(DSCParseLine(CSVLine, 2))
> .TimeStamp = DSCFormatTimeStamp(DSCParseLine(CSVLine, 3))
> .ReadSource = DSCParseLine(CSVLine, 4)
> .RegisterValue = Val(DSCParseLine(CSVLine, 5))
> .ReadType = ""
> Alarms = DSCParseLine(CSVLine, 6)
> If Len(Alarms) = 0 Then
> .AlarmFlag = "N"
> Else
> .AlarmFlag = "Y"
> End If
> ' Add the meter reading to the REGISTER_DATA table
> adoCmdRegisterDataAdd.Parameters(0).Value = .MeterID
> adoCmdRegisterDataAdd.Parameters(1).Value = .ChannelID
> adoCmdRegisterDataAdd.Parameters(2).Value = .TimeStamp
> adoCmdRegisterDataAdd.Parameters(3).Value = .RegisterValue
> adoCmdRegisterDataAdd.Parameters(4).Value = .ReadSource
> adoCmdRegisterDataAdd.Parameters(5).Value = .ReadType
> adoCmdRegisterDataAdd.Parameters(6).Value = .AlarmFlag
> adoCmdRegisterDataAdd.Execute
> End With
> Next I
> ' Free any objects that were allocated during the function
> If adoConnection.State <> adStateClosed Then adoConnection.Close
> If Not adoConnection Is Nothing Then Set adoConnection = Nothing
> If Not adoCmdRegisterDataAdd Is Nothing Then Set adoCmdRegisterDataAdd
=
> Nothing
> ' Continue the current the transaction
> mtsContext.SetComplete
> If Not mtsContext Is Nothing Then Set mtsContext = Nothing
> Exit Sub
> HandleError:
> ' Continue if the error is a duplicate row
> If adoConnection.Errors.Count > 0 Then
> If adoConnection.Errors(0).NativeError = 2627 Then Resume Next
> End If
> ' Free any objects that were allocated during the function
> If adoConnection.State <> adStateClosed Then adoConnection.Close
> If Not adoConnection Is Nothing Then Set adoConnection = Nothing
> If Not adoCmdRegisterDataAdd Is Nothing Then Set adoCmdRegisterDataAdd
=
> Nothing
> ' Abort the transaction and raise the error to the client
> mtsContext.SetAbort
> If Not mtsContext Is Nothing Then Set mtsContext = Nothing
> ' Raise the error to the client
> Err.Raise Err.Number, STR_CLASSNAME & Err.Source, Err.Description
> End Sub
|
| Sat, 24 Nov 2001 03:00:00 GMT |
|
| |
|
