SQL Server Native Client / ODBC Driver for SQL Server & DAO can't insert blob > 8k into varbinary(MAX) field

Question

Consider the following table:

USE [MyTestDb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MyTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FileBlob] [varbinary](max) NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Here I would like to insert a PDF file as a binary blob:

Function FileToBlob(FilePath As String) As Byte()    
    Dim File As Integer
    Dim FileBlob() As Byte
    File = FreeFile(0)
    Open FilePath For Binary Access Read As #File
    ReDim FileBlob(LOF(File) - 1)
    Get #File, , FileBlob
    Close #File

    FileToBlob = FileBlob
End Function

Private Sub Command1_Click()    
    On Error GoTo ErrHdl

    Dim db As DAO.Database
    Set db = DBEngine(0).OpenDatabase("", False, False, "ODBC;Driver={ODBC Driver 13 for SQL Server};Server=(local);Database=MyTestDb;Trusted_Connection=yes;DataTypeCompatibility=80")

    Dim rec As DAO.Recordset
    Set rec = db.OpenRecordset("MyTable", dbOpenDynaset, dbSeeChanges)

    rec.AddNew 

    rec![FileBlob] = FileToBlob("D:\TestFile.pdf")

    rec.Update

    rec.Close    
    db.Close

    Exit Sub

ErrHdl:    
    Dim i As Long
    For i = 0 To Errors.Count - 1
        Debug.Print Errors(i).Number, Errors(i).Description
    Next i
End Sub

When rec.Update runs, the error handler prints the following output:

0 [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation

3146 ODBC--call failed.

I've observed that the following changes cause the blob to be inserted successfully:

Using a file with size <= 8k
Switching to the legacy "SQL Driver" as follows: "ODBC;Driver={SQL Server};..."
Changing the data type of the blob column from varbinary(MAX) to (deprecated) image
Switching to ADO connection & recordset

However, none of these represent a real solution for me. Is there any way to succeed with the original insert command?

Debug.Print rec.Fields("FileBlob").Type prints 11, which, according to this site, resolves to dbLongBinary. — Aurora
Same result, unfortunately. I split the blob into multiple 8k byte arrays and added them one by one via AppendChunk. However the final call to rec.Update fails with the same error message. If merely one 8k byte array is appended, the update command succeeds. — Aurora

Ben Ben · Accepted Answer · 2017-05-17T15:16:08

You need to set DataTypeCompatibility=0. 80 means you are limited to Sql Server 2005 data types, which don't include MAX types.

https://docs.microsoft.com/en-us/sql/relational-databases/native-client/system-requirements-for-sql-server-native-client

OLE DB and ADO applications can use the DataTypeCompatibility connection string keyword with SQL Server Native Client to operate with older data types. When DataTypeCompatibility=80, OLE DB clients will connect using the SQL Server 2005 tabular data stream (TDS) version, rather than the TDS version. This means that for SQL Server 2008 and later data types, down-level conversion will be performed by the server, rather than by SQL Server Native Client. It also means that the features available on the connection will be limited to the SQL Server 2005 feature set.

SQL Server Native Client / ODBC Driver for SQL Server & DAO can't insert blob > 8k into varbinary(MAX) field

1 Answers