CODEWITHSUNDEEP
sqlsql-servercsvt-sqlstored-procedures
coffeetime
coffeetime

Reputation: 131

sp_send_dbmail Column Header Format Issue (Incorrect syntax near '@Column1Name')

I keep getting an "Incorrect syntax near '+' error when trying to concatenate the column header. I'm trying to follow the tutorial below:

https://www.purplefrogsystems.com/blog/2014/04/excel-doesnt-open-csv-files-correctly-from-sp_send_dbmail/

DECLARE @tab char(1) = CHAR(9)
DECLARE @Column1Name VARCHAR(255)
SET @Column1Name = '[sep=,' + CHAR(13) + CHAR(10) + 'Date]'


EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@attach_query_result_as_file = 1,
@query_attachment_filename='test.csv',
@query_result_separator=@tab,
@query_result_no_padding=1,

@body = 'See attached file',

@subject = 'Testing Data',
@query = '(SELECT CONVERT(CHAR(20), Date, 20) AS' + @Column1Name + ', 
           Column2 FROM testTable)'

enter image description here

Upvotes: 1

Views: 896

Answers (2)

EzLo
EzLo

Reputation: 14209

You can't supply expressions as parameters when executing stored procedures.

Set the full result to a variable first, then simply pass that variable as the parameter:

DECLARE @tab char(1) = CHAR(9)
DECLARE @Column1Name VARCHAR(255)
SET @Column1Name = '[sep=,' + CHAR(13) + CHAR(10) + 'Date]'

DECLARE @query VARCHAR(MAX) = '(SELECT CONVERT(CHAR(20), Date, 20) AS' + @Column1Name + ', Column2 FROM testTable)'

EXEC msdb.dbo.sp_send_dbmail
    @recipients = '[email protected]',
    @attach_query_result_as_file = 1,
    @query_attachment_filename='test.csv',
    @query_result_separator=@tab,
    @query_result_no_padding=1,
    @body = 'See attached file',
    @subject = 'Testing Data',
    @query = @query

PD: You might want to check if you need to add a space between concatenations when building dynamic SQL. In this case it's not needed as you already have the square brackets, but might as well get used to adding spaces.

 ... '20) AS ' + @Column1Name + ' , Column2 FROM testTable)'

Upvotes: 4

James
James

Reputation: 21

You might need to add an extra space in. Currently it will resolve to CONVERT(CHAR(20), Date, 20) AS@Column1Name

Upvotes: 1

Related Questions