How to put a variable content as attachment file saida.txt ?

Question

How to put a variable content as attachment file saida.txt ?

vpmaciel

Old Hand

Points: 367
More actions
June 15, 2022 at 2:13 am

#4049635

DECLARE @saida VARCHAR(MAX)
SET @saida = ' =================== '
SELECT @saida = @saida + 'SERVER NAME:'+ @@SERVERNAME
SET @saida = @saida + = ' =================== '
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBA',
@recipients = 'terra@gmail.com',
@subject = 'variable as atachment',
@body_format = 'HTML',
@body = @saida,
@file_attachments=@SAIDA; -- how to put the variable content as txt file ?
June 16, 2022 at 5:36 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

Why do you mean by a "txt file"? As in the file has a .txt extension? You define the name of the file with the @query_attachment_filename parameter; so you could just have @query_attachment_filename = N'MyFileName.txt.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

Best is to have sp_send_dbmail execute the query and store the result in a given filename to be added to the email

e.g:

declare @subject varchar(1000)
set @subject = @@servername + ': Count of master sysfiles'
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'yourprofile'
, @recipients = 'johan.bijnens@Aperam.com'
, @query = 'SELECT * FROM master.sys.sysfiles '
, @subject = @subject
, @body = 'Have a look at the attachment'
, @query_result_width = 8000
, @query_result_separator = ''
, @attach_query_result_as_file = 1
, @query_attachment_filename = 'deQueryResultFileName.csv'
, @append_query_error = 1 ;

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

vpmaciel Old Hand Points: 367 More actions · Answer 3

I need to use a variable because I have to do many things before send a message, follow part of code:

DECLARE @saida VARCHAR(MAX), @MSG VARCHAR(MAX)

SET @saida = ' ==================================================================================================== '

SELECT @saida = @saida + 'SERVER NAME:'+ @@SERVERNAME

SET @saida = @saida + ' ==================================================================================================== '

SELECT @saida = @saida + [VERSION] FROM ##INFORMACOES_VERSAO_SQL

SET @saida = @saida + ' ==================================================================================================== '

DECLARE @Instance_Type VARCHAR(MAX), @Current_Node VARCHAR(MAX), @Cluster_Nodes VARCHAR(MAX), @Uptime VARCHAR(MAX), @SQLAgentStatus VARCHAR(MAX)

SELECT @Instance_Type = CAST ([Instance_Type] AS VARCHAR(MAX)),

@Current_Node = CAST ([Current_Node] AS VARCHAR(MAX)),

@Cluster_Nodes = CAST ([Cluster_Nodes] AS VARCHAR(MAX)),

@Uptime = CAST ([Uptime SQL SERVER: DD:HRS:MIN:SEC] AS VARCHAR(MAX)),

@SQLAgentStatus = CAST ([SQLAgentStatus] AS VARCHAR(MAX))

FROM ##INFORMACOES_SERVER

--SELECT * FROM ##INFORMACOES_SERVER

SET @saida = @saida + ' ==================================================================================================== '

SELECT @saida = @saida + ' INFORMAÇÕES DO SERVIDOR '

SELECT @saida = @saida + ' ' + 'Instance_Type' + ': ' + @Instance_Type + ' '

SELECT @saida = @saida + ' ' + 'Current_Node' + ': ' + @Current_Node + ' '

SELECT @saida = @saida + ' ' + 'Cluster_Nodes' + ': ' + @Cluster_Nodes + ' '

SELECT @saida = @saida + ' ' + 'Uptime SQL SERVER: DD:HRS:MIN:SEC' + ': ' + @Uptime + ' '

SELECT @saida = @saida + ' ' + 'SQLAgentStatus' + ': ' + @SQLAgentStatus + ' '

SET @saida = @saida + ' ==================================================================================================== '

SELECT @saida = @saida + ' ÚLTIMO INICIO DO SQL SERVER '

SELECT @MSG = [Uptime SQL SERVER: DD:HRS:MIN:SEC] FROM ##ULTIMO_INICIO_SQL_SERVER

SELECT @saida = @saida + ' ' + 'Uptime SQL SERVER: DD:HRS:MIN:SEC' + ': ' + @MSG + ' '

SET @saida = @saida + ' ==================================================================================================== '

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

You could put it all in a table ( non-# and non-## ), kind of a key-value pair thing.

Then have the query selecting your stored row from the table

-- Example
@Query = 'Select AttachmentValue from myschema.assembled_for_email where keycol = ''' + convert(varchar(26), mydatetime2, 121)+''''
sp_send_dbmail 
...
, @query = @Query 
...

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

vpmaciel Old Hand Points: 367 More actions · Answer 5

But I have many tables, many querys, this is only a short part of code, the code have more than 450 lines, I need to do a report, I belive that does not have a way to do it with a variable. Thanks everebody.

Thom A SSC Guru Points: 99130 More actions · Answer 6

vpmaciel wrote:

But I have many tables, many querys, this is only a short part of code, the code have more than 450 lines, I need to do a report, I belive that does not have a way to do it with a variable. Thanks everebody.

I don't think you're telling us the real problem here. perhaps you should take a step back and explain the real problem. Why do you need to send many emails, with the contents of many tables (one table in each email)?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk