SQL Server Performance

How to save binary file from Sql Server database to the server hard disk using SQL

Discussion in 'SQL Server 2005 General Developer Questions' started by tarekj, Jul 18, 2010.

  1. tarekj Member

    Hi,I'm Using:SELECT * FROM OPENROWSET(BULK 'G:1.jpg', SINGLE_BLOB)to save binary files from hard disk to sql server databaseNow I need an SQL code to retrieve a binary file from Sql Server database to hard disk.Also, is there a better method than using " OPENROWSET "?
    tarekj, Jul 18, 2010
    #2

  2. satya Moderator

    What was the reason behind to use OPENROWSET?
    As you are using SQL Server 2008 FILESTREAM is best option for you to manage the BLOB related data,
    BOL recommends about SINGLE_BLOB: We recommend that you import XML data only using the SINGLE_BLOB option, rather than SINGLE_CLOB and SINGLE_NCLOB, because only SINGLE_BLOB supports all Windows encoding conversions. See http://msdn.microsoft.com/en-us/library/ms190312.aspx link.


    satya, Jul 18, 2010
    #3

  3. tarekj Member

    Sorry, I know I am in the wrong forum, am using Sql server 2005, so what is the best option for me?.
    tarekj, Jul 18, 2010
    #4

  4. satya Moderator

    Thats good you confirm the version (I will move the post to relevant section).
    Your best bet is use other coding practice such as .NET to manage the data that is stored on SQL server:
    CREATE TABLE [dbo].[Files](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [fname] [nvarchar](1000) NOT NULL,
    [file] [varbinary](max) NULL
    Like the same referred here http://decipherinfosys.wordpress.co...r-varcharmax-data-type-columns-in-sql-server/ you can take help of good old WRITETEXT statments to handle such binary data.
    satya, Jul 19, 2010
    #5

Share This Page