Archive

Posts Tagged ‘Full-Text Search’

Store file data to SQL without streaming on server – MSDN TSQL forum

October 14, 2015 1 comment

–> Question:

I need to store file(s) to SQL without streaming / reading at Server. I have created a Web API with AngularJS and SQL.

e.g.

var fileType = httpRequest.Files[file].ContentType;
var fileStrm = httpRequest.Files[file].InputStream;
var fileSize = httpRequest.Files[file].ContentLength;
byte[] fileRcrd = new byte[fileSize];
var file_Name = Path.GetFileName(filePath);
fileStrm.Read(fileRcrd, 0, fileSize);

Is it possible to send file data to SQL (in bytes) without streaming / reading at server?

I don’t want to put a load on server for large files. just read the data and send them to SQL where SQL will do the streaming and store data as varbinary.
 

–> Answer:

Store the file in File System via FileTable feature that uses filestream out of the box.

Check this blog on how to setup and use FileTables starting SQL Server 2012.

You can also setup Full Text Search over these files here.
 

Ref link.

Advertisement
Categories: SQL Server Questions Tags: , , ,

Table to store MS Word docs, only be accessible via SQL Queries – MSDN TSQL forum

April 15, 2015 Leave a comment

–> Question:

In Microsoft SQL Server 2012 database, I want to create a table to store Microsoft Word documents.

Documents must only be accessible via Transact-SQL queries.

How to do this?

 

–> My Answer:

As you are in SQL Server 2012 you can try using Filetables, a new feature introduced with this version. Check how to implement this in my blog post.

This features gives you an abstraction layer to store your files in File System but cannot directly navigate to the files. You will have to use SSMS to open the folders to view, add, or update the files.

Do check this link also on how to setup & do a Full Text Search on those Documents stored in the File System, link.
 

–> Another Answer by Eric:

Create a file C:\test.docx in the Server where SQL Server is hosted.

CREATE TABLE dbo.FilesTbl
(
name VARCHAR(99),
extension VARCHAR(99),
content IMAGE
)

--insert the word document into table
INSERT INTO FilesTbl 
SELECT 'test', 'docx', BULKCOLUMN 
FROM OPENROWSET (BULK N'C:\test\test.docx', SINGLE_BLOB) a;

 

Ref Link.

Categories: SQL Server Questions Tags: , , , ,