Calling WCF service from excel gives error on received message size

Question

I am calling my WCF service from excel VBA code using moniker string. However, as my service returns large data as response, excel gives error message

"Maximum message size quota for incoming messages (65534) has been exceeded. To increase the quota used the MaxReceivedMessageSize property on the appropriate binding element"

Here is the moniker string:

addrToService = "service4:mexAddress=""net.tcp://localhost/MyApp/API/Excel/ExcelAPIService.svc/mexTCP"", "
addrToService = addrToService + "address=""net.tcp://localhost/PruCapWebCMHost/API/Excel/ExcelAPIService.svc"", "
addrToService = addrToService + "contract=""IExcelAPIService"", contractNamespace=""http://Prucap/Services"", "
addrToService = addrToService + "binding=""NetTcpBinding_IExcelAPIService"", bindingNamespace=""http://MyApp/Services"""

To resolve this, I increased the size in my WCF service's web.config file as shown below:

<netTcpBinding>
    <binding name="NetTcpBinding_IPublicService" maxBufferPoolSize="8388608"  maxBufferSize="8388608" maxReceivedMessageSize="8388608" portSharingEnabled="true">
    </binding>
</netTcpBinding>

<basicHttpBinding>
    <binding name="BasicHttpBidning_IPublicService" closeTimeout="00:05:00" openTimeout="00:05:00" sendTimeout="00:05:00" receiveTimeout="00:05:00" maxReceivedMessageSize="8388608" />
    <binding name="BasicHttpBidning_ISecureService" closeTimeout="00:05:00" openTimeout="00:05:00" sendTimeout="00:05:00" receiveTimeout="00:05:00" maxReceivedMessageSize="8388608" />
</basicHttpBinding>

....

  <service name="ExcelAPIService" behaviorConfiguration="PublicServiceTypeBehaviors">
    <endpoint address="" bindingNamespace="http://MyApp/Services" binding="netTcpBinding" bindingConfiguration="NetTcpBinding_IPublicService" contract="API.Service.ExcelAPI.IExcelAPIService" name="NetTcpBinding_IExcelAPIService" />
    <endpoint address="" bindingNamespace="http://MyApp/Services" binding="basicHttpBinding" bindingConfiguration="BasicHttpBidning_IPublicService" contract="API.Service.ExcelAPI.IExcelAPIService" name="BasicHttpBidning_IExcelAPIService" />
    <endpoint address="mex" bindingNamespace="http://MyApp/Services" binding="mexHttpBinding" contract="IMetadataExchange" />
    <endpoint address="mexTCP" bindingNamespace="http://MyApp/Services" binding="mexTcpBinding" bindingConfiguration="" contract="IMetadataExchange" />
  </service>

According to various forums on this topic, the above solution should work. But this does not work in my case when called from excel. Is there anything I need to do from excel side to set the maxReceivedMessageSize? If yes then how can I do this using VBA code?

Additional information:

I use Office 2010 (with VBA), Windows 7 Prof, 64bit OS

Answer 1

You should set maxReceivedMessageSize="2147483647" to increase message size.

Try increasing message size like:

 <binding maxBufferSize="2147483647" 
             maxBufferPoolSize="2147483647" 
             maxReceivedMessageSize="2147483647">
        <readerQuotas maxDepth="2147483647" 
                      maxStringContentLength="2147483647" 
                      maxArrayLength="2147483647" 
                      maxBytesPerRead="2147483647"
                      maxNameTableCharCount="2147483647" />

   </binding>

--OR

<basicHttpBinding>
      <binding name="BasicHttpBinding_IManagementService" closeTimeout="00:01:00"
        openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"
        allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
        maxBufferSize="2147483647" maxBufferPoolSize="524288" maxReceivedMessageSize="2147483647"
        messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
        useDefaultWebProxy="true">
        <readerQuotas maxDepth="128" maxStringContentLength="2147483647"
          maxArrayLength="16384" maxBytesPerRead="4096" maxNameTableCharCount="16384" />
        <security mode="None">
          <transport clientCredentialType="None" proxyCredentialType="None"
            realm="" />
          <message clientCredentialType="UserName" algorithmSuite="Default" />
        </security>
      </binding>
    </basicHttpBinding>

refer WCF Error "Maximum number of items that can be serialized or deserialized in an object graph is '65536'"

Wcf-The maximum message size quota for incoming messages (65536) has been exceeded?

---

UPDATE

You also can change endpoint/service behavior programatically.

Refer links:

How to: Specify a Service Binding in Code

How to: Programmatically Configure a WCF Endpoint

---

Update2:

Sorry Anil, Previously I totally overlook you are doing this in excel.

The easiest way for your scenario to use WCF service from VB6 is to create a .Net ComObject wrapper for the service client. Then in VB6 all your are doing is a create object and calling some methods on the object. All the WCF work takes place in the .Net com object.

Simply create the WCF client to the service in a separate project as described in this link. Register the .NET assembly as a type library which you would then link from the VB6 app : link.

Sources:

Using WCF in VB6

Integrating WCF Services with COM+

Communicate with WCF Windows Service in VB6?

Hope it helps. :)

Answer 2

The maximum size must be set by the client as well as the server. However, the service moniker form you are using does not support specifying this parameter. From first hand experience I can tell you, using monikers may seem appealing at first, since it allows you to call services from VBA with minimal coding, but it is very limited in what it can do. I discovered, as no doubt you are in the process of dicovering as well, the best way to approach this is to build a proper WCF client - probably in .NET - and call the client class from your VBA, or even Excel directly. If you are trying that and are still having trouble, please start a new thread so you can post your code, and more fully explain what you have tried, and what the problem is.