Solved: The rollback transaction request has no corresponding BEGIN TRANSACTION

Look at your code for a "rollback" that has no matching "begin transaction". It could be a situation where the "begin transaction" is really there, but gets skipped because of conditionals in the code. Given the information, this is my best guess. If you would like to post the code that you are using, please do so and I can evaluate it further.

jwellis

ASKER

When you are referring to the code, I'm assuming you are talking about the code in VB. Don't mean to sound so stupid, but I'm new at this SQL Server stuff.

jwellis

ASKER

Option Explicit

Private Const MODULE_NAME As String = "Generic"

Public g_objDatabase As MetroIS.Database
Public g_objGUIClass As Eligibility.clsGUIRoutines
Public g_objGlobal As Eligibility.clsGlobal
Public ErrorHandler As MetroIS.ErrorHandler

Public Type ChangeElement
NewValue As String
OldValue As String
Changed As Boolean
End Type

Public Const vbDarkGreen As Long = &HC000&
Public Const APP_NAME As String = "Eligibility Preprocessing Application"

Public Sub Main()

Const PROC_NAME As String = "Main"
Dim bTest As Boolean

Set ErrorHandler = New MetroIS.ErrorHandler

ErrorHandler.PushToStack MODULE_NAME, PROC_NAME, Screen.MousePointer
Screen.MousePointer = vbHourglass

On Error GoTo Err_Handler

bTest = (InStr(1, Command(), "/t") <> 0)
Set g_objDatabase = New MetroIS.Database
Set g_objGlobal = New Eligibility.clsGlobal
Set g_objGUIClass = New Eligibility.clsGUIRoutines

With g_objDatabase
.Provider = adbSQLServer
.ApplicationName = APP_NAME
If bTest Then
.DataSource = "MCI2"
.Catalog = "Eligibility_Test"
Else
.DataSource = "MCI3"
.Catalog = "Eligibility"
End If
.Login
If (.Status <> vbOK) Then
Call TerminateApp
Exit Sub
End If
End With
frmMain.Show
frmQuery.Show
Load frmFiles
Screen.MousePointer = ErrorHandler.PullFromStack
Exit Sub

Err_Handler:
ErrorHandler.ShowError Err
Call TerminateApp

End Sub

Public Sub TerminateApp()
Dim iIndex As Integer

For iIndex = (Forms.Count - 1) To 0 Step -1
Unload Forms(iIndex)
Next iIndex
Set g_objDatabase = Nothing
Set g_objGUIClass = Nothing
Set g_objGlobal = Nothing
End Sub

Public Sub ShowError()
MsgBox Err.Number & " - " & Err.Description, vbInformation
End Sub

Public Sub CreateUploadFile(ByVal psFileName As String)

On Error GoTo Err_CreateUploadFile

Dim cmmExport As ADODB.Command
Dim rcsExport As ADODB.Recordset
Dim rsRecord10 As ADODB.Recordset
Dim rsRecord11 As ADODB.Recordset
Dim rsRecord20 As ADODB.Recordset
Dim rsRecord30 As ADODB.Recordset
Dim nFileNo As Integer
Dim sLine As String
Dim sFormat As String
Dim iIndex As Integer

frmMain.ShowStatus "Retrieving eligibility information"

'* Define the parameters for exporting
Set cmmExport = New ADODB.Command
With cmmExport
Set .ActiveConnection = g_objDatabase.Connection
.CommandType = adCmdStoredProc
.CommandText = "sp_eligibility_export"
.CommandTimeout = 0
.Parameters.Append .CreateParameter("FileNo", adInteger, adParamInput, , frmFiles.FileNo)
End With

'* Retrieve the recordsets from execution of the stored procedure
Set rcsExport = New ADODB.Recordset
With rcsExport
Set .Source = cmmExport
.CursorLocation = adUseClient
.Open
End With
Set rsRecord10 = rcsExport.Clone
Set rsRecord11 = rcsExport.NextRecordset
Set rsRecord20 = rcsExport.NextRecordset
Set rsRecord30 = rcsExport.NextRecordset

nFileNo = FreeFile()
Open psFileName For Output As #nFileNo

frmMain.InitializeMeter "Creating Eligibility Extract", rsRecord11.RecordCount

iIndex = 0
Do While Not (rsRecord11.EOF)
iIndex = iIndex + 1
With rsRecord10
sLine = "10" & Format$(iIndex, "0000000") & " "
sLine = sLine & Format$(.Fields("SubscriberNo").Value, "!@@@@@@@@@@@@@@@")
sLine = sLine & Format$(.Fields("RelationshipCode").Value, "!@")
sLine = sLine & Format$(.Fields("MemberNo").Value, "!@@@@@@@@@@@@@@@")
sLine = sLine & Format$(.Fields("ActionCode").Value, "!@")
sLine = sLine & Format$(.Fields("GroupNo").Value, "!@@@@@@")
sLine = sLine & Format$(.Fields("EffectiveDate").Value, "yyyymmdd")
If IsNull(.Fields("TerminatedDate").Value) Then
sLine = sLine & "99999999"
Else
sLine = sLine & Format$(.Fields("TerminatedDate").Value, "yyyymmdd")
End If
sLine = sLine & UCase$(Format$(.Fields("LastName").Value, "!@@@@@@@@@@@@@@@@@@@@"))
sLine = sLine & UCase$(Format$(.Fields("FirstName").Value, "!@@@@@@@@@@@@@@@"))
sLine = sLine & UCase$(Format$(Left$(.Fields("MidInit").Value & " ", 1), "!@"))
sLine = sLine & UCase$(Format$(Left$(.Fields("Title").Value & " ", 4), "!@@@@"))
sLine = sLine & "4" '* Name format code
sLine = sLine & UCase$(Format$(Left$(.Fields("SSN").Value & " ", 9), "!@@@@@@@@@"))
sLine = sLine & UCase$(Format$(Left$(.Fields("AltMemberNo").Value & " ", 15), "!@@@@@@@@@@@@@@@"))
sLine = sLine & UCase$(Format$(Left$(.Fields("MemberBillingAcct").Value & " ", 15), "!@@@@@@@@@@@@@@@"))
sLine = sLine & UCase$(Format$(.Fields("DOB").Value, "yyyymmdd"))
sLine = sLine & UCase$(Format$(.Fields("Gender").Value, "!@"))
sLine = sLine & UCase$(Format$(Left$(.Fields("Race").Value & " ", 1), "!@"))
If IsNull(.Fields("HiredDate").Value) Then
sLine = sLine & "00000000"
Else
sLine = sLine & Format$(.Fields("HiredDate").Value, "yyyymmdd")
End If
sLine = sLine & " " '* Marital status
sLine = sLine & Left$(.Fields("Status").Value & " ", 1)
If IsNull(.Fields("StatusDate").Value) Then
sLine = sLine & "00000000"
Else
sLine = sLine & Format$(.Fields("StatusDate").Value, "yyyymmdd")
End If
If IsNull(.Fields("DOD").Value) Then
sLine = sLine & "00000000"
Else
sLine = sLine & Format$(.Fields("DOD").Value, "yyyymmdd")
End If
sLine = sLine & IIf(.Fields("PrintMemberCard").Value, "Y", "N")
sLine = sLine & IIf(.Fields("PrintElectronicCard").Value, "Y", "N")
sLine = sLine & IIf(.Fields("PrintAddressLabel").Value, "Y", "N")
sLine = sLine & IIf(.Fields("PrintPolicyCertificate").Value, "Y", "N")
sLine = sLine & Format$(UCase$(Left$(.Fields("MedicaidCode").Value & " ", 3)), "!@@@")
End With
Print #nFileNo, sLine

With rsRecord11
sLine = "11" & Format$(iIndex, "0000000") & " "
sLine = sLine & UCase$(Format$(Left$(.Fields("Address1").Value & " ", 25), "!@@@@@@@@@@@@@@@@@@@@@@@@@"))
sLine = sLine & UCase$(Format$(Left$(.Fields("Address2").Value & " ", 25), "!@@@@@@@@@@@@@@@@@@@@@@@@@"))
sLine = sLine & UCase$(Format$(Left$(.Fields("City").Value & " ", 16), "!@@@@@@@@@@@@@@@@"))
sLine = sLine & UCase$(Format$(Left$(.Fields("State").Value & " ", 2), "!@@"))
sLine = sLine & UCase$(Format$(Left$(.Fields("ZipCode").Value & " ", 10), "!@@@@@@@@@@"))
sLine = sLine & Format$(Left$(.Fields("HomePhone").Value & " ", 10), "!@@@@@@@@@@")
sLine = sLine & Format$(Left$(.Fields("WorkPhone").Value & " ", 10), "!@@@@@@@@@@")
sLine = sLine & Format$(Left$(.Fields("WorkPhoneExtension").Value & " ", 4), "!@@@@")
sLine = sLine & Format$(Left$(.Fields("MemberType").Value & " ", 2), "!@@")
sLine = sLine & Format$(Left$(.Fields("AreaCode").Value & " ", 4), "!@@@@")
sLine = sLine & UCase$(Format$(Left$(.Fields("Comments").Value & " ", 20), "!@@@@@@@@@@@@@@@@@@@@"))
sLine = sLine & UCase$(Format$(Left$(.Fields("Employer").Value & " ", 25), "!@@@@@@@@@@@@@@@@@@@@@@@@@"))
sLine = sLine & UCase$(Format$(Left$(.Fields("PrimaryLanguage").Value & " ", 4), "!@@@@"))
End With
Print #nFileNo, sLine

With rsRecord20
Do While ((.Fields("MemberNo").Value = rsRecord11.Fields("MemberNo").Value) _
And (.Fields("GroupSequence").Value = rsRecord11.Fields("GroupSequence").Value))
sLine = "20" & Format$(iIndex, "0000000")
sLine = sLine & Format$(.Fields("OccurrenceNo").Value, "00000")
sLine = sLine & UCase$(Format$(.Fields("Tier").Value, "!@@@@"))
If ((.Fields("EffectiveDate").Value > CDate("12/31/1999")) And (.Fields("BenefitPkg").Value = "TMH1")) Then
sLine = sLine & "TMH2"
Else
sLine = sLine & UCase$(Format$(.Fields("BenefitPkg").Value, "!@@@@"))
End If
sLine = sLine & Format$(.Fields("EffectiveDate").Value, "yyyymmdd")
If IsNull(.Fields("TerminatedDate").Value) Then
sLine = sLine & "99999999"
Else
sLine = sLine & Format$(.Fields("TerminatedDate").Value, "yyyymmdd")
End If
sFormat = "!" & String(60, "@")
sLine = sLine & UCase$(Format$(Left$(.Fields("Riders").Value & " ", 60), sFormat))
sLine = sLine & UCase$(Format$(Left$(.Fields("ChangeCode").Value & " ", 4), "!@@@@"))
sLine = sLine & UCase$(Format$(Left$(.Fields("BillingAcctNo").Value & " ", 15), "!@@@@@@@@@@@@@@@"))
Print #nFileNo, sLine
.MoveNext
If (.EOF) Then Exit Do
Loop
End With

