June 22, 2009 at 9:00 am
Hi all, hope you can help,
I'm trying to add an attatchment (that is stored in an Image field) like this:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Intraport',
@recipients = 'atlek@art.com',
@body = 'Hi atle',
@body_format ='HTML',
@subject = 'Testing mail',
@query='SELECT DOC_FILE FROM DOCUMENTS WHERE DOC_ID=3',
@execute_query_database='IP',
@attach_query_result_as_file =1,
@query_attachment_filename='testdoc.doc',
@query_no_truncate=1
The mail and attachment are sucessfully sent, but the file is unreadable for Word. I know the data is fine since I can download it from my web-app just fine.
Is what i'm trying to do even possible, or how can I save the file to the file system and attach it from there?
Atle
June 22, 2009 at 11:28 am
Are you using SQL Server 2000 / 2005?
Are you calling this sp from front end application like c#.net or vb.net code etc.. ?
Rajesh Kasturi
June 22, 2009 at 11:32 am
If you are reading from .net application go through the below url
http://support.microsoft.com/kb/326502
http://www.akadia.com/services/dotnet_load_blob.html
Rajesh Kasturi
June 22, 2009 at 12:27 pm
I don't think you can do it directly from sql. I was able to get image emailed by using powershell to output the file to filesystem and calling the powershell script from the query parameter in the dbmail proc.
In my example i have the sql staement hardcoded into the powershell script. If this does not work for you you can always create the powershell script dynamically. Hope this helps.
Powershell script(viewimage.ps1)
$conn = new-object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "server=localhost;database=dbname;integrated security=true"
$cmd = new-object System.Data.SqlClient.SqlCommand
$cmd.CommandText = "SELECT DOC_FILE FROM DOCUMENTS WHERE DOC_ID=3"
$cmd.Connection = $conn
$adapter = new-object System.Data.SqlClient.SqlDataAdapter
$adapter.SelectCommand = $cmd
$ds = new-object System.Data.DataSet
$adapter.Fill($ds)
$conn.close()
#$ds.Tables[0]
$bytes = $ds.Tables[0].Rows[0][0]
[void][reflection.assembly]::LoadWithPartialName("System.IO")
$memoryStream = new-object System.IO.MemoryStream
$memoryStream.write($bytes,0,$bytes.Length)
[void][reflection.assembly]::LoadWithPartialName("System.Drawing")
$bm = [System.Drawing.Bitmap]::FromStream($memorystream)
$bm.Save("c:\testdoc.doc")
sp_send_dbmail parameters
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'test@test.com',
@body = 'test',
@body_format ='HTML',
@subject = 'Testing mail',
@query='xp_cmdshell ''c:\powershell\image.bat''',
@execute_query_database='test',
@file_attachments='c:\testdoc.doc'
image.bat code
powershell -command "& 'c:\powershell\viewimage.ps1' "
June 22, 2009 at 2:03 pm
Thank you all for the input!
I'm calling the SP from a .net 2.0 web app created with Ironspeed Designer and VS. I use SQL Server 2005.
I understand it is no way to do this directly. The Powershell scripting is interesting!. I will give that a try first, also because it will give me a new tool I can use for other purposes 🙂
I now got enough to investigate for a while, so I am very grateful - thanks!
Atle
June 22, 2009 at 2:09 pm
You can pull the file out of the database and onto the file system in a similar fashion that I did with the powershell script because they use the same framework. Then when calling the sp_send_dbmail stored proc, you would just need to supply the file_attachment parameter and not the query.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply