Vaishali

Any SQL error will show up in the SQL log. This includes statements that are driven by the Web Services interface. Have a look in the SQL log and it should indicate the problem.

queryrecord follows the pattern below

CRMService.queryrecord("PrimaryFieldName, FieldName", "comp_type='Customer'", "EntityName", "FieldName");

Your first parameter which defines the fields in the select statement is null and I think that is the problem.

Vaishali

But the code you quote used queryrecord

webService.queryrecord("", ”comp_companyId>0 and comp_updateddate >= ’08-05-2016 00:00:00‘”, "Company", "comp_companyid")

The point about queryrecord is that you only specify the columns that you need for the immediate need of the process.

And what does the generated SQL statement look like?

Thank you Jeff for reply, But i am using CRMService.query method to get data. as My company table has 191 column , i don't want to specify each column name in the queryrecord

Is any other way to resolve this issue.

queryrecord will only pull back from the table that is mentioned in the parameters of the method. These SQL errors you have list from the Log are not related to that web service request.

How does the behaviour change if you do provide a field list in the first parameter?

Have you checked including and removing the 'date' from the whereclause.

So try something like

webService.queryrecord("comp_companyid,comp_type", ”comp_companyId>0 and comp_updateddate >= ’08-05-2016 00:00:00‘”, "Company", "comp_companyid")

or

webService.queryrecord("comp_companyid,comp_type", ”comp_companyId>0”, "Company", "comp_companyid")

yes, i want all column value that's why i have kept select parameter as blank so it should return me all column data

I have set System Logging level -1 Minimum so i think its not logging all the SQL query.

But here is what i find from my Webservice log and SQL Log:

WebService log:

Aug 12 2016 0:01:42.034 8584 12416 1 Exception Caught In DispatchAPI. Message - Query failed to run successfully. .

Aug 12 2016 10:23:43.640 5588 14968 1 Exception Caught In DispatchAPI. Message - Query failed to run successfully. .

SQL Log:

Aug 12 2016 0:01:42.003 8584 12416 1 fselectsql,time,sql,errormsg 47 Select * from address where addr_deleted IS NULL AND addr_addressid in (select adli_addressid from address_link where adli_companyid=9762 AND AdLi_PersonID is null and AdLi_Deleted is null) Operation has been cancelled by the user

Aug 12 2016 10:23:43.578 5588 14968 1 fselectsql,time,sql,errormsg 31 Select * from address where addr_deleted IS NULL AND addr_addressid in (select adli_addressid from address_link where adli_personid=172535 AND AdLi_Deleted is null) Operation has been cancelled by the user

don't find both log on same timestamp but i shared related time logs.

Also randomly i am getting webservice log as

Sep 1 2016 6:48:15.408 12704 13192 1 Exception Caught In DispatchAPI. Message - Operation is not allowed when the object is closed.

Is it due to ERP integration with my SageCRM. The SageCRM instance i am using is 7.2i

actually as per my requirement, i have to find records which are modified after specific date that's why we are using where cluase with date field condition.

Also i changed the System Logging level -1 Maximum now i am getting complete SQL Log when queryrecord gets executed.

Please have a look.

As per my understanding when select parameter was not specified for secondary entity its excute indiviual query to get phone,email and address detail

And when company don't have this detail its return these detail from the primary person associat with company.

So previous Log mention by me are related to failed webservice Method.

Please have a look for below logs and let me whether i am on correct track

Select * from company where comp_deleted IS NULL AND comp_companyid> 0 and comp_updateddate >= '08-05-2000 00:00:00' ORDER BY comp_companyid

Sep 14 2016 17:15:57.871 16768 6716 3 fselectsql,time,sql 32 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'Comp_Type' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:57.880 16768 6716 3 fselectsql,time,sql 0 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'Comp_Status' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:57.897 16768 6716 3 fselectsql,time,sql 16 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'Comp_Source' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:57.906 16768 6716 3 fselectsql,time,sql 0 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'Comp_Territory' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:57.931 16768 6716 3 fselectsql,time,sql 31 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'Comp_Revenue' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:57.945 16768 6716 3 fselectsql,time,sql 16 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'Comp_Employees' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:57.953 16768 6716 3 fselectsql,time,sql 0 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'Comp_Sector' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:57.960 16768 6716 3 fselectsql,time,sql 15 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'Comp_IndCode' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:57.967 16768 6716 3 fselectsql,time,sql 0 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_US FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'Comp_IndCode' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:57.976 16768 6716 3 fselectsql,time,sql 16 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'Comp_MailRestriction' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:58.034 16768 6716 3 fselectsql,time,sql 47 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'Territory' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:58.048 16768 6716 3 fselectsql,time,sql 0 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'comp_demo' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:58.056 16768 6716 3 fselectsql,time,sql 0 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'comp_picklist' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:58.088 16768 6716 3 fselectsql,time,sql 31 Select * from email where emai_deleted IS NULL AND Emai_CompanyID = 18 AND Emai_PersonID is null AND Emai_Deleted is null

Sep 14 2016 17:15:58.159 16768 6716 3 fselectsql,time,sql 31 Select * from phone where phon_deleted IS NULL AND Phon_CompanyID = 18 AND phon_PersonID is null AND Phon_Deleted is null

Sep 14 2016 17:15:58.326 16768 6716 3 fselectsql,time,sql 125 Select * from address where addr_deleted IS NULL AND addr_addressid in (select adli_addressid from address_link where adli_companyid=18 AND AdLi_PersonID is null and AdLi_Deleted is null)

Sep 14 2016 17:15:58.362 16768 6716 3 fselectsql,time,sql 15 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'addr_country' ORDER BY SUBSTRING(Capt_UK,1,255)

Sep 14 2016 17:15:58.421 16768 6716 3 fselectsql,time,sql 31 Select * from person where pers_deleted IS NULL AND Pers_CompanyId = 18 AND pers_Deleted is null

Sep 14 2016 17:15:58.459 16768 6716 3 fselectsql,time,sql 32 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'pers_salutation' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:58.486 16768 6716 3 fselectsql,time,sql 15 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'Pers_Gender' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:58.511 16768 6716 3 fselectsql,time,sql 15 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'Pers_TitleCode' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:58.518 16768 6716 3 fselectsql,time,sql 0 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'Pers_Status' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:58.527 16768 6716 3 fselectsql,time,sql 0 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'Pers_Source' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:58.558 16768 6716 3 fselectsql,time,sql 0 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'Pers_DepartmentCode' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:58.568 16768 6716 3 fselectsql,time,sql 16 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'pers_picklisttest' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:58.576 16768 6716 3 fselectsql,time,sql 0 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'pers_testpicklist1' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:58.582 16768 6716 3 fselectsql,time,sql 0 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'pers_picklist1109' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:58.589 16768 6716 3 fselectsql,time,sql 0 SELECT Capt_Code, Capt_Family, Capt_IntegrationId, Capt_UK FROM vCustom_Captions WITH (NOLOCK) WHERE Capt_Family = N'pers_mutitest' ORDER BY Capt_Family, Capt_Order, Capt_Code

Sep 14 2016 17:15:58.604 16768 6716 3 fselectsql,time,sql 16 Select * from email where emai_deleted IS NULL AND Emai_PersonID = 17 AND Emai_Deleted is null

Sep 14 2016 17:15:58.711 16768 6716 3 fselectsql,time,sql 93 Select * from phone where phon_deleted IS NULL AND Phon_PersonID = 17 AND Phon_Deleted is null

Sep 14 2016 17:15:58.817 16768 6716 3 fselectsql,time,sql 78 Select * from address where addr_deleted IS NULL AND addr_addressid in (select adli_addressid from address_link where adli_personid=17 AND AdLi_Deleted is null)

Sep 14 2016 17:15:58.899 16768 6716 3 fselectsql,time,sql 15 Select * from email where emai_deleted IS NULL AND Emai_PersonID = 19 AND Emai_Deleted is null

Sep 14 2016 17:15:58.918 16768 6716 3 fselectsql,time,sql 0 Select * from phone where phon_deleted IS NULL AND Phon_PersonID = 19 AND Phon_Deleted is null

Sep 14 2016 17:15:58.985 16768 6716 3 fselectsql,time,sql 15 Select * from address where addr_deleted IS NULL AND addr_addressid in (select adli_addressid from address_link where adli_personid=19 AND AdLi_Deleted is null)

Sep 14 2016 17:15:59.045 16768 6716 3 fselectsql,time,sql 0 Select * from email where emai_deleted IS NULL AND Emai_CompanyID = 19 AND Emai_PersonID is null AND Emai_Deleted is null

