Click here to Skip to main content
15,890,438 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi,

im saving the document content in a table with IMAGE datatype.
when im fetching the content from the table and passing into @query parameter to send mail using

sp_send_dbmail:
problems 1.only txt files will be able to open
problem 2.pdf,docx,xls im unable to open,its showing msg like content corrupted,'i think encoding defect...
so i searched in net ,found using the BCP to convert the file formats, so i used code like
SQL
select @sql= 'bcp "select cast(cast(documentContent as varbinary(max)) as varchar(max)) from tbl_CarDocuments_Log where Id = 5018"  queryout ' + @Filename + ' -T -n -CACP -S10.98.104.28'
exec master..xp_cmdshell @sql

and then executing the proc below
SQL
EXEC msdb.dbo.sp_send_dbmail
   @from_address = 'dsfsd@fsdf.com',
   @recipients=  'fsdfsd@dfs.com',
   @copy_recipients = fssdf@fsfsd.com', 
   @subject= 'test',
   @body= 'testing' ,
   @body_format = 'html',
   @file_attachments = @Filename

SET @sql = 'del "' + @Filename + '"'
exec master..xp_cmdshell @sql

my doubt is what contents in @sql which we exectuing master..xp_cmdshell,and how did it converts the exact format of file fetching from table.

where can i write the filename,since im fetching only content
Posted
Updated 8-May-13 6:36am
v2
Comments
RedDk 8-May-13 12:59pm    
A few things look suspicious so let me ask, before I venture any answer, does this TSQL statement work for you, as-is, right now?

select cast(cast(documentContent as varbinary(max)) as varchar(max)) from tbl_CarDocuments_Log where Id = 5018

And for starters, its "@copy_recipients = 'fssdf@fsfd.com'" not "@copy_recipients = fssdf@fsfd.com'
sreenuk 20-May-13 3:20am    
hey RedDK its mistake while writing in codeproject,dats not a big deal to think.
any way finally i got what i want..

thanks any way
sreenuk 20-May-13 3:21am    
HI i Finally submitted this code in BCP,it opens all types of files Format like .doc .docx .pdf .jpg .xlsx


select @BCPCommand='bcp "select TOP 1 cast(cast(documentContent as varbinary(max)) as varchar(max)) from databasename.dbo.tbl_Log where documentTypeId=sfsd and requestId =fsd " queryout ' + @filepath + ' -T -c -r\n -CACP -SServer\instance name -V(100)'


For more refer this link....
http://www.tek-tips.com/viewthread.cfm?qid=545711[^]
If any one using BCP jst copy above command and execute it will work fine...
i've finally done after searching for more than 20 days.

Thank God Finally i did

1 solution

HI i Finally submitted this code in BCP,it opens all types of files Format like .doc .docx .pdf .jpg .xlsx


select @BCPCommand='bcp "select TOP 1 cast(cast(documentContent as varbinary(max)) as varchar(max)) from databasename.dbo.tbl_Log where documentTypeId=sfsd and requestId =fsd " queryout ' + @filepath + ' -T -c -r\n -CACP -SServer\instance name -V(100)'


For more refer this link....
http://www.tek-tips.com/viewthread.cfm?qid=545711[^]
If any one using BCP jst copy above command and execute it will work fine...
i've finally done after searching for more than 20 days.

Thank God Finally i did :):):)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900