locked
Insert an image into an image data type in SQL Azure RRS feed

  • Question

  • I need to insert an image into an image data type in SQL Azure.  In SQL Server, I use the following statement.

     

    UPDATE [WHData].[dbo].[MyTable]

       SET MyImage =

       (SELECT * FROM OPENROWSET  

          (BULK 'D:\code\images\myImage.jpg',SINGLE_CLOB) as MyImage)

     WHERE CHARacterid=5

     

    When I do this in SQL Azure, I get the following:

     

    Msg 40517, Level 16, State 1, Line 9

    Keyword or statement option 'single_clob' is not supported in this version of SQL Server.

     

    How do I insert an Image?

     

    Thank you

    Wednesday, December 22, 2010 6:54 PM

Answers

All replies

  • You can't really do this in Azure for a few reasons. First, OPENROWSET is part of the unsupported functions (http://msdn.microsoft.com/en-us/library/ee336248.aspx#rowset). Second, you do not have access to your local drives - so you can't run a statement in SSMS that would upload a document as far as I know.

    But if you change your approach, you may be able to get something working. In one of my projects I had to store PDF documents. I created a column of type varbinary and used .NET code to insert the PDF documents in the database using a regular INSERT statement.


    Herve Roggero Blue Syntax - Azure Consulting Services Co-Author: Pro SQL Azure
    Thursday, December 23, 2010 1:57 PM
  • Hi,

    Could you try to use local database as staging database, import the image to the staging and then load it to the SQL Azure?

     

    You can also use SSIS to achieve that, create a transform ‘Import Column’ in Data Flow. More details, you can refer to:

    http://netindonesia.net/blogs/kasim.wirama/archive/2007/09/16/Importing-Image-into-SQL-Server-with-SSIS.aspx

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    Friday, December 24, 2010 4:50 AM
  • Raymond's solution works if you want do it by T-SQL. And you can try SQL Azure DATA Sync CTP2 (coming soon) to sync your data between on-promise database and SQL Azure in the future.
    • Marked as answer by Raymond-Lee Friday, December 31, 2010 10:36 AM
    Friday, December 24, 2010 7:31 AM