Store a file & Retrive it in Sql Server

Question

Post reply

Store a file & Retrive it in Sql Server

Vimal Lohani

SSCommitted

Points: 1650
More actions
May 13, 2015 at 2:12 pm

#308119

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

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

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

programmertripatsingh SSC Journeyman Points: 82 More actions · Answer 1

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.

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 2

--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

Dan Bragg Old Hand Points: 372 More actions · Answer 3

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?

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 4

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

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

vesa.juvonen Old Hand Points: 399 More actions · Answer 6

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.

trimjib SSC Veteran Points: 234 More actions · Answer 7

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/