locked
varbinary data RRS feed

  • Question

  • Hello

    I have a bunch of images I need to put in the database and then retrieve later using a Script task in SSIS.
    Is the only way to save the images to the database in the application? Is there anyway to do it using t-sql?

    Thank you
    AE, MCTS
    Wednesday, July 22, 2009 6:21 PM

Answers

  • Use the FOR EACH container and make the

    INSERT INTO myTable(Document) SELECT * FROM OPENROWSET(BULK N'C:\Image1.jpg', SINGLE_BLOB)

    dynamic sql.

    Then execute the sql string.

    • Marked as answer by Abdshall Wednesday, July 29, 2009 10:20 PM
    Wednesday, July 29, 2009 9:56 PM

All replies

  • hi,
    you can use the SSIS to dump the images into database itself. let me know your problem.

    thanks-
    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Wednesday, July 22, 2009 6:24 PM
  • Which task would I use? These images are jpeg saved on one of our network drives.


    AE, MCTS
    Wednesday, July 22, 2009 6:26 PM
  • hi,
    I never tried this case in SSIS. let me do some work around it. mean while please check the following link to create Stored procedure for it.  http://www.mssqlcity.com/Articles/KnowHow/Textcopy.htm

    Thanks- 
    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Wednesday, July 22, 2009 6:35 PM
  • Hello Peso

    What if I have 50 images?

    Thank you
    AE, MCTS
    Wednesday, July 29, 2009 9:52 PM
  • FOR EACH container?? Are you talking about SSIS?
    AE, MCTS
    Wednesday, July 29, 2009 9:57 PM
  • I started testing on one file using SSMS, and I got this error.

    A correlation name must be specified for the bulk rowset in the from clause.

    Here is the code I'm using

    CREATE TABLE myTable(id int identity(1,1),
    					Document varbinary(max))
    
     INSERT INTO myTable(Document)
     SELECT * FROM OPENROWSET(BULK 'C:\Image1.jpg', SINGLE_BLOB) 



    AE, MCTS
    Wednesday, July 29, 2009 10:07 PM
  • I added as after the bracket, that did it, but now I have this error

    Cannot bulk load. The file "C:\Image1.jpg" does not exist.


    AE, MCTS
    Wednesday, July 29, 2009 10:09 PM
  • Haha, just realized that and tested a txt file on the server and it worked fine.
    I'll try with the image

    THANK YOU
    AE, MCTS
    Wednesday, July 29, 2009 10:14 PM
  • Just one more thing to add, here is what I used and it worked

    CREATE TABLE myTable(id int identity(1,1),
    		Document varbinary(max))
    
     INSERT INTO myTable(Document)
     SELECT * FROM OPENROWSET(BULK 'C:\Image1.jpg', SINGLE_BLOB) as ImageLoad

    AE, MCTS
    Wednesday, July 29, 2009 10:20 PM