With rsRecord30
Do While ((.Fields("MemberNo").Value = rsRecord11.Fields("MemberNo").Value) _
And (.Fields("GroupSequence").Value = rsRecord11.Fields("GroupSequence").Value))
sLine = "30" & Format$(iIndex, "0000000")
sLine = sLine & Format$(.Fields("OccurrenceNo").Value, "00000")
sLine = sLine & UCase$(Format$(Left$(.Fields("PCPType").Value & " ", 1), "!@"))
sLine = sLine & UCase$(Format$(Left$(.Fields("ProviderOrg").Value & " ", 4), "!@@@@"))
sLine = sLine & UCase$(Format$(Left$(.Fields("ProviderNumber").Value & " ", 15), "!@@@@@@@@@@@@@@@"))
sLine = sLine & Format$(.Fields("EffectiveDate").Value, "yyyymmdd")
If IsNull(.Fields("TerminatedDate").Value) Then
sLine = sLine & "99999999"
Else
sLine = sLine & Format$(.Fields("TerminatedDate").Value, "yyyymmdd")
End If
sLine = sLine & UCase$(Format$(Left$(.Fields("ChangeCode").Value & " ", 4), "!@@@@"))
Print #nFileNo, sLine
.MoveNext
If (.EOF) Then Exit Do
Loop
End With
rsRecord11.MoveNext
rsRecord10.MoveNext
frmMain.UpdateMeter iIndex
Loop

MsgBox "Eligibility file has been created.", vbInformation

Exit_CreateUploadFile:
On Error Resume Next
rcsExport.Close
frmMain.RemoveMeter
frmMain.ShowStatus "Ready."
Set rcsExport = Nothing
Set cmmExport.ActiveConnection = Nothing
Set cmmExport = Nothing
CloseRecordset rsRecord10
CloseRecordset rsRecord11
CloseRecordset rsRecord20
CloseRecordset rsRecord30
Close nFileNo
Exit Sub

Err_CreateUploadFile:
Generic.ShowError
Resume Exit_CreateUploadFile
Resume
End Sub

Public Sub CreateMPowerUploadFile(ByVal psFileName As String)

On Error GoTo Err_Handler

Const PROC_NAME As String = "CreateMPowerUploadFile"
Dim cmmMPower As ADODB.Command
Dim rcsExtract As ADODB.Recordset
Dim rcsADDFAM1 As ADODB.Recordset
Dim rcsADDFAM2 As ADODB.Recordset
Dim rcsMODFAM1 As ADODB.Recordset
Dim rcsMODFAM2 As ADODB.Recordset
'Dim rcsMCDFAM1 As ADODB.Recordset
Dim sLine As String
Dim nFileNo As Long
Dim sAddFamFileName As String
Dim sModFamFileName As String
'Dim sMcdFamFileName As String
Dim nPeriodPos As Integer
Dim sPosition As Long
Dim sStatus As String

ErrorHandler.PushToStack MODULE_NAME, PROC_NAME, Screen.MousePointer
Screen.MousePointer = vbHourglass

frmMain.ShowStatus "Retrieving eligibility data from server"

Set cmmMPower = New ADODB.Command
With cmmMPower
Set .ActiveConnection = g_objDatabase.Connection
.CommandText = "sp_eligibility_export_mpower"
.CommandType = adCmdStoredProc
.CommandTimeout = 0
.Parameters.Append .CreateParameter("FileNo", adInteger, adParamInput, , frmFiles.FileNo)
End With

Set rcsExtract = New ADODB.Recordset
With rcsExtract
Set .Source = cmmMPower
.CursorLocation = adUseClient
.Open
End With

Set rcsADDFAM1 = rcsExtract.Clone
Set rcsADDFAM2 = rcsExtract.NextRecordset
Set rcsMODFAM1 = rcsExtract.NextRecordset
Set rcsMODFAM2 = rcsExtract.NextRecordset
'Set rcsMCDFAM1 = rcsExtract.NextRecordset

Call frmMain.InitializeMeter("Creating MPower Extract ", _
rcsADDFAM1.RecordCount + rcsMODFAM1.RecordCount) ' + rcsMODFAM2.RecordCount)

nFileNo = FreeFile
nPeriodPos = InStr(1, psFileName, ".")
sAddFamFileName = Left$(psFileName, nPeriodPos - 1) & "A" & Mid$(psFileName, nPeriodPos)
sModFamFileName = Left$(psFileName, nPeriodPos - 1) & "M" & Mid$(psFileName, nPeriodPos)
'sMcdFamFileName = Left$(psFileName, nPeriodPos - 1) & "D" & Mid$(psFileName, nPeriodPos)

Open sAddFamFileName For Output As #nFileNo
sPosition = 0

Do While (Not rcsADDFAM1.EOF)
sLine = "3000000000ADDFAM1"
If (Not (rcsADDFAM1.EOF And rcsADDFAM1.BOF)) Then
With rcsADDFAM1
sLine = sLine & Format$(Left$(.Fields("SubscriberNo").Value & " ", 9), "!@@@@@@@@@")
sLine = sLine & String(11, "0")
sLine = sLine & Format$(.Fields("Address2").Value & " ", "!@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@")
sLine = sLine & Format$(.Fields("Address1").Value & " ", "!@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@")
sLine = sLine & Format$(.Fields("City").Value & " ", "!@@@@@@@@@@@@@@@@@@@@@@@@")
sLine = sLine & Format$(Left$(.Fields("State").Value & " ", 2), "!@@")
sLine = sLine & Format$(Left$(.Fields("ZipCode").Value & " ", 5), "!@@@@@")
sLine = sLine & Format$(Left$(g_objDatabase.StripNonNumeric(.Fields("ZipCode").Value & ""), 9), "!@@@@@@@@@") & Space(2)
If (Not IsNull(.Fields("HomePhone").Value)) Then
sLine = sLine & Format$(Left$(g_objDatabase.StripNonNumeric(.Fields("HomePhone").Value & "") & " ", 10), "!@@@@@@@@@@")
Else
sLine = sLine & String(10, "0")
End If
If (Not IsNull(.Fields("WorkPhone").Value)) Then
sLine = sLine & Format$(Left$(g_objDatabase.StripNonNumeric(.Fields("WorkPhone").Value & "") & " ", 10), "!@@@@@@@@@@")
Else
sLine = sLine & String(10, "0")
End If
If (Not IsNull(.Fields("OtherID").Value)) Then
sLine = sLine & "1" & Format$(Left$(.Fields("OtherID").Value & " ", 20), "!@@@@@@@@@@@@@@@@@@@@")
Else
sLine = sLine & Space(21)
End If
sLine = sLine & "00000000"
sLine = sLine & Format$(Left$(.Fields("OutType").Value & " ", 1), "!@")
sLine = sLine & Format$(.Fields("EffectiveDate").Value, "yyyymmdd")
sLine = sLine & Format$(Left$(.Fields("Policy").Value & " ", 2), "!@@")
sLine = sLine & Format$(Left$(.Fields("GroupNo").Value & " ", 9), "!@@@@@@@@@")
sLine = sLine & String(36, "0")
If (Not IsNull(.Fields("HiredDate").Value)) Then
sLine = sLine & Format$(.Fields("HiredDate").Value, "yyyymmdd")
Else
sLine = sLine & String(8, "0")
End If
sLine = sLine & String(8, "0")
Print #nFileNo, sLine
End With
With rcsADDFAM2
Do While (Not rcsADDFAM2.EOF) And (.Fields("SubscriberNo").Value = rcsADDFAM1.Fields("SubscriberNo").Value)
sLine = "3010000" & Format$(.Fields("SequenceNo").Value, "000") & "ADDFAM2"
sLine = sLine & Format$(Left(Trim(.Fields("SubscriberNo").Value & " "), 9), "!@@@@@@@@@")
sLine = sLine & Format$(.Fields("SequenceNo").Value, "00")
sLine = sLine & Format$(.Fields("LastName").Value & " ", "!@@@@@@@@@@@@@@@@@@@@@@@@")
sLine = sLine & Format$(.Fields("FirstName").Value & " ", "!@@@@@@@@@@@@@@@@")
sLine = sLine & .Fields("Gender").Value
sLine = sLine & Format$(.Fields("DOB").Value, "yyyymmdd")
sLine = sLine & Format$(.Fields("RelationshipCode").Value, "!@")
sLine = sLine & Space(8)
If (Not IsNull(.Fields("SSN").Value)) Then
sLine = sLine & Format$(Left$(.Fields("SSN").Value & " ", 9), "!@@@@@@@@@")
Else
sLine = sLine & String(9, "0")
End If
sLine = sLine & Format$(Left$(.Fields("Network").Value & " ", 5), "!@@@@@")
sLine = sLine & Format$(Left$(.Fields("PriorPCP").Value & " ", 1), "!@")
sLine = sLine & Format$(Left$(.Fields("PCP").Value & " ", 12), "!@@@@@@@@@@@@")
sLine = sLine & String(24, "0") & Space(11)
Print #nFileNo, sLine
.MoveNext
If (.EOF) Then Exit Do
Loop
End With
With rcsADDFAM1
.MoveNext
sPosition = sPosition + 1
If ((Not .EOF) And ((sPosition Mod 100) = 0)) Then
Call frmMain.UpdateMeter(sPosition)
If (sPosition Mod 1000 = 0) Then
DoEvents
End If
End If
End With
End If
Loop

Close #nFileNo
Open sModFamFileName For Output As #nFileNo

