运行select @ sql ='BCP CODE'并执行xp_cmdshell proc后的结果是什么 [英] What is the result after running select @sql='BCP CODE' and executing xp_cmdshell proc
问题描述
我将文档内容保存在一个包含IMAGE数据类型的表中。
当我从表中获取内容时传入@query参数发送邮件使用
sp_send_dbmail:
问题1.只有txt文件才能打开
问题2.pdf,docx,xls我无法打开,它显示msg内容已损坏,''我认为编码缺陷...
所以我在网上搜索,发现使用BCP转换文件格式,所以我使用的代码如
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
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
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
我的疑问是@sql中的哪些内容我们实现了master..xp_cmdshell,以及它是如何转换从表中获取文件的确切格式的。
我在哪里可以写文件名,因为我只提取内容
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
推荐答案
HI我最后在BCP中提交了这段代码,它打开所有类型的文件格式如.doc .docx .pdf .jpg .xlsx
select @BCPCommand =''bcpselect来自databasename.dbo.tbl_Log的TOP 1强制转换(强制转换(documentContent as varbinary(max)))其中documentTypeId = sfsd和requestId = fsdqueryout''+ @filepath +'' - T -c -r \\ \\ n -CnP -SServer \ Instance name -V(100)''
欲了解更多,请参阅此链接....
http://www.tek-tips.com/viewthread.cfm?qid=545711 [^]
如果任何人使用BCP jst copy上面的命令并执行它都会正常工作...
我在搜索超过20天后终于完成了。
感谢上帝最后我做了:)):))
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 :):):)
这篇关于运行select @ sql ='BCP CODE'并执行xp_cmdshell proc后的结果是什么的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!