Extended stored procedure xp_create_subdir returns "Invalid Parameter" message

Question

Since version 2005 Microsoft SQL Server allows creating path if it's not exist. Simply call stored procedure with parameter leading to desired path on disk.

DECLARE @targetDirectory nvarchar(max) = N'E:\backup' 
exec xp_create_subdir @targetDirectory

So, if you write code above into query pane in Microsoft SQL Server Management Studio, you receive following error message:

Msg 22048, Level 15, State 0, Line 0 Error executing extended stored procedure: Invalid Parameter

It's strange that if you change code to

exec xp_create_subdir N'E:\backup'

and execute it, directory is created at desired path, assuming that sql service account has rights to write.

Answer 1

Although nvarchar(max) has maximum length of 2GB characters, if you change @targetDirectory variable declaration to

DECLARE @targetDirectory nvarchar(4000) = N'E:\backup' 

and execute following statement

exec xp_create_subdir @targetDirectory

you successfully create subdirectory backup on disk E: if it not exists and return message

Command(s) completed successfully. in the message window.

The reason is that stored procedure xp_create_subdir doesn't accept nvarchar(max) data type.

**EDIT:**According to Sean Lange comment, the reason was that variable length was different in statements above.