Import Binary field from Oracle DB into MSSql 2000

The company I'm currentyl emplyed with is migrating their data from an Oracle driven environment to an MSSQL2000 environment.
the problem I'm facing is that some Oracle DB fields are binary.
If I write a DTS package to import the data from Oracle into MSSQL all goes well, but MSSQL displays this data simply as <binary>.

Is there a way to transform that data into it's original value while exporting/importing ?

Any help would be greatly appreciated !
dokusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

regbesCommented:
Hi dokus,

im not too sure what the binary data type in oracle equates to in MS but  im a assuming it is TEXT

if the data type of the field in SQL if it is TEXT

please read the following
http://msdn2.microsoft.com/en-us/library/ms186838.aspx
and
http://msdn2.microsoft.com/en-us/library/ms187365.aspx

Cheers!
Kobe_LenjouCommented:
the <binary> thing is fine.
The real data is in the field, but since there is no way to display baniry data in a consistent way, the Enterprise Manager is showing <binary>

If you ask the same thing in Query analyser, you'll het a better result.
You can verify the length of the field with len() for example.

As said before here, never ever trust Enterprise Manager of Query Analyser to give you the real stored data.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dokusAuthor Commented:
if you import it and let things go automatically its defined as a varbinary field in MSSQL

the actual problem is that when i'm tring to use CAST or CONVERT to display it's contents as a "readable" value, then all I see is jibberish (for example CCtÜÁÎø )

So I want to be actually able to read the contents of that varbinary field.  Can that be done ?
(the reason to do this is because I have to export that data afterwards from MSSQL towards CSV for import usage in another application)
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

regbesCommented:
Kobe_Lenjou,

You may need to convert it to the required datatype

try this

declare @b binary(4), @str varchar(255)
select @b = 3455643
exec master..xp_varbintohexstr @b, @str out
print @str

for sql
http://msdn2.microsoft.com/en-us/library/ms179366.aspx
http://msdn2.microsoft.com/en-us/library/ms191530.aspx#_binary

For VB.Net

http://forums.devx.com/showthread.php?t=55343
dokusAuthor Commented:
Regbes,

your function gives me the hex representation, much in the same way I get it when querying it with Query Analyzer.
The problem is that that field should contain text ( a sort of commentary field)
Is it possible to transform those values into a readable string of some kind ?
Kobe_LenjouCommented:
Convert may be indeed needed, all depending in the actual content in the binary field.

@dokus:
How will you be exporting binary data to a CSV?
I mean, binary data usually represent an object like a PDF / JPG document or some other obscure stuff. And the gibberitch you see is the actual contentm displayed in ascii however. With the master..xp_varbintohexstr(as regbes pointed out) you can get the hexadecimal string of the content (wich is probably what you expect.
dokusAuthor Commented:
Kobe_Lenjou,

the original data is from an ERP solution under Unix
Some fields in these tables contain data from commentary fields (in the ERP-solution).  these fields seem to be stored as binary data in Oracle.
Now I need to find a way to make those fields readable again in MSSQL so we can import it into the a soon-to-be integrated ERP solution

i'm getting the impression that the original unix software still does somekind of conversion on it, because, like you say, the displayed ASCII code is unreadable, yet that is what you get when converting from HEX to varchar or string
regbesCommented:
Ok

Cast will work as evidenced by

declare @b binary(4), @str varchar(255)
set @str = 'bob'
set @b=cast (@str as varbinary(4))
select  @b "Unconverted"
select cast(@b as varchar(255)) "Converted"

if not it could mean that your ERP system was doing some further conversion on the binary data? and is in fact expecting unconverted binary data
Kobe_LenjouCommented:
Ahh dokus, you should have mentioned you were expecting a text output in the first place :-)

I believe regbes' suggestion is the right one here.
Note the difference between *varchar* and *nvarchar*! Maybe that can give you your original text back.
dokusAuthor Commented:
nope, none works.

I guess the software still does it's own conversion.

I'm gonna split the points between Kobe & regbes, hope you agree
Kobe_LenjouCommented:
You could still try to figure out what's going on.

I suppose you have a known text, and a corresponding binary (preferably in HEX) string, maybe some manual peeking could help here.
Try pasting an example.


(thanks for the points anyway)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.