Error inserting large values into varbinary(max) on SQL Server 2008.

[GoogleCodeExporter]

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.

Original issue reported on code.google.com by timothyawiseman@gmail.com on 17 Jul 2012 at 9:54

[GoogleCodeExporter]

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.

Original comment by mkleehammer on 29 Sep 2012 at 3:06

• Changed state: Hold

[GoogleCodeExporter]

Original comment by mkleehammer on 29 Sep 2012 at 3:10

• Added labels: Driver-SQLServer

[GoogleCodeExporter]

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.

Original comment by timothyawiseman@gmail.com on 29 Sep 2012 at 6:22

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

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.

Original comment by timothyawiseman@gmail.com on 10 Oct 2012 at 10:10

[GoogleCodeExporter]

@ 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.

Original comment by gisch...@gmail.com on 31 Jul 2015 at 7:44