nvarchar(max) size issue in SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

nvarchar(max) size issue in SP

Author

Topic

PadSQL
Starting Member

5 Posts

Posted - 2008-01-18 : 04:16:22

Hi All,
I've an SP. In that i'm building an SQL query with lot of criteria and based on lot of parameters. So here the query is very big. I used a variable say @SQLString nvarchar(max). Actually 2gb we can store thru this. But if i used only one variable like this to build a query, its not building the exact whole query. Some where its cutting and remaining query conditions are binding into the variable. But we can use number of variable to store sql query with lot pieces and finally join together and use it. its working. but i think this is not a correct method.
I would like to know why its not storing more lengthy query in a variable with max type. whats the issue and how to solve this one ?.

Thanks in Advance.

PAD

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-18 : 04:31:26

You mean you have a dynamic query larger than 2 gig characters?
Hmmm.....

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-18 : 04:43:24

How did you know it truncates the query?
Dont print or select that variable
Execute and see

Madhivanan

Failing to plan is Planning to fail

PadSQL
Starting Member

5 Posts

Posted - 2008-01-19 : 06:36:03

Hi
Total character is not more than 2gb. but may be more than 8000 characters.

I directly executed, its cut in middle of somewhere and displayed syntax error. but there is no syntax error. its an issue with the length. Need urgent reply.

Thanks
PAD

PadSQL
Starting Member

5 Posts

Posted - 2008-01-21 : 23:35:32

Hi
I didn't get any right solution for this issue. Help will be appreciated.

Thanks in advance.
PAD

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 02:16:26

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

E 12°55'05.25"
N 56°04'39.16"

PadSQL
Starting Member

5 Posts

Posted - 2008-01-22 : 04:26:58

Hi
I've seen the link. But nothing is close to my requirement.
Once again, i'll give u details

Create procedure Proc1(@param1 varchar(max)=null, @param2 datetime, @para3 int)
as
SET NOCOUNT ON
DECLARE @sql nvarchar(max)
set @sql = ' SELECT ... ' --The big query will come inside the quotes
if @para3=0
begin
set @sql = @sql + (@param1
end
set @sql = @sql + ' WHERE DateOfBirth = @xparam2' --like this lot criteria and conditions will be included
set @sql = @sql + ' AND ...' -- Some Criteria
EXEC sp_executesql @sql, N'@param1 varchar(max), @xparam2 datetime, @param3 int', @param1, @param2, @param3
print @sql

This is the Stored procedure.
After created the SP i executed in T-SQL as
Exec Proc1 '', '01/01/1985', 1

But its giving some syntax error and also i checked the printed @sql. Its cut middle of somewhere. I didn't get full query. I could get any results too. Only error and Printed @sql query

Need solution at the earliest

Thanks in advance.
PAD

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 04:41:11

Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52274

E 12°55'05.25"
N 56°04'39.16"

PadSQL
Starting Member

5 Posts

Posted - 2008-01-22 : 06:30:00

Hi
Already saw the link which u posted. But its not usefull. Thats why i send the exact criteria and i need exact solution for this.

Thanks in Advance
PAD

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2008-01-22 : 06:46:09

The print statement truncates any data longer than 8000 characters even if varchar(max) is used:

This is what I have got from BOL: Arguments - string_expr
Is an expression that returns a string. Can include concatenated literal values, functions, and variables. The message string can be up to 8,000 characters long; any characters after 8,000 are truncated.

So the exec statement is using the entire length of the string, it's just that the print statement doesn't print the whole thing.

If you are building such a big dynamic sql statement then you are definately doing something terribly wrong, there has to be a much more elagant solution to this.

Duane.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 07:11:52

PRINT @SQL1
PRINT @SQL2
PRINT @SQL3

E 12°55'05.25"
N 56°04'39.16"

fuckovski
Starting Member

1 Post

Posted - 2008-11-02 : 04:47:21

You probably have an error in your sql statement. The print will truncate the varchar(max) variable to 8000 characters. To display your sql for debuging the syntax error use code like this:

declare @sql_txt varchar(max)
set @sql_txt = '.........a very long string.........'
print(@sql_txt)
if (len(@sql_txt) > 8000)
print(substring(@sql_txt, 8001, len(@sql_txt) - 8000))
if (len(@sql_txt) > 16000)
print(substring(@sql_txt, 16001, len(@sql_txt) - 16000))
if (len(@sql_txt) > 24000)
print(substring(@sql_txt, 24001, len(@sql_txt) - 24000))
if (len(@sql_txt) > 32000)
print(substring(@sql_txt, 32001, len(@sql_txt) - 32000))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-02 : 10:57:02

or use export import task to copy contenets of column to a file.

BFDill
Starting Member

1 Post

Posted - 2009-07-20 : 21:24:57

I blogged on this. It might not be the best fix in the world, but it works great for me :-)

YMMV

http://weblogs.asp.net/bdill/archive/2007/09/29/sql-server-print-max.aspx

saralstalin
Starting Member

11 Posts

Posted - 2009-10-27 : 10:49:58

I faced a similar issue, varchar(max) local variable inside one SP was getting clipped of at 8000 characters. A colleague suggested to convert one of the SP input parameter to varchar(max) and surprisingly that fixed the problem.

Saral S Stalin

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-27 : 18:22:50

what happens when you take the "big query" by utself and run it in query analyzer?
show us your big query or dump the big query into view
did you copy and paste your code or is this a typo

set @sql = @sql + (@param1

also can you try it without using dynamic query and instead use
WHERE DateOfBirth = @xparam2 OR @xparam2 IS NULL type of thing.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources