Hitting nVarchar(max) limitation when running exec sp_executesql

Question

Hitting nVarchar(max) limitation when running exec sp_executesql

Viewing 10 posts - 16 through 24 (of 24 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Heh... wouldn't be the first time MS documentation is, ummm... "misleading". Guess I need to do some digging and find out if VARCHAR(MAX) is implicitly convertable before I bad mouth them too much, though.

--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)

mtassin SSC-Insane Points: 23099 More actions · Answer 2

I'll bet its not... and here's why

Microsoft SQL Server 2005 introduces the max specifier. This specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types. You can use the large-value data types to store up to 2^31-1 bytes of data.

Varchar 2^31-1 bytes = 2^31-1 characters

Nvarchar 2^31-1 bytes = 2^30-1 characters

Because nvarchar(max) can only hold 1/2 as many characters as varchar(max) it's probably not implicitely convertable.

I could be wrong... but that's why at least I think.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

mtassin SSC-Insane Points: 23099 More actions · Answer 3

Hmmm based on that I'd expect this to work though

declare @bob-2 varchar(2000)

select

@bob-2 = 'select * from sys.tables'

exec sp_executesql @bob-2

but instead I get

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 6

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

all over again.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

matzer SSCrazy Eights Points: 9727 More actions · Answer 4

Here's what my BOL (september 2007) says:

[ @stmt = ] stmt
Is a Unicode string that contains a Transact-SQL statement or batch. stmt must be either a Unicode constant or a Unicode variable. More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. Character constants are not allowed. If a Unicode constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is valid, but the character constant 'sp_who' is not. The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).

matzer SSCrazy Eights Points: 9727 More actions · Answer 5

I just downloaded the latest version of BOL 2005 (november 2008). The text didn't change. The topic 'sp_executesql (Transact-SQL)' is annotated as follows: Updated: 5 December 2005. So the quote from Jeff's Books Online seems to come from a very old version. Jeff, can you confirm that?

Peter

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 6

I figured out the problem.

None of my @sql = '' included the N'' in any statement. I converted every assignment to this and it works like a charm now...sigh

Link to my blog http://notyelf.com/

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Peter Brinkhaus (1/8/2010)
I just downloaded the latest version of BOL 2005 (november 2008). The text didn't change. The topic 'sp_executesql (Transact-SQL)' is annotated as follows: Updated: 5 December 2005. So the quote from Jeff's Books Online seems to come from a very old version. Jeff, can you confirm that?
Peter

I can't confirm because my copy has no such annotation. It does have a copyright date of 2005, though.

This brings up another subject... I'm at SP3 with my copy of SQL Server... I thought service packs were supposed to include updates to BOL.

--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)

matzer SSCrazy Eights Points: 9727 More actions · Answer 8

I'm also at SP3, but BOL has to be updated separately. See the readme for SP3.

Peter

Edit: added hyperlink

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Thanks for the tip, Peter. That's one thing I never really paid attention to.

--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)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 10

shannonjk (1/7/2010)
How would I use this in my code?

It's quite tricky to answer because we haven't seen your code yet.

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