locked
Attach File name RRS feed

  • Question

  • Hopefully someone out there will have an idea how to resolve this.

    I want to send a files in attachment  , here is the example of my files.

    1234_XYBABC.PDF
    5678_PCTARR.PDF
    9012_CNRPCT.PDF

    Without using full name of the file, I want to use the first 4 digits of the number to send the file (4 digits number is Unique for each file).

    Can some one please let me know is it possible to use like function in file attachment.

    @file_attachments=N'\\v-ptcr-pmy-201\storage\Invoices\'+1234+'_'+ *.pdf'

    Any suggestions appreciated,

    Monday, November 6, 2017 1:39 PM

Answers

All replies

  • Hi,

    You have to convert 1234 into varchar :

    N'\\v-ptcr-pmy-201\storage\Invoices\'+CAST(1234 AS VARCHAR(4))+'_'+ '*.pdf'


    Please mark as answered, If you feel happy with this answer.

    Monday, November 6, 2017 1:43 PM
  • I tired no luck. Here is the error message .

    Attachment file \\v-ptcr-pmy-201\storage\Invoices\1234_*.PDF is Invalid.

    Monday, November 6, 2017 2:02 PM
  • Judging from the error message, sp_send_email does not have that capability, so you will need to supply the full name. Since I don't know from where you get the names, or why you only want to supply the number, I cannot help you with the details.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, November 6, 2017 2:08 PM
  • Are the names of your files stored somewhere in a table?

    You have to put the exact file name concat with your directory :

    N'\\v-ptcr-pmy-201\storage\Invoices\'+filename+'.pdf', and the filename must be stored in a variable or a table.


    Please mark as answered, If you feel happy with this answer.


    Monday, November 6, 2017 2:17 PM
  • Numeric values are randomly created and stored in the table. Based on that It creates Invoice file with Numeric value and some name.

    Here is my SP.

        declare @v_Regid nvarchar(30)
        declare @v_invoice_no nvarchar(30)
        declare @v_email nvarchar(255)
        declare @Amount_Due nvarchar(255)
    declare @v_body nvarchar(max)
        declare @v_attach_file nvarchar(255)
    declare @subject1 nvarchar(255)

        declare db_cursor cursor for 
        select [Regnum],[Invoice],InvoiceEmail,[Amount Due]
    from [InvoiceAttachTable] 

        open db_cursor

        FETCH NEXT FROM db_cursor INTO @v_Regid,@v_invoice_no,@v_email,@Amount_Due
        WHILE @@FETCH_STATUS = 0
        begin
       
               set @v_body= 
          '<p>To Whom It May Concern,</p>'
       set @v_attach_file=N'\\v-ptcr-pmy-201\storage\Invoices\'+CAST(@v_Regid AS VARCHAR(4))+'_'+ '*.pdf'
                          
        set  @subject1= 'XXXX XXXXX Invoice '+@v_invoice_no

           EXEC msdb.dbo.sp_send_dbmail
                @profile_name = 'Invoice',
                @recipients = @v_email,
                @subject = @subject1,
                @body =@v_body,
            @file_attachments =@v_attach_file,
    @body_format='HTML'

           FETCH NEXT FROM db_cursor INTO @v_regid,@v_invoice_no,@v_email,@Amount_Due
        end


    • Edited by NaveenCR Monday, November 6, 2017 2:32 PM
    Monday, November 6, 2017 2:20 PM
  • How do you assign value to @v_Regid, I don't see that in your script?


    Please mark as answered, If you feel happy with this answer.


    Monday, November 6, 2017 2:24 PM
  • Name is not a standard format, Also it was not populated in the table.
    • Edited by NaveenCR Monday, November 6, 2017 2:31 PM
    Monday, November 6, 2017 2:29 PM
  • I corrected the script. @_v_regid value comes from the table column [Regnum]
    Monday, November 6, 2017 2:34 PM
  • Name is not a standard format, Also it was not populated in the table.

    then what you need to do is this

    Have a iterative logic to iterate through the required files in your folder. Then inside the loop have a variable to retrieve the filenames during each iteration. Then use this variable to pass to sp_send_dbmail to sent the file as an attachment. 

    If using t-sql coding you can use xp_dirtree for listing the files in folder 

    see

    http://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html

    populate table with its result and iterate through it

    If using SSIS you can use For Each loop with file enumerator to iterate through files in a loop

    http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by NaveenCR Monday, November 6, 2017 3:00 PM
    Monday, November 6, 2017 2:38 PM
  • Ok, but you have to fetch the filename in your cursor too and use this instruction :

     set @v_attach_file=N'\\v-ptcr-pmy-201\storage\Invoices\'+CAST(@v_Regid AS VARCHAR(4))+'_'+ @v_filename +'.pdf'


    Please mark as answered, If you feel happy with this answer.

    Monday, November 6, 2017 2:45 PM
  • Thank you so much. It working now.
    Monday, November 6, 2017 3:00 PM