With rcsMODFAM1
If (Not (.EOF And .BOF)) Then
.MoveFirst
sPosition = rcsADDFAM1.RecordCount
Do While (Not .EOF)
sLine = "3100000000MODFAM1"
sLine = sLine & Format$(Left$(.Fields("SubscriberNo").Value & " ", 9), "!@@@@@@@@@")
sLine = sLine & Format$(Left$(.Fields("PriorID").Value & " ", 11), "!@@@@@@@@@@@")
sLine = sLine & Format$(Left$(.Fields("Address2").Value & " ", 35), "!" & String(35, "@"))
sLine = sLine & Format$(Left$(.Fields("Address1").Value & " ", 35), "!" & String(35, "@"))
sLine = sLine & Format$(Left$(.Fields("City").Value & " ", 24), "!" & String(24, "@"))
sLine = sLine & Format$(Left$(.Fields("State").Value & " ", 2), "!@@")
sLine = sLine & Format$(Left$(.Fields("ZipCode").Value & " ", 5), "!@@@@@")
sLine = sLine & Format$(Left$(g_objDatabase.StripNonNumeric(.Fields("ZipCode").Value & "") & " ", 9), "!@@@@@@@@@")
sLine = sLine & " " & Format$(Left$(g_objDatabase.StripNonNumeric(.Fields("HomePhone").Value & "") & " ", 10), "!@@@@@@@@@@")
sLine = sLine & Format$(Left$(g_objDatabase.StripNonNumeric(.Fields("WorkPhone").Value & "") & " ", 10), "!@@@@@@@@@@")
sLine = sLine & Format$(Left$(.Fields("OtherIDType").Value & " ", 1), "!@")
sLine = sLine & Format$(Left$(.Fields("OtherID").Value & " ", 20), "!" & String(20, "@"))
sLine = sLine & String(8, " ")
sLine = sLine & " " & Format$(.Fields("ActionDate").Value, "yyyymmdd")
sLine = sLine & Format$(Left$(.Fields("Policy").Value & " ", 2), "!@@")
sLine = sLine & .Fields("FamilyStatus").Value
sLine = sLine & " " & Format$(Left$(.Fields("GroupNo").Value & " ", 9), "!@@@@@@@@@")
sLine = sLine & String(36, " ")
If (IsNull(.Fields("HiredDate").Value)) Then
sLine = sLine & " "
Else
sLine = sLine & Format$(.Fields("HiredDate").Value, "yyyymmdd")
End If
sLine = sLine & Space(8)
Print #nFileNo, sLine
.MoveNext
sPosition = sPosition + 1
If (Not .EOF And ((sPosition Mod 100) = 0)) Then
Call frmMain.UpdateMeter(sPosition)
If (sPosition Mod 1000 = 0) Then
DoEvents
End If
End If
Loop
End If
End With

With rcsMODFAM2
If (Not (.EOF And .BOF)) Then
.MoveFirst
sPosition = rcsADDFAM1.RecordCount + rcsMODFAM1.RecordCount

Do While (Not .EOF)
If (.Fields("RelationshipCode") <> 1) Or _
((.Fields("RelationshipCode") = 1) And _
(.Fields("MemberStatus") = "E")) Then

sLine = "3110000" & Format$(.Fields("MPowerSequence").Value, "000")
sLine = sLine & "MODFAM2"
sLine = sLine & Format$(Left$(.Fields("SubscriberNo").Value & " ", 9), "!@@@@@@@@@")
sLine = sLine & Format$(.Fields("MPowerSequence").Value, "00")
sLine = sLine & Format$(Left$(.Fields("LastName").Value & " ", 24), "!" & String(24, "@"))
sLine = sLine & Format$(Left$(.Fields("FirstName").Value & " ", 16), "!" & String(16, "@"))
sLine = sLine & " " & Format$(Left$(.Fields("MemberStatus").Value & " ", 1), "!@") & " "
If (IsNull(.Fields("ActionDate").Value)) Then
sLine = sLine & " "
Else
sLine = sLine & Format$(.Fields("ActionDate").Value, "yyyymmdd")
End If
sLine = sLine & Format$(Left$(.Fields("ChangeCode").Value & " ", 2), "!@@")
sLine = sLine & Format$(Left$(.Fields("Gender").Value & " ", 1), "!@")
If (IsNull(.Fields("DOB").Value)) Then
sLine = sLine & " "
Else
sLine = sLine & Format$(.Fields("DOB").Value, "yyyymmdd")
End If
sLine = sLine & Format$(Left$(.Fields("RelationshipCode").Value & " ", 1), "!@")
sLine = sLine & Space(8) & Format$(Left$(.Fields("SSN").Value & " ", 9), "!@@@@@@@@@")
sLine = sLine & Format$(Left$(.Fields("Network").Value & " ", 5), "!@@@@@")
'sLine = sLine & " " & Format$(Left$(.Fields("PCPNumber").Value & " ", 12), "!@@@@@@@@@@@@")
sLine = sLine & " " & Format$(.Fields("PCPNumber").Value & " ", "!000000000000")
sLine = sLine & Space(34)
Print #nFileNo, sLine

sPosition = sPosition + 1
If (Not .EOF And ((sPosition Mod 100) = 0)) Then
Call frmMain.UpdateMeter(sPosition)
If (sPosition Mod 1000 = 0) Then
DoEvents
End If
End If
End If
.MoveNext
Loop
End If

End With

Close #nFileNo
'Added for MCDFAM File
'Open sMcdFamFileName For Output As #nFileNo

' With rcsMCDFAM1
' If (Not (.EOF And .BOF)) Then
' .MoveFirst
' sPosition = rcsADDFAM1.RecordCount
' Do While (Not .EOF)
' sLine = "3500000000MCDFAM1"
' sLine = sLine & Format$(Left$(.Fields("SubscriberID").Value & " ", 9), "!@@@@@@@@@")
' sLine = sLine & Format$(Left$(.Fields("Name").Value & " ", 20), "!" & String(20, "@"))
' sLine = sLine & Format$(Left$(.Fields("HoHID").Value & " ", 9), "!@@@@@@@@@")
' sLine = sLine & Format$(Left$(.Fields("HoHName").Value & " ", 25), "!" & String(25, " "))
' sLine = sLine & Format$(Left$(.Fields("CIN").Value & " ", 9), "!@@@@@@@@@")
' sLine = sLine & Format$(Left$(.Fields("Language").Value & " ", 1), "!@")
' sLine = sLine & Format$(Left$(.Fields("Race").Value & " ", 1), "!@")
' sLine = sLine & Format$(Left$(.Fields("AidCategory").Value & " ", 2), "!@")
' sLine = sLine & Format$(Left$(.Fields("ProvMedicaidCd").Value & " ", 10), "!@@@@@@@@@@")
' sLine = sLine & Format$(Left$(.Fields("CaseNo").Value & " ", 10), "!@@@@@@@@@@")
' If (IsNull(.Fields("CertificationDate").Value)) Then
' sLine = sLine & " "
' Else
' sLine = sLine & Format$(.Fields("CertificationDate").Value, "yyyymmdd")
' End If
' If (IsNull(.Fields("RvwDate").Value)) Then
' sLine = sLine & " "
' Else
' sLine = sLine & Format$(.Fields("RvwDate").Value, "yyyymmdd")
' End If
' sLine = sLine & Format$(Left$(.Fields("CaseName").Value & " ", 24), "!" & String(24, "@"))
' Print #nFileNo, sLine
' .MoveNext
' sPosition = sPosition + 1
' If (Not .EOF And ((sPosition Mod 100) = 0)) Then
' Call frmMain.UpdateMeter(sPosition)
' If (sPosition Mod 1000 = 0) Then
' DoEvents
' End If
' End If
' Loop
' End If
'End With

sStatus = "Successfully created MPower extract files!"

Exit_Handler:
On Error Resume Next
CloseRecordset rcsExtract
CloseRecordset rcsADDFAM1
CloseRecordset rcsADDFAM2
CloseRecordset rcsMODFAM1
CloseRecordset rcsMODFAM2
'CloseRecordset rcsMCDFAM1
Set cmmMPower = Nothing
Call frmMain.RemoveMeter
If (nFileNo <> 0) Then Close #nFileNo
Screen.MousePointer = ErrorHandler.PullFromStack
frmMain.ShowStatus sStatus
Exit Sub

Err_Handler:
ErrorHandler.ShowError Err
sStatus = "Unable to create MPower Extract files"
Resume Exit_Handler
Resume
End Sub

Public Sub CloseRecordset(ByVal Recordset As ADODB.Recordset)
If (Not Recordset Is Nothing) Then
If (Recordset.State = adStateOpen) Then Recordset.Close
Set Recordset = Nothing
End If
End Sub

Public Function StandardFont() As stdole.StdFont
Dim ReturnValue As stdole.StdFont
Set ReturnValue = New stdole.StdFont
With ReturnValue
.Name = "Times New Roman"
.Size = 8
.Bold = False
.Italic = False
.Underline = False
.Strikethrough = False
End With
Set StandardFont = ReturnValue
End Function

Public Function UpdateGroupTiers()

On Error GoTo Err_Routine

Const HEADER_LINES As Integer = 5
Dim sFileName As String
Dim bInPage As Boolean
Dim bInGroup As Boolean
Dim nFileNo As Integer
Dim sLine As String
Dim sGroupNo As String
Dim iCounter As Integer
Dim cmmGroupTier As ADODB.Command

ErrorHandler.PushToStack "Generic", "UpdateGroupTiers", Screen.MousePointer
Screen.MousePointer = vbHourglass

sFileName = g_objGUIClass.GetFileName()
If (sFileName = "") Then
GoTo Exit_Routine
End If

nFileNo = FreeFile()
Open sFileName For Input As #nFileNo
iCounter = 0
Do While (Not EOF(nFileNo))
Line Input #nFileNo, sLine
iCounter = iCounter + 1
Loop
Close #nFileNo

frmMain.InitializeMeter "Loading groups and tiers", iCounter

Set cmmGroupTier = New ADODB.Command
With cmmGroupTier
Set .ActiveConnection = g_objDatabase.Connection
.CommandType = adCmdStoredProc
.CommandText = "sp_grouptier_update"
.Parameters.Append .CreateParameter("GroupNo", adChar, adParamInput, 6)
.Parameters.Append .CreateParameter("BenefitPkg", adChar, adParamInput, 4)
.Parameters.Append .CreateParameter("TierCode", adChar, adParamInput, 4)
.Parameters.Append .CreateParameter("EffectiveDate", adDate, adParamInput)
.Parameters.Append .CreateParameter("TerminatedDate", adDate, adParamInput)
End With

nFileNo = FreeFile
Open sFileName For Input As #nFileNo

'* Uses results of GRPTIERS query in TMH library of TXEN
iCounter = 0
Do While (Not EOF(nFileNo))
Line Input #nFileNo, sLine
With cmmGroupTier
.Parameters("GroupNo").Value = g_objDatabase.GetFieldValue(sLine, 1, 6)
.Parameters("BenefitPkg").Value = g_objDatabase.GetFieldValue(sLine, 7, 4)
.Parameters("TierCode").Value = g_objDatabase.GetFieldValue(sLine, 11, 4)
.Parameters("EffectiveDate").Value = g_objDatabase.ConvertToDate(Mid$(sLine, 15, 8))
.Parameters("TerminatedDate").Value = g_objDatabase.ConvertToDate(Mid$(sLine, 23, 8))
.Execute
iCounter = iCounter + 1
If ((iCounter Mod 10) = 0) Then
frmMain.UpdateMeter iCounter
End If
End With
Loop

frmMain.ShowStatus "Groups and tiers updated successfully"

Exit_Routine:
On Error Resume Next
Set cmmGroupTier.ActiveConnection = Nothing
Set cmmGroupTier = Nothing
Close nFileNo
frmMain.RemoveMeter
Screen.MousePointer = ErrorHandler.PullFromStack
Exit Function

Err_Routine:
ErrorHandler.ShowError Err
frmMain.ShowStatus "Unable to update groups and tiers"
Resume Exit_Routine
Resume
End Function

jwellis

ASKER

Here is the step that processes the file.

Private Sub mnuFileProcess_Click()

On Error GoTo Err_Handler

Const PROC_NAME As String = "ProcessFile"
Dim sMessage As String
Dim typResult As VBA.VbMsgBoxResult
Dim cmmProcess As ADODB.Command
Dim sFileDescription As String

ErrorHandler.PushToStack MODULE_NAME, PROC_NAME, Screen.MousePointer

sMessage = "Once a file has been processed, no further "
sMessage = sMessage & "changes can be made can be made to "
sMessage = sMessage & "the records contained on this file. "
sMessage = sMessage & "This option should be chosen only after "
sMessage = sMessage & "all changes contained in this file have "
sMessage = sMessage & "been recorded in TXEN and/or MPower." & vbCrLf & vbCrLf
sMessage = sMessage & "Do you wish to continue?"

typResult = MsgBox(sMessage, vbYesNo + vbQuestion)
If (typResult = vbYes) Then
sFileDescription = InputBox("Enter a file description", "File Description", _
frmFiles.FileDescription)
Screen.MousePointer = vbHourglass
g_objDatabase.BeginTrans
Set cmmProcess = New ADODB.Command
With cmmProcess
Set .ActiveConnection = g_objDatabase.Connection
.CommandText = "sp_file_process"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("FileNo", adInteger, adParamInput, , frmFiles.FileNo)
.Parameters.Append .CreateParameter("FileDescription", adVarChar, adParamInput, 255, sFileDescription)
.Execute
End With
g_objDatabase.CommitTrans
frmFiles.RefreshGrid
End If

Exit_Handler:
On Error Resume Next
If (Not (cmmProcess Is Nothing)) Then
Set cmmProcess.ActiveConnection = Nothing
Set cmmProcess = Nothing
End If
Screen.MousePointer = ErrorHandler.PullFromStack
Exit Sub

Err_Handler:
g_objDatabase.RollbackTrans
ErrorHandler.ShowError Err
Resume Exit_Handler
End Sub

wqw

the most plausible place to look for error is frmFiles.RefreshGrid just after commiting the transaction. you are rollback-ing in error handler but your trans is already commited.

other less plausible code is your ErrorHandler.PullFromStack gadget.

</wqw>

formula

Actually, I'm thinking more of sql code. I am not a VB person, but if I looked at you process correctly, it appears that you are calling a stored procedure 'sp_file_process'. Can you run this stored procedure in a ISQL window successfully? The error message is where you have the SQL engine attempting to rollback a command in which it has no corresponding "begin transaction". So look at the stored procedure code and make sure a "begin transaction" exists. Then look at your VB code and make sure that your "begin transaction" is performed in the current connection where the "rollback transaction" was issued that gave you the error. I can't tell about the VB code for sure. You're the expert on that. But, yes, this is a SQL error which is happening either within the SP or the VB code. Hope this helps!!

jwellis

ASKER

Here are both of those, but I still don't know how to fix the problem.

Public Sub RefreshGrid()
With adgFiles
.SQL = "EXEC sp_files_get"
.AddColumn "File No.", 576, True
.AddColumn "Date", 1152, True
.AddColumn "File Type", 1152, True, , True
.AddColumn "Processed", 1152, True
.AddColumn "Description", 2880, True, , True
Set .Connection = g_objDatabase.Connection
.Execute
.Refresh
End With
End Sub

Public Function PullFromStack() As MousePointerConstants

Dim objItem As clsStackItem

Set objItem = m_clnStack(m_nCurrentIndex)
PullFromStack = objItem.MouseCursor
Set objItem = Nothing
m_clnStack.Remove m_nCurrentIndex
m_nCurrentIndex = m_nCurrentIndex - 1

End Function

jwellis

ASKER

Formula, I ran sp_file_process in ISQL and did not have any problems. Here is the code if you want to take a look at it.

print 'sp_file_process'
GO

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'sp_file_process' AND type = 'P') BEGIN
      DROP PROCEDURE sp_file_process
END
GO

CREATE PROCEDURE sp_file_process
      @FileNo                  int,
      @FileDescription      varchar(255)
AS
/*
*******************************************************************************
**
** Procedure: sp_file_process
**
** Description:
**      This procedure is used to process all transactions associated
**      with a file. New and updated records will be created in the
**      tb_members, tb_group_history, tb_addresses, tb_billing_history,
**      and tb_pcp_history tables if necessary.
**
** Parameters:
**      <???>
**
** Returns:
**      <???>
**
** Modification History:
** Author            Date            Modifications
** -------------------- --------------- ---------------------------------------
** Martin Mason            8/30/1999      Original creation
** MM                  11/3/1999      Fixed problem with reinstatements.
** MM                  11/12/1999      Set up to call sp_temp_TXEN_storage
*******************************************************************************
*/
      SET NOCOUNT ON

      /*
      ** ********************************************************************
      ** Check to see that file has not already been processed.
      ** ********************************************************************
      */
      IF EXISTS(SELECT * FROM tb_files WHERE file_no = @FileNo AND file_has_processed = 'Y')
            RETURN

      CREATE TABLE #Members (
            MemberNo      char(15)      NOT NULL,
            FileNo            tinyint            NOT NULL,
            SubscriberNo      char(15)      NOT NULL,
            MPowerSequence      tinyint            NOT NULL,
            Relationship      char(1)            NOT NULL,
            FirstName      char(15)      NOT NULL,
            LastName      char(20)      NOT NULL,
            MidInit            char(1)            NULL,
            Suffix            char(4)            NULL,
            SSN            char(12)      NULL,
            AltMemberNo      char(15)      NULL,
            AccountNo      char(15)      NULL,
            DOB            datetime      NOT NULL,
            DOD            datetime      NULL,
            Gender            char(1)            NOT NULL,
            Race            char(1)            NULL,
            Status            char(1)            NULL,
            StatusDate      datetime      NULL,
            HiredDate      datetime      NULL,
            Employer      char(25)      NULL,
            Comments      char(20)      NULL,
            Address1      char(25)      NOT NULL,
            Address2      char(25)      NULL,
            City            char(16)      NOT NULL,
            State            char(2)            NOT NULL,
            ZipCode            char(10)      NOT NULL,
            HomePhone      char(10)      NULL,
            WorkPhone      char(10)      NULL,
            WorkPhoneExt      char(4)            NULL,
            MemberType      char(2)            NULL,
            AreaCode      char(4)            NULL,
            PrimaryLanguage      char(4)            NULL,
            PrintMCard      bit            NOT NULL,
            PrintECard      bit            NOT NULL,
            PrintLabel      bit            NOT NULL,
            PrintPolicy      bit            NOT NULL,
            MedicaidCode      char(3)            NULL,
            PRIMARY KEY (MemberNo)
      )

      CREATE TABLE #GroupHistory (
            MemberNo      char(15)      NOT NULL,
            GroupSequence      tinyint            NOT NULL,
            GroupNo            char(6)            NOT NULL,
            FileNo            int            NOT NULL,
            BillingAcct      char(15)      NULL,
            EffectiveDate      datetime      NOT NULL,
            TerminatedDate      datetime      NULL,
            PRIMARY KEY (MemberNo, GroupSequence)
      )

      CREATE TABLE #BillingHistory (
            MemberNo      char(15)      NOT NULL,
            GroupSequence      tinyint            NOT NULL,
            BillingSequence      tinyint            NOT NULL,
            TierID            int            NOT NULL,
            FileNo            int            NOT NULL,
            BenefitRiders      char(60)      NULL,
            EffectiveDate      datetime      NOT NULL,
            TerminatedDate      datetime      NULL,
            ChangeCode      char(4)            NULL,
            AccountNo      char(15)      NULL,
            PRIMARY KEY (MemberNo, GroupSequence, BillingSequence)
      )

      CREATE TABLE #PCPHistory (
            MemberNo      char(15)      NOT NULL,
            GroupSequence      tinyint            NOT NULL,
            PCPSequence      tinyint            NOT NULL,
            ProviderID      int            NOT NULL,
            FileNo            int            NOT NULL,
            ProviderOrg      char(4)            NULL,
            PCPType            char(1)            NOT NULL,
            EffectiveDate      datetime      NOT NULL,
            TerminatedDate      datetime      NULL,
            ChangeCode      char(4)            NULL,
            PRIMARY KEY (MemberNo, GroupSequence, PCPSequence)
      )

      EXEC sp_temp_TXEN_storage @FileNo with recompile

      BEGIN TRAN

      /*
      ** ********************************************************************
      ** Update member table with records that have changed in the
      ** #Members temporary table.
      ** ********************************************************************
      */
      UPDATE      tb_members
      SET      file_no                  = t.FileNo,
            mem_fname            = t.FirstName,
            mem_lname            = t.LastName,
            mem_mid_init            = t.MidInit,
            mem_suffix            = t.Suffix,
            mem_ssn                  = t.SSN,
            mem_alt_member_no      = t.AltMemberNo,
            mem_account_no            = t.AccountNo,
            mem_dob                  = t.DOB,
            mem_dod                  = t.DOD,
            mem_gender            = t.Gender,
            mem_race            = t.Race,
            mem_status            = t.Status,
            mem_status_date            = t.StatusDate,
            mem_hired_date            = t.HiredDate,
            mem_employer            = t.Employer,
            mem_comments            = t.Comments,
            mem_address1            = t.Address1,
            mem_address2            = t.Address2,
            mem_city            = t.City,
            mem_state            = t.State,
            mem_zip_code            = t.ZipCode,
            mem_home_phone            = t.HomePhone,
            mem_work_phone            = t.WorkPhone,
            mem_work_extension      = t.WorkPhoneExt,
            mem_member_type_code      = t.MemberType,
            mem_area_code            = t.AreaCode,
            mem_primary_language      = t.PrimaryLanguage,
            mem_print_member_card      = t.PrintMCard,
            mem_print_electronic_card = t.PrintECard,
            mem_print_address_label      = t.PrintLabel,
            mem_print_policy_certificate = t.PrintPolicy,
            mem_medicaid_relationship = t.MedicaidCode
      FROM      #Members t
      WHERE      tb_members.mem_member_no = t.MemberNo
       AND      t.FileNo            = @FileNo

      UPDATE      tb_group_history
      SET      grp_number            = t.GroupNo,
            file_no                  = t.FileNo,
            grp_billing_account      = t.BillingAcct,
            grp_effective_date      = t.EffectiveDate,
            grp_terminated_date      = t.TerminatedDate
      FROM      #GroupHistory t
      WHERE      tb_group_history.mem_member_no      = t.MemberNo
       AND      tb_group_history.grp_seq_no      = t.GroupSequence
       AND      t.FileNo                  = @FileNo

      UPDATE      tb_billing_history
      SET      file_no                  = t.FileNo,
            tier_id                  = t.TierID,
            bil_benefit_riders      = t.BenefitRiders,
            bil_effective_date      = t.EffectiveDate,
            bil_terminated_date      = t.TerminatedDate,
            bil_change_code            = t.ChangeCode,
            bil_account_no            = t.AccountNo
      FROM      #BillingHistory t
      WHERE      tb_billing_history.mem_member_no      = t.MemberNo
       AND      tb_billing_history.grp_seq_no            = t.GroupSequence
       AND      tb_billing_history.bil_seq_no            = t.BillingSequence
       AND      t.FileNo                        = @FileNo

      UPDATE      tb_pcp_history
      SET      file_no                  = t.FileNo,
            prv_id                  = t.ProviderID,
            pcp_provider_org      = t.ProviderOrg,
            pcp_type            = t.PCPType,
            pcp_effective_date      = t.EffectiveDate,
            pcp_terminated_date      = t.TerminatedDate,
            pcp_change_code            = t.ChangeCode
      FROM      #PCPHistory t
      WHERE      tb_pcp_history.mem_member_no      = t.MemberNo
       AND      tb_pcp_history.grp_seq_no      = t.GroupSequence
       AND      tb_pcp_history.pcp_seq_no      = t.PCPSequence
       AND      t.FileNo                  = @FileNo

      INSERT INTO tb_members (
            mem_member_no,            file_no,            mem_subscriber_no,
            mem_mpower_seq_no,
            mem_relationship_code,      mem_fname,            mem_lname,
            mem_mid_init,            mem_suffix,            mem_ssn,
            mem_alt_member_no,      mem_account_no,            mem_dob,
            mem_dod,            mem_gender,            mem_race,
            mem_status,            mem_status_date,      mem_hired_date,
            mem_employer,            mem_comments,            mem_address1,
            mem_address2,            mem_city,            mem_state,
            mem_zip_code,            mem_home_phone,            mem_work_phone,
            mem_work_extension,      mem_member_type_code,      mem_area_code,
            mem_primary_language,      mem_print_member_card,      mem_print_electronic_card,
            mem_print_address_label,mem_print_policy_certificate, mem_medicaid_relationship
      ) SELECT
            MemberNo,            FileNo,                  SubscriberNo,
            MPowerSequence,
            Relationship,            FirstName,            LastName,
            MidInit,            Suffix,                  SSN,
            AltMemberNo,            AccountNo,            DOB,
            DOD,                  Gender,                  Race,
            Status,                  StatusDate,            HiredDate,
            Employer,            Comments,            Address1,
            Address2,            City,                  State,
            ZipCode,            HomePhone,            WorkPhone,
            WorkPhoneExt,            MemberType,            AreaCode,
            PrimaryLanguage,      PrintMCard,            PrintECard,
            PrintLabel,            PrintPolicy,            MedicaidCode
      FROM      #Members
      WHERE      NOT EXISTS (
                  SELECT      * FROM tb_members m
                  WHERE      m.mem_member_no            = #Members.MemberNo
            )

      INSERT INTO tb_group_history (
            mem_member_no,            grp_seq_no,            file_no,
            grp_number,            grp_billing_account,      grp_effective_date,
            grp_terminated_date
      ) SELECT
            MemberNo,            GroupSequence,            FileNo,
            GroupNo,            BillingAcct,            EffectiveDate,
            TerminatedDate
      FROM      #GroupHistory
      WHERE      NOT EXISTS(
                  SELECT      * FROM tb_group_history gh
                  WHERE      gh.mem_member_no      = #GroupHistory.MemberNo
                   AND      gh.grp_seq_no            = #GroupHistory.GroupSequence
            )

      INSERT INTO tb_billing_history (
            mem_member_no,            grp_seq_no,            bil_seq_no,
            file_no,            tier_id,            bil_benefit_riders,
            bil_effective_date,      bil_terminated_date,      bil_change_code,
            bil_account_no
      ) SELECT
            MemberNo,            GroupSequence,            BillingSequence,
            FileNo,                  TierID,                  BenefitRiders,
            EffectiveDate,            TerminatedDate,            ChangeCode,
            AccountNo
      FROM      #BillingHistory
      WHERE      NOT EXISTS (
                  SELECT      * FROM tb_billing_history bh
                  WHERE      bh.mem_member_no      = #BillingHistory.MemberNo
                   AND      bh.grp_seq_no            = #BillingHistory.GroupSequence
                   AND      bh.bil_seq_no            = #BillingHistory.BillingSequence
            )

      INSERT INTO tb_pcp_history(
            mem_member_no,            grp_seq_no,            file_no,
            pcp_seq_no,            pcp_type,            prv_id,
            pcp_provider_org,      pcp_effective_date,      pcp_terminated_date,
            pcp_change_code
      ) SELECT
            MemberNo,            GroupSequence,            FileNo,
            PCPSequence,            PCPType,            ProviderID,
            ProviderOrg,            EffectiveDate,            TerminatedDate,
            ChangeCode
      FROM      #PCPHistory
      WHERE      NOT EXISTS (
                  SELECT      * FROM tb_pcp_history ph
                  WHERE      ph.mem_member_no      = #PCPHistory.MemberNo
                   AND      ph.grp_seq_no            = #PCPHistory.GroupSequence
                   AND      ph.pcp_seq_no            = #PCPHistory.PCPSequence
            )

      UPDATE      tb_files
      SET      file_description      = @FileDescription,
            file_has_processed      = 'Y'
      WHERE      file_no                  = @FileNo

      COMMIT TRAN

      DROP TABLE #Members
      DROP TABLE #GroupHistory
      DROP TABLE #BillingHistory
      DROP TABLE #PCPHistory

GO

GRANT EXECUTE ON sp_file_process TO ELIG_WORKER_GROUP
GO

jwellis

ASKER

Formula, you still there? I think I found the problem. The procedure sp_file_process was executing a procedure in the test database called sp_temp_TXEN_storage. I commented out the EXECUTE for this sp_temp_TXEN_storage and the application ran correctly. The only problem now is that I need to find out where the problem is in this procedure and get it to work again. When I try to run the TXEN_storage again, I get the following error: Cannot add rows to Sysdepends for the current stored procedure because it depends on the missing object 'sp_temp_TXEN_storage'. The stored procedure will still be created. How do I get sp_file_process to recognize that it is in the other database?

print 'sp_temp_TXEN_storage'
GO

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'sp_temp_TXEN_storage' AND type = 'P') BEGIN
      DROP PROCEDURE sp_temp_TXEN_storage
END
GO

CREATE PROCEDURE sp_temp_TXEN_storage
      @FileNo            int

AS
/*
*******************************************************************************
**
** Procedure: sp_temp_TXEN_storage
**
** Description:
**      <???>
**
** Parameters:
**      <???>
**
** Returns:
**      <???>
**
** Modification History:
** Author            Date            Modifications
** -------------------- --------------- ---------------------------------------
** Martin Mason            11/11/1999      Original creation
**
*******************************************************************************
*/
      --DECLARE      @FileNo            int
      --SELECT      @FileNo            = 2

      CREATE TABLE #AccountList(
            AccountNo            char(15),
            GroupSequence            tinyint,
            PRIMARY KEY(AccountNo, GroupSequence)
      )

      DECLARE      @MemberNo            char(15),
            @AccountNo            char(9),
            @OldGroupSequence      tinyint,
            @NewGroupSequence      tinyint,
            @OldBillingSequence      tinyint,
            @NewBillingSequence      tinyint,
            @PackageID            int,
            @GroupNo            char(6),
            @Riders                  varchar(60),
            @EffectiveDate            datetime,
            @TerminatedDate            datetime,
            @ProviderID            int,
            @ProviderOrg            char(4),
            @OldPCPSequence            tinyint,
            @NewPCPSequence            tinyint

      SET NOCOUNT ON

     DECLARE      @StartTime      datetime,
            @InDebug      bit
      SELECT      @StartTime      = CURRENT_TIMESTAMP,
            @InDebug      = 0

      DELETE FROM wktb_TXEN_export WHERE SPID = @@SPID
      DELETE FROM wktb_pcp_history WHERE SPID = @@SPID
      DELETE FROM wktb_billing_history WHERE SPID = @@SPID
      DELETE FROM wktb_group_history WHERE SPID = @@SPID
      DELETE FROM wktb_members WHERE SPID = @@SPID
      DELETE FROM wktb_accounts WHERE SPID = @@SPID

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 0: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END


      /*
      ** ********************************************************************
      ** Get list of accounts for particular file (Step 1)
      ** ********************************************************************
      */
      INSERT INTO #AccountList (
            AccountNo,            GroupSequence
      ) SELECT
            a.act_account_no,      gh.grp_seq_no
      FROM      tb_accounts a (NOLOCK INDEX=ci_account_no) INNER JOIN tb_group_history gh (NOLOCK INDEX=fkci_account) ON
                  a.act_account_no      = gh.act_account_no
      WHERE      a.file_no            = @FileNo
       OR      gh.file_no            = @FileNo

       OR      EXISTS (
                  SELECT * FROM tb_members m (NOLOCK INDEX=cifk_account)
                  WHERE      m.act_account_no      = a.act_account_no
                   AND      m.file_no            = a.file_no
            )
       OR      EXISTS (
                  SELECT * FROM tb_billing_history bh (NOLOCK INDEX=ci_account)
                  WHERE      bh.act_account_no      = a.act_account_no
                   AND      bh.file_no            = a.file_no
            )
       OR      EXISTS (
                  SELECT * FROM tb_pcp_history ph (NOLOCK INDEX=ci_account)
                  WHERE      ph.act_account_no      = a.act_account_no
                   AND      ph.file_no            = a.file_no
            )

      INSERT INTO wktb_accounts (
            SPID,                  AccountNo,            file_no,
            AltAccountNo,            HiredDate,            Employer,
            Address1,            Address2,            City,
            State,                  ZipCode,            HomePhone,
            WorkPhone,            WorkExtension,            AreaCode,
            PrintMCard,            PrintECard,            PrintLabel,
            PrintPolicy
      ) SELECT
            @@SPID,                  a.act_account_no,      a.file_no,
            a.act_alt_account_no,      a.act_hired_date,      a.act_employer,
            a.act_address1,            a.act_address2,            a.act_city,
            a.act_state,            a.act_zip_code,            a.act_home_phone,
            a.act_work_phone,      a.act_work_extension,      a.act_area_code,
            a.act_print_member_card, a.act_print_electronic_card, a.act_print_address_label,
            a.act_print_policy_certificate
      FROM      tb_accounts a (NOLOCK INDEX=ci_account_no)
      WHERE      EXISTS (SELECT * FROM #AccountList t WHERE t.AccountNo = a.act_account_no)

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 1: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      INSERT INTO #AccountList (
            AccountNo,            GroupSequence
      ) SELECT ta.tra_account_no,      1
      FROM      tb_account_transactions ta (NOLOCK INDEX=ci_accttrans)
      WHERE      ta.file_no            = @FileNo
       AND      NOT EXISTS (
                  SELECT      * FROM #AccountList t
                  WHERE      t.AccountNo            = ta.tra_account_no
            )
       AND      EXISTS (
                  SELECT      * FROM tb_member_transactions tm (NOLOCK INDEX=cifk_accttrans)
                  WHERE      tm.tra_account_no      = ta.tra_account_no
            )

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 1e: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Select all members that are associated with the subscribers for
      **       this file (Step 2)
      ** ********************************************************************
      */
      INSERT INTO wktb_members (
            SPID,
            AccountNo,            TXENSuffix,            file_no,
            MPowerSequenceNo,      Relationship,            FirstName,
            LastName,            MidInit,            Suffix,
            SSN,                  DOB,                  DOD,
            Gender,                  Race,                  Status,
            StatusDate,            Comments,            MemberType,
            PrimaryLanguage,      MedicaidRelationship
      ) SELECT
            @@SPID,
            m.act_account_no,      m.mem_txen_suffix,      m.file_no,
            m.mem_mpower_seq_no,      m.mem_relationship_code,m.mem_fname,
            m.mem_lname,            m.mem_mid_init,            m.mem_suffix,
            m.mem_ssn,            m.mem_dob,            m.mem_dod,
            m.mem_gender,            m.mem_race,            m.mem_status,
            m.mem_status_date,      m.mem_comments,            m.mem_member_type_code,
            m.mem_primary_language,      m.mem_medicaid_relationship
      FROM      tb_members m (NOLOCK INDEX=cifk_account) INNER JOIN #AccountList t ON
                  m.act_account_no      = t.AccountNo
              AND      t.GroupSequence            = 1

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 2: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Copy all group history records for members (Step 3)
      ** ********************************************************************
      */
      INSERT INTO wktb_group_history (
            SPID,
            AccountNo,            GroupSequence,            GroupNo,
            file_no,            BillingAcct,            EffectiveDate,
            TerminatedDate
      ) SELECT
            @@SPID,
            gh.act_account_no,      gh.grp_seq_no,            gh.grp_number,
            gh.file_no,            gh.grp_billing_account,      gh.grp_effective_date,
            gh.grp_terminated_date
      FROM      tb_group_history gh (NOLOCK INDEX=fkci_account) INNER JOIN #AccountList t ON
                  gh.act_account_no      = t.AccountNo
              AND      gh.grp_seq_no            = t.GroupSequence

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 3: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Copy all billing history records for members (Step 4)
      ** ********************************************************************
      */
      INSERT INTO wktb_billing_history (
            SPID,
            AccountNo,            GroupSequence,            BillingSequence,
            PackageID,            file_no,            BenefitRiders,
            EffectiveDate,            TerminatedDate,
            ChangeCode,            BillingAcctNo
      ) SELECT
            @@SPID,
            bh.act_account_no,      bh.grp_seq_no,            bh.bil_seq_no,
            bh.bpkg_id,            bh.file_no,            bh.bil_benefit_riders,
            bh.bil_effective_date,      bh.bil_terminated_date,
            bh.bil_change_code,      bh.bil_account_no
      FROM      tb_billing_history bh (NOLOCK INDEX=ci_account) INNER JOIN #AccountList t ON
                  bh.act_account_no            = t.AccountNo
              AND      bh.grp_seq_no                  = t.GroupSequence

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 4: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Copy pcp history records for all members (Step 5)
      ** ********************************************************************
      */
      INSERT INTO wktb_pcp_history (
            SPID,
            AccountNo,            TXENSuffix,            GroupSequence,
            PCPSequence,            ProviderID,            file_no,
            ProviderOrg,            PCPType,            EffectiveDate,
            TerminatedDate,            ChangeCode
      ) SELECT
            @@SPID,
            ph.act_account_no,      ph.mem_txen_suffix,      grp_seq_no,
            ph.pcp_seq_no,            ph.prv_id,            ph.file_no,
            ph.pcp_provider_org,      ph.pcp_type,            ph.pcp_effective_date,
            ph.pcp_terminated_date,      ph.pcp_change_code
      FROM      tb_pcp_history ph (NOLOCK INDEX=ci_account) INNER JOIN #AccountList t (NOLOCK) ON
                  ph.act_account_no            = t.AccountNo
              AND      ph.grp_seq_no                  = t.GroupSequence

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 5: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Create account records for all new accounts. (Step 6a)
      ** ********************************************************************
      */
      INSERT INTO wktb_accounts (
            AccountNo,            SPID,                  file_no,
            AltAccountNo,
            BillingAcctNo,            HiredDate,            Employer,
            Address1,            Address2,            City,
            State,                  ZipCode,            HomePhone,
            WorkPhone,            WorkExtension,            AreaCode,
            PrintMCard,            PrintECard,            PrintLabel,
            PrintPolicy
      ) SELECT
            ta.tra_account_no,      @@SPID,                  ta.file_no,
            ta.tra_alt_account_no,
            ta.tra_billing_acct_no,      ta.tra_hired_date,      ta.tra_employer,
            ta.tra_address1,      ta.tra_address2,      ta.tra_city,
            ta.tra_state,            ta.tra_zip_code,      ta.tra_home_phone,
            ta.tra_work_phone,      ta.tra_work_extension,      ta.tra_area_code,
            ta.tra_print_member_card, ta.tra_print_electronic_card, ta.tra_print_address_label,
            ta.tra_print_policy_certificate
      FROM      tb_account_transactions ta (NOLOCK INDEX=ci_accttrans) INNER JOIN tb_member_transactions tm (NOLOCK INDEX=cifk_accttrans) ON
                  ta.tra_account_no      = tm.tra_account_no
              AND      tm.trm_txen_suffix      = '00'
              AND      tm.trm_action_code      = 'A'
              AND      tm.trm_include_in_export= 1
            INNER JOIN tb_files f ON
                  ta.file_no            = f.file_no
              AND      f.file_has_processed      = 'N'
              AND      f.file_no            = @FileNo

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 6a: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Create member records for all the new additions. (Step 6b)
      ** ********************************************************************
      */
      INSERT INTO wktb_members (
            SPID,
            AccountNo,            TXENSuffix,            file_no,
            MPowerSequenceNo,      Relationship,            FirstName,
            LastName,            MidInit,            Suffix,
            SSN,                  DOB,                  DOD,
            Gender,                  Race,                  Status,
            StatusDate,            Comments,            MemberType,
            PrimaryLanguage,      MedicaidRelationship
      ) SELECT
            @@SPID,
            tm.tra_account_no,      tm.trm_txen_suffix,      tm.file_no,
            tm.trm_mpower_seq_no,      tm.trm_relationship_code,tm.trm_fname,
            tm.trm_lname,            tm.trm_mid_init,      tm.trm_suffix,
            tm.trm_ssn,            tm.trm_dob,            NULL,
            tm.trm_gender,            tm.trm_race,            tm.trm_status,
            tm.trm_status_date,      NULL,                  NULL,
            NULL,                  NULL
      FROM      tb_member_transactions tm (NOLOCK) INNER JOIN tb_files f (NOLOCK) ON
                  tm.file_no            = f.file_no
              AND      f.file_has_processed      = 'N'
              AND      f.file_no            = @FileNo
      WHERE      tm.trm_include_in_export      = 1
       AND      tm.trm_action_code            = 'A'
       AND      NOT EXISTS (
                  SELECT      * FROM wktb_members wm (NOLOCK INDEX=cifk_waccount)
                  WHERE      wm.AccountNo            = tm.tra_account_no
                   AND      wm.TXENSuffix            = tm.trm_txen_suffix
                   AND      wm.SPID                  = @@SPID
            )

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 6b: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Process drops for all dependents for members that have dropped
      ** and may not appear on the file. This step will also terminate
      ** dependents on file whose record may not reflect the correct
      ** termination date. (Step 7)
      ** ********************************************************************
      */
      UPDATE      wgh
      SET      TerminatedDate                  = tm.trm_terminated_date,
            file_no                        = tm.file_no
      FROM      wktb_group_history wgh (NOLOCK INDEX=cifk_account) INNER JOIN tb_member_transactions tm (NOLOCK INDEX=cifk_accttrans) ON
                  wgh.AccountNo            = tm.tra_account_no
              AND      tm.trm_relationship_code= '1'
              AND      tm.trm_include_in_export= 1
              AND      tm.trm_action_code      = 'T'
            INNER JOIN tb_files f (NOLOCK) ON
                  tm.file_no            = f.file_no
              AND      f.file_has_processed      = 'N'
              AND      f.file_no            = @FileNo
      WHERE      wgh.TerminatedDate            IS NULL
       AND      wgh.SPID                  = @@SPID

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 7: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Process drops from transaction file in the billing history table.
      ** This step will also terminate dependents for all subscribers whose
      ** record may not reflect the correct termination date. (Step 8)
      ** ********************************************************************
      */
      UPDATE      wbh
      SET      TerminatedDate                        = tm.trm_terminated_date,
            file_no                              = tm.file_no
      FROM      wktb_billing_history wbh (NOLOCK INDEX=ci_account) INNER JOIN tb_member_transactions tm (NOLOCK INDEX=cifk_accttrans) ON
                  wbh.AccountNo            = tm.tra_account_no
              AND      tm.trm_relationship_code= '1'
              AND      tm.trm_include_in_export= 1
              AND      tm.trm_action_code      = 'T'
            INNER JOIN tb_files f (NOLOCK) ON
                  tm.file_no            = f.file_no
              AND      f.file_has_processed      = 'N'
              AND      f.file_no            = @FileNo
      WHERE      wbh.TerminatedDate            IS NULL
       AND      wbh.SPID                  = @@SPID

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 8: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Process drops from transaction file in the PCP history table.
      ** This step will also terminate dependents for all subscribers whose
      ** record may not reflect the correct termination date. (Step 9)
      ** ********************************************************************
      */
      UPDATE      wph
      SET      TerminatedDate                  = tm.trm_terminated_date,
            file_no                        = tm.file_no
      FROM      wktb_pcp_history wph (NOLOCK INDEX=ci_account) INNER JOIN tb_member_transactions tm (NOLOCK INDEX=cifk_accttrans) ON
                  wph.AccountNo            = tm.tra_account_no
              AND      wph.TXENSuffix            = tm.trm_txen_suffix
              AND      tm.trm_action_code      = 'T'
              AND      tm.trm_include_in_export= 1
            INNER JOIN tb_files f (NOLOCK) ON
                  tm.file_no            = f.file_no
              AND      f.file_has_processed      = 'N'
              AND      f.file_no            = @FileNo
      WHERE      wph.TerminatedDate            IS NULL
       AND      wph.SPID                  = @@SPID

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 9: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Create group history records for all new additions. (Step 10)
      ** ********************************************************************
      */
      INSERT INTO wktb_group_history (
            SPID,
            AccountNo,            GroupSequence,            GroupNo,
            file_no,            BillingAcct,            EffectiveDate,
            TerminatedDate
      ) SELECT
            @@SPID,
            ta.tra_account_no,      (
                        SELECT       ISNULL((MAX(wgh2.GroupSequence) + 1), 1)
                        FROM       wktb_group_history wgh2 (NOLOCK INDEX=cifk_account)
                        WHERE       wgh2.AccountNo       = ta.tra_account_no
                          AND      wgh2.SPID      = @@SPID
                              ),
                                                ta.grp_number,
            f.file_no,            NULL,                  tm.trm_effective_date,
            tm.trm_terminated_date
      FROM      tb_account_transactions ta (NOLOCK INDEX=ci_accttrans) INNER JOIN tb_member_transactions tm (NOLOCK INDEX=cifk_accttrans) ON
                  ta.tra_account_no      = tm.tra_account_no
              AND      tm.trm_relationship_code= '1'
              AND      tm.trm_action_code      = 'A'
            INNER JOIN tb_files f (NOLOCK) ON
                  ta.file_no            = f.file_no
              AND      f.file_has_processed      = 'N'
              AND      f.file_no            = @FileNo
      WHERE      NOT EXISTS (
                  SELECT      * FROM wktb_group_history wgh (NOLOCK INDEX=cifk_account)
                  WHERE      wgh.SPID            = @@SPID
                   AND      wgh.AccountNo            = ta.tra_account_no
                   AND      wgh.TerminatedDate      IS NULL
            )

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 10: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Create billing history records for all new additions. (Step 11)
      ** ********************************************************************
      */
      INSERT INTO wktb_billing_history (
            SPID,
            AccountNo,            GroupSequence,            BillingSequence,
            PackageID,            file_no,            BenefitRiders,
            EffectiveDate,            TerminatedDate,
            ChangeCode,            BillingAcctNo
      ) SELECT
            @@SPID,
            ta.tra_account_no,       (
                                    SELECT       MAX(wgh.GroupSequence)
                                    FROM       wktb_group_history wgh (NOLOCK INDEX=cifk_account)
                                    WHERE       wgh.SPID      = @@SPID
                                      AND      wgh.AccountNo       = ta.tra_account_no
                              ),                  1,
            ta.bpkg_id,            ta.file_no,            NULL,
            tm.trm_effective_date,      tm.trm_terminated_date,
            NULL,                  ta.grp_number
      FROM      tb_account_transactions ta (NOLOCK INDEX=ci_accttrans) INNER JOIN tb_member_transactions tm (NOLOCK INDEX=cifk_accttrans) ON
                  ta.tra_account_no      = tm.tra_account_no
              AND      tm.trm_relationship_code= '1'
              AND      tm.trm_action_code      = 'A'
              AND      tm.trm_include_in_export= 1
            INNER JOIN tb_files f (NOLOCK) ON
                  ta.file_no            = f.file_no
              AND      f.file_has_processed      = 'N'
      WHERE      NOT EXISTS (
                  SELECT      * FROM wktb_billing_history wbh (NOLOCK INDEX=ci_account)
                  WHERE      wbh.SPID            = @@SPID
                   AND      wbh.AccountNo            = ta.tra_account_no
                   AND      wbh.TerminatedDate      IS NULL
            )

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 11: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Update member demographics fields that have changed. (Step 13)
      ** ********************************************************************
      */
      UPDATE      wm
      SET      file_no            = tm.file_no,
            LastName      = CASE
                              WHEN (tm.trm_lname_changed = 1) THEN tm.trm_lname
                              ELSE wm.LastName
                          END,
            FirstName      = CASE
                              WHEN (tm.trm_fname_changed = 1) THEN tm.trm_fname
                              ELSE wm.FirstName
                          END,
            MidInit            = CASE
                              WHEN (tm.trm_mid_init_changed = 1) THEN tm.trm_mid_init
                              ELSE wm.MidInit
                          END,
            Suffix            = CASE
                              WHEN (tm.trm_suffix_changed = 1) THEN tm.trm_suffix
                              ELSE wm.Suffix
                          END,
            SSN            = CASE
                              WHEN (tm.trm_ssn_changed = 1) THEN tm.trm_ssn
                              ELSE wm.SSN
                          END,
            DOB            = CASE
                              WHEN (tm.trm_dob_changed = 1) THEN tm.trm_dob
                              ELSE wm.DOB
                          END,
            Status            = CASE
                              WHEN (tm.trm_status_changed = 1) THEN tm.trm_status
                              ELSE wm.Status
                          END,
            StatusDate      = CASE
                              WHEN (tm.trm_status_date_changed = 1) THEN tm.trm_status_date
                              ELSE wm.StatusDate
                          END
      FROM      wktb_members wm (NOLOCK INDEX=cifk_waccount) INNER JOIN tb_member_transactions tm (NOLOCK INDEX=cifk_accttrans) ON
                  wm.SPID                  = @@SPID
              AND      wm.AccountNo            = tm.tra_account_no
              AND      wm.TXENSuffix            = tm.trm_txen_suffix
              AND      tm.trm_include_in_export= 1
              AND      tm.trm_action_code      = 'C'
              AND      tm.file_no            = @FileNo
            INNER JOIN tb_files f (NOLOCK) ON
                  tm.file_no            = f.file_no
              AND      f.file_has_processed      = 'N'
      WHERE      wm.SPID                  = @@SPID

      UPDATE      wa
      SET      AltAccountNo      = CASE
                              WHEN (ta.tra_alt_account_no_changed = 1) THEN ta.tra_alt_account_no
                              ELSE wa.AltAccountNo
                          END,
            HiredDate      = CASE
                              WHEN (ta.tra_hired_date_changed = 1) THEN ta.tra_hired_date
                              ELSE wa.HiredDate
                          END,
            Employer      = CASE
                              WHEN (ta.tra_employer_changed = 1) THEN ta.tra_employer
                              ELSE wa.Employer
                          END,
            Address1      = CASE
                              WHEN (ta.tra_address1_changed = 1) THEN ta.tra_address1
                              ELSE wa.Address1
                          END,
            Address2      = CASE
                              WHEN (ta.tra_address2_changed = 1) THEN ta.tra_address2
                              ELSE wa.Address2
                          END,
            City            = CASE
                              WHEN (ta.tra_city_changed = 1) THEN ta.tra_city
                              ELSE wa.City
                          END,
            State            = CASE
                              WHEN (ta.tra_state_changed = 1) THEN ta.tra_state
                              ELSE wa.State
                          END,
            ZipCode            = CASE
                              WHEN (ta.tra_zip_code_changed = 1) THEN ta.tra_zip_code
                              ELSE wa.ZipCode
                          END,
            HomePhone      = CASE
                              WHEN (ta.tra_home_phone_changed = 1) THEN ta.tra_home_phone
                              ELSE wa.HomePhone
                          END,
            WorkPhone      = CASE
                              WHEN (ta.tra_work_phone_changed = 1) THEN ta.tra_work_phone
                              ELSE wa.WorkPhone
                          END,
            PrintMCard      = ta.tra_print_member_card,
            PrintECard      = ta.tra_print_electronic_card,
            PrintLabel      = ta.tra_print_address_label,
            PrintPolicy      = ta.tra_print_policy_certificate
      FROM      wktb_accounts wa (NOLOCK INDEX=ci_waccount_no) INNER JOIN tb_account_transactions ta (NOLOCK INDEX=ci_accttrans) ON
                  wa.AccountNo            = ta.tra_account_no
              AND      ta.file_no            = @FileNo
            INNER JOIN tb_member_transactions tm (NOLOCK INDEX=cifk_accttrans) ON
                  ta.tra_account_no      = tm.tra_account_no
              AND      tm.trm_relationship_code= '1'
              AND      tm.trm_action_code      = 'C'
              AND      tm.trm_include_in_export= 1
      WHERE      wa.SPID                        = @@SPID
      IF (@InDebug = 1) BEGIN
            SELECT      'Step 13: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Terminate group history records for all members whose subscribers
      ** group has been modified. (Step 14)
      ** ********************************************************************
      */
      UPDATE      wgh
      SET      TerminatedDate                        = DATEADD(day, -1, tm.trm_effective_date),
            file_no                              = tm.file_no
      FROM      wktb_group_history wgh (NOLOCK INDEX=cifk_account) INNER JOIN tb_member_transactions tm (NOLOCK INDEX=cifk_accttrans) ON
                  wgh.AccountNo            = tm.tra_account_no
              AND      tm.trm_relationship_code= '1'
              AND      tm.trm_action_code      = 'C'
              AND      tm.trm_include_in_export= 1
            INNER JOIN tb_account_transactions ta (NOLOCK INDEX=ci_accttrans) ON
                  wgh.AccountNo                  = ta.tra_account_no
              AND      ta.tra_group_changed            = 1
            INNER JOIN tb_files f (NOLOCK) ON
                  ta.file_no            = f.file_no
              AND      f.file_has_processed      = 'N'
              AND      f.file_no            = @FileNo
      WHERE      wgh.TerminatedDate                  IS NULL

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 14: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Create new group records for members whose subscriber group number
      ** has been modified. (Step 15)
      ** ********************************************************************
      */
      INSERT INTO wktb_group_history (
            SPID,                  AccountNo,
            GroupSequence,            GroupNo,
            file_no,            BillingAcct,            EffectiveDate,
            TerminatedDate
      ) SELECT
            @@SPID 'SPID',            tm.tra_account_no,
            (
                  SELECT       ISNULL((MAX(wgh2.GroupSequence) + 1), 1)
                  FROM       wktb_group_history wgh2 (NOLOCK INDEX=cifk_account)
                  WHERE       wgh2.AccountNo       = tm.tra_account_no
                   AND      wgh2.SPID      = @@SPID
            ) 'GroupSequence',      ta.grp_number,
            tm.file_no,            NULL 'BillingAcct',      tm.trm_effective_date,
            tm.trm_terminated_date
      FROM      tb_member_transactions tm (NOLOCK INDEX=cifk_accttrans) INNER JOIN tb_account_transactions ta (NOLOCK INDEX=ci_accttrans) ON
                  tm.tra_account_no            = ta.tra_account_no
              AND      tm.trm_include_in_export      = 1
              AND      tm.trm_relationship_code      = '1'
              AND      ta.tra_group_changed            = 1
              AND      tm.trm_action_code            = 'C'
            INNER JOIN tb_files f (NOLOCK) ON
                  tm.file_no                  = f.file_no
              AND      f.file_has_processed            = 'N'

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 15: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Terminate present billing tier record and create new billing tier
      ** record for all members whose group or tier has changed. Create
      ** index on SubscriberNo for Member table to speed up cursor.
      ** Also, terminate and recreate pcp history records for all members
      ** whose group has changed and pcp has not. (Step 16)
      ** ********************************************************************
      */
      SELECT      tm.tra_account_no 'AccountNo',
            DATEADD(day, -1, tm.trm_effective_date) 'TerminatedDate'
      INTO      #TierTerms
      FROM      tb_member_transactions tm (NOLOCK) INNER JOIN tb_account_transactions ta (NOLOCK) ON
                  tm.tra_account_no      = ta.tra_account_no
              AND      tm.file_no            = ta.file_no
              AND      (ta.tra_group_changed      = 1
              OR       ta.tra_tier_changed      = 1
                  )
            INNER JOIN tb_files f (NOLOCK) ON
                  tm.file_no            = f.file_no
              AND      f.file_has_processed      = 'N'
      WHERE      tm.trm_txen_suffix            = '00'
       AND      tm.file_no                  = @FileNo
       AND      tm.trm_include_in_export      = 1
       AND      tm.trm_action_code            = 'C'

      UPDATE      wktb_billing_history
      SET      TerminatedDate                  = t.TerminatedDate,
            file_no                        = @FileNo
      FROM      #TierTerms t
      WHERE      wktb_billing_history.AccountNo            = t.AccountNo
       AND      wktb_billing_history.TerminatedDate      IS NULL

      /*
      ** ********************************************************************
      ** Delete billing history records where the wrong tier
      ** was initially assigned. (ie. EE+1 and only a subscriber
      ** record is present.
      ** ********************************************************************
      */
      DELETE FROM wktb_billing_history
      WHERE      TerminatedDate < EffectiveDate
       AND      TerminatedDate      IS NOT NULL
       AND      file_no            = @FileNo

      INSERT INTO wktb_billing_history (
            AccountNo,            GroupSequence,            BillingSequence,
            SPID,                  file_no,            PackageID,
            BenefitRiders,            EffectiveDate,
            TerminatedDate,            ChangeCode,            BillingAcctNo
      )
      SELECT
            ta.tra_account_no,      wgh.GroupSequence,      (
                                          SELECT      ISNULL((MAX(wbh.BillingSequence) + 1), 1)
                                          FROM      wktb_billing_history wbh (NOLOCK INDEX=ci_account)
                                          WHERE      wbh.AccountNo            = ta.tra_account_no
                                           AND      wbh.GroupSequence      = wgh.GroupSequence
                                                ),
            @@SPID,                  ta.file_no,            ta.bpkg_id,
            ta.tra_benefit_riders,      tm.trm_effective_date,
            tm.trm_terminated_date,      NULL,                  ta.grp_number
      FROM      tb_account_transactions ta (NOLOCK INDEX=ci_accttrans) INNER JOIN tb_member_transactions tm (NOLOCK INDEX=cifk_accttrans) ON
                  ta.tra_account_no            = tm.tra_account_no
              AND      tm.trm_relationship_code      = '1'
              AND      tm.trm_action_code            = 'C'
              AND      tm.trm_include_in_export      = 1
            INNER JOIN wktb_group_history wgh (NOLOCK INDEX=cifk_account) ON
                  ta.tra_account_no            = wgh.AccountNo
              AND      wgh.SPID                  = @@SPID
              AND      wgh.GroupSequence            = (
                          SELECT      MAX(wgh1.GroupSequence)
                          FROM      wktb_group_history wgh1 (NOLOCK INDEX=cifk_account)
                          WHERE      wgh1.AccountNo      = wgh.AccountNo
                            AND      wgh1.SPID      = wgh.SPID
                    )
      WHERE      ta.tra_group_changed                  = 1
       OR      ta.tra_tier_changed                  = 1

      IF (@InDebug = 1) BEGIN
            SELECT      'Step 16: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Create pcp history information for all new additions (Step 12)
      ** ********************************************************************
      */
      INSERT INTO wktb_pcp_history (
            SPID,
            AccountNo,            TXENSuffix,            GroupSequence,
            PCPSequence,
            ProviderID,            file_no,            ProviderOrg,
            PCPType,            EffectiveDate,            TerminatedDate,
            ChangeCode
      ) SELECT
            @@SPID,
            tm.tra_account_no,      tm.trm_txen_suffix,
                                                (
                                                      SELECT       MAX(wgh.GroupSequence)
                                                      FROM       wktb_group_history wgh (NOLOCK INDEX=cifk_account)
                                                      WHERE       wgh.AccountNo       = tm.tra_account_no
                                                       AND      wgh.SPID      = @@SPID
                                                ),
            1,
            tm.prv_id,            tm.file_no,            p.porg_txen_no,
            'P',                  tm.trm_effective_date,      tm.trm_terminated_date,
            'EN01'
      FROM      tb_member_transactions tm (NOLOCK INDEX=cifk_accttrans),
            tb_providers p (NOLOCK),
            tb_files f (NOLOCK)
      WHERE      tm.prv_id            = p.prv_id
       AND      tm.file_no            = f.file_no
       AND      tm.trm_action_code      = 'A'
       AND      tm.trm_include_in_export= 1
       AND      f.file_no            = @FileNo
       AND      f.file_has_processed      = 'N'
       AND      NOT EXISTS (
                  SELECT      * FROM wktb_pcp_history wph (NOLOCK INDEX=ci_account)
                  WHERE      wph.SPID            = @@SPID
                   AND      wph.AccountNo            = tm.tra_account_no
                   AND      wph.TXENSuffix            = tm.trm_txen_suffix
                   AND      wph.TerminatedDate      IS NULL
            )
      IF (@InDebug = 1) BEGIN
            SELECT      'Step 12: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      /*
      ** ********************************************************************
      ** Terminate present pcp record and create new pcp
      ** record for all members whose group has changed but pcp
      ** has not. (Step 17)
      ** ********************************************************************
      */
      SELECT      tm.tra_account_no 'AccountNo',
            tm.trm_txen_suffix 'TXENSuffix',
            DATEADD(day, -1, tm.trm_effective_date) 'TerminatedDate'
      INTO #PCPTerms
      FROM      tb_member_transactions tm (NOLOCK) INNER JOIN tb_account_transactions ta (NOLOCK) ON
                  tm.tra_account_no      = ta.tra_account_no
              AND      tm.file_no            = ta.file_no
            INNER JOIN tb_files f (NOLOCK) ON
                  tm.file_no            = f.file_no
              AND      f.file_has_processed      = 'N'
              AND      f.file_no            = @FileNo
      WHERE      tm.trm_action_code            = 'C'
       AND      tm.trm_include_in_export      = 1
       AND      ( tm.trm_pcp_changed            = 1
       OR        ta.tra_group_changed            = 1
            )

      UPDATE      wktb_pcp_history
      SET      TerminatedDate                  = t.TerminatedDate,
            file_no                        = @FileNo
      FROM      #PCPTerms t
      WHERE      wktb_pcp_history.AccountNo      = t.AccountNo
       AND      wktb_pcp_history.TXENSuffix      = t.TXENSuffix
       AND      wktb_pcp_history.TerminatedDate      IS NULL

      INSERT INTO wktb_pcp_history (
            AccountNo,            TXENSuffix,            GroupSequence,
            PCPSequence,            SPID,                  file_no,
            ProviderID,            ProviderOrg,            PCPType,
            EffectiveDate,            TerminatedDate,            ChangeCode
      ) SELECT
            ta.tra_account_no,      tm.trm_txen_suffix,      wgh.GroupSequence,
            (
            SELECT ISNULL((MAX(wph.PCPSequence) + 1), 1)
            FROM      wktb_pcp_history wph (NOLOCK INDEX=ci_account)
            WHERE      wph.AccountNo            = ta.tra_account_no
              AND      wph.TXENSuffix            = tm.trm_txen_suffix
              AND      wph.GroupSequence      = wgh.GroupSequence
              AND      wph.SPID            = @@SPID
            ),                  @@SPID,                  tm.file_no,
            tm.prv_id,            p.porg_txen_no,            'P',
            tm.trm_effective_date,      tm.trm_terminated_date,      'EN01'
      FROM      tb_account_transactions ta (NOLOCK INDEX=ci_accttrans) INNER JOIN tb_member_transactions tm (NOLOCK INDEX=cifk_accttrans) ON
                  ta.tra_account_no            = tm.tra_account_no
              AND      tm.trm_action_code            = 'C'
              AND      tm.trm_include_in_export      = 1
            INNER JOIN wktb_group_history wgh (NOLOCK INDEX=cifk_account) ON
                  ta.tra_account_no            = wgh.AccountNo
              AND      wgh.SPID                  = @@SPID
              AND      wgh.GroupSequence            = (
                          SELECT      MAX(wgh1.GroupSequence)
                          FROM      wktb_group_history wgh1 (NOLOCK INDEX=cifk_account)
                          WHERE      wgh1.AccountNo      = wgh.AccountNo
                            AND      wgh1.SPID      = wgh.SPID
                    )
            INNER JOIN tb_providers p (NOLOCK) ON
                  tm.prv_id            = p.prv_id
      WHERE      ta.tra_group_changed            = 1
       OR      tm.trm_pcp_changed            = 1


      IF (@InDebug = 1) BEGIN
            SELECT      'Step 17: ' + CONVERT(char, DATEDIFF(millisecond, @StartTime, CURRENT_TIMESTAMP))
            SELECT      @StartTime = CURRENT_TIMESTAMP
      END

      DROP TABLE #AccountList, #TierTerms, #PCPTerms

GO

GRANT EXECUTE ON sp_temp_TXEN_storage TO ELIG_WORKER_GROUP
GO

jwellis

ASKER

formula, I'm going to give you the points but did you ever find anything wrong with the procedure sp_temp_TXEN_storage?

formula

Nothing stands out. All of your transactions are implicit, therefore there are no begin/end transactions and no rollbacks. I don't think this SP was causing the problem you first reported. I also believe since you ran it successfully in ISQL that the 'sp_file_process' SP doesn't seem to have a problem, either. (assuming you ran it with the same parameters you ran from the VB program). My guess is there is some issue with the begin/end/rollback transaction in your VB code.

Look for something like the following in your VB:
1) A "begin" tran in a loop that gets passed by because of the conditionals or before the current connection gets established.
2) A "rollback" that occurs because of an error in a conditional value.
3) A "rollback" that occurs for some other reason, like a SQL engine error. You can compare the error you receive with the error in the SQL errorlog and see if they are the same or different.

Since you were so kind to give me the points, I will continue to monitor this Q. If you pinpoint the problem or need any additional info, please let me know.