Sql Send_Db_mail Vertical Table
-
Hi guys,
Sorry about my english....
I have this code below to send a mail with send_db_mail.
DECLARE @Body NVARCHAR(MAX),
@TableHead VARCHAR(1000),
@TableTail VARCHAR(1000)
SET @TableTail = '</table></body></html>' ;
SET @TableHead = '<html><head>' + '<style>'
+ 'td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 11px arial} '
+ '</style>' + '</head>' + '<body>' + 'E-mail enviado em : '
+ CONVERT(VARCHAR(50), GETDATE(), 106) +'
'
+ 'Segue abaixo dados da nota fiscal emitida
'
+ '
<table cellpadding=0 cellspacing=0 border=0>'
+ '<tr bgcolor=#E6E6FA><b>Data Nota</b></td>'
+ '<td bgcolor=#E6E6FA><b>Numero</b></td>'
+ '<td bgcolor=#E6E6FA><b>Série</b></td>'
+ '<td bgcolor=#E6E6FA><b>Emitente</b></td>'
+ '<td bgcolor=#E6E6FA><b>CNPJ Emit.</b></td>'
+ '<td bgcolor=#E6E6FA><b>Destinatario</b></td>'
+ '<td bgcolor=#E6E6FA><b>CNPJ Desti</b></td>'
+ '<td bgcolor=#E6E6FA><b>Chave</b></td>'
+ '<td bgcolor=#E6E6FA><b>Caminho</b></td></tr>' ;
SET @Body = ( SELECT td = CONVERT(VARCHAR(10), N.DATEMI, 120), '',
td = N.NUMNFE, '',
td = N.SERNFE, '',
td = N.NOMEMI, '',
td = N.DOCEMI,'',
td = N.NOMDES,'',
td = N.DOCDES, '',
td = N.CHVNFE, '',
td = SUBSTRING(N.ARQUIV_XML,1,76),''
FROM DFEREQ_NOTA N
WHERE N.CODNOT = 7941
FOR XML RAW('tr'),
ELEMENTS
)
SELECT @Body = @TableHead + ISNULL(@Body, '') + @TableTail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileEnvioEmail',
@recipients = 'adriano@transprimo.com.br',
@subject = 'Nota Fiscal de Transporte Emitida',
@body = @body,
@body_format = 'html',
@from_address = 'suporte@transprimo.com.br'
This is result
But.. i need the result table in vertical like this.
Someone can help me change the code for the expected result
Tks:-):-)
God be praised!
-
You need to change your html. You built this as a table with a single row. You will will need to modify your html so that you get a new row after each value.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Tks!!
I copyed this from the web
You can post a E.G ?
God be praised!
-
adrhohmann (10/14/2016)
Tks!!I copyed this from the web
You can post a E.G ?
You have an example. Are you familiar with HTML? I get that you copied this from somewhere else but how are you going to be able to fix this if you can't even adjust the HTML? The point is that you need to understand what this code is doing and it seems you do not. Can you post what you have tried?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
tks for your reply
I'm a little familiar with HTML but not so much.
I get this code from the web and changed to my data. so I got the first result
I tried modify the code sometimes, but not worked well
I need like a second result, but I dont know do this.
God be praised!
-
adrhohmann (10/14/2016)
tks for your replyI'm a little familiar with HTML but not so much.
I get this code from the web and changed to my data. so I got the first result
I tried modify the code sometimes, but not worked well
I need like a second result, but I dont know do this.
Well what you have is one row of data. What you want is one row for each name/value pair. The only way to give you an example is to do it for you. That rather defeats the purpose. At this point I haven't seen you put in any effort other than asking for others to fix it for you. And if we fix it for you it is not going to help you learn how to do it yourself next time. HTML is not difficult. If you can write sql queries you can write HTML.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Ok!
Tks again
I really not could do it them I resorted this forum
But.. I will keep trying ;-);-)
God be praised!
-
adrhohmann (10/14/2016)
Ok!Tks again
I really not could do it them I resorted this forum
But.. I will keep trying ;-);-)
Show us some work. So far all you have shown is code you found on the internet and don't know how to modify it to suit your requirements. Keep in mind we all do this for free to help others learn. No offense but you don't seem to want to learn or work at it. You seem to want others to do it for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
the maximum result i got was this
DECLARE @Body NVARCHAR(MAX),
@TableHead VARCHAR(1000),
@TableTail VARCHAR(1000)
SET @TableTail = '</table></body></html>' ;
SET @TableHead = '<html><head>' + '<style>'
+ 'td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 11px arial} '
+ '</style>' + '</head>' + '<body>' + 'E-mail enviado em : '
+ CONVERT(VARCHAR(50), GETDATE(), 106) +'
'
+ 'Segue abaixo dados da nota fiscal emitida
'
+ '
<table cellpadding=0 cellspacing=0 border=0>'
+ '<tr><tr bgcolor=#E6E6FA><b>Data Nota</b></td></tr>'
+ '<tr><td bgcolor=#E6E6FA><b>Numero</b></td></tr>'
+ '<tr><td bgcolor=#E6E6FA><b>Série</b></td></tr>'
+ '<tr><td bgcolor=#E6E6FA><b>Emitente</b></td></tr>'
+ '<tr><td bgcolor=#E6E6FA><b>CNPJ Emit.</b></td></tr>'
+ '<tr><td bgcolor=#E6E6FA><b>Destinatario</b></td></tr>'
+ '<tr><td bgcolor=#E6E6FA><b>CNPJ Desti</b></td></tr>'
+ '<tr><td bgcolor=#E6E6FA><b>Chave</b></td></tr>'
+ '<tr><td bgcolor=#E6E6FA><b>Caminho</b></td></tr>' ;
SET @Body = ( SELECT tr = CONVERT(VARCHAR(10), N.DATEMI, 120), '',
tr = N.NUMNFE, '',
tr = N.SERNFE, '',
tr = N.NOMEMI, '',
tr = N.DOCEMI,'',
tr = N.NOMDES,'',
tr = N.DOCDES, '',
tr = N.CHVNFE, '',
tr = SUBSTRING(N.ARQUIV_XML,1,76),''
FROM DFEREQ_NOTA N
WHERE N.CODNOT = 7941
FOR XML RAW('td'),
ELEMENTS
)
SELECT @Body = @TableHead + ISNULL(@Body, '') + @TableTail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileEnvioEmail',
@recipients = 'adriano@transprimo.com.br',
@subject = 'Nota Fiscal de Transporte Emitida',
@body = @body,
@body_format = 'html',
@from_address = 'suporte@transprimo.com.br'
God be praised!
-
You will need to make your rows contain all the data for the row. Something like this in a select statement.
'<tr><tr bgcolor=#E6E6FA><b>Data Nota</b></td>' + CONVERT(VARCHAR(10), N.DATEMI, 120) + '</td></tr>'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I changed my code them I got this result
DECLARE @CODNOT INTDECLARE @TIPOC VARCHAR(1)
SELECT @CODNOT = CODNOT , @TIPOC = TIPDOC FROM INSERTED
IF @TIPOC = 'T'
BEGIN
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Nota Fiscal de Transporte Emitida</H1>' +
N'<table>' +
CAST ( ( SELECT
td ='Data da Nota: ' + CONVERT(VARCHAR(10), N.DATEMI, 103)
FROM DFEREQ_NOTA N
WHERE N.CODNOT = @CODNOT
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
CAST ( ( SELECT
td ='Numero da Nota: ' + CONVERT(VARCHAR(10), N.NUMNFE)
FROM DFEREQ_NOTA N
WHERE N.CODNOT = @CODNOT
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
CAST ( ( SELECT
td ='Série: ' + CONVERT(VARCHAR(10), N.SERNFE)
FROM DFEREQ_NOTA N
WHERE N.CODNOT = @CODNOT
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
CAST ( ( SELECT
td ='Emitente: ' + CONVERT(VARCHAR(100), N.NOMEMI)
FROM DFEREQ_NOTA N
WHERE N.CODNOT = @CODNOT
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
CAST ( ( SELECT
td ='CNPJ (Emitente): ' + CONVERT(VARCHAR(100), N.DOCEMI)
FROM DFEREQ_NOTA N
WHERE N.CODNOT = @CODNOT
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
CAST ( ( SELECT
td ='Destinatario: ' + CONVERT(VARCHAR(100), N.NOMDES)
FROM DFEREQ_NOTA N
WHERE N.CODNOT = @CODNOT
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
CAST ( ( SELECT
td ='CNPJ (Destinatario): ' + CONVERT(VARCHAR(100), N.DOCDES)
FROM DFEREQ_NOTA N
WHERE N.CODNOT = @CODNOT
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
CAST ( ( SELECT
td ='Chave: ' + CONVERT(VARCHAR(100), N.CHVNFE)
FROM DFEREQ_NOTA N
WHERE N.CODNOT = @CODNOT
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
CAST ( ( SELECT
td ='Caminho: ' + SUBSTRING(N.ARQUIV_XML,1,76)
FROM DFEREQ_NOTA N
WHERE N.CODNOT = @CODNOT
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileEnvioEmail',
@recipients = 'adriano@transprimo.com.br',
@subject = 'Nota Fiscal de Transporte Emitida',
@body = @tableHTML,
@body_format = 'html',
@from_address = 'suporte@transprimo.com.br'
God be praised!
-
Nicely done. I assume you can handle it from here?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply