Pros and cons of using image data type

Can anybody let me know the pros and cons of storing
images using SQL server image data type as opposed to just
storing the image file in system file directories. I'm
more concern on the efficiency and the space used if I
store images using SQL server 2000. Anything bad that
might occur?That's a sensible question to ask ... And many out there forget when they
use image datatypes ... BTW, Iam not a against image datatype but I always
prefer in storing them in the web server itself rather than passing them
around to and forth to the SQL Server. Moreover storing large files at the
sql server is also not advisable ... But if you are storing just a small
signature image for example .. It is fine to use SQL Server ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
www.extremeexperts.com
"twk" <twengkeat@.hotmail.com> wrote in message
news:01ce01c35d84$fce7c5e0$a001280a@.phx.gbl...
> Can anybody let me know the pros and cons of storing
> images using SQL server image data type as opposed to just
> storing the image file in system file directories. I'm
> more concern on the efficiency and the space used if I
> store images using SQL server 2000. Anything bad that
> might occur?|||The Pros of storing the images in SQL Server are pretty simple: You gain
all the "ACID" properties of a database system. The images are backed up
with the data in a coordinated fashion. All "pointers" between your
structured data and the images are maintained. No "broken link" problems,
no out of sync problems, no multiple namespaces, etc. If you store the data
in the database and the images in the file system then you have multiple
backups and various ways for the database and image information to end up
out of sync. Plus, you have to manage two different security environments.
The big negative of storing images in SQL Server is performance. There are
three issues here. The first is that SQL Server breaks images up into
chunks that fit on database pages. This makes reassembling the images
slower than if they are stored, without additional internal structure, in a
file. It also makes it impossible to use the operating system's built-in
facilities to transmit a file directly from disk out over a communications
link in kernel mode. So, from the standpoint of serving the image out onto
the web it is definitely much slower. Second, images stored in SQL Server
are returned to the application via the TDS protocol and the data access
APIs. Again, these are not optimal for image processing and impose overhead
that doesn't exist with a file. Third, most applications that process
images read and write them from the file system. So, if the image is stored
in SQL Server then you have to read the image out of the server, write it to
a temporary file, then invoke the image processing software against the
temporary file. It is these performance issues that lead many people to
store the images outside the database itself.
The real suitability of storing images inside SQL Server versus in the file
system comes down to the analysis of the application itself. For example,
if you have an HR application and one of the pieces of information that you
store about an employee is the picture that is on their id card then I think
you should store that image inside the database. Why? Well, you don't
access it very often. You aren't serving it up to the web constantly. It's
always accessed in conjunction with other employee data. You need to
protect access to the images under your HR policies. The performance hit is
thus not a significant factor when compared with the application and
operational issues.
If I had a server whose primary function was to serve up images all day,
then I'd store them in the file system. Or, at least maybe I would. I
actually have a preferred architecture for this scenario that addresses both
the management and performance issues. But it cost disk space. I would
store the authoritative copies of the images in the database, and then
create copies (a cache) in the file system. I would serve up the images
from the file system, but my recovery procedures would blast the
authoritative copies from the database into the filesystem. So I get the
best of both worlds. And with 120GB disks going for less money than I
usually carry around in my wallet, the duplication hardly seems to be a
problem for most situations.
One system that runs counter to conventional wisdom is the TerraServer
(http://terraserver-usa.com/). You can get details of how it works from
http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-99-29.
Even though it serves up images all day long the images are stored in SQL
Server. This was done for a few reasons, chief among them to show that SQL
Server was capable of hosting such an application. Terraserver has been
operational since the summer of 1998, at times serving up several million
images per day.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"twk" <twengkeat@.hotmail.com> wrote in message
news:01ce01c35d84$fce7c5e0$a001280a@.phx.gbl...
> Can anybody let me know the pros and cons of storing
> images using SQL server image data type as opposed to just
> storing the image file in system file directories. I'm
> more concern on the efficiency and the space used if I
> store images using SQL server 2000. Anything bad that
> might occur?

Properly copying data of datatype Image

I have a column called "Image" in a table that stores all the user information. Image holds the data for the user's badge photo. I'm currently working on a project to move some of the data from the users table to more relevant tables. With the SQL script I wrote to copy the data into the new tables the image data appears to not have been transferred correctly. I had tried storing the image data into a variable of type varbinary(8000) before inserting it back in. Is there a certain datatype that must be used to store the data when reading from a column of data type "image" and then inserting it into another column of data type "image" without getting truncation or corruption of the data?

Can you please try the following?

HOWTO: Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/default.aspx?scid=kb;en-us;194975
HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q258038
How To Read and Write BLOB Data by Using ADO.NET with Visual Basic .NET
http://support.microsoft.com/kb/308042/EN-US

Programming report viewer at run time

I have a report that works. It has been added to a webform (dot net 2.0)
that works. It has an image control in the upper left hand of the page
header. I would like to be able to change that graphic depending on a sql
table setting as the web form loads,
Does anyone know of a good detailed example of how to do this?
I have tried this and it does NOT work:
Dim myImage1 As New Image
myImage1 = Me.ReportViewer1.FindControl("Image1")
myImage1.ImageUrl = "Images/small_info_logo.jpg"
Thanks
ChuckOn Apr 20, 9:58 pm, ChuckT <Chu...@.discussions.microsoft.com> wrote:
> I have a report that works. It has been added to a webform (dot net 2.0)
> that works. It has an image control in the upper left hand of the page
> header. I would like to be able to change that graphic depending on a sql
> table setting as the web form loads,
> Does anyone know of a good detailed example of how to do this?
> I have tried this and it does NOT work:
> Dim myImage1 As New Image
> myImage1 = Me.ReportViewer1.FindControl("Image1")
> myImage1.ImageUrl = "Images/small_info_logo.jpg"
> Thanks
> Chuck
Instead of setting the ImageUrl in the web form, set it in the report
itself. You should be able to provide an Expression value for the
ImageUrl in the report builder - just point it to a dataset (possibly
using the Iif() function if the url is not stored in the database
records).
- Tokes|||Great idea, thank you very much. Plus it is a simplier implementation
Chuck
"Tokes" wrote:
> On Apr 20, 9:58 pm, ChuckT <Chu...@.discussions.microsoft.com> wrote:
> > I have a report that works. It has been added to a webform (dot net 2.0)
> > that works. It has an image control in the upper left hand of the page
> > header. I would like to be able to change that graphic depending on a sql
> > table setting as the web form loads,
> >
> > Does anyone know of a good detailed example of how to do this?
> >
> > I have tried this and it does NOT work:
> > Dim myImage1 As New Image
> > myImage1 = Me.ReportViewer1.FindControl("Image1")
> > myImage1.ImageUrl = "Images/small_info_logo.jpg"
> >
> > Thanks
> >
> > Chuck
> Instead of setting the ImageUrl in the web form, set it in the report
> itself. You should be able to provide an Expression value for the
> ImageUrl in the report builder - just point it to a dataset (possibly
> using the Iif() function if the url is not stored in the database
> records).
> - Tokes
>|||Actually that will not work. If you use any external pointer the designer
imports the garphic at design time. I am hoping to get a run time interrface
that loads the graphic based on a table value.
"ChuckT" wrote:
> Great idea, thank you very much. Plus it is a simplier implementation
> Chuck
> "Tokes" wrote:
> > On Apr 20, 9:58 pm, ChuckT <Chu...@.discussions.microsoft.com> wrote:
> > > I have a report that works. It has been added to a webform (dot net 2.0)
> > > that works. It has an image control in the upper left hand of the page
> > > header. I would like to be able to change that graphic depending on a sql
> > > table setting as the web form loads,
> > >
> > > Does anyone know of a good detailed example of how to do this?
> > >
> > > I have tried this and it does NOT work:
> > > Dim myImage1 As New Image
> > > myImage1 = Me.ReportViewer1.FindControl("Image1")
> > > myImage1.ImageUrl = "Images/small_info_logo.jpg"
> > >
> > > Thanks
> > >
> > > Chuck
> >
> > Instead of setting the ImageUrl in the web form, set it in the report
> > itself. You should be able to provide an Expression value for the
> > ImageUrl in the report builder - just point it to a dataset (possibly
> > using the Iif() function if the url is not stored in the database
> > records).
> >
> > - Tokes
> >
> >|||On Apr 21, 11:12 am, ChuckT <Chu...@.discussions.microsoft.com> wrote:
> Actually that will not work. If you use any external pointer the designer
> imports the garphic at design time. I am hoping to get a run time interrface
> that loads the graphic based on a table value.
That shouldn't be the case if you're setting the Url to an
expression... also check that the image control is not set to
'embedded'?
- Tokes
> "ChuckT" wrote:
> > Great idea, thank you very much. Plus it is a simplier implementation
> > Chuck
> > "Tokes" wrote:
> > > On Apr 20, 9:58 pm, ChuckT <Chu...@.discussions.microsoft.com> wrote:
> > > > I have a report that works. It has been added to a webform (dot net 2.0)
> > > > that works. It has an image control in the upper left hand of the page
> > > > header. I would like to be able to change that graphic depending on a sql
> > > > table setting as the web form loads,
> > > > Does anyone know of a good detailed example of how to do this?
> > > > I have tried this and it does NOT work:
> > > > Dim myImage1 As New Image
> > > > myImage1 = Me.ReportViewer1.FindControl("Image1")
> > > > myImage1.ImageUrl = "Images/small_info_logo.jpg"
> > > > Thanks
> > > > Chuck
> > > Instead of setting the ImageUrl in the web form, set it in the report
> > > itself. You should be able to provide an Expression value for the
> > > ImageUrl in the report builder - just point it to a dataset (possibly
> > > using the Iif() function if the url is not stored in the database
> > > records).
> > > - Tokes

Programmatically chosing an image in table

So we've got a report, and some external images (i.e. sitting on the
server's HD). I don't really want to put them in the SQL 2000 database if I
don't have to (mainly because I don't know how), but I want one column of
the report to be one of 5 images, depending on the value a column in the
table. If I embed the images, I can't see a way to chose one of the images
in the table; I can just make all the column the same. Or can I just code
the HTML line manually... Have to try that, I guess. Any other ideas?
If it helps, the report is a table of calls our customers have submitted,
and the image displays a bug, dollar sign, plus sign, script page or
question mark depending on whether the call is a bug, customization, feature
request, script request, or 2nd level support question.
ClintWe have created some "dashboard" like reports that have different icons
(for data audits). Obviously this might not be the best method for
everyone, but it works for us. We have folder structure like below:
Production
Images
Audit Reports
User Reports
Manager Reports
We implement our images through relative URLs. We put the images we
need inside the Images folder (this allows for reuse). In the report,
I insert an image from a URL (When using the wizard, I use a random
image url). Once the wizard is finished, I change the Value property
to be the URL like the following =iif(condition,
""../../Images/StoplightSingleRed.gif",
"../../Images/StoplightSingleGreen.gif")
You could also just use an absolute path (we used the relative in case
we changed server names).
Hopefully this solution will work for you.
Regards,
Dan|||Maybe I wasn't as clear as I could've been. The folder structure above
is what we have implemented in the Report Manager. We then uploaded
the files to the report manager inside the image folder.|||Dan,
Worked like a hot darn! Thanks. FYI, the solution was to drop in an image,
link it to one of my existing images, just for giggles. Then I modified the
Value section on the Properties of that object to:
=Switch(Fields!PDPType.Value="Bug", "http://localhost/images/bug.gif",
Fields!PDPType.Value="Customization Request",
"http://localhost/images/customization.gif", Fields!PDPType.Value="New
Feature/Enhancement", "http://localhost/images/enhancement.gif",
Fields!PDPType.Value="Script Request", "http://localhost/images/script.gif",
Fields!PDPType.Value="Support Request", http://localhost/images/support.GIF)
I might have to tweak and tune my file locations, and I've got to re-size
all my images so they're consistent (darn graphics people...). But I didn't
know you could put a conditional value in the image Value section. That's
the piece I was missing. The documentation on a lot of this stuff seems to
be rather lacking. It seems to cover the very minimum amount.
Clint
"Dan" <daniel.lenz@.qg.com> wrote in message
news:1141321207.081912.144230@.z34g2000cwc.googlegroups.com...
> Maybe I wasn't as clear as I could've been. The folder structure above
> is what we have implemented in the Report Manager. We then uploaded
> the files to the report manager inside the image folder.
>|||Ok, so this started working. But now I've thrown sorting on the report, and
as soon as I sort, the images get lost. If I look at the source for the
page, the image tag comes up like:
<IMG class="r1" src="http://pics.10026.com/?src="/>
, whereas the pre-sorted image tag looks like:
<IMG class="r1"
SRC="/Reports/Reserved.ReportViewerWebControl.axd?ReportSession=cc3iww2i5xcx2jea3gi0ia45&ControlID=33d1f91a-7bc7-4876-a195-7210eae11061&Culture=1033&UICulture=9&ReportStack=1&OpType=ReportImage&StreamID=56"/>
All this works fine in the Preview in VS2005 (the sorting, that is). Any
ideas? I'm beginning to think I should have imported the images into SQL...
Clint
"Clint" <nobody@.nowhere.non> wrote in message
news:%23zeX2MjPGHA.3728@.tk2msftngp13.phx.gbl...
> Dan,
> Worked like a hot darn! Thanks. FYI, the solution was to drop in an
> image, link it to one of my existing images, just for giggles. Then I
> modified the Value section on the Properties of that object to:
> =Switch(Fields!PDPType.Value="Bug", "http://localhost/images/bug.gif",
> Fields!PDPType.Value="Customization Request",
> "http://localhost/images/customization.gif", Fields!PDPType.Value="New
> Feature/Enhancement", "http://localhost/images/enhancement.gif",
> Fields!PDPType.Value="Script Request",
> "http://localhost/images/script.gif", Fields!PDPType.Value="Support
> Request", http://localhost/images/support.GIF)
> I might have to tweak and tune my file locations, and I've got to re-size
> all my images so they're consistent (darn graphics people...). But I
> didn't know you could put a conditional value in the image Value section.
> That's the piece I was missing. The documentation on a lot of this stuff
> seems to be rather lacking. It seems to cover the very minimum amount.
> Clint
> "Dan" <daniel.lenz@.qg.com> wrote in message
> news:1141321207.081912.144230@.z34g2000cwc.googlegroups.com...
>> Maybe I wasn't as clear as I could've been. The folder structure above
>> is what we have implemented in the Report Manager. We then uploaded
>> the files to the report manager inside the image folder.
>|||Ahhh, sweetness happens! We happened to have a MS rep here today who
specializes in ReportServer, and I mentioned my problem. He suggested
embedding the images, changing the source to Embedded, and then changing the
Value statement to:
=Switch(Fields!PDPType.Value="Bug", "Bug",
Fields!PDPType.Value="Customization Request", "Customization",
Fields!PDPType.Value="New Feature/Enhancement", "Enhancement",
Fields!PDPType.Value="Script Request", "Script",
Fields!PDPType.Value="Support Request", "Support")
Besides working properly with the sort, it has be added benefit of
distributing the images with the report. Now if I could only get the
RowNumber function to work!
Clint
"Clint" <nobody@.nowhere.non> wrote in message
news:exU7IhwPGHA.140@.TK2MSFTNGP12.phx.gbl...
> Ok, so this started working. But now I've thrown sorting on the report,
> and as soon as I sort, the images get lost. If I look at the source for
> the page, the image tag comes up like:
> <IMG class="r1" src="http://pics.10026.com/?src="/>
> , whereas the pre-sorted image tag looks like:
> <IMG class="r1"
> src="http://pics.10026.com/?src=/Reports/Reserved.ReportViewerWebControl.axd?ReportSession=cc3iww2i5xcx2jea3gi0ia45&ControlID=33d1f91a-7bc7-4876-a195-7210eae11061&Culture=1033&UICulture=9&ReportStack=1&OpType=ReportImage&StreamID=56"/>
> All this works fine in the Preview in VS2005 (the sorting, that is). Any
> ideas? I'm beginning to think I should have imported the images into
> SQL...
> Clint
> "Clint" <nobody@.nowhere.non> wrote in message
> news:%23zeX2MjPGHA.3728@.tk2msftngp13.phx.gbl...
>> Dan,
>> Worked like a hot darn! Thanks. FYI, the solution was to drop in an
>> image, link it to one of my existing images, just for giggles. Then I
>> modified the Value section on the Properties of that object to:
>> =Switch(Fields!PDPType.Value="Bug", "http://localhost/images/bug.gif",
>> Fields!PDPType.Value="Customization Request",
>> "http://localhost/images/customization.gif", Fields!PDPType.Value="New
>> Feature/Enhancement", "http://localhost/images/enhancement.gif",
>> Fields!PDPType.Value="Script Request",
>> "http://localhost/images/script.gif", Fields!PDPType.Value="Support
>> Request", http://localhost/images/support.GIF)
>> I might have to tweak and tune my file locations, and I've got to re-size
>> all my images so they're consistent (darn graphics people...). But I
>> didn't know you could put a conditional value in the image Value section.
>> That's the piece I was missing. The documentation on a lot of this stuff
>> seems to be rather lacking. It seems to cover the very minimum amount.
>> Clint
>> "Dan" <daniel.lenz@.qg.com> wrote in message
>> news:1141321207.081912.144230@.z34g2000cwc.googlegroups.com...
>> Maybe I wasn't as clear as I could've been. The folder structure above
>> is what we have implemented in the Report Manager. We then uploaded
>> the files to the report manager inside the image folder.
>>
>

Programing: RENDER METHOD and PAGE COUNTS for HTML Rendering

We understand how to use the Render method of the RS web service. And can
successfully render reports in image and PDF formats. I am having a problem
with HTML rendering. we understand how to (and are able to) render HTML
reports as a single "section" (i.e. page) or to generate all sections in a
single call. I am not sure how to ask the RS web service to tell me the
total number of "sections" (pages) in an HTML rendered report. Can anyone
help with this? we know it must be straight forward but have not found how.
thanks.
dlrThere is no direct way of determining number of pages
returned by the render method.
But when you call the render method you can see that the
method returns streamIds.
Your first page comes as as the byte array and the
remaining pages have streamIds associated.
So the number of pages is basically : StreamIds.Length+1
>--Original Message--
>We understand how to use the Render method of the RS web
service. And can
>successfully render reports in image and PDF formats. I
am having a problem
>with HTML rendering. we understand how to (and are able
to) render HTML
>reports as a single "section" (i.e. page) or to generate
all sections in a
>single call. I am not sure how to ask the RS web service
to tell me the
>total number of "sections" (pages) in an HTML rendered
report. Can anyone
>help with this? we know it must be straight forward but
have not found how.
>thanks.
>dlr
>
>.
>|||beg to differ. When I render using and IMAGE format I get StreamIDs (and
thus a page count as StreamIDs + 1) but when I render using an HTML format
StreamIDs are always null!
(I have no image links in the report).
what saz's you now?
thanks.
dlr
"Ravi" <ravikantkv@.rediffmail.com> wrote in message
news:84c401c495d0$4572bca0$a301280a@.phx.gbl...
> There is no direct way of determining number of pages
> returned by the render method.
> But when you call the render method you can see that the
> method returns streamIds.
> Your first page comes as as the byte array and the
> remaining pages have streamIds associated.
> So the number of pages is basically : StreamIds.Length+1
> >--Original Message--
> >We understand how to use the Render method of the RS web
> service. And can
> >successfully render reports in image and PDF formats. I
> am having a problem
> >with HTML rendering. we understand how to (and are able
> to) render HTML
> >reports as a single "section" (i.e. page) or to generate
> all sections in a
> >single call. I am not sure how to ask the RS web service
> to tell me the
> >total number of "sections" (pages) in an HTML rendered
> report. Can anyone
> >help with this? we know it must be straight forward but
> have not found how.
> >
> >thanks.
> >
> >dlr
> >
> >
> >.
> >