title: How can I generate an INSERT script for a table with a VARBINARY(MAX) field?
body: <p>I have a table with a <code>VARBINARY(MAX)</code> field (SQL Server 2008 with <code>FILESTREAM</code>)</p>
  
  <p>My requirement is that when I go to deploy to production, I can only supply my IT team with a group of SQL scripts to be executed in a certain order.  A new table I am making in production has this <code>VARBINARY(MAX)</code> field.  Usually with new tables, I will script out the <code>CREATE TABLE</code> script. And, if I have data I need to go with it, I will then script out the <code>INSERT</code> scripts. Not too complicated.</p>
  
  <p>But with <code>VARBINARY(MAX)</code>, the Stored Procedure I was using to generate the <code>INSERT</code> statements fails on that table. I tried selecting that field, printing it, copying it, converting to hex, etc. The main issue I have with that is that it doesn't select all the data in the field. I do a check <code>DATALENGTH([FileColumn])</code> and if the source row contains 1,004,382 bytes, the max I can get the copied or selected data when inserting again is 8000. So basically it is truncated (i.e. invalid) data.....</p>
  
  <p>How can I do this better?  I tried Googling this like crazy but I must be missing something. Remember, I can't access the filesystem. This has to be all scripted.</p>