I have a varbinary column which I am able to retrieve with bcp to a file.
I then wish to update the varbinary column with changes I have made to said file.
I attempt to update this using T-SQL with UPDATE, OPENROWSET and BULK to import the file.
The initial retrieval with bcp gives me a file that is ASCII with CR LF.
But when I retrieve the column again after updating it (even if I don't change the file) I end up with jibberish (lot of questions marks and what not) and it's a ISO-8859 text, with very long lines, with CRLF line terminators.
Initially I do the following:
Create a test table:
sqlcmd -S .\SQLEXPRESS -d da_dbname -U SomeDatabaseUsr -P SomePassVerd -Q 'CREATE TABLE myTable(FileName nvarchar(60), FileType nvarchar(60), Document varbinary(max))'
Store the original in myTable so we can set it back and test again:
sqlcmd -S .\SQLEXPRESS -d datastore -U SomeDatabaseUsr -P SomePassVerd -Q 'INSERT INTO myTable (Filename, FileType, Document) VALUES (''ME_TBL_WITH_VARBIN'', ''DataBlob'', (SELECT varbincol FROM CONTENT_DATA WHERE ContentDataID=575757))'
Initial and 2nd retrieval after update is done with:
bcp "SELECT CONVERT(NVARCHAR(MAX), varbincol) FROM ME_TBL_WITH_VARBIN WHERE ID=57575" queryout C:\Users\me\Documents\test2.txt -USomeDatabaseUsr -PSomePassVerd -dda_dbname -S .\SQLINST -c"
Starting copy...
1 rows copied.
Network packet size (bytes): 4096
Which results in my file as expected.
And then using the same content pulled into
C:\Users\me\Documents\test2.txt
I try to update the same column to get the same file; but I do not.
How I update the record for the test:
sqlcmd -S .\SQLEXPRESS -d da_dbname -U SomeDatabaseUsr -P SomePassVerd -Q 'UPDATE ME_TBL_WITH_VARBIN SET varbincol = ( SELECT a.* FROM OPENROWSET (BULK ''C:\Users\me\Documents\test2.txt'', SINGLE_BLOB) AS a) WHERE ME_TBL_WITH_VARBIN.ID=57575'
How I test it (and so far changing nothing in the above statement has worked)?
If this results in 2 records it failed; if it results in 1 it succeeded:
TEST make sure they are both the same...if the same return 1.
sqlcmd -S .\SQLEXPRESS -d da_dbname -U SomeDatabaseUsr -P SomePassVerd -Q 'SELECT count(*) FROM ((SELECT Document FROM myTable WHERE Filename = ''ME_TBL_WITH_VARBIN'') UNION (SELECT varbincol As Document FROM ME_TBL_WITH_VARBIN WHERE ID=575757)) AS Worked'"
I have tried several variations on "How I update the record" above such as using SINGLE_BLOB, SINGLE_CLOB, SINGLE_NCLOB (link) and CONVERT but nothing seems to import the item as the same binary value.