mibuso.com

I have to create a manpower report Deptwise, in this report working hours and production hours need to calculate.

working hour is the attendance of employee, which is captured in SaviorDatabase.
Production hours are uploaded in NavisionDatabase.

Means data need to fetch from different databases on the basis of DeptCode.
-------------------------------------------------------
To achieve this have create a procedure in sql server. this is working

ALTER Procedure [dbo].[ManpowerLatest] (@FromDay int,@FromMon int,@FromYear int,@ToDay int,@ToMon int,@ToYear int,@CompCode Varchar(5))
As
BEGIN

Insert into ManPower1(PAYCODE,DepartmentCode,DepartmentName,isContractor,V_NET,VOT_AMT,VPRE)
Select EMP.PAYCODE,Emp.DepartmentCode,DepartmentName,isContractor,suM(isnull(pr.V_GROSS,0) +isnull(ARRV_Net,0)) V_NET,
SUM(isnull(VOT_AMT,0)) VOT_AMT,SUM(isnull(VPRE,0)) VPRE from pay_DAILY_result PR,TblEmployee Emp,TblCompany,
TblDepartment where PR.Mon_year between (Select Convert(Varchar,@FromYear)+'-'+Convert(Varchar,@FromMon)+'-'+Convert(Varchar,@FromDay))
AND (Select Convert(Varchar,@ToYear)+'-'+Convert(Varchar,@ToMon)+'-'+Convert(Varchar,@ToDay))
AND Emp.paycode=PR.PayCode and TblCompany.CompanyCode=Emp.CompanyCode and TblDepartment.DepartmentCode=Emp.DepartmentCode
And emp.Companycode =@CompCode Group by
EMP.PAYCODE,Emp.DepartmentCode,DepartmentName,isContractor


Insert Into Manpower2(PAYCODE,HOURSWORKED,OTDURATION)
(Select PAYCODE,Sum(HOURSWORKED),Sum(OTDURATION) from tblTimeRegister Where DATEOFFICE between
(Select Convert(Varchar,@FromYear)+'-'+Convert(Varchar,@FromMon)+'-'+Convert(Varchar,@FromDay))
AND (Select Convert(Varchar,@ToYear)+'-'+Convert(Varchar,@ToMon)+'-'+Convert(Varchar,@ToDay))
Group by PAYCODE )

Insert into Database..[Table](PAYCODE,DepartmentCode,DepartmentName,isContractor,V_NET,VOT_AMT,VPRE,Hoursworked,OTDURATION)
Select MP1.PAYCODE,MP1.DepartmentCode,MP1.DepartmentName,MP1.isContractor,MP1.V_NET,MP1.VOT_AMT,MP1.VPRE,MP2.HOURSWORKED,MP2.OTDURATION
from ManPower1 MP1
Inner Join ManPower2 MP2 On MP2.PAYCODE=MP1.PAYCODE

Delete ManPower1
Delete ManPower2

END
----------------------------------------------------------


Now i create a report and then call this procedure..........

CreateConnection;

ADO_Connection.Execute(STRSUBSTNO('EXEC ManpowerLatest '+FORMAT(FDate)+','+FORMAT(FMonth)+','+FORMAT(FYear)+','
+FORMAT(TDate)+','+FORMAT(TMonth)+','+FORMAT(TYear))+','''+CompCode+'''');

CloseConnection;



------------------------------------------------------

Now when i am running the report following error is populating. Sometime report get executed or most of time its shows error:--



---------------------------
Microsoft Dynamics NAV
---------------------------
This message is for C/AL programmers:

The call to member Execute failed. Microsoft OLE DB Provider for SQL Server returned the following message:
Timeout expired

---------------------------
OK
---------------------------

Hi,

I would try using the ADO Command.
With the ADO command you have to set the different type of commands you are issueing:

Command Type
SQL commandtypeEnum

adCmdText ,1 ,SQL String
adCMDTable ,2 ,Table
adCMDTableDirect ,512 ,Table
adCmdStoredProc ,4 , Stored Procedure
adCmdFile ,256 ,A Saved recordset.
adCmdUnknown ,8 ,Unknown command type
adAsyncExecute ,16 , Asynchronous Execution.
adAsyncFetch ,32 ,Asynchronous Execution
adAsyncFetchNonBlocking ,64, Async Fetching that does not block
adExecuteNoRecords, 128 ,for a Non-row returning command. A null recordset is returned.
Improves performance if you use this.

The automation to use is:
tsADOCommand 'Microsoft ActiveX Data Objects 2.8 Library'.Command Automation

With the ADO Command you can set the Timeout to anything you want.

here is an example how I used it:



As you can see I set my command type to "Stored procedure (4)"
The Timeout is set to 0 which means that it waits until it completes.

I hope this helps,

Regards,

Willy