I was here looking and working with this. Your problem is that the passed in string was being used within SP, but exceeded its max and ended up with miss formed syntax.

I changed it to (max) and this worked fine. I also had to declare a BCPSQL string as xp_cmdshell does not like (max).

Other than these 2 changes, this works really well to combine Header with Data.

create proc spExportData_n

(

@dbName varchar(100) = 'master',

@sql Nvarchar(max) = '',

@fullFileName varchar(100) = ''

)

as

...declare...

, @tempSQL varchar(8000)

...

Exec master..xp_cmdshell @tempSQL