Syntax for INSERT-ing VARBINARY(MAX) values

KamenG · #1 (**permalink**) 02-17-10, 11:08

Hello again, everyone,

I haven't posted here in a while, meaning I haven't had to do much SQL programming lately. So, I'm quite rusty and might be asking silly questions again but that's what forums are for, right? Plus, I did search the Internet and this forum (reasonably).

Here's the question. I am trying to write some relatively large (~MB) binaries to a database table (SQL 2005, for now) from a C# program but (for now) not using ADO.Net for much more than executing SQL statements as performance is not important for this operation and I don't want to take the time to optimize anything at this point. So, I just want to do something like:

CREATE TABLE GVProgramsTable (ProgramID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, Name VARCHAR (128), Description VARCHAR (128), Program VARBINARY(MAX))

and then:

INSERT INTO GVProgramsTable (ProgramID, Name, Description, Program) VALUES ('ProgID', 'ProgName", 'ProgDescription', '

')

Obviously, SQL is not going to let you write a

, so I want to know what to put in there? I know it doesn't matter but I happen to be keeping the data in a MemoryStream object, which can be converted to a byte array. How do I represent that? A long hexadecimal representation of all the data as one large number, like so: '0x0001000000FFFFFFFF01000000000000000C020000004A4 ...'? If that is millions of characters long, wouldn't that cause a problem? After trying that with only 31KB of data, I get the "Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query." error. What am I doing wrong? Thanks in advance for your replies.

Kamen

MCrowley · #2 (**permalink**) 02-17-10, 11:15

SQL Server is treating your binary stream as a string, because of the quotes. Take off the quotes, and you may have something:

Code:

create table test1 
(col1 int,
 col2 varbinary (max))

insert into test1 values (1, 0x123) -- works
insert into test1 values (2, '0x123') -- does not work

KamenG · #3 (**permalink**) 02-17-10, 11:31

Thank you very much! That did it.
Kamen

KamenG · #4 (**permalink**) 02-17-11, 17:45

Me again, a year later!

This piece of code had been working for a long time and now it's giving me fits again. I am getting the following error:

Code:

The identifier that starts with 'x0001000000FFFFFFFF01000000000000000C020000004A4756436F6D6D4966634D67642C2056657273696F6E3D312E302E343036352E32373036352C2043756' is too long. Maximum length is 128.
Incorrect syntax near 'x0001000000FFFFFFFF01000000000000000C020000004A4756436F6D6D4966634D67642C2056657273696F6E3D312E302E343036352E32373036352C2043756'.

And this on the execution of two successive ExecuteNonQuery with almost identical data. The first one (that succeeds) looks like this (data is shortened to fit):

Code:

INSERT INTO GVDeviceInterfaceSpecTable (DevIfcID, Name, Description, Specifications, LockingModule) VALUES ('ddccbbaa-ffee-1100-2233-445566778899', 'High-Resolution Performance Counter with Loopback Interface', '', 0x0001000...00000B, 'fd53d32d-aaed-4366-ac70-ee0b3c7a1b5a')

and the one that fails is like so:

Code:

INSERT INTO GVDeviceInterfaceSpecTable (DevIfcID, Name, Description, Specifications, LockingModule) VALUES ('da59ae69-abcd-bf40-b9c8-e2ce64506d95', 'NI-DAQmx Universal/Unified', '', 0x0001000...CCCC0B, 'fd53d32d-aaed-4366-ac70-ee0b3c7a1b5a')

The second one has a little longer binary part but both are several thousand bytes, anyway. I can post the full queries, if necessary. The table specification is pretty much as above, but here it is, just in case:

Code:

CREATE TABLE GVDeviceInterfaceSpecTable (DevIfcID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, Name VARCHAR (128), Description VARCHAR (128), Specifications VARBINARY(MAX), LockingModule UNIQUEIDENTIFIER)

I don't see any difference between the two statements, I even tried putting the same identifier for 'Name' in the failing one (in case SQL didn't like something inside the literal) but the result was the same. The table is empty before the two rows are to be written. Why would one succeed and the other - fail when they are almost identical? The only difference is the size and content of the VARBINARY(MAX) but both are way over 128. The error message is not helping me at all. Any ideas would be appreciated.

Kamen

KamenG · #5 (**permalink**) 02-17-11, 18:01

Quick update: if the second statement is executed alone, without having the first one being executed right before it, it succeeds... So, it doesn't seem to be anything wrong with the statement itself but with the combination of the two. This is getting weirder, I'll have to go dig deeper.
Kamen

MCrowley · #6 (**permalink**) 02-18-11, 11:29

From the error message, it looks like the leading '0' is being dropped from the data. This leads the query engine to interpret the value as a column identifier (which caps out at 128 characters).

KamenG · #7 (**permalink**) 02-21-11, 11:54

I couldn't figure out the problem. In the context of the program it made mode sense to modify the code so that on each call of the function where the query is made only a single query was executed, and that worked, so I stopped trying to solve this problem. It might have something to do with how ADO.Net makes the ExecuteNonQuery call. I've had problems like that before, where I needed to reset the reader or the connection to fix an otherwise senseless failure; this might just be another case of that. Thanks for giving this a thought!
Kamen

Internet Services

Web Development

Digital Marketing

Consumer Tech