CODEWITHSUNDEEP
sql-serverblobssms
qJake
qJake

Reputation: 17139

How can I get the full hex string of a blob via SQL Server Management Studio?

If I have a blob in the database and I select it, I get the following result:

Blob
------
0xABCDEF1234567890...

What query can I perform, or what options can I set that will retrieve the full hexadecimal string so that I can copy and paste it out of SSMS?

I want the string exactly as it's displayed in the grid view, in hexadecimal format, it just truncates it.

I am aware that you can export the data, generate a file, or hook up a .NET CLR and retrieve the data that way, but for the sake of this question, the process must work when connected to a remote database, AND must only use SSMS features. No exports, no files, just a result in SSMS (e.g. "0x12345...") that is copy and pasteable.

Upvotes: 1

Views: 1732

Answers (2)

Brian Pressler
Brian Pressler

Reputation: 6713

If you go into to Tools -> Options menu on SSMS, and under the Query Results -> SQL Server section you can change the maximum number of characters to return under the "Results to Grid" and "Results to Text" sections.

Upvotes: 0

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

I don't think you can. If you look at the advanced options (Tools -> Options -> Query Results -> SQL Server -> Results to Grid), the largest option for non-XML data is 65535 bytes.

Maybe you could cast it as XML and remove the mark up.

Upvotes: 2

Related Questions