How to fix filestream?

Question

I have SQL Server 2008 R2, when I try to access any value stored in filestream I get the following error:

Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Also the following error thrown in the SQL ERRORLOG file :

SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

I read about that exception and Microsoft has KB for it (KB972936), but it also didn't work after I installed its hotfix.

If I SELECT the count of the records in that table using :

SELECT COUNT(1) FROM [Table_Name]

I get the correct result.

Here are some details for the database files and filegroups. The database has 2 files the "Row Data" file and the "Log" file where it should also contains the "Filestream Data" item.

The database has the following filegroups :

• Rows : PRIMARY with 1 File
• Filestream : [MyFileName] with 0 Files !

Here is a snapshots for the DB properties page

And here is the full SQL ERRORLOG file.

Answer 1

The error seems to point at the SQL Server Service account not being able to access the filestream data. Try to change the SQL Server Service account using the SQL Server Configuration Manager (NOT the Windows Services manager!) to one of the build in accounts. Then change it right back to the original account (you will need to know the password here!).

That should fix any access right issues. However it will cause two service restarts.