Send image by mail using SQL SERVER 2012

Navigation
2

Good, I would like you to help me, I am sending an email using SQL MAIL SQL SERVER which I get the email generated but now I want to attach an image for that I have a table that has a field that stores a Format image IMAGE .

When I try to insert an image and execute it in my procedure, I miss 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

    
asked by PieroDev 13.04.2017 в 01:20
source

2 answers

1

First, let's see what the official documentation :

  

IMPORTANT! The data types ntext , text and image will be removed in the future version of SQL Server (SQL Server 2016). Avoid using these types of data in new development jobs and plan to modify the applications that currently use them. Use nvarchar (max) , varchar (max) and varbinary (max) instead.

That just as a note in case you want to update the SQL Server version in the future.

Meanwhile, I put two possible solutions:

Solution 1:

The first option is to store the images to a physical route and later, include that route in the stored procedure to attach the file:

To save the image to a physical route: 

DECLARE @ImageData varbinary(max);
SELECT @ImageData = (SELECT convert(varbinary(max), filecontent, 1) FROM TTareasAtender WHERE oficina=9 AND registro=1)

DECLARE @Path nvarchar(1024);
SELECT @Path = 'C:\Imagenes\RutaDondeSeGuardanLasImagenes';

DECLARE @Filename NVARCHAR(1024);
SELECT @Filename = (SELECT fileilename FROM TTareasAtender WHERE oficina=9 AND registro=1);

DECLARE @FullPathToOutputFile NVARCHAR(2048);
SELECT @FullPathToOutputFile = @Path + '\' + @Filename;

DECLARE @ObjectToken INT
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT;
EXEC sp_OASetProperty @ObjectToken, 'Type', 1;
EXEC sp_OAMethod @ObjectToken, 'Open';
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData;
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2;
EXEC sp_OAMethod @ObjectToken, 'Close';
EXEC sp_OADestroy @ObjectToken;

To send it by email: 

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Incidencias_Qapaq',
@recipients = '[email protected]',
@blind_copy_recipients = '[email protected]',
@subject = 'REGISTRO TAREA QAPAQ - ESTADO: RECIBO REGISTRADO: LOGISTICA - ADMINISTRACION'
@body = @tableHTML,
@body_format = 'HTML',
@file_attachments = @Filename-- --> Este es la ruta del archivo que se generó anteriormente

References:

Solution 2:

Since you have the image saved in a data type image , the first thing we will do is convert it to varbinary , so that later we can integrate it into the body of the HTML:

We declare the variable of type VARBINARY and assign the content of image : 

DECLARE @ImageData varbinary(max);
SELECT @ImageData = (SELECT convert(varbinary(max), filecontent, 1) FROM TTareasAtender WHERE oficina=9 AND registro=1)

Inside the HTML body we put the tag <img> in the following way: 

<img src="cid:' + @ImageData + '"/>
    
answered by 13.04.2017 / 03:16
source
0

It beats me that your problem is in this line: 

Declare 
      @cantidad as varchar(10),
      @Asunto as Varchar(100), 
      @Mensaje as Varchar(100),
      @Tarea as varchar(150),
      @agencia as varchar(250),
      @FVR1 as varchar(250), 
      @img as image

Replace with: 

Declare 
      @cantidad as varchar(max),
      @Asunto as Varchar(max), 
      @Mensaje as Varchar(max),
      @Tarea as varchar(max),
      @agencia as varchar(max),
      @FVR1 as varchar(max), 
      @img as varbinary(max)

Use varbinary(max) instead of image : 

@img as image
@img as varbinary(max)

A variable varbinary(max) can be passed as a value for an image parameter.

Recommendation by experience: when developing emails, styles go inline because not all mail clients render the same, check: link

Edit

Replaces: 

<img =" '+@img+'"/>

By 

<img src="'+@img+'"/>

If in the variable @img you have a defined route, it will work as long as you replace it in the edition of the code prior to this edition, that is: 

@img as image

By: 

@img as varchar(max)

Issue 2

In case you do not have a URL where you can see the image I recommend you do the following:

In a web project, create a handler or a controller or a webmethod that when you pass the name of the file, it renders it, something like this: 

http://miwebsite.com/cms/imagen?nombre=imagen-891248a4d30ce74c120a0ebba1ba4af.png

With this working, in the script use: 

<img src="http://miwebsite.com/cms/imagen?nombre='+@img+'"/>

and checking that when visiting the link the image is shown, it will work and it will be displayed in the sent mail: D

    
answered by 13.04.2017 в 01:48
Use input () within a for cycle (Python) Error in inRange () "TypeError: unknown is not a numpy array"