Sharepoint list reader returning 401 error "request exceeds the configured maxQueryStringLength"

Hi ​@ecx 
Can you break the table up? Ask sharepoint for fields 1-20 and then ask for fields 21-40….etc. Can you get a list of all the fields? Or am I missunderstanding?

If you can pull the table in parts then put back together that might work for you.

Hello,

I've had a look at the settings in the SharePoint list reader but I can't seem to find any setting where i can specify which specific fields i want to extract, it's either all visible fields or visiable fields and hidden fields (unless I'm missing something)

Thanks

​@ecx Ah. I get it now. You are getting an error on the SharePoint reader. It won’t even read the SharePoint list without an error. I was thinking split of the table but you have to read it first right?

Yeah, when I run it - I’m getting the error above, and no data at all. :(

We’re seeing the same error with a large list and managed to narrow the limit down to 2170-2173 characters (a request of 2169 characters worked, while 2173 did not).

Did you manage to find a workaround?

Interesting, maybe the character limit isn't exactly 2048, or maybe thats just a red herring. I spoke to the sharepoint admin at my company and did not get an answer, nor could find any solution myself.

I ended up using a 3rd party SSIS package which would connect to SharePoint instead. I would not recommend it, as since then I’ve used powershell PNP to extract data from sharepoint lists, which works much better and is much quicker to set up. 
 

Since the URL used by the SharePoint readers include all the list’s attributes twice, more or less, we could ask the list admins to remove some attributes or shorten their names I guess.

But instead I ended up using the URL posted by FME in the error log, but without any of the query parameters.

Something like this;

https://mycompany.sharepoint.com/sites/oursharepointsite//_api/web/Lists(guid'some-guid')/Items?%24select=Title%2Clotsofattributes...%2C...etc&%24expand=ContentType%2CAuthor%2Fid%2CEditor%2Fid%2Clotsofattributes...%2C...etc&%24top=5000

is shortened to this;

https://mycompany.sharepoint.com/sites/oursharepointsite//_api/web/Lists(guid'some-guid')/Items

Then I just used HTTPCaller with an “Accept: application/json” header and exploded the response with JSONFragmenter:
 

 

Wow, good stuff!

A very simple and obvious in hindsight way to resolve this!
Funnily enough my ETL process which I set up with SSIS failed today, tomorrow I’ll give this a go and likely replace it with this. 

Thanks for posting your method to fix it!

I’d like to also add that the best answer by default will only return 100 items, you can add
items?$top=5000 to the end of the HTTPCaller url to get up to 5k items in one request.

If you have nested data (for example a person object in your sharepoint list) you will also need to alter query to extract this info by using:
items?$top=5000&$select=*,PersonObject1/Id,PersonObject1/Title&$expand=PersonObject1

Also by obtaining the list via this method the names are not resolved, unfortunately. But at least it works!