queryrange length limitation

(0) Share

Report

Posted on by Community Member

hi,

i am dynamically building comma separated values to be passed in query range. It would be very big string and may go upto 8000 characters.

Can anyone please let me know what's the maximum allowed length for the queryvalue in queryrange? Thanks.

*This post is locked for comments

I have the same question (0)

All responses (12)

Answers (1)

Suggested answer

Community Member on at

Like (0)

Report

I am not sure, but I think its limit is about 2000 symbols

I had a similar problem in the past.

Solved it by using a separate table with values from the range and then using the EXIST JOIN clause on this table.

It's the best solutions, actually.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Qureshi,

Can you elaborate your solution? I think it would be useful.

Thanks.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

I created a separate table and dump my range values in it. After that I make an exists join with the newly created table containing range values with my query.

Hope this helps you

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Is that the physical AOT table you created to dump those range values?

i guess, if it's AOT table, then only you can add it in QBDS for joining with other table.

am i right?

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

That totally depends upon your implementation either you can achieve via permanent or temporary table. Yes you are right, if you are implementing it via permanent table you can add it in QBDS.

Hope this helps you.

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,987 Most Valuable Professional on at

Like (0)

Report

A TempDB table would be a good choice in this case. Also, another alternative to using a comma-separated list of values in a single range is creating several ranges (each with its own value) for the same field.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Martin ,

can you please provide example?

Thanks

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

Hi VDY,

Refer to this blog post of mine regarding the solution suggested by Martin. You need to apply range on one field multiple times so that the result is an OR condition. This way you will not be required to pass a big string.

http://basicax.blogspot.com.au/2012/12/x-code-to-define-or-conditions-in.html

Was this reply helpful? Yes No
Martin Dráb 237,987 Most Valuable Professional on at

Like (0)

Report

I've shown an example in Lines for multiple headers.

Was this reply helpful? Yes No
ismaelivan 578 on at

Like (0)

Report
It didn't work for me to do it multiple times

(as an example I send the same value several times, if I send it 2089 times it gives error)

[Microsoft] [SQL Server Native Client 10.0] [SQL Server] The remote procedure call protocol (RPC) sequence of the incoming tabular data stream (TDS) is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

But if I send it 2088 times, it works!!

]

Was this reply helpful? Yes No