Prev: RE
Next: Strip off time
From: CraigHB on 4 Nov 2009 09:45
Question
In SQL Server 2005, I have a table with images (data type: image). Using
only SQL, how do I save an image as a file (on the same server that SQL
Server is running). If I have to, I'll use SQL CLR, but I want to avoid that
if possible.

Background
I want a SQL Server job to run no a schedule that calls a proc that will
send emails with embedded images using SQL Server Database Mail like this:

exec msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = 'bob(a)hotmail.com',
@subject = 'hello',
@file_attachments = 'C:\MyLogo.gif',
@body=N'<p>Image Test</p><img src="MyLogo.gif" /><p>See image there?</p>',
@body_format = 'HTML';

That SQL works, but I need to get the image saved as a file first. If I can
get the image directly on the email without saving it as a file, that's fine,
but it needs to be embedded on the email and I only want to use SQL.
From: Uri Dimant on 4 Nov 2009 10:06
CraigHB
I think you need to read the binary file first and then save it as a file,
I mean you will have to use .NET.

BTW , why are you using IMAGE datatype since there is VARBINARY(MAX)?


"CraigHB" <craighb(a)nospam.nospam> wrote in message
news:2AA25304-03FD-4DEF-817A-8D2EDF44F33C(a)microsoft.com...
> Question
> In SQL Server 2005, I have a table with images (data type: image). Using
> only SQL, how do I save an image as a file (on the same server that SQL
> Server is running). If I have to, I'll use SQL CLR, but I want to avoid
> that
> if possible.
>
> Background
> I want a SQL Server job to run no a schedule that calls a proc that will
> send emails with embedded images using SQL Server Database Mail like this:
>
> exec msdb.dbo.sp_send_dbmail
> @profile_name = 'MyProfile',
> @recipients = 'bob(a)hotmail.com',
> @subject = 'hello',
> @file_attachments = 'C:\MyLogo.gif',
> @body=N'<p>Image Test</p><img src="MyLogo.gif" /><p>See image there?</p>',
> @body_format = 'HTML';
>
> That SQL works, but I need to get the image saved as a file first. If I
> can
> get the image directly on the email without saving it as a file, that's
> fine,
> but it needs to be embedded on the email and I only want to use SQL.


From: CraigHB on 5 Nov 2009 04:38
I'm using IMAGE, because that is how the table was originally created -- but
it can be changed. Would VARBINARY(MAX) be a better data type for storing
images (jpg, gif, tiff etc)


"Uri Dimant" wrote:

> CraigHB
> I think you need to read the binary file first and then save it as a file,
> I mean you will have to use .NET.
>
> BTW , why are you using IMAGE datatype since there is VARBINARY(MAX)?
>
>
> "CraigHB" <craighb(a)nospam.nospam> wrote in message
> news:2AA25304-03FD-4DEF-817A-8D2EDF44F33C(a)microsoft.com...
> > Question
> > In SQL Server 2005, I have a table with images (data type: image). Using
> > only SQL, how do I save an image as a file (on the same server that SQL
> > Server is running). If I have to, I'll use SQL CLR, but I want to avoid
> > that
> > if possible.
> >
> > Background
> > I want a SQL Server job to run no a schedule that calls a proc that will
> > send emails with embedded images using SQL Server Database Mail like this:
> >
> > exec msdb.dbo.sp_send_dbmail
> > @profile_name = 'MyProfile',
> > @recipients = 'bob(a)hotmail.com',
> > @subject = 'hello',
> > @file_attachments = 'C:\MyLogo.gif',
> > @body=N'<p>Image Test</p><img src="MyLogo.gif" /><p>See image there?</p>',
> > @body_format = 'HTML';
> >
> > That SQL works, but I need to get the image saved as a file first. If I
> > can
> > get the image directly on the email without saving it as a file, that's
> > fine,
> > but it needs to be embedded on the email and I only want to use SQL.
>
>
> .
>
From: Uri Dimant on 5 Nov 2009 04:48
Yes, that was recommended by MS

