Freigeben über

Write method of OpenSQLFileStream is generating Event 2000 Errors on SQL Server

  • Artikel

One of my customers was experiencing a lot of event 2000 errors on the SQL Server Machine. These were happening every time when they call the Write method to a SQLfilestream from an XP client. The issue occurs only on XP Client OS and not on any other Operating System.

Below is the OpenSQLFileStreamAPI Call to write a BLOB into a SQL Server database.

SafeFileHandle handle = OpenSqlFilestream(

      path,

      DESIRED_ACCESS_WRITE,

      SQL_FILESTREAM_OPEN_NO_FLAGS,

      txCtx,

      (UInt32)txCtx.Length,

      0);

The functionality of the application is not affected but we still see below error message being logged in the windows system event logs.

Log Name: System

Source: srv

Date: 4/10/2010 2:32:12 AM

Event ID: 2000

Task Category: None

Level: Error

Keywords: Classic

User: N/A

Computer: <MachineName>.microsoft.com

Description:

The server's call to a system service failed unexpectedly.

 

Whenever we call OpenSQLFileStream to write a BLOB to the SQL Server, Internally it calls CreateFile method which is a windows API Call. The OpenSqlFilestream API obtains a Win32 compatible file handle for a FILESTREAM binary large object (BLOB) that is stored in the file system. The handle can be passed to any of the following Win32 APIs: ReadFile, WriteFile, TransmitFile, SetFilePointer, SetEndOfFile, or FlushFileBuffers.

One way to prevent this error from occurring on an XP client is to change the FLAGS passed to OpenSQLFileStream from SQL_FILESTREAM_OPEN_NO_FLAGS to SQL_FILESTREAM_OPEN_FLAG_NO_BUFFERING.

SafeFileHandle handle = OpenSqlFilestream(

                         path,

                         DESIRED_ACCESS_WRITE,

                         SQL_FILESTREAM_OPEN_FLAG_NO_BUFFERING,

                         txCtx,

                         (UInt32)txCtx.Length,

                         0);

When opening or creating a file with the CreateFile function, the FILE_FLAG_NO_BUFFERING flag can be specified to disable system caching of data being read from or written to the file.

This flag eliminates all read-ahead file buffering and disk caching, so that all reads are guaranteed to come from the file and not from any system buffer or disk cache.

So the issue has nothing to do with SQL Server specifically. It is normally recommended is to use SQL_FILESTREAM_OPEN_FLAG_NO_BUFFERING flag with OpenSQLFileStream function.

Author : Praveen(MSFT), SQL Developer Technical Lead , Microsoft

Reviewed by : Tejas(MSFT), SQL Escalation Services, Microsoft

Comments

  • Anonymous
    June 10, 2015
    Thank you! Waste lot of time, to try explain what happen. Very helpful.