on 03-05-2015 8:58 AM
hi,
I need your valuable suggestions...
i am getting the above error.
i am loading the cfl doc entries by using query.
below query i am running .
but exactly cfl.SetConditions(cons)
i am getting the Error.
The call to QI for interface 'SAPbouiCOM.IApplication' with IID '{D1F75D47-137C-4335-AC2A-3FE209831B6A}' failed with HRESULT
0x80010100 (System call failed. (Exception from HRESULT: 0x80010100 (RPC_E_SYS_CALL_FAILED))). One likely reason this failed
is that the object does not have a proxy/stub dll properly registered.
Things are i observed,
1)Query is taking almost all 6 minutes in sql for executing.
2)same time it is taking in coding too
3)One important point is same query in diff data base means 1 month earlier it is taking ony 3-4 minutes
that time coding is working
4) The records are nearly 300 only
the difference between the two data base sql query result is also same 30-40 records are difference
5) the records are existing in the tables almost all same may be 30-50 records are newly added.
i am not able to understood why it is taking this much of time in the new database...
Any information please update me..
select a.DocNum,a.PIndicator from OPDN a,pdn1 b,oitm c where a.docentry=b.docentry and b.itemcode=c.itemcode and a.U_SYFL='Y' and c.U_qcinsp='Y' and a.PIndicator='14-15' and a.docnum not in (select U_grnno from [@qc_inwardbasic] where U_itype='Bought Out' and U_grnno is not null and U_sgrnno='14-15') union select a.docnum,a.PIndicator from opdn a,pdn1 b,oitm c where a.docentry=b.docentry and b.itemcode=c.itemcode and c.U_qcinsp='Y' and a.U_SYFL='Y' and a.PIndicator='14-15' and convert(nvarchar,a.docnum)+'-'+convert(nvarchar,b.itemcode) not in (select a.U_grnno+'-'+b.U_itemid from [@qc_inwardbasic] a,[@qc_inwarddetail] b where a.docentry=b.docentry and b.U_status='Completed' and a.U_itype='Bought Out' and a.U_grnno is not null and b.U_itemid is not null and U_sgrnno='14-15') union select a.docnum,a.PIndicator from opdn a,pdn1 b,(select a.U_grnno grnno,b.U_itemid itemid,sum(b.U_insqty) qty from [@qc_inwardbasic] a,[@qc_inwarddetail] b where a.docentry=b.docentry and b.U_status='completed' and U_sgrnno='14-15' group by a.U_grnno,b.U_itemid) c where a.docentry=b.docentry and a.docnum=c.grnno and a.PIndicator='14-15' and b.itemcode=c.itemid and b.quantity > c.qty and a.U_SYFL='Y' order by DocNum
---
Sub GRNCflFilter()
Try
Dim cfl As SAPbouiCOM.ChooseFromList
Dim cons As SAPbouiCOM.Conditions
Dim con As SAPbouiCOM.Condition
Dim econ As New SAPbouiCOM.Conditions
Dim grnrecset As SAPbobsCOM.Recordset
Dim strQuery As String
grnrecset = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
cfl = frmInwardInspection.ChooseFromLists.Item("grn")
cfl.SetConditions(econ)
cons = cfl.GetConditions()
Dim oeditoseries1 As SAPbouiCOM.ComboBox = frmInwardInspection.Items.Item("i_sgrnno").Specific
Dim oeditoseries As String
oeditoseries = oeditoseries1.Selected.Value
'strQuery = "select a.DocNum from OPDN a,pdn1 b,oitm c where a.U_grntype='PO GRN' and a.docentry=b.docentry and b.itemcode=c.itemcode and c.U_qcinsp='Y' and a.docnum not in (select U_grnno from [@qc_inwardbasic] where U_itype='Bought Out' and U_grnno is not null) union select a.docnum from opdn a,pdn1 b,oitm c where a.docentry=b.docentry and b.itemcode=c.itemcode and c.U_qcinsp='Y' and convert(nvarchar,a.docnum)+'-'+convert(nvarchar,b.itemcode) not in (select a.U_grnno+'-'+b.U_itemid from [@qc_inwardbasic] a,[@qc_inwarddetail] b where a.docentry=b.docentry and b.U_status='Completed' and a.U_itype='Bought Out' and a.U_grnno is not null and b.U_itemid is not null ) union select a.docnum from opdn a,pdn1 b,(select a.U_grnno grnno,b.U_itemid itemid,sum(b.U_insqty) qty from [@qc_inwardbasic] a,[@qc_inwarddetail] b where a.docentry=b.docentry and b.U_status='completed' group by a.U_grnno,b.U_itemid) c where a.docentry=b.docentry and a.docnum=c.grnno and b.itemcode=c.itemid and b.quantity > c.qty order by DocNum"
grnrecset.DoQuery(strQuery)
Dim RCOUNT As String = grnrecset.RecordCount
'oApplication.SetStatusBarMessage(RCOUNT, BoMessageTime.bmt_Short, False)
grnrecset.MoveFirst()
For i As Integer = 1 To grnrecset.RecordCount
If i = (grnrecset.RecordCount) Then
' oApplication.SetStatusBarMessage(i, BoMessageTime.bmt_Short, False)
'con = cons.Add()
'con.Alias = "DocNum"
'con.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL
'con.CondVal = Trim(grnrecset.Fields.Item(0).Value)
con = cons.Add()
con.BracketOpenNum = 2
con.Alias = "DocNum"
con.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL
con.CondVal = Trim(grnrecset.Fields.Item(0).Value)
con.BracketCloseNum = 1
con.Relationship = SAPbouiCOM.BoConditionRelationship.cr_AND
con = cons.Add()
con.BracketOpenNum = 1
con.Alias = "PIndicator"
con.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL
con.CondVal = Trim(grnrecset.Fields.Item(1).Value)
con.BracketCloseNum = 2
Else
' oApplication.SetStatusBarMessage(i & "else", BoMessageTime.bmt_Short, False)
'con = cons.Add()
'con.Alias = "DocNum"
'con.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL
'con.CondVal = Trim(grnrecset.Fields.Item(0).Value)
'con.Relationship = SAPbouiCOM.BoConditionRelationship.cr_OR
con = cons.Add()
con.BracketOpenNum = 2
con.Alias = "DocNum"
con.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL
con.CondVal = Trim(grnrecset.Fields.Item(0).Value)
con.BracketCloseNum = 1
con.Relationship = SAPbouiCOM.BoConditionRelationship.cr_AND
con = cons.Add()
con.BracketOpenNum = 1
con.Alias = "PIndicator"
con.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL
con.CondVal = Trim(grnrecset.Fields.Item(1).Value)
con.BracketCloseNum = 2
con.Relationship = SAPbouiCOM.BoConditionRelationship.cr_OR
End If
grnrecset.MoveNext()
Next
cfl.SetConditions(cons)
cfl = frmInwardInspection.ChooseFromLists.Item("cgrn")
cfl.SetConditions(cons)
If grnrecset.RecordCount = 0 Then Me.GRNSetEmptyCondition()
Catch ex As Exception
oApplication.StatusBar.SetText("GRNCflFilter Method Failed:" & ex.Message, SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Warning)
Finally
End Try
End Sub
Hi Srinivas,
6 minutes is awfully long time for such a query.
Definitely your add on will crash after the query.
Two solutions :
1. Implement a timer to execute the SBO_Application.RemoveWindowsMessage(BoWindowsMessageType.bo_WM_TIMER,True)
This need to be executed say every 30 seconds while your query is running.
2. Rework your query.
I would recommend you to rework on your query, I dont have your UDT and UDF so I am not able to give you solution.
But looking at your query, the three main select statement are very similar, I am sure it can be simplified.
Use the SSMS Show Execution Plan menu if you need to see which part is taking the biggest resource and work from there.
Regards
Edy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.