Sep 14 2016 17:15:59.093 16768 6716 3 fselectsql,time,sql 0 Select * from phone where phon_deleted IS NULL AND Phon_CompanyID = 19 AND phon_PersonID is null AND Phon_Deleted is null

Sep 14 2016 17:15:59.302 16768 6716 3 fselectsql,time,sql 172 Select * from address where addr_deleted IS NULL AND addr_addressid in (select adli_addressid from address_link where adli_companyid=19 AND AdLi_PersonID is null and AdLi_Deleted is null)

Sep 14 2016 17:15:59.366 16768 6716 3 fselectsql,time,sql 31 Select * from person where pers_deleted IS NULL AND Pers_CompanyId = 19 AND pers_Deleted is null

Sep 14 2016 17:15:59.411 16768 6716 3 fselectsql,time,sql 15 Select * from email where emai_deleted IS NULL AND Emai_PersonID = 20 AND Emai_Deleted is null

Sep 14 2016 17:15:59.433 16768 6716 3 fselectsql,time,sql 0 Select * from phone where phon_deleted IS NULL AND Phon_PersonID = 20 AND Phon_Deleted is null

Sep 14 2016 17:15:59.507 16768 6716 3 fselectsql,time,sql 31 Select * from address where addr_deleted IS NULL AND addr_addressid in (select adli_addressid from address_link where adli_personid=20 AND AdLi_Deleted is null)

Sep 14 2016 17:15:59.572 16768 6716 3 fselectsql,time,sql 0 Select * from email where emai_deleted IS NULL AND Emai_PersonID = 18 AND Emai_Deleted is null

Sep 14 2016 17:15:59.592 16768 6716 3 fselectsql,time,sql 0 Select * from phone where phon_deleted IS NULL AND Phon_PersonID = 18 AND Phon_Deleted is null

Sep 14 2016 17:15:59.667 16768 6716 3 fselectsql,time,sql 31 Select * from address where addr_deleted IS NULL AND addr_addressid in (select adli_addressid from address_link where adli_personid=18 AND AdLi_Deleted is null)

Sep 14 2016 17:15:59.751 16768 6716 3 fselectsql,time,sql 31 Select * from email where emai_deleted IS NULL AND Emai_CompanyID = 20 AND Emai_PersonID is null AND Emai_Deleted is null

Sep 14 2016 17:15:59.800 16768 6716 3 fselectsql,time,sql 47 Select * from phone where phon_deleted IS NULL AND Phon_CompanyID = 20 AND phon_PersonID is null AND Phon_Deleted is null

Sep 14 2016 17:15:59.944 16768 6716 3 fselectsql,time,sql 110 Select * from address where addr_deleted IS NULL AND addr_addressid in (select adli_addressid from address_link where adli_companyid=20 AND AdLi_PersonID is null and AdLi_Deleted is null)

Hi Jeff,

Thank you very much for all your instance reply.

what i find that when select parameter was not mention then it run

select * from company where condition...

and execute individual query on Phone,Email and Address table for each company records.

So issue is that in Web-service log i am getting:

Aug 12 2016 0:01:42.034 8584 12416 1 Exception Caught In DispatchAPI. Message - Query failed to run successfully. .

SQL Log:

Aug 12 2016 0:01:42.003 8584 12416 1 fselectsql,time,sql,errormsg 47 Select * from address where addr_deleted IS NULL AND addr_addressid in (select adli_addressid from address_link where adli_companyid=9762 AND AdLi_PersonID is null and AdLi_Deleted is null) Operation has been cancelled by the user

So why in SQL Log has encounter error as "Operation has been cancelled by the user"

I think you should be using query() not queryrecord() in this circumstance.

Then consider using a mix of the queryid() method to find those records and another method to look at the detail?

we are facing some limitation with query method,

Let me explain in detail:

Some time we are getting huge records in query then it will set more flag as true indicating more data in session. But some time session sets time out then we have get data from where it was failed .

but what if all data have same updated date they we should apply order by records which is not provided in Query.

also if we are using query then we have to set Dropdown fields as strings in WSDL file:Yes ,

if we set that as No and SOAP API failed to serialize the response.

We have added the SOAP Reference of local instance into project while binding to another SOAP reference at run time by chanaging webservice.URL