Issue 277: Error inserting large values into varbinary(max) on SQL Server 2008.
Status:  Hold
Owner: ----
Reported by timothyawiseman@gmail.com, Jul 17, 2012
What steps will reproduce the problem?

When I try to insert a large value into a varbinary(max) column on SQL Server 2008 it is producing an error "The text, ntext, or image pointer value conflicts with the column name specified. (7125)"  Based on this MSDN article http://msdn.microsoft.com/en-us/library/ms130896.aspx this problem seems to be tied to the data bindings that the ODBC driver is invoking.

Here is a sample DDL:
use TestDb 
Go

create table blobTest (
	id int identity primary key,
	dataDesc varchar(200),
	imageData varbinary(max)
	)

And sample code that generates the error:
import pyodbc, pickle
from PIL import Image
from StringIO import StringIO

#Create the SQL connection
sqlConnStr = ('DRIVER={SQL Server};Server=(local);Database=TestDb;'+
            'Trusted_Connection=YES')

sqlConn = pyodbc.connect(sqlConnStr)

#Create a image to store and prepare it for starge
img = Image.new('RGB', #mode
                (1024, 768), #size
                (0, 0, 255) #color, this is dark blue
                )
imgBuffer = StringIO()
img.save(imgBuffer, "BMP")
img.save('testblue.bmp')

#Store the image in the database
curs = sqlConn.cursor()
sql = """
    insert into blobTest (dataDesc, imageData) 
    values (?, ?)"""
curs.execute(sql, ('Blue Image', pyodbc.Binary(imgBuffer.getvalue())))
sqlConn.commit()

This code works quite nicely if the image is made smaller before trying to insert it.



What is the expected output? What do you see instead?

Receiving a "The text, ntext, or image pointer value conflicts with the column name specified. (7125)"   error.

What version of the product are you using? On what operating system?
This is version 3.0.2 on Python 2.7.1 running on Windows 7


Please provide any additional information below.


Sep 29, 2012
Project Member #1 mkleehammer
Exactly how big, in bytes, is the value that fails?

I've updated the SQL Server unittests (tests2/sqlservertests) to include 'max' tests for varchar, nvarchar, and binary and to include 5MB and 50MB inserts, which work.

Status: Hold
Sep 29, 2012
Project Member #2 mkleehammer
(No comment was entered for this change.)
Labels: Driver-SQLServer
Sep 29, 2012
#3 timothyawiseman@gmail.com
The imbBuffer.len property returns 2359350 which matches the 2,305KB size of the file if I save it as a file.  I also receive the error at 640x480 (921654 bytes)

If I reduce the size of the image to 320x420 which takes 230454 bytes the script executes perfectly and SQL Server confirms that it stores the varbinary data.

I also tested this on a completely separate machine with SQL Server 2012 Express with the same results.
Oct 10, 2012
#5 timothyawiseman@gmail.com
And this is where I realize how I have been overlooking something obvious and apologize for wasting your time.  I work with numerous machines with several versions of SQL Server, and it never occurred to me while I was troubleshooting this to check that I was using the right driver for the version I was using.  I failed to notice this even though I made certain to test this on more than one machine and more than one install of SQL Server 2008 (and later 2012) before filing the bug report.

In short, when I switch to DRIVER={SQL Server Native Client 10.0} instead of DRIVER={SQL Server} in the connection string, the error vanishes and works properly.

I finally figured this out after playing with and then starting to actually read through the SQL Server Unittests you provided.  

Thanks again.
Jul 31, 2015
#6 gisch...@gmail.com
@ Timothy A Wiseman: I experienced a very similar issue but with inserting Geometry data into a varchar(max) field (in a tmp table so it could be inserted into a Geometry data type field). Using the Sql Server driver, it would throw the error you described but once I switched to the Native Client 11.0 driver, it was successful. Thanks for posting this.