Store a file & Retrive it in Sql Server

  • Comments posted to this topic are about the item Store a file & Retrive it in Sql Server

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • Sir

    I am trying to use your script but i found.....

    this error......

    Msg 102, Level 15, State 1, Line 12

    Incorrect syntax near 'show advanced options'.

    Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

    The configuration option 'Ole Automation Procedures' does not exist, or it may be an advanced option.

    Msg 102, Level 15, State 1, Line 41

    Incorrect syntax near 'sp_configure'.

    Configuration option 'show advanced options' changed from 0 to 0. Run the RECONFIGURE statement to install.

  • --Configure advance options

    sp_configure 'show advanced options', 1;

    go

    reconfigure;

    go

    sp_configure 'Ole Automation Procedures', 1;

    go

    reconfigure;

    go

    execute above code first. Then use this script, modify the script according to need like path. remove above code and below code

    sp_configure 'Ole Automation Procedures', 0;

    go

    reconfigure;

    go

    sp_configure 'show advanced options', 0;

    go

    reconfigure;

    go

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • Does the file's source have to be on the server where SQL Server is installed? or, can this retrieve a file from a remote location, like a user's local computer accessed through an IIS web session?

  • It could be any where, but both place should be accessible by sql server account. It will work on a network path too or a remote location.

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • Thanks for the script. I need to look at it closer before trying it.

  • sp_configure 'Ole Automation Procedures', 0;

    go

    reconfigure;

    go

    sp_configure 'show advanced options', 0;

    go

    reconfigure;

    go

    This kind of feature disabling is dangerous, if you have other functionality on the server that relies on that,

    those will stop working.

    I would add code to check the configuration status first and at the end

    restore status whatever it was.

  • Great code but difficult to implement with security concerns.

    If available to you: Polybase (SQL 2016) or FileTable(SQL 2012)

    I have not yet the pleasure of using Polybase, but have implemented a FileTable production system currently with 20,000 folders and 100,000 files.

    FileTable: IMO, far more functionality with more or less the same effort. Think of FileTable as integrating Windows Explorer within SQL. Security is integrated. SQL sees file events as triggers.

    Some resources

    http://www.sqlservercentral.com/blogs/basits-sql-server-tips/2012/06/24/using-sql-server-2012-filetables/

    https://www.simple-talk.com/sql/database-administration/full-text-searches-on-documents-in-filetables/

Viewing 8 posts - 1 through 7 (of 7 total)

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