Post reply

Putting image files in sql server 2005

  • October 6, 2007 at 2:28 am

    #175789

    Hi all,

    I posted to this forum recently, and I have one more question.

    I worked for 3 years in a church library, and used MS ACCESS to maintine my data of books, members, loans etc. In one db I made a field called MemberImages (datatype was OLE) and inserted some images of members).

    Now I am working in a simple publishing house, but with more complexed data, so I turned to sql server 2005 express ed. I nwant to put some photos of our employess in SQL database. I heard that I can use image data type for this, but I don't know how.

    Please help me out

  • October 6, 2007 at 7:55 pm

    #739831

    It's probably a good thing you don't know how because my recommendation is not to store images in SQL... store them in files and store the file names in SQL. Makes life real easy when you want to change the content of a file...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 7, 2007 at 1:19 am

    #739843

    At first thanks for reply.

    Let's suppose that I have file named Tex1.doc with all images that I need in it. How to put that file in SQL? Through Visual Studio, or?

    Sincerelly,

    Ivan Delic

  • October 8, 2007 at 5:25 pm

    #740198

    You'd need to split out the images into separate files...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 8, 2007 at 10:15 pm

    #740233

    You'll usually find people split on this issue. I tend to agree with Jeff, but lots of people like having the images/BLOBs in SQL Server. Everything is then in once nice backup package, easy to manage.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • October 9, 2007 at 12:53 am

    #740252

    Thanks for the quick replies.

    In MSAccess that I used, I imported images files from local hard. Images files are called, for example:

    Image01.jpg, Image02.jpg, Image03.jpg.

    As you can see, these are three separate files.

    I found some VS Project for importing images in Access database, and when I deployed a project I added five images in db. Then I used upsize wizard to export that db to my SQL Server, and on server I got db with table dbo.images, and five records on it. Data type was image, but in records it stands what got me confused. I saw something like that in Adventure Works Samples. My questions are:

    Is it possible to view those images after deploying sql db into VS? And is it possible to view images in SQL Server?

  • October 9, 2007 at 7:39 am

    #740351

    Dunno about your first question but I'm pretty sure that the answer to your second question of can you "view images in SQL Server" is an emphatic "NO".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 9, 2007 at 3:51 pm

    #740566

    Using a visual tool (allowing for rendering) like VS would allow you to view data (including images) stored in SQL. That being said - in a lot of cases - it's really NOT a great idea to store images in SQL. It tends to be a LOT more efficient to store link info in SQL, and organize them on the OS (preferrably on a different server or drive from SQL anyway).

    Your SQL server in most cases would be busy enough not to be the best tool to store and serve up little bobby's MP3 collection. I'm being a little facetious here - but you could really slow your other processing down with that nonsense. Have SQL server store data it can do something with - have the OS hold the big files/images/etc....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • October 10, 2007 at 2:42 am

    #740666

    Thanks a lot, you gave me a solution for my problem.

  • October 11, 2007 at 10:29 am

    #741274

    One other consideration is that Ivan said one critical word: Express.

    I'd be concerend about the amount of space taken up by the blobs. Ivan you said photos of the employee's and so you might not have that many. Then someone will want you to store photos of the products and then scanned images of sales receipts. It's a slippery slope.

    ATBCharles Kincaid

  • October 12, 2007 at 2:55 am

    #741469

    Download the linked research paper, as it covers your situation. In a few words, it is saying any file under 256 KB is handeled more efficiently in SQL than in the file system, while anything over 1MB is handled more efficiently in the file system, and how to work out what is best for intermediate-sized files.

    The paper makes clear it is not covering issues such as synchronising the file system with SQL meta data, or backup and restore issues (such as the jagged edge on recovery) that might make SQL storage more manageble.

    http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45

    (Thanks go to Simon Sabin for making me aware of this paper.)

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • October 13, 2007 at 1:31 am

    #741779

    Thanks and really thanks a lot

Viewing 12 posts - 1 through 11 (of 11 total)

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