Unanswered: Syntax for INSERT-ing VARBINARY(MAX) values
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))
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.
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:
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):
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')
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:
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.
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.
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!