SQL Server FileStream Data Type and Error : the remote procedure call failed. [0x800706be]

FILESTREAM

SQL Server is great for storing relational data in a highly structured format, but it has never been particularly good at storing unstructured data, such as videos, graphic files, Word documents, Excel spreadsheets, and so on. In the past, when developers wanted to use SQL Server to manage such unstructured data, developers essentially had two choices. They could store unstructured data in  VARBINARY(MAX) columns inside the database; or they could store the data outside of the database as part of the file system, and include pointers inside a column that pointed to the file’s location. This allowed an application that needed access to the file to find it by looking up the file’s location from inside a SQL Server table.

Neither of these options was a perfect solution. Storing unstructured data in VARBINARY(MAX) columns offers less than ideal performance, has a 2 GB size limit, and can dramatically increase the size of a database.

Storing unstructured data in the file system requires that the files have a unique naming system that allows hundreds, if not thousands of files to be keep track of; it requires managing folders to store the data; security is a problem and often requires using NTFS permissions to keep people from accessing the files inappropriately; it requires separate backups of the database and the files; and it doesn’t prevent problems that arise when outside files are modified or moved and the database is not updated to reflect this.

To help resolve these problems, SQL Server 2008 has introduced what is called FILESTREAM storage, which is essentially a hybrid approach that combines the best features of the previous two options.

FILESTREAM storage is implemented in SQL Server 2008 by storing VARBINARY(MAX) binary large objects (BLOBs) outside of the database and in the NTFS file system. While this sounds very similar to the older method of storing unstructured data in the file system and pointing to it from a column, it is much more sophisticated. Instead of a simple link from a column to an outside file, the SQL Server Database Engine has been integrated with the NTFS file system for optimum performance and ease of administration. For example, FILESTREAM data uses the Windows OS system cache for caching data instead of the SQL Server buffer pool. This allows SQL Server to do what it does best: manage structured data; and allows the Windows OS to do what is does best: manage large files. In addition, SQL Server handles all of the links between database columns and the files, so we don’t have to.

ERROR: 0x800706be

This FILESTREAM option is disabled by default. One has to enable it from the SQL Server Configuration Manager and that’s where I came across this problem. For some reason SQL Server Services was throwing this error. In one line I would say that the wrong answer to this problem would be to try to start the Configuration Manager in administrator mode!

Don’t know why this answer kept popping up again & again when I was trying to search for a solution to this problem. If I am correct then one opens the Configuration Manager in admin mode by default (at least that’s the case for Win7). So as far as I am concerned, opening the Configuration Manager in admin mode is not the solution.

So what’s the solution?  Well its pretty simple, just install the SQL Server 2008 SP1. I don’t know if its a generic problem or what but it seems that updating your SQL Server installs the missing components into it.

SOLUTION

Install the SQL Server 2008 R2 Service Pack 1. It will automatically get updated if your Windows Update option is enabled otherwise, you can download it from the link given above.

For more information regarding new data types of SQL Server 2008 you can visit to the following address http://www.simple-talk.com/sql/learn-sql-server/sql-server-2008-the-new-data-types/

Advertisements