Image Data Type

Question

Image Data Type

jyoti2705

Old Hand

Points: 344
More actions
July 19, 2010 at 11:13 am

#221366

Hi, i am storing one company information with their Logo in a table. Logo has Image Data type, i am able to display Logo on asp.net code side. Problem is when we send some notifications to Company using SQL jobs, i need to display Logo with company info. In SQL job stored procedure i am creating Email body and able to convert field with differnt sql statements. How can i display Logo in Email body(In html code, Select @Body = replace(@Body,'@CompanyLogo',@Logo)).

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

AFAIK, images that will appear in an email must be an attachment. because html is interpreted, you can't just find and replace to ge tthe image in there.

your choices are to use a fully qualified url to the image(http://somedomain/com/images/thatlogo.gif) , or embed it like in this example i created: so you'll need to get the IMAGE datatype to a file so it can be attached prior to teh email, i'm thinking.

declare @body1 varchar(4000)

set @body1 = '<html><head>

<title> Embedded Logo Example</title>

<meta name="Generator" content="EditPlus">

<meta name="Author" content="">

<meta name="Keywords" content="">

<meta name="Description" content="">

</head>

<body>

<table><tr><td valign="top" align="left">MyHeader</td></tr>

<tr><td valign="top" align="left"><img src="cid:sqlservercentral_logo.gif" width="235" height="70" border="0" alt=""></td></tr>

</table>

</body></html>'

EXEC msdb.dbo.sp_send_dbmail

@profile_name='Your Email Profile Name',

@recipients='lowell@somedomain,

@subject = 'SQl 2008 email test',

@body = @body1,

@body_format = 'HTML',

@query = 'SELECT top 3 * from sysobjects where xtype=''U''',

@query_result_header = 0,

@exclude_query_output = 1,

@append_query_error = 1,

@attach_query_result_as_file = 1,

@query_attachment_filename = 'results.txt',

@query_result_no_padding = 1,

@file_attachments = 'C:\sqlservercentral_logo.gif'

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!

jyoti2705 Old Hand Points: 344 More actions · Answer 2

"embed it like in this example i created: so you'll need to get the IMAGE datatype to a file so it can be attached prior to the email", how can i store the image in a text file in this stored procedure?

Please explain these :

<img src=”cid:sqlservercentral_logo.gif” /> - cid -?

@query_attachment_filename = 'results.txt',

@file_attachments = 'C:\sqlservercentral_logo.gif'

will Txt file & Image will be at 2 places?

Lowell SSC Guru Points: 323504 More actions · Answer 3

jyoti2705 (7/19/2010)
"embed it like in this example i created: so you'll need to get the IMAGE datatype to a file so it can be attached prior to the email", how can i store the image in a text file in this stored procedure?
Please explain these :
<img src=”cid:sqlservercentral_logo.gif” /> - cid -?
@query_attachment_filename = 'results.txt',
@file_attachments = 'C:\sqlservercentral_logo.gif'
will Txt file & Image will be at 2 places?

as far as "store the image in a text file in this stored procedure". , search the forums here, you'll need to use bcvp i think, or hte 2008 filestream...

I was thinking you were confusing the presentation of an image with the ability to copy/paste...it's not.

the EASIEST way to display your logos on a per-client basis is to put the logos on a web server some where, and reference the path to that image in the email. otherwise, like i said, you will need to put each image in a folder on the SQL server, and attach the correct image based ont he client. While it may be possible to use the filestream object in SQL 2008 to stream the IMAGE datatype intot eh email, i've never done that...you'll have to google that one up.

for the two attachments, results.txt and the logog.gif, my example does a lot more than jsut the image display, it also ran a query and attached the results of that query as 'results.txt'; you would ignore that portion.

you said you were sending an html email. if you ever do View>>Source of any email with images, you'll see the same thing. you must get 1000 examples of this with Cialis ads in your spam folder, same as me, every day.

http://www.systemnetmail.com/faq/4.4.aspx
The "src=cid:whatever" part is required for the email client to recognize the <img> tag as an embedded image, while the "whatever" part is the actual Content-Id of the LinkedResource image. This will instruct the mail client to find an embedded image named "whatever" and display the contents *without* making a http:// request.

the src tells the html browser where to find the resource...in this case, cid means as part of the attachments associated to the email or embedded html file.

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!

jyoti2705 Old Hand Points: 344 More actions · Answer 4

Thanks for your Explanation. I think this will help me.

jyoti2705 Old Hand Points: 344 More actions · Answer 5

the EASIEST way to display your logos on a per-client basis is to put the logos on a web server some where, and reference the path to that image in the email.

I tried to do in this way & it worked, again Thanks.