question

Massimo-4626 avatar image
0 Votes"
Massimo-4626 asked MelissaMa-msft commented

Failed to initialize sqlcmd library with error number -2147024809.

Hi,
I use the following script to send the result of a query as attachment to email. The script works correctly if I delete the argument @query_no_truncate = 1, instead when I use it the script returns the error in subject. Can anyone help me?

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'XXXXXXXX',
@recipients='xxxxx@xxxx.it',
@subject= @oggetto,
@body='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.',
@query ='SELECT * FROM TABLENAME_CSV',
@attach_query_result_as_file = 1,
@query_attachment_filename = @filename,
@query_result_separator = '',
@query_result_header = 0,
@append_query_error = 1,
@query_no_truncate = 1;

thanks
Massimo

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

Try setting @query_result_header to 1.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @Massimo-4626,

Welcome to Microsoft Q&A!

In the Official Document sp_send_dbmail, it mentions as below:

[ @query_result_header = ] query_result_header Specifies whether the query results include column headers. The query_result_header value is of type bit. When the value is 1, query results contain column headers. When the value is 0, query results do not include column headers. This parameter defaults to 1. This parameter is only applicable if @query is specified.

85009-note.png

So as also mentioned by other expert, please setting @query_result_header to 0 and setting @query_no_truncate to 1 and have aother try.

If above settings are still working , you could try with below:

  • Rather than using '*', actually list the columns you want.

  • To fully qualified the DATABASE + SCHEMA_NAME + TABLE_NAME ('select * from sales.dbo.sales_month'), even if you're in the same database.

  • Check whether the file attachment was too large.

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


note.png (21.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Massimo-4626 avatar image
0 Votes"
Massimo-4626 answered MelissaMa-msft commented

Hi
I tried to set @query_result_header to 1 and now script works without errors.
many thanks

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Massimo-4626,

Thanks for your update and glad that you solved this issue.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·