Solved

how do i convert binary values obtained from sql server 2005 to varchar and display in ms access 2007 queries

Posted on 2008-10-16
8
657 Views
Last Modified: 2010-04-21
i am trying to create a query in ms access where in i am obtaining a binary value from sql server 2005. i am not able to display the same in ms access 2007. it is showing some junk values. i actually want to convert this to varchar so that i can link that field to another varchar field in the tables to display the values. please help me out. i am trying to create a custom function in ms access which convert binary to varchar so that i can use that varchar in queries
0
Comment
Question by:karthik_kota
  • 5
  • 2
8 Comments
 
LVL 9

Expert Comment

by:Sander Stad
Comment Utility
There is a function in MS SQL Server 2005 that converts it for you. This is the function: fn_varbintohexstr
Look at this for more information: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20289438.html
0
 
LVL 9

Expert Comment

by:Sander Stad
Comment Utility
In conjunction with the method you could use a linked server to the access database to get the data from the MS SQL server to the table in MS Access
0
 

Author Comment

by:karthik_kota
Comment Utility
yes i do know about the function fn_varbintohexstr. also there is dbo.hex function. but i dont want to make any changes in sql 2005. i am actually looking for a custom function(vb script) in ms access which converts binary to varchar and displays the binary value properly(instead of using junk values) when used in ms access queries and should be able to link that field to another varchar field to obtain values
0
 

Author Comment

by:karthik_kota
Comment Utility
the binary data is like this "0x174795BED6B1B141B55628E500FC50FC". this is of binary datatype. i want to convert this to varchar format by creating a custom function in ms access. please help me out. i dont want to make any changes in sql
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:karthik_kota
Comment Utility
is there any function in ms access which does that.
0
 

Author Comment

by:karthik_kota
Comment Utility
ok i will make it simple. i have dbo.contact. persid(varchar) and dbo.chg. uuid(binary). both are from different tables. i have imported these tables from sql server 2005. now i want to link these two fields in ms access 2007 query ( when contact.persid = chg.uuid) and get the results displayed. but since both are of different datatypes. i am not able to get the results from querry. please help me out in having a custom function to convert binary to varchar. ultimately i want to see the results from the querry overcoming the problem in datatype
0
 
LVL 2

Accepted Solution

by:
jchevali earned 500 total points
Comment Utility
There is a way:

Navigate to this address and borrow the TSToHex() function:
http://kbalertz.com/170380/TimeStamp-Value-Server.aspx

Change the function parameter to be a String instead of a rdocolumn.
Change the For command in TSToHex() to read like this:
For b = 1 To LenB(sBinRep)

In Access, add the function to a public code module.
Add a linked table to the SQL table containing your binary(16) field.
Add a new Access query, switch to SQL view, and enter the following:
SELECT TSToHex(LeftB(uuid,16)) FROM dbo_chg;
Switch to data view.

The contents of the binary field should now be displayed as a hex string.
0
 

Author Closing Comment

by:karthik_kota
Comment Utility
perfect sollution
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now