Advertisements

Archive

Posts Tagged ‘FileTables’

How to import/store photos with file name and other properties into SQL Server table – MSDN TSQL forum

January 22, 2016 Leave a comment

–> Question:

I was tasked to load several thousand of photos into the SQL Server. Those photos are saved in a specific folder. I want to store the file name in one column and the photo in another column. It would take month for me to enter one by one.

Is there a way to loop through a given folder and add the file name and photo into the tables using TSQL?
 

–> Answer:

If you are on SQL Server 2012 and ahead, you can use FileTables feature, which is built on top of FileStream and very easy to implement.

FileTable feature does not store files (or images in your case) in SQL Database, but in a secure Windows File-System location which is only visible via SSMS.

Check my blog post on FileTables: https://sqlwithmanoj.com/2012/02/28/sql-server-2012-a-k-a-denali-new-feature-filetables/

After implementing FileTables feature you just need to copy-paste all these images or any kind of file on this folder and you are done.

The retrieval of data is very easy and you get all the properties stored in the metadata files.
 

Ref link.


Advertisements

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.


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.


Using FullText search with FileTables in SQL Server 2012

December 21, 2012 5 comments

In my previous post sometime back I talked about FILETABLES, [link]. This is a new feature introduced in SQL Server 2012, which is built on top of FILESTREAM.

I also talked about how we can use them to store files and retrieve the information from them.

Here, in this post we will see how we can search in the files stored in FileTables using an existing feature i.e. “FullText Search”.

So, let’s check this by an example. I’ll be using the same code from my previous post to create a new Database and Filetables.

–> You will need to create a directory to allocate a location in any of your drive, lets’s create it in D: Drive, D:\SQL_Server2012\FileTables
Now execute the code below, which will create a new DataBase and a FileTable to store documents:

USE [master]
GO

-- Create a new Database with Filestream enabled:
CREATE DATABASE [newFileStreamDB]
 CONTAINMENT = NONE
 ON  PRIMARY ( 
	NAME = N'newFileStreamDB', 
	FILENAME = N'D:\SQL_Server2012\DATA\newFileStreamDB.mdf' , 
	SIZE = 5120KB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 1024KB 
	),
 FILEGROUP newFileStreamGroup CONTAINS FILESTREAM DEFAULT (
	NAME = newFileStreamGroupFiles,
	FILENAME= 'D:\SQL_Server2012\FileTables\Files', 
	MAXSIZE = UNLIMITED 
	)
 LOG ON ( 
	NAME = N'newFileStreamDB_log', 
	FILENAME = N'D:\SQL_Server2012\DATA\newFileStreamDB_log.ldf' , 
	SIZE = 1024KB , 
	MAXSIZE = 2048GB , 
	FILEGROWTH = 10% 
	)
 WITH FILESTREAM (
	NON_TRANSACTED_ACCESS = FULL,
	DIRECTORY_NAME = N'FileTables' 
	)
GO

-- Use the new Database:
USE [newFileStreamDB]
GO

-- Creating a new FileTable
CREATE TABLE [dbo].[firstFileTable] 
	AS FILETABLE ON [PRIMARY] 
	FILESTREAM_ON [newFileStreamGroup]
WITH (
	FILETABLE_DIRECTORY = N'myfirstFileTable', 
	FILETABLE_COLLATE_FILENAME = Latin1_General_CI_AI
)
GO

To check the FileTables link with Drive:
– Goto database in Object Explorer, Right click on the FileTable and click on “Explore FileTable Directory” option. This will open up a shared folder which is on the Virtual Network Name of you computer.
– Copy-Paste some documents/files in the folder above where FileTable is configured.

Check details of files maintained in the FileTable:

SELECT * FROM [firstFileTable]

–> Now we will see how we can search inside those files stored in Folder and in the FileTables:
We will be using the FullText feature of SQL Server to configure it.

-- Creating FullText Catalog:
CREATE FULLTEXT CATALOG CATG_firstFileTable AS DEFAULT;

-- Creating FullText Index on the table and column:
CREATE FULLTEXT INDEX ON dbo.firstFileTable
    (name LANGUAGE 2057,
    file_stream TYPE COLUMN file_type LANGUAGE 2057)
    KEY INDEX PK__firstFil__5A5B77D5C6F38A8E				-- REPLACE this with new PK created
    ON CATG_firstFileTable
    WITH
        CHANGE_TRACKING AUTO,
        STOPLIST = SYSTEM;

Now let’s search inside those files by using FullText Search Functions:

-- Using FREETEXT() function:
SELECT *
FROM dbo.firstFileTable
WHERE
FREETEXT (file_stream, '<Search_String>') -- Put the search string here, which is in your document/file.

-- Using CONTAINS() function:
SELECT *
FROM dbo.firstFileTable
WHERE
CONTAINS (file_stream, '<Search_String>') -- Put the search string here, which is in your document/file.
-- Final Cleanup
USE [master]
GO

DROP DATABASE [newFileStreamDB]
GO

NOTE: You also might need to check what all Document-Filters are currently supported in your SQL Server.
If not, then you can also download some filters from following [link].

-- Check existing Document-Filters:
select * from sys.fulltext_document_types

-- Load Services after installing missing Document-Filters
EXEC sp_fulltext_service @action='load_os_resources', @value=1;
EXEC sp_fulltext_service 'restart_all_fdhosts';

For more on FullText Search check following MSDN link: http://msdn.microsoft.com/en-us/library/ms142571.aspx

SQL Server 2012 (a.k.a Denali) – New feature | FileTables

February 28, 2012 7 comments

In my [previous posts] I discussed about new features of Denali, now SQL Server 2012.

Here, in this post I’ll talk about a new feature that I explored recently, when I was working with creating tables in SQL Server 2012. I observed a new item in object explorer that took my attention and created eagerness to explore it, and that new thing is FileTables.

As per MS BOL, The new FileTable feature brings support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server. FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services – including full-text search and semantic search – over unstructured data and metadata.

In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications. The FileTable feature builds on top of SQL Server FILESTREAM technology.
 

–> Let’s see how can we use this new feature by a simple example below:

USE [master]
GO

-- Create a new Database with Filestream enabled:
CREATE DATABASE [newFileStreamDB]
 CONTAINMENT = NONE
 ON  PRIMARY ( 
	NAME = N'newFileStreamDB', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER12RC0\MSSQL\DATA\newFileStreamDB.mdf' , 
	SIZE = 5120KB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 1024KB 
	),
 FILEGROUP newFileStreamGroup CONTAINS FILESTREAM DEFAULT (
	NAME = newFileStreamGroupFiles,
	FILENAME= 'D:\SQL_Server2012\FileTables\Files', 
	MAXSIZE = UNLIMITED 
	)
 LOG ON ( 
	NAME = N'newFileStreamDB_log', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER12RC0\MSSQL\DATA\newFileStreamDB_log.ldf' , 
	SIZE = 1024KB , 
	MAXSIZE = 2048GB , 
	FILEGROWTH = 10% 
	)
 WITH FILESTREAM (
	NON_TRANSACTED_ACCESS = FULL,
	DIRECTORY_NAME = N'FileTables' 
	)
GO

-- Use the new Database:
USE [newFileStreamDB]
GO

-- Creating a new FileTable
CREATE TABLE [dbo].[firstFileTable] 
	AS FILETABLE ON [PRIMARY] 
	FILESTREAM_ON [newFileStreamGroup]
WITH (
	FILETABLE_DIRECTORY = N'myfirstFileTable', 
	FILETABLE_COLLATE_FILENAME = Latin1_General_CI_AI
)
GO

 

So, what all objects it creates under this new FileTable:

1. The table is created with following predefined Columns:

2. with Indexes:

3. with Constraints:

4. It also creates a FK reference key:

 

–> Now, when checking under Object Explorer under Tables you won’t see any table (even after refreshing it). But there is a new folder named FileTables, expand it and you can see this table there, shown below:


 

–> Right click on it and select ‘Explore FileTable Directory’, it will open the folder as shown below:

Here, I’ve manually created 3 files, 1 notepad, 1 powerpoint and 1 word doc. You can also copy files from other locations and paste/drop them here. As you paste/drop files here, SQL Server internally updates the [firstFileTable] file table.
 

–> We can check the table by simply issuing SELECT statement on the table:

SELECT * FROM dbo.firstFileTable

-- Final Cleanup
DROP TABLE dbo.firstFileTable
GO

 

I liked this new feature very much as this will ease the work and reduce the overhead of maintaining files with databases.
 

In my [next post] you can check how you to use Full Text Search with files stored in FileTables.