sql 2k5 nvarchar(max) only allowing 4000 chars 1

[glgcag]

I've created a stored proc with a dynamic where clause - I've declared sSQL as a variable of type nvarchar(max) so that I can then run sp_executesql at the end of the stored proc passing in the sSQL variable.

Everything was working fine last week, but now, the sp won't return any results and when I view the printed sSQL variable in the messages window, it is only showing 4000 characters of my sSQL variable. (I know this because I changed the variable type to nvarchar(4000) and it errored out and what printed was identical. Ironically, using (max) doesn't error out, it just doesn't return any records!)

Any ideas of what might be causing this? I'm using SQL Server 2005 Standard and I haven't changed anything in the db since last week.

 

[vbkris]

hi,

>>it just doesn't return any records

when you print the is the query is it complete?

also directly try to EXEC the query and let me know if that gives an ouptut too...

Known is handfull, Unknown is worldfull

 

[SQLSister]

could anyone have set the db to backwards compatibility with 2000?

"NOTHING is more important in a database than integrity." ESquared

 

[glgcag]

Thanks for the reply vbkris and SQLSister!

What I discovered is the nvarchar(max) variable IS storing all the characters, but when I print the variable to the message tab for troubleshooting, it only prints 4000 characters. Hence, I'm not able to find the problem in the WHERE clause because it doesn't even print it.

Is there a limitation to how many characters will print in the message tab?

 

[philhege]

Maybe this can steer you in the right direction.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[glgcag]

philhege,

Thank you for the reply- that answers it perfectly. Looks like nvarchar(max) doesn't work so well with the PRINT option in SQL Server if you have more than 4000 chars. I'll try their workaround.