There is a way to send an email file that is stored in a column 'binary' through 'msdb.dbo.sp_send_dbmail'?

  • There is a way to send an email file that is stored in a column 'binary' through 'msdb.dbo.sp_send_dbmail'?

  • well here's one way using CLR:

    if you were to install Elliot Whitlow's project at http://nclsqlclrfile.codeplex.com/

    you could save the binary to disk on the server, and then send the attachment.

    --MSPSaveFileImage

    -- Parameters: @FilePath,@FileName,@FileBytes

    -- purpose: given an varbinary image column in a table, write that image to disk

    -- usage:

    --assumes table and the file from the example above for dbo.MFGetFileImage exists already.

    declare @myfile varbinary(max)

    SELECT @myfile = rawimage FROM myImages WHERE id = 1

    EXEC dbo.MSPSaveFileImage 'C:\Data','spinning.doc',@myfile

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name='My DB Profile Name',

    @recipients='lowell@somedomain.com',

    @subject = 'Image Attachment',

    @body = 'nothing exciting',

    @body_format = 'HTML',

    @file_attachments='C:\Data\spinning.doc'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thank for answering

    Is there a way without using CLR?

  • i haven't done it since i discovered CLR, but it's possible with bcp; google bcp binary blob for examples, but here's one:

    bcp "select document_binary_data from databaseName.dbo.tableName where id = 12345" queryout "c:\filename.doc" -S server -U username -P password

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

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