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

Any specific reason your using moniker string and not just the usual ChannelFactory mechanism? — 3dd, Jun 10 '14 at 08:40
Care to explain, as it seems like you wasting coding efforts by trying to solve this issue. If you used ChannelFactory on the client you could make use of a config file and set the maximum size that way — 3dd, Jun 10 '14 at 09:22
I see.. I will give it a try to use the ChannelFactory in Excel VB code, and check if this works for me. Thanks! I will let you know. — Anil Soman, Jun 10 '14 at 09:25
I am trying to use channelfactory code in my VBA (Office 2010) and it gives error "Expected end of line" on the line : Dim myEndpoint As New EndpointAddress("http://.....") — Anil Soman, Jun 10 '14 at 11:18
Perhaps look at the following, this shouldbe the easiest way to get everything working http://msdn.microsoft.com/en-us/library/aa140260%28v=office.10%29.aspx — 3dd, Jun 10 '14 at 13:24
@3dd - The article is meant for XP OS only? As I use Windows 7 for development — Anil Soman, Jun 11 '14 at 06:42
See the following http://oreilly.com/pub/h/1306, it uses office XP webservice kit. You did not mention which versionof office you rae running — 3dd, Jun 11 '14 at 06:48
@3dd: Updated my question with additional information of software versions. Thanks for the link. I will go through it. — Anil Soman, Jun 12 '14 at 06:04

score 1 · Answer 1 · edited May 23 '17 at 12:20

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. :)

Pranav - I changed the size as you mentioned, still gives same error. The post says, it should be changed on client as well as server side. How do I change it in client side, which is excel in my case? — Anil Soman, May 07 '14 at 11:19
You can programatically also can change endpoint/service behavior. I am updating answer. — Pranav Singh, Jun 11 '14 at 07:17
Pranav - I am using Excel as my client, which has VB6 as code behind. Those links talk about vb.net versions and that code cannot work in vb6 i believe. — Anil Soman, Jun 12 '14 at 06:00
Thanks @Pranav. I will try it out. As this approach involves registration of assembly, I first need to take my client's permission to do that. Because he is very stringent on installing anything in their secured environment and also the number of users of this excel are multiple. — Anil Soman, Jun 12 '14 at 06:39
You just need to add assembly to GAC so that it is considered as safe. Anyway refer first two msdn link that might help in some way. http://blogs.msdn.com/b/a_pasha/archive/2006/09/25/770709.aspx http://msdn.microsoft.com/en-us/library/bb735856.aspx — Pranav Singh, Jun 12 '14 at 06:43

score 1 · Answer 2 · answered Jun 10 '14 at 20:58

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.

Calling WCF service from excel gives error on received message size

2 Answers2

Linked