Feeds:
Posts
Comments

Posts Tagged ‘NON_TRANSACTED_ACCESS’

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.

Advertisements

Read Full Post »