mibuso.com

Microsoft Business Solutions online community
It is currently Fri Oct 31, 2014 6:23 am

All times are UTC + 1 hour




Post new topic Reply to topic  [ 2 posts ] 
Author Message
 Post subject: The call to member Execute failed. Timeout expired
PostPosted: Wed Aug 08, 2012 10:40 am 
Offline

Joined: Tue Apr 12, 2011 7:12 am
Posts: 7
Location: Delhi
Country: India (in)
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
---------------------------

_________________
Regards
Pradeep Bhardwaj


Top
 Profile E-mail  
 
 Post subject: Re: The call to member Execute failed. Timeout expired
PostPosted: Wed Aug 08, 2012 7:38 pm 
Offline

Joined: Thu Apr 26, 2007 1:28 pm
Posts: 325
Location: Lawrenceburg, Kentucky
Country: United States (us)
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:

Code: Select all
  IF ISCLEAR(tsADOCommand) THEN
    CREATE(tsADOCommand);

  tsVarADOConnection := tsADOConnection;
  tsADOCommand.ActiveConnection := tsVarADOConnection;
  tsADOCommand.CommandText := 'master.dbo.tsGetLogFiles';
  tsADOCommand.CommandType := 4;
  tsADOCommand.CommandTimeout := 0;
  tsADOCommand.Execute;


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

_________________
Fostering a homeless, abused child is the hardest yet most rewarding thing I have ever done.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: Exabot [Bot], Google [Bot] and 10 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: