Hello, I would like you to help me, I am sending an email using SQL MAIL from SQL SERVER for which the generated email does arrive but now I want to attach an image for that I have a table that has a field that stores an image format IMAGE
.
When I try to insert an image and execute it in my procedure I get an error that is this:
My code of the stored procedure that generates the email is this:
alter table mail
if exists(select * from TTareasAtender where comentario='' and oficina=9)
begin
Declare @CampoSTOT7 varchar(max)
Declare @NReg7 int
Declare @NRegTot7 int
IF OBJECT_ID('DBProAuxQP_QA..#temp7') IS NOT NULL
BEGIN
DROP TABLE DBProAuxQP_QA..#temp7
END
else
Select ROW_NUMBER() OVER (ORDER BY NumeroRecibo) as NN,* into #temp7 from TTareasAtender where comentario='' and oficina=9
--select * from #temp1
Set @NReg7=0
Set @NRegTot7= (select count(*) as Cantidad from #temp7 where comentario='' and oficina=9 and NN=@NReg6)
set @CampoSTOT7='0'
WHILE @NReg7<=@NRegTot7
begin
set @NReg7= @NReg7 + 1
Declare @cantidad7 as varchar(10),@Asunto7 as Varchar(500), @Mensaje7 as Varchar(500),@Tarea7 as varchar(500),@agencia7 as varchar(160),@img as varbinary(max)
Set @cantidad7= (select count(*) as Cantidad from #temp7 where comentario='' and oficina=9 and NN=@NReg7)
set @agencia7 =(select b.oficina as Agencia from #temp7 a inner join TTareasOficina b on a.oficina=b.idoficina where a.comentario='' and a.oficina=9 and NN=@NReg7)
--set @FVR3=(select fechacumplimiento from #temp3 where IDEstadoTarea=3 and DiasFaltantes=3 and AgenciaID=7 and diasfaltantes is not null and NN=@NReg2)
set @Tarea7 = (select tarea from #temp7 where comentario='' and oficina=9 and NN=@NReg7)
set @Asunto7 = @Tarea7
set @img=(select filecontent from TTareasAtender where oficina=9 and registro=1)
set @tableHTML=
'<style type="text/css">
body
{
font: 70.5% Tahoma;
margin-left: 0px;
margin-top: 0px;
margin-right: 0px;
margin-bottom: 0px;
}
.TableFrm {
width:75%;
font: 12px Arial, Helvetica, sans-serif;
align-content:center;
margin: 0 auto;
}
.TableFrm th {
border:1px solid #E0E0E0;
background:#F5F5F5;
padding:6px;
font-weight:bold;
text-align:left;
}
.TableFrm td {
border:1px solid #E0E0E0;
background:#FFF;
padding:6px;
text-align:left;
}
.table-hover tbody tr:hover td,
.table-hover tbody tr:hover th {
background-color: #f5f5f5;
}
</style>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1"><title>
.: INFORME DE REGISTRO DE TAREA | FINANCIERA QAPAQ S.A :.
</title>
<body >
<form method="post" id="form1">
<table class="TableFrm">
<tr>
<th style="width: 100%" colspan="4" >..:: INFORME DE REGISTRO DE TAREA | FINANCIERA QAPAQ S.A::..</th>
</tr>
<tr>
<th style="width: 30%">Tipo de Tarea: </th>
<td style="width: 70%" colspan="3">'+@Tarea7+'</td>
</tr>
<tr>
<th style="width: 30%">Estado Tarea: </th>
<td style="width: 70%" colspan="3">REGISTRO DE TAREAS</td>
</tr>
<tr>
<th style="width: 30%">Cantidad: </th>
<td style="width: 70%" colspan="3">'+@cantidad7+'</td>
</tr>
<tr>
<th style="width: 30%">Agencia: </th>
<td style="width: 70%" colspan="3">'+@agencia7+'</td>
</tr>
<tr>
<th style="width: 30%">Área Responsable de Atención: </th>
<td style="width: 70%" colspan="3">LOGISTICA - ADMINISTRACIÓN</td>
</tr>
<tr>
<th style="width: 30%">imagen </th>
<img src="'+@img+'"/>
</tr>
<tr>
<th colspan="4">Informe del Seguimiento de Tareas Por Vencer</th>
</tr>
</table></br>
<table>
<tr><td class="PiePagina"> SISTEMA DE REGISTRO Y ATENCIÓN DE TAREAS, FINANCIERA QAPAQ S.A. - ©2017</td></tr>
</table>
</form>
</body> '
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Incidencias_Qapaq',--Perfil de correo configurado.
--@recipients = '[email protected]; [email protected] ; [email protected] ', -- A quien se va enviar el correo.
--@copy_recipients ='[email protected]',--'[email protected]; [email protected]', --Quien lo va a atender
@blind_copy_recipients='[email protected]',
@body =@tableHTML,-- 'Este correo es enviado desde SQL Server 2005 con código TSQL por sqlPsyKrest Blog',--Cuerpo del correo.
@body_format = 'HTML',
@subject = 'REGISTRO TAREA QAPAQ - ESTADO: RECIBO REGISTRADO: LOGISTICA - ADMINISTRACION' --@Asunto ,--Asunto del correo.
--@importance = 'High' --[Low | Normal| High] Prioridad del correo.
END
end
end
First of all, let's see what the official documentation says :
That just as a note in case in the future you want to update the version of SQL Server.
Meanwhile, I put two possible solutions:
Solution 1:
The first option is to store the images to a physical path and then include that path in the stored procedure to attach the file:
To save the image to a physical path:
To send it by email:
References:
Solution 2:
Since you have the image saved in a data type
image
, the first thing we will do is convert it tovarbinary
, so that we can later integrate it into the body of the HTML:We declare the variable of type
VARBINARY
and assign the content of theimage
:Inside the HTML body we put the tag
<img>
as follows:It beats me that your problem is in this line:
Replace with:
Use
varbinary(max)
instead ofimage
:A variable
varbinary(max)
can be passed as a value for an image parameter.Recommendation from experience: when you develop mailing, the styles go inline because not all email clients render the same, check: https://templates.mailchimp.com/resources/email-client-css-support/
Edition
Replaces:
By
If you have a route defined in the @img variable, it will work as long as you replace it in the code edition prior to this edition, that is:
By:
Edition 2
In case you do not have a URL where the image can be viewed, I recommend you do the following:
In a web project, create a handler or a controller or a webmethod that when passed the name of the file, it renders it, something like this:
With this working, in the script use:
and checking that when visiting the link the image is displayed, it will work and will be displayed in the sent mail :D