SQL Server 2000 4000 character query limit for adhoc queries

Question

SQL Server 2000 4000 character query limit for adhoc queries

alex_pangcats

SSC Veteran

Points: 226
More actions
October 1, 2009 at 9:01 pm

#74102

Hi everyone,
I have an issue about adhoc query limitation.
My application used sql server 2000 and jasper report.
In my select query I have a parameter $P{parameter} that recieved and holds characters above to 4000 size limit.
My sample query goes like this:
SELECT field,........
FROM table
WHERE field <condition> $p{parameter}
the sample value of $P{parameter} is:
"1234,5678,9001,9002................... and so on."
the value exceeds to 4000 characters.
everttime i this query to display the in the jasper report, i have got an error saying "Invalid operator for data type. Operator equals add, type equals ntext."
And I know the cause of the error due to the nvarchar limitation.
Now my question is how to handle this kind of issue?
And note, $P{parameter} value is dynamic so sometime it could be 8000 characters which it depends of the user's selection.
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

I'm not familiar with Jasper, but the usual way around this is to split the string into 4000 character chunks and concatenate them in your dynamic sql.

So you'd split your input parameter if it was over 4000 characters.

If you don't need to allow limitless characters and can set a reasonable limit, you could do something like this: (Assuming @P is your input parameter (the long string) and @sql is your dynamic SQL (That I'm assuming you are using because you are passing in a CS list as the parameter)

-- 20K char (unicode) limit

DECLARE @P1 nvarchar(4000),@P2 nvarchar(4000), @P3 nvarchar(4000), @P4 nvarchar(4000), @P5 nvarchar(4000)

SELECT @P1 = SUBSTRING(@P,1,4000),

@P2 = SUBSTRING(@P,4000,4000),

@P3 = SUBSTRING(@P,8000,4000),

@P4 = SUBSTRING(@P,12000,4000),

@P5 = SUBSTRING(@P,16000,4000)

EXEC (@SQL + @P1 + @P2 + @P3 + @P4 + @P5)

You can do it with a limitless number of characters as well, but it's a bit more complicated. Note that you cannot do something like SET @sql = @sql + P1 + P2... because you'd pass the 4k Limit. The above method circumvents this

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 2

Most people don't know about it, but you can also create temporary stored procedures that begin with #.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 3

Jeff Moden (10/3/2009)
Most people don't know about it, but you can also create temporary stored procedures that begin with #.

A dark hand waves in the mist...

'Your SQL skill has just improved 3 points'

😀

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

alex_pangcats SSC Veteran Points: 226 More actions · Answer 4

Hi Gardin,

Thanks for the reply.

Your answer is nice, but I think it's more complicated and messy.

As I mentioned the value recieved by the parameter is dynamic so we can't expect that number of values pass from the serverside because it could be more than 48K chars. or could be less than 4K chars. all will depends from the user selection.

So declaring list of parameters that hold the cut-off values (which the 4K chars.) each is not flexible.

And your solutions might goes this way:

Select field.........

from table

where field <condition> $p1

and field <condition> $p2

and field <condition> $p3

and field <condition> $p4

...... and $p12.

Hope you understand what I mean.

thanks.

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 5

alex_pangcats (10/4/2009)
Hi Gardin,
Thanks for the reply.
Your answer is nice, but I think it's more complicated and messy.
As I mentioned the value recieved by the parameter is dynamic so we can't expect that number of values pass from the serverside because it could be more than 48K chars. or could be less than 4K chars. all will depends from the user selection.
So declaring list of parameters that hold the cut-off values (which the 4K chars.) each is not flexible.
And your solutions might goes this way:
Select field.........
from table
where field <condition> $p1
and field <condition> $p2
and field <condition> $p3
and field <condition> $p4
...... and $p12.
Hope you understand what I mean.
thanks.

You could "slice" a TEXT datatype variable in a stored proc because you can declare a TEXT datatype as a parameter in a stored proc. Once you've sliced (split) the TEXT parameter into a table, then you can read from the table to make a temporary proc and execute it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 6

Matt Whitfield (10/4/2009)
Jeff Moden (10/3/2009)
Most people don't know about it, but you can also create temporary stored procedures that begin with #.
A dark hand waves in the mist...
'Your SQL skill has just improved 3 points'
😀

Heh... the never ending game continues... 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)