SQL Server Performance

Problem to come out of Main Function in ActiveX Sc

Discussion in 'SQL Server DTS-Related Questions' started by Shikhajain25, Jan 17, 2005.

  1. Shikhajain25 New Member

    Hi All,<br />I am facing a problem in my this script where i have to fetch some values from Access and then there corresponding values in SQL Server tables and load in a Text File.<br />But, when i try this code in VB Editor it runs perfectly Fine.....<br />But in DTS It is going in an indefinate loop<br />But, when i say explicitly Exit Function then it throws an error!!!<br />Can you pls help and guide me where i am facing problem.<br />Thanks and Warm Regards,<br />Shikha Jain<br />Conversion-Team<br />'**********************************************************************<br />' Visual Basic Transformation Script<br />'************************************************************************<br /><br />' Copy each source column to the destination column<br />Function Main()<br />Call Code<br />'Exit Function''If this is used it throws error<br /> 'Now it will go inside an indefinate loop.<br /><br /> 'Main = DTSTaskExecResult_Success<br /> Main = DTSTransformStat_OK<br />End Function<br /><br />Sub Code<br />msgbox"Entered Code procedure"<br /><br />Dim Cnn<br />Dim Cnn1<br />Dim ObjCon<br />Dim ObjCmdContractId<br />Dim ObjCmdPartyId<br />Dim ObjRsContractId<br />Dim ObjRsPartyId<br />DimObjCmdEdgeUserId<br />DimObjRsEdgeUserId<br />Dim Cnt<br />Dim ICPartyId<br />Dim PartyId<br />Dim ClientBusnPartId<br />Dim objFSO1 <br />Dim objFSO2<br />Dim objStream1<br />Dim objStream2<br />Const OUTPUT_FILE1 = "C:MasterContractStaticErrors_PartyId.txt"<br />Const OUTPUT_FILE2 = "C:MasterContractStaticErrors_ClientBusnPartId.txt"<br />Const fsoForWriting = 2<br /><br /> Set objFSO1 = CreateObject("Scripting.FileSystemObject")<br /> 'MsgBox "Created a PartyId File Object"<br /> Set objStream1 = objFSO1.OpenTextFile(OUTPUT_FILE1, fsoForWriting, True)<br /> 'MsgBox "Opened a Output Error-PartyId Log File"<br /> <br /> Set objFSO2 = CreateObject("Scripting.FileSystemObject")<br /> 'MsgBox "Created a PartyId File Object"<br /> Set objStream2 = objFSO2.OpenTextFile(OUTPUT_FILE2, fsoForWriting, True)<br /> 'MsgBox "Opened a Output Error-Edge User Id Log File"<br /> <br />Set Cnn = CreateObject("ADODB.Connection")<br /><br /> Cnn.connectionstring = "Provider=SQLOLEDB.1<img src='/community/emoticons/emotion-4.gif' alt=';P' />ersist Security Info=False;User ID=OD1Test<img src='/community/emoticons/emotion-4.gif' alt=';p' />wd=OD1Test;Initial Catalog=Edge_Staging;Data Source=WB10f059"<br /> Cnn.Open<br /><br /> Set Cnn1 = CreateObject("ADODB.Connection")<br /> <br /> <br /> Cnn1.connectionstring = "Provider=SQLOLEDB.1<img src='/community/emoticons/emotion-4.gif' alt=';P' />ersist Security Info=False;User ID=OD1Test<img src='/community/emoticons/emotion-4.gif' alt=';p' />wd=OD1Test;Initial Catalog=Edge_Legacy_Xref;Data Source=WB10f059"<br /> Cnn1.Open<br /><br /> Set ObjCon = CreateObject("ADODB.Connection")<br /> <br /> 'MsgBox "Created Access Connection"<br /> <br /> ObjCon.CursorLocation = 3 'adUseClient<br /> ObjCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _<br /> "Data Source=c:MastercontractStaticMasterContract.mdb;" & _<br /> "User Id=admin;" & _<br /> "Password="<br /> <br /> 'MsgBox "Open Access Connection"<br /><br /> Set ObjCmdContractId = CreateObject("ADODB.Command")<br /> ObjCmdContractId.ActiveConnection = ObjCon<br /> ObjCmdContractId.CommandType = 1 'adCmdText<br /> ObjCmdContractId.CommandText = "SELECT ContractId,ICPartyOID FROM tblBusnPartClientSuppContract"<br /> <br /> <br /> 'MsgBox "set command access"<br /> <br /> Set ObjCmdPartyId = CreateObject("ADODB.Command")<br /> ObjCmdPartyId.ActiveConnection = Cnn<br /> ObjCmdPartyId.CommandType = 1 'adCmdText<br /> ' ObjCmdPartyId.CommandText = "SELECT PartyId FROM IC2_CLIENT "<br /> 'MsgBox "Set command Party Id SQL Server"<br /> <br /> <br /> Set ObjCmdEdgeUserId = CreateObject("ADODB.Command")<br /> ObjCmdEdgeUserId.ActiveConnection = Cnn1<br /> ObjCmdEdgeUserId.CommandType = 1 'adCmdText<br /> ' ObjCmdEdgeUserId.CommandText = "SELECT EdgeUserId FROM tblEdgeLegacyUserXref"<br /> 'MsgBox "Set command EdgeUserID SQL Server"<br /> <br /> Set ObjRsContractId = CreateObject("ADODB.Recordset")<br /> Set ObjRsContractId = ObjCmdContractId.Execute<br /><br /> 'MsgBox "SetCreated Access Recordset"<br /><br /> Set ObjRsPartyId = CreateObject("ADODB.Recordset")<br /> 'Set ObjRsPartyId = ObjCmdPartyId.Execute<br /><br /> 'MsgBox "Created SQL Server PartyId Recordset"<br /> Set ObjRsEdgeUserId = CreateObject("ADODB.Recordset")<br /> 'Set ObjRsEdgeUserId = ObjCmdEdgeUserId.Execute<br /><br /> 'MsgBox "Created SQL Server Edge User Id Recordset"<br /> <br /> Do While Not ObjRsContractId.EOF<br /> <br /> 'Msgbox ObjRsContractId("ContractId").Value<br /> ICPartyId = ObjRsContractId("ICPartyOID").Value<br /> <br /> 'MsgBox "stored ICPartyOID in a variable"<br /><br /> ' Set ObjRsPartyId = ObjCmdPartyId.Execute<br /> <br /> ObjRsPartyId.Open "SELECT PartyId FROM IC2_CLIENT WHERE DivNo=000 AND ClientNo= " & ICPartyId, Cnn<br /> <br /> If (ObjRsPartyId.BOF = True And ObjRsPartyId.EOF = True) Or (ObjRsPartyId.RecordCount = 0) Then<br /> PartyId = ICPartyId<br /> objStream1.WriteLine (PartyId)<br /> objStream1.WriteBlankLines (1)<br /> 'MsgBox "Entered Wrong Party Id in the Error File"<br /> 'Exit Do<br /> ObjRsPartyId.Close<br /> 'ObjRsEdgeUserId.Close<br /> Else<br /> PartyId = ObjRsPartyId("PartyId").Value <br /><br /> 'MsgBox "SELECT PartyId FROM Client table WHERE DivNo=000 AND ClientNo= " & ICPartyId<br /><br /> ObjRsEdgeUserId.Open "SELECT EdgeUserId FROM tblEdgeLegacyUserXref WHERE LegacyUserTypeCd=15 AND ICPartyId= " & PartyId, Cnn1<br /> If (ObjRsEdgeUserId.BOF = True And ObjRsEdgeUserId.EOF = True) Or (ObjRsEdgeUserId.RecordCount = 0) Then<br />ClientBusnPartId = PartyId<br />objStream2.WriteLine (ClientBusnPartId)<br />objStream2.WriteBlankLines (1)<br />'Exit Do<br /> Else<br />ClientBusnPartId = ObjRsEdgeUserId("EdgeUserId").Value<br /> End If<br /> 'MsgBox "SELECT EdgeUserId FROM tblEdgeLegacyUserXref WHERE LegacyUserTypeCd=15 AND ICPartyId= " & PartyId<br /> 'MsgBox ClientBusnPartId<br /> 'MsgBox "stored ClientBusnPartId in a variable"<br /><br /> ObjRsPartyId.MoveNext<br /> ObjRsEdgeUserId.MoveNext<br /> ObjRsPartyId.Close<br /> ObjRsEdgeUserId.Close<br /> <br /> End If <br /> <br /> DTSDestination("ContractId") = ObjRsContractId("ContractId").Value+DTSGlobalVariables("maxObjContractid").Value<br />DTSDestination("ClientBusnPartId") = ClientBusnPartId<br />DTSDestination("CreateId") = 1<br />DTSDestination("CreateDt") = NOW<br />DTSDestination("UpdateId") = 1<br />DTSDestination("UpdateDt") = NOW<br />DTSDestination("FunctionId") = 1<br />DTSDestination("AppId") = "OD1-Conv"<br />DTSDestination("DBAPartitionKey") = 1<br /> <br /> 'MsgBox "Reached last :-Entered client Successfully"<br /> ObjRsContractId.MoveNext<br /> <br /> <br /> Loop<br /><br />ObjRsContractId.Close<br /><br />If ObjRsPartyId.Open = True Then <br />ObjRsPartyId.Close<br />End If <br /><br />If ObjRsEdgeUserId.Open= True Then <br />ObjRsEdgeUserId.Close<br />End If <br /> <br /> Set ObjRsContractId = Nothing<br /> Set ObjRsPartyId = Nothing<br /> Set ObjRsEdgeUserId = Nothing<br /> Set ObjCmdContractId = Nothing<br /> Set ObjCmdPartyId = Nothing<br /> Set ObjCmdEdgeUserId = Nothing<br /> Set ObjCon = Nothing<br /> Set Con = Nothing<br /> Set Con1 = Nothing<br /><br /> <br /> MsgBox "done"<br /> Exit Sub<br /><br />End Sub
  2. Raulie New Member

    Uncomment the message boxes and see where it performs the "indifinate loop" what error is it giving you when you use Exit function?

    Raulie
    HP

Share This Page