hi,
i plan to use the database mail to send the mail. i created stored procedure to send the mail to all receipents ,.
Table fields like, profile,Toaddress,Bcc,Ccc,Blbcontents ,filename..subj,msgbody ,etc...,
download the blb & attach with the mail for the particular recepent
.
so., is it possible to download the blobcontent using Query? or any Other function available in Sqlserver 2005?
In order to attach files to an email in Database Mail on SQL Server 2005, the files must be on disk. If you are storing files at blobs (VarBinary or image) in your SQL Server database, you must extract them to disk before attaching them to your mail.
Does that answer your question?
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
|||thanks Mr.Paul A. Mestemaker,
my question is, Is any system procedure available to download the blob-content in sql server 2005 database itself?
Please let me know ..
any way, myself found the solution, please let me know the procedure is correct
i wrote the clr integrated procedure using c# application
the below steps i have done
1. created the ExportBlob.dll file
2. Created assembly with ExportBlob.dll.
3. Created Procedure to call the Dll file with param.
4. Created ownProcedure to send mail
5. if you want to send mail as Automatically, create new job & set the schedule time to run in sql server agents.
this code is working fine.
Create assembly
==============
USE [master]
Go
/* register the assembly in a SQL Server database using the CREATE ASSEMBLY statement */
CREATE ASSEMBLY ExportBlob
FROM 'D:\Extended_Proc\sp_exportBlob.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
GO
/****** Object: StoredProcedure [dbo].[xp_ExportBlob]
CREATE PROCEDURE [dbo].[xp_ExportBlob]
@.ServerName [nvarchar](25),
@.UID [nvarchar](50),
@.Pwd [nvarchar](25),
@.DBName [nvarchar](25),
@.Tbname [nvarchar](50),
@.FldName [nvarchar](100),
@.cndt [nvarchar](max),
@.Exportpath [nvarchar](100)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ExportBlob].[sp_exportBlob].[exportBlob] //
mail send
=========
SET @.DBName=db_name() /* Get & Assign the Current Database Name*/
SET @.Server=@.@.servername
Select @.ListOfAttachFiles='', @.Delimiter=';'
/*Create a Local Dir to save the File*/
set @.ExtractPath='C:\SQLdbmail_attched_files\
DECLARE CursorMailList CURSOR FOR
SELECT [MailID],
[Profile],
left(Subject,255),
[importance],
[MsgTo],
[CopyTo],
[BCCTo],
[Contents],
isnull(HasAttachment,0)
FROM MailTb Where Processed=0 Order by MailID
OPEN CursorMailList
FETCH NEXT FROM CursorMailList INTO @.MailID,@.Profile,@.m_subject,@.m_importance,@.m_Toaddress,@.m_ccaddress,@.m_Bccaddress,@.m_body,@.HasAttachment
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
Select @.ListOfAttachFiles=@.ListOfAttachFiles + @.ExtractPath+Filename+@.Delimiter from Attachments where MailID=@.MailID
SET @.ListOfAttachFiles=left(@.ListOfAttachFiles,len(@.ListOfAttachFiles)-1)
SET @.cndtn='where Datalength(Blobcontents)>0 and MailID='+ convert(VARCHAR,@.MailID,25)
select @.FldNames='Blobcontents,Filename'
/*Download File in the server Dir */
/*passing param to dll , param=servername,UID,PWD,DatabaseName,Tablename,Feildname,wherecndtn,extractDir */
EXEC master..xp_ExportBlob @.Server, 'sa', 'pass', @.DBName, 'Attachments', @.FldNames, @.cndtn, @.ExtractPath
/*Get Missing Attchment Files */
BEGIN TRY
EXECUTE msdb..sp_send_dbmail
@.profile_name = @.Profile
,@.recipients = @.m_Toaddress
,@.copy_recipients = @.m_ccaddress
,@.blind_copy_recipients = @.m_Bccaddress
,@.body = @.m_body
,@.subject = @.m_subject
,@.importance = @.m_importance
,@.file_attachments = @.ListOfAttachFiles /* Eg. D:\mailDownload\filename.ext;c D:\mailDownload\filename2.ext */
,@.mailitem_id = @.mailitemid output
Update MailTb set Processed=1 where MailID=@.MailID /*Update back Mail sent scessfully*/
END TRY /*TRY*/
BEGIN CATCH
SET @.err=1
set @.ErrMsg = @.ErrMsg +'Failed to send Mail with Attachments. SDS MailID ='+convert(varchar,@.MailId,25)
END CATCH /*Catch*/
END /*
FETCH NEXT FROM CursorMailList INTO @.MailID,@.Profile,@.m_subject,@.m_importance,@.m_Toaddress,@.m_ccaddress,@.m_Bccaddress,@.m_body,@.HasAttachment
END /*WHILE*/
-- clean up; close & Deallocate the cursor
CLOSE CursorMailList
DEALLOCATE CursorMailList
c# application {Create library}
====================
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.SqlClient;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
public class sp_exportBlob
{
// Identify that this is a SQL Stored Procedure
[SqlProcedure]
//summary
//FldNames should be BlobContents and Filename with Extension using comma Sepearator.
// [i.e., Fldnames="Blobcontents,Filename+Extension" ]
//
public static void exportBlob(string ServerName, string UID, string Pwd, string DBName, string TbName, string Fldnames, string Cndt, string ExtractPath)
{
SqlConnection sqlconn = new SqlConnection();
SqlDataReader sqlDr;
SqlCommand cmd;
FileStream fs;
BinaryWriter bw;
int Blobsize;
long blob, startIndex;
byte[] outBuffer;
string ExtractFileName = "";
try
{
int IndexBlobContents = 0;
sqlconn.ConnectionString = "Server=" + ServerName + ";UID=" + UID + ";Pwd=" + Pwd + ";Database=" + DBName;
sqlconn.Open();
Blobsize = 1024;// Initalize the BlobSize
cmd = new SqlCommand("SELECT " + Fldnames + " FROM " + TbName + " " + Cndt, sqlconn);
sqlDr = cmd.ExecuteReader();
while (sqlDr.Read())
{
ExtractFileName = ExtractPath + sqlDr[1].ToString();//dirname + filename
startIndex = 0; // Reset the starting byte for the new BLOB.
outBuffer = new byte[Blobsize];
if (File.Exists(@.ExtractFileName))
{
File.Delete(@.ExtractFileName);
}
fs = new FileStream(@.ExtractFileName, FileMode.OpenOrCreate, FileAccess.Write); // Create a file to hold the output.
bw = new BinaryWriter(fs);
blob = sqlDr.GetBytes(IndexBlobContents, startIndex, outBuffer, 0, Blobsize); // Read bytes into outByte[] and retain the number of bytes returned.
while (blob == Blobsize) // Continue while there are bytes beyond the size of the buffer.
{
bw.Write(outBuffer);
bw.Flush();
startIndex += Blobsize;
blob = sqlDr.GetBytes(IndexBlobContents, startIndex, outBuffer, 0, Blobsize);
}
bw.Write(outBuffer, 0, (int)blob);// Write the remaining buffer.
bw.Flush();
bw.Close();
fs.Close();
SqlContext.Pipe.Send("Downloaded successfully in " + ExtractFileName);
}
sqlDr.Close();
}
catch (Exception er)
{
SqlContext.Pipe.Send(er.Message);
}
finally
{
if (sqlconn.State == System.Data.ConnectionState.Open)
{
sqlconn.Close();
}
fs = null;
bw = null;
cmd = null;
sqlDr = null;
sqlconn = null;
}
}
}
|||Looks pretty cool. I generally don't like to see dynamic SQL, but it seems like it's working for your architecture. Thanks for posting this so the community can benefit :-)
People ask about this quite a bit at conferences... now I have a place to direct them.
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
|||Hi Raj,
This solution is great. However, I am using a different environment :-
SQL 2005 and Java for this application.
So, i will not be able to use the c# piece of code.
c# application {Create library}
Thanks,
