Share via

MSSQL prefer way to transfer big file from server to client.

Vivek Mendse (vmendse) 0 Reputation points
2026-05-29T09:44:03.1433333+00:00

Hello

I have a cpp client which connects to MSSQL server 2025 using ODBC or Native client.

I need to transfer large number of files (can vary from 100 MB to 1GB there can be many files from 1 to 100)

what is best way to transfer such large amount of data from server to client ?

SQL Server Database Engine

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 134.6K Reputation points MVP Volunteer Moderator
    2026-05-29T12:28:36.1+00:00

    If the files are stored on disk, about the worst way to copy them to another machine is to use SQL Server for the task. You would have to read them into a table with BULK INSERT or OPENROWSET, and the read from those tables with your C++ application. You can improve the situation by using FILESTREAM for the target columns for these files, and then read the files with OpenSqlFilestream. But you would still be copying the files an extra time, which is a waste of time, disk and resources.

    If they are files in the file system, they should be copied without involving SQL Server. The one thing to be careful is not to copy too many in parallel, as you could consume a lot of memory in Windows, which could require SQL Server to trim its caches and therefore suffer from bad performance.

    The files are xml and are stored on disk. In test environment we just copy them manually but can't do same in productions as we may not have server file system access directly.

    So that is the issue you need to fix. The access to the file system, that is. Anything else is just nuts.

    There is one more question that keeps nagging me: What are these large files doing on the SQL Server machine in the first place?

    Was this answer helpful?

  2. AI answer

    2026-05-29T09:44:27.2733333+00:00

    For transferring large files from SQL Server to a C++ client over ODBC or Native Client, use the FILESTREAM/LOB streaming patterns rather than loading the entire value into memory at once.

    Key options from the provided material:

    1. FILESTREAM with ODBC (recommended pattern for large varbinary(max))
      • Store the file data in a varbinary(max) FILESTREAM column.
      • On the client side (ODBC):
        • Bind the output column as unbound (do not use SQLBindCol for the FILESTREAM column).
        • Call SQLGetData repeatedly to fetch the data in chunks until all bytes are read.
      • For sending data larger than 2 GB to the server, bind parameters with SQLBindParameter using:
        • ColumnSize = SQL_SS_LENGTH_UNLIMITED for the FILESTREAM/LOB column.
        • StrLen_or_IndPtr = SQL_DATA_AT_EXEC.
        • Then stream the data with SQLParamData + SQLPutData in chunks.
      • This approach avoids loading the entire file into memory and is explicitly designed for large FILESTREAM values.
    2. FILESTREAM with ODBC sample
      • The sample “Send and Receive Data Incrementally with FILESTREAM (ODBC)” demonstrates exactly this pattern: using SQLPutData and SQLGetData to send and receive FILESTREAM data incrementally between client and server.
      • Follow that pattern to implement chunked transfer for each file (100 MB–1 GB) from SQL Server to the C++ client.
    3. FILESTREAM with OLE DB / Native Client (if using OLE DB instead of ODBC)
      • For OLE DB, FILESTREAM and other large values can be streamed using ISequentialStream bindings:
        • Bind a parameter or column to ISequentialStream.
        • Read or write the data in chunks through the ISequentialStream interface.
      • There are specific samples:
        • “Send Data to FILESTREAM in SQL Server Native Client – ISequentialStream Bound to ICommandText” (for sending large data to a FILESTREAM column).
        • “Send Data to a FILESTREAM Column Using IRowsetFastUpload (OLE DB)” (for high‑throughput bulk-style upload).
      • For downloads, use ISequentialStream on the consumer side to read the FILESTREAM column incrementally.
    4. Large CLR UDTs (if applicable)
      • If large CLR UDTs are used instead of plain FILESTREAM, they are treated like LOBs:
        • ODBC: send values in pieces as data-at-execution parameters via SQLPutData.
        • OLE DB: stream via ISequentialStream.
      • The same chunked-transfer principles apply.
    5. Bulk copy (for server-to-file, not directly server-to-client)
      • SQL Server Native Client bulk copy (IRowsetFastLoad, bcp-style operations) is optimized for moving large volumes of data between SQL Server and OS files.
      • This is best when exporting/importing to files on disk, not for interactive per-client streaming, but can be combined with an application layer that then serves those files to clients.

    Summary for the scenario:

    • Store files as varbinary(max) FILESTREAM in SQL Server.
    • For a C++ ODBC client, use SQLGetData in a loop to stream each FILESTREAM column to the client in manageable buffers.
    • For uploads or if using OLE DB/Native Client, use SQL_DATA_AT_EXEC + SQLPutData (ODBC) or ISequentialStream bindings (OLE DB) to stream data.
    • These patterns are the preferred and supported way to handle 100 MB–1 GB files and many such files without excessive memory usage.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.