"CraigHB" <craighb(a)nospam.nospam> wrote in message
news:BCCECDBA-ABCA-4F80-8121-7A7ECB3AC271(a)microsoft.com...
> I'm using IMAGE, because that is how the table was originally created --
> but
> it can be changed. Would VARBINARY(MAX) be a better data type for storing
> images (jpg, gif, tiff etc)
>
>
> "Uri Dimant" wrote:
>
>> CraigHB
>> I think you need to read the binary file first and then save it as a
>> file,
>> I mean you will have to use .NET.
>>
>> BTW , why are you using IMAGE datatype since there is VARBINARY(MAX)?
>>
>>
>> "CraigHB" <craighb(a)nospam.nospam> wrote in message
>> news:2AA25304-03FD-4DEF-817A-8D2EDF44F33C(a)microsoft.com...
>> > Question
>> > In SQL Server 2005, I have a table with images (data type: image).
>> > Using
>> > only SQL, how do I save an image as a file (on the same server that SQL
>> > Server is running). If I have to, I'll use SQL CLR, but I want to avoid
>> > that
>> > if possible.
>> >
>> > Background
>> > I want a SQL Server job to run no a schedule that calls a proc that
>> > will
>> > send emails with embedded images using SQL Server Database Mail like
>> > this:
>> >
>> > exec msdb.dbo.sp_send_dbmail
>> > @profile_name = 'MyProfile',
>> > @recipients = 'bob(a)hotmail.com',
>> > @subject = 'hello',
>> > @file_attachments = 'C:\MyLogo.gif',
>> > @body=N'<p>Image Test</p><img src="MyLogo.gif" /><p>See image
>> > there?</p>',
>> > @body_format = 'HTML';
>> >
>> > That SQL works, but I need to get the image saved as a file first. If I
>> > can
>> > get the image directly on the email without saving it as a file, that's
>> > fine,
>> > but it needs to be embedded on the email and I only want to use SQL.
>>
>>
>> .
>>


From: Bob on 10 Nov 2009 10:06
You can use SSIS Export Column to do this. A better option than CLR?

Obvioulsy not pure T-SQL but within the realms of vanilla SQL Server, rather
than custom code. An agent job could also run the package; you could even
start the job from SQL using sp_start_job?

Start here:
http://msdn.microsoft.com/en-us/library/ms139818.aspx

HTH
wBob

"Uri Dimant" wrote:

> CraigHB
> I think you need to read the binary file first and then save it as a file,
> I mean you will have to use .NET.
>
> BTW , why are you using IMAGE datatype since there is VARBINARY(MAX)?
>
>
> "CraigHB" <craighb(a)nospam.nospam> wrote in message
> news:2AA25304-03FD-4DEF-817A-8D2EDF44F33C(a)microsoft.com...
> > Question
> > In SQL Server 2005, I have a table with images (data type: image). Using
> > only SQL, how do I save an image as a file (on the same server that SQL
> > Server is running). If I have to, I'll use SQL CLR, but I want to avoid
> > that
> > if possible.
> >
> > Background
> > I want a SQL Server job to run no a schedule that calls a proc that will
> > send emails with embedded images using SQL Server Database Mail like this:
> >
> > exec msdb.dbo.sp_send_dbmail
> > @profile_name = 'MyProfile',
> > @recipients = 'bob(a)hotmail.com',
> > @subject = 'hello',
> > @file_attachments = 'C:\MyLogo.gif',
> > @body=N'<p>Image Test</p><img src="MyLogo.gif" /><p>See image there?</p>',
> > @body_format = 'HTML';
> >
> > That SQL works, but I need to get the image saved as a file first. If I
> > can
> > get the image directly on the email without saving it as a file, that's
> > fine,
> > but it needs to be embedded on the email and I only want to use SQL.
>
>
> .
>
 | 
Pages: 1
Prev: RE
Next: Strip off time