Solved

The rollback transaction request has no corresponding BEGIN TRANSACTION

Posted on 2000-02-28
12
1,969 Views
Last Modified: 2010-05-18
I am currently getting this error when I try to process a file in production.  I have not gotten this error before now and the application has run quite smoothly.  I thought it might be a space problem, so I increase the space on the database.  Still get the same error.  Anyone have any idea what this error represents?????
0
Comment
Question by:jwellis
  • 7
  • 4
12 Comments
 
LVL 2

Expert Comment

by:formula
Comment Utility
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.
0
 

Author Comment

by:jwellis
Comment Utility
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.
0
 

Author Comment

by:jwellis
Comment Utility
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
0
 

Author Comment

by:jwellis
Comment Utility
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
0
 
LVL 4

Expert Comment

by:wqw
Comment Utility
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>
0
 
LVL 2

Expert Comment

by:formula
Comment Utility
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!!
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:jwellis
Comment Utility
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
0
 

Author Comment

by:jwellis
Comment Utility
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
0
 

Author Comment

by:jwellis
Comment Utility
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

0
 
LVL 2

Accepted Solution

by:
formula earned 85 total points
Comment Utility
Formula is still here.  I reviewed what you've said.  Here are my comments:

It looks like to me that  the "sp_file_Process" calls the "sp_temp_TXEN_storage" stored procedure, so the "temp" SP should be either in the same database or in the master DB the way your code currently calls them.  Just make sure they exist in the same DB and you should be OK.  Otherwise, call the "temp" SP like so:

  exec test..sp_temp_TXEN_storage @FileNo with recompile

I've added the DB name to the call, that's it.  EXEC command will only look for stored procedure in current DB unless you explicitly pass the DB name (as I have in my example) or the SP is in master DB with the prefix "sp_".


0
 

Author Comment

by:jwellis
Comment Utility
formula, I'm going to give you the points but did you ever find anything wrong with the procedure sp_temp_TXEN_storage?
0
 
LVL 2

Expert Comment

by:formula
Comment Utility
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.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now