Feeds:
Posts
Comments

Posts Tagged ‘filetable’

In my earlier article, I have explained that creating a directory / sub directory in any file table is same like creating a directory / sub directory in windows itself. However, sometimes you need to create these directories and sub directories via T-SQL. I came across a case recently where I had to create a series of sub directories inside a directly using T-SQL.

Prerequisite :
I strongly recommend that you read the given below articles to have a clear understanding about FileTables.

  1. FileTables – Prerequisites
  2. FileTables – Data Definition Language (DDL)
  3. FileTables – Data Manipulation Language (DML)

Let me explain the solution step by step.

Step 1 :
First of all you need to create a sequence (A new object shipped with SQL Server 2012) to generate a series of IDs. However you can use any other techniques to create these series.

--This script is compatible with SQL Server 2012 and above.
--DROP SEQUENCE [dbo].[NewID]
--GO
CREATE SEQUENCE [dbo].[NewID]
 AS [bigint]
 START WITH 100000000
 INCREMENT BY 1
 CACHE
GO

Step 2 :
In this step, you need to create the given below procedure that can generate a new ID for your sub directory. Remember that filetable maintains directory and sub directory IDs in a hierarchy ID datatype. So you must get the parent folder ID (directory hierarchy ID) in order to create a child folder (sub directory). You can get further detail about hierarchy ID here. This stored procedure is self explanatory.

--This script is compatible with SQL Server 2012 and above.
--DROP PROCEDURE dbo.GetNewPathLocator
--GO
CREATE PROCEDURE dbo.GetNewPathLocator
@MainFolderID HIERARCHYID,
@SubDirectoryPath VARCHAR(MAX) OUTPUT

AS
BEGIN       

	DECLARE @FirstSeqNum sql_variant,
	        @LastSeqNum  sql_variant

     EXEC sys.sp_sequence_get_range
     @sequence_name = N'dbo.NewID'
   , @range_size = 3
   , @range_first_value = @FirstSeqNum OUTPUT
   , @range_last_value = @LastSeqNum OUTPUT 

    SELECT @SubDirectoryPath = CONCAT(COALESCE(@MainFolderID.ToString(),'/'),
	CONVERT(VARCHAR(20),@FirstSeqNum) ,'.',
	CONVERT(VARCHAR(20),Convert(BIGINT,@FirstSeqNum)+1) ,'.',
	CONVERT(VARCHAR(20),@LastSeqNum) ,'/')

END
GO

Step 3 :
Now, it is time to create a sub directory in any directory using T-SQL. I already created a directory inside a filetable as shown below.

creating sub directory in filetable

Let us create a sub directory inside that directory.

--This script is compatible with SQL Server 2012 and above.
DECLARE @MainFolderPath AS HIERARCHYID
--You must have the given below file table (dbo.DataBank)
--and directory (IT) inside file table in the existing database.
SELECT @MainFolderPath=path_locator FROM dbo.DataBank
WHERE [name]='IT'

DECLARE @SubDirectoryPath varchar(max)
EXEC dbo.GetNewPathLocator
  @MainFolderID=@MainFolderPath
, @SubDirectoryPath = @SubDirectoryPath OUTPUT 

--SELECT @SubDirectoryPath

INSERT INTO dbo.DataBank (name,path_locator,is_directory,is_archive)
VALUES ('sub directory', @SubDirectoryPath, 1, 0);
GO

Given below is the new sub directory created inside IT folder via T-SQL.

creating sub directory in filetable 2

Conclusion :
Remember, filetable keeps directory and sub directory IDs in Hierarchy ID datatype. So you must go through this concept. In addition, the whole process is self explanatory.

Let me know if you came across this situation and how you handled it.

Advertisements

Read Full Post »

File table is one of the best features shipped with SQL Server 2012, it is a special type of table which allows us to store files and folders in windows and we can easily access it through windows application & SQL Server without any customization. I have written multiple articles on file table. However, still doing research on this special table. Today, I came across an issue with filetable at the client side once they were doing some activity on it. The issue is when they tried to explore FileTable Directory in a FileTable, the option was disabled as shown below. However I can perform any activity on file table except to explore it.

Explore FileTable Directory.1.1

Now, I cannot explore this file table using explore the filetable directory option. It means that I cannot view the filetable’s files and folder using windows directory. So what is the problem and possible solution ?

Resolution:
I asked the client what activity they did with filetable but they had no clue. So, I started doing my research and finally resolved it by MSDN help. In fact the Filetable’s NON_TRANSACTED_ACCESS has been switched OFF by mistake as shown below.

Explore FileTable Directory.1.2

Wow, I found the problem that has disabled the explore FileTable directory as mentioned above. Now what is the solution ?
Given below is the script that can enable the NON_TRANSACTED_ACCESS that can result in enabling the explore FileTable directory.

--This script is compatible with SQL Server 212 and above.
 ALTER DATABASE SameplDB
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL );
GO

Once you execute the above script, try again to check if the explore FileTable Directory in the FileTable has been enabled or not. This time you will succeed as shown below.

Explore FileTable Directory.1.3

Conclusion:
Whenever, you come across such issue, you must check the NON_TRANSACTED_ACCESS of the particular database in order to fix this issue.

Read Full Post »

In my earlier articles, I wrote how to insert files inside the file table and this is more than enough if you maintain few files inside one filetable. But once a filetable is crowded you should maintain the files inside the folders to manage it properly. But how to create a folder inside a filetable ?

Note : I strongly recommend that you should read given below articles to have a clear understanding about FileTables.

  1. FileTables – Prerequisites
  2. FileTables – Data Definition Language (DDL)
  3. FileTables – Data Manipulation Language (DML)

There are two methods to create a folder inside file table. Given below are the details.

  • Using SQL Server Management Studio (SSMS)
  • Using T-SQL

Let me explain each method in detail.

  • Using SQL Server Management Studio (SSMS)

This is the easiest way to create a folder inside filetable. In order to follow this method we need to achieve it step by step.

Step 1 :
First of all you should select the respective filetable in which you need to create a folder and right click on it and select “Explore filetable directory” as shown in the image below.

create_folder_inside_filetable.1.1

Step 2 :
Once you select “Explore filetable directory”, it will open that particular filetable directory folder. You can create folder here, just like in windows, as shown below.

create_folder_inside_filetable.1.2

create_folder_inside_filetable.1.3

  • Using T-SQL

This method we usually use when we need to create a folder dynamically using T-SQL. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE SampleDB
GO
INSERT INTO dbo.CVBank
(name,is_directory) VALUES ('New folder', 1);

Read Full Post »