locked
Bulk Loading an Image from URL RRS feed

  • Question

  • I know I can load an image from file system to a binary field in SQL Server :

    CREATE TABLE Foobar (
    image_data VARBINARY(MAX));
    
    INSERT INTO Foobar (image_data)
    SELECT image_data
    FROM OPENROWSET(
    BULK N'C:\image.jpg',
    SINGLE_BLOB) AS ImageSource(image_data);
    

    But I need to know if I can load image from a URL like 'http://www.mysite.com/test.jpg' .

    I tried it with no success. Any ideas?

    Monday, September 13, 2010 10:26 AM

Answers

  • You could read a CLR stored procedure that goes and retrieves the image, but I would definitely not recommend that. Your DBA may not appreciate if you have SQL Server to wait for response from external web servers.

    Have a client program to download the image.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Marked as answer by Kalman Toth Friday, September 17, 2010 7:51 AM
    Monday, September 13, 2010 10:45 AM

All replies

  • Hmm, never tried, have  you got the error? I do not think it is possible .....
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, September 13, 2010 10:38 AM
    Answerer
  • You could read a CLR stored procedure that goes and retrieves the image, but I would definitely not recommend that. Your DBA may not appreciate if you have SQL Server to wait for response from external web servers.

    Have a client program to download the image.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Marked as answer by Kalman Toth Friday, September 17, 2010 7:51 AM
    Monday, September 13, 2010 10:45 AM
  • If you're doing this from ASP.NET application, then the techniques of saving and restoring images (using webservice, for example) are well known and can be found in many blogs.

    Let me know if you need a specific pointer, as I remember discussing this problem several times in ASP.NET forums.

    In any case, I think, you'll need to have the file on disk first.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Kalman Toth Friday, September 17, 2010 7:52 AM
    Monday, September 13, 2010 1:12 PM