Text BLOB in SQL Server 2008R2

Jenkinsj5

I have a SQL Server 2008R2 database written from HP?s Service Manager, where the values look like 5F 52 43 46 4D 2A 3D 80 ...<br />
<br />
The actual data stored is text string arrays (displays as field and text value in the application), I want to deliver it in reports as human readable. The format can be changed in Service Manager, but there are lots of BLOB fields and performance concerns prohibit converting them all. I am looking for an ad-hock reporting solution.<br />
<br />
I found a decent <a class='bbc_url' href='http://h30499.www3.hp.com/t5/HP-Service-Manager-Service/Converting-BLOB-CLOB-ServiceCenter-data-for-reporting/m-p/4508975'>forum discussion</a> at HP for retrieving the data <br />
<br />
But it uses Crystal/Oracle reporting which has <a class='bbc_url' href='http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm'>DBMS_LOB</a&gt; <br />
<br />
There are a couple of BIRT Exchange Wiki mentions on BLOB and a <a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/interactive-reporting/1062-browse-a-database-with-birt/ '>Devshare</a> but still I am lost.<br />
<br />
It seems there should be two paths to solution<br />
1. SQL through FILESTREAM or Slice(), and give it to BIRT as text string<br />
2. Bring the unaltered BLOB field into BIRT and deal with it through JS somehow<br />
<br />
The final solution requires more knowledge about SQL, JS, BIRT and Service Manager then I have. If someone could suggest a direction of study in SQL, JS and/or BIRT, I should focus on as the most likely path that would be extremely helpful.

Clement Wong

Your best bet is to have a cleaned set of data available from SQL Server that BIRT can consume.

It appears that HP?s Service Manager write its values to text string arrays as you mentioned rather than simple text BLOB/CLOB datatypes. If it were simple, then we would attempt to solve this at the DB level, by updating your query and converting the text BLOB column(s) to string.

Examples:
SELECT CONVERT(NVarChar(99), yourBLOBcolumn) FROM yourTable;
SELECT CONVERT(VarChar(99), yourBLOBcolumn) FROM yourTable;

However, it is not this simple. Even third party companies have produced solutions and state that there are "Challenges in Reporting" with HP's Service Manager @ http://www.slideshare.net/hpsoftwaresolutions/btot-we09009

You may want to consider this blog entry @ http://www.tactek.com/2011/02/improve-hp-service-manager-performance-with-array-tables/ that describes a potential solution of how the Service Manager data can be stored in traditional db tables and columns that can be used in BIRT.

Jenkinsj5

Thank you Clement

I was really hoping you were going to say it would be easy if I was smarter. Then I could just go get smarter.

The blog entry at tactek, is not a path to solution (but does help educate) because I need a solution that is invisible to the DB and application.

But your answer and references combine to give me just the answer I was looking for, I now know where I need to concentrate. Get readable data between the data base I can't alter and before BIRT/JS.

I will work toward SQL that is specific to HP's Service Manager [image] (BLOB/Text) field formatting. Then if it turns out to be simple enough I can alter it on the fly. Else I will harvest from the DB (on some schedule), alter it, and write it to another DB and report from the copied DB piece. Because the fields are always part of a table with an easily readable key, I will only need to copy the key and BLOB, then read from it like it was a separate array table.

Jenkinsj5

Here is the solution using SQL, without any changes to the database or Service Manager. Reading from the table dbo.SCMESSAGEM1 an out of the box table with an [IMAGE] formatted BLOB.<br />
<br />
There are two and a half steps (<em class='bbc'>converts array to text string</em>)<br />
<br />
First create ?InnerQ? this function grabs all the fields from the table and cast(s) the [IMAGE] to varbinary. Per <a class='bbc_url' href='http://msdn.microsoft.com/en-us/library/ms187928.aspx'>Microsoft</a&gt; there are only 3 types you can cast from image. Default length on cast is 30, so set to 8000. <br />
<br />
Second from ?InnerQ? grab all the fields, everything looks the same on review but now you can cast varbinary to varchar. Now you can read it!<br />
<br />
2.5 All the values have a leading string ?_RCFM*=- ? that I don?t need, so use substring to start with the 9th character.<br />
<br />
<br />
select SYSLANGUAGE<br />
, MESSAGE_ID<br />
, SEVERITY<br />
-- ignore first 9 characters of MessageToVarbinary after casting<br />
, substring (<br />
-- Change varbinary to varchar (so a person can read it)<br />
cast (MessageToVarbinary <br />
as varchar (8000))<br />
, 9, 7991) as 'Message'<br />
, CLASS<br />
, COMMENTS<br />
, SYSMODTIME<br />
, SYSMODCOUNT<br />
, SYSMODUSER<br />
<br />
-- Create InnerQ for subset of data to work on<br />
from (<br />
select SYSLANGUAGE<br />
, MESSAGE_ID<br />
, SEVERITY<br />
-- Raw is an image, limits options for cast, so cast it as varbinary<br />
, cast (MESSAGE <br />
-- only get the first 8000 bits/characters<br />
as varbinary (8000)) as 'MessageToVarbinary'<br />
, CLASS<br />
, COMMENTS<br />
, SYSMODTIME<br />
, SYSMODCOUNT<br />
, SYSMODUSER<br />
<br />
from dbo.SCMESSAGEM1<br />
)InnerQ<br />
-- end InnerQ