0x004700DD24EBF06E46E0A707561C0BE83EC0 is changing to Binary on import

• justin.lamantia

  SSC Veteran

  Points: 220

  More actions

  July 12, 2018 at 8:35 am

  #366126

  Hello all!

  When I run this Query as an Import:
  SELECT ID, OWNER, EMAIL
  FROM  dbo.CLEAR_AUX

  i get...

  <Binary data> as ID
  Barry Bonds as OWNER
  abbonds@gmail.com as EMAIL

  I need it to Read...
  0x004700DD24EBF06E46E0A707561C0BE83EC0 as ID
  Barry Bonds as Owner
  abbonds@gmail.com as EMAIL

  For some reason when i right click the table and select either SELECT TOP or EDIT TOP i get the right values...
  But when i GOTO Views and add the table with checkmarks for ID, Owner, and EMAIL its changing the ID to Binary...same with doing an import from that table  what am i doing wrong?

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  July 12, 2018 at 10:25 am

  #1997343

  justin.lamantia - Thursday, July 12, 2018 8:35 AM

  Hello all!

  When I run this Query as an Import:
  SELECT ID, OWNER, EMAIL
  FROM  dbo.CLEAR_AUX

  i get...

  <Binary data> as ID
  Barry Bonds as OWNER
  abbonds@gmail.com as EMAIL

  I need it to Read...
  0x004700DD24EBF06E46E0A707561C0BE83EC0 as ID
  Barry Bonds as Owner
  abbonds@gmail.com as EMAIL

  For some reason when i right click the table and select either SELECT TOP or EDIT TOP i get the right values...
  But when i GOTO Views and add the table with checkmarks for ID, Owner, and EMAIL its changing the ID to Binary...same with doing an import from that table  what am i doing wrong?

  Can you elaborate further on this please? We need to know where the data is coming from, what you are using for the import, the destination definition and any other details that are relevant.
  😎

• justin.lamantia

  SSC Veteran

  Points: 220

  More actions

  July 12, 2018 at 10:30 am

  #1997346

  The Query Is from SQL, an im Running that Query thru EXCEL Power Query.  When i put that query into Excel Power Query it Returns 

  <Binary data> as ID

  and not...

  0x004700DD24EBF06E46E0A707561C0BE83EC0 as ID

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  July 12, 2018 at 10:38 am

  #1997349

  justin.lamantia - Thursday, July 12, 2018 10:30 AM

  The Query Is from SQL, an im Running that Query thru EXCEL Power Query.  When i put that query into Excel Power Query it Returns 

  <Binary data> as ID

  and not...

  0x004700DD24EBF06E46E0A707561C0BE83EC0 as ID

  Create a view on the SQL with the type conversion and then query it!
  😎
  You can also do the conversion as a step in the Power Query but that is less efficient.

• justin.lamantia

  SSC Veteran

  Points: 220

  More actions

  July 12, 2018 at 10:50 am

  #1997352

  how is it done in SQL?

• justin.lamantia

  SSC Veteran

  Points: 220

  More actions

  July 12, 2018 at 10:51 am

  #1997353

  I created the Query with view...How do i convert the ID in VIEW?

• justin.lamantia

  SSC Veteran

  Points: 220

  More actions

  July 12, 2018 at 10:53 am

  #1997354

  Here is the View Query...

  SELECT TOP (100) PERCENT dbo.CLEAR.ID, dbo.CLEAR.DESCRP, dbo.CLEAR_AUX.OWNER, dbo.CLEAR_AUX.EMAIL
  FROM  dbo.CLEAR INNER JOIN
       dbo.CLEAR_AUX ON dbo.CLEAR.ID = dbo.CLEAR_AUX.ID
  WHERE (NOT (dbo.CLEAR.DESCRP LIKE N'%----%'))
  ORDER BY dbo.CLEAR.DESCRP

  How do i convert the ID

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  July 12, 2018 at 11:06 am

  #1997359

  justin.lamantia - Thursday, July 12, 2018 10:53 AM

  Here is the View Query...

  SELECT TOP (100) PERCENT dbo.CLEAR.ID, dbo.CLEAR.DESCRP, dbo.CLEAR_AUX.OWNER, dbo.CLEAR_AUX.EMAIL
  FROM  dbo.CLEAR INNER JOIN
       dbo.CLEAR_AUX ON dbo.CLEAR.ID = dbo.CLEAR_AUX.ID
  WHERE (NOT (dbo.CLEAR.DESCRP LIKE N'%----%'))
  ORDER BY dbo.CLEAR.DESCRP

  How do i convert the ID

  Roughly, something like this
  😎

  SELECT
  CONVERT(VARCHAR(36),CL.CLEAR.ID,0) AS ID
 ,CL.CLEAR.DESCRP
 ,CLA.OWNER
 ,CLA.EMAIL
FROM dbo.CLEAR CL
INNER JOIN dbo.CLEAR_AUX CLA
ON dbo.CLEAR.ID = dbo.CLEAR_AUX.ID
WHERE (NOT (dbo.CLEAR.DESCRP LIKE N'%----%'))
ORDER BY dbo.CLEAR.DESCRP;

• justin.lamantia

  SSC Veteran

  Points: 220

  More actions

  July 12, 2018 at 11:18 am

  #1997360

  

• justin.lamantia

  SSC Veteran

  Points: 220

  More actions

  July 12, 2018 at 11:19 am

  #1997361

  

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 12, 2018 at 11:53 am

  #1997366

  Eirikur Eiriksson - Thursday, July 12, 2018 11:06 AM

  justin.lamantia - Thursday, July 12, 2018 10:53 AM

  Here is the View Query...

  SELECT TOP (100) PERCENT dbo.CLEAR.ID, dbo.CLEAR.DESCRP, dbo.CLEAR_AUX.OWNER, dbo.CLEAR_AUX.EMAIL
  FROM  dbo.CLEAR INNER JOIN
       dbo.CLEAR_AUX ON dbo.CLEAR.ID = dbo.CLEAR_AUX.ID
  WHERE (NOT (dbo.CLEAR.DESCRP LIKE N'%----%'))
  ORDER BY dbo.CLEAR.DESCRP

  How do i convert the ID

  Roughly, something like this
  😎

  SELECT
  CONVERT(VARCHAR(36),CL.CLEAR.ID,0) AS ID
 ,CL.CLEAR.DESCRP
 ,CLA.OWNER
 ,CLA.EMAIL
FROM dbo.CLEAR CL
INNER JOIN dbo.CLEAR_AUX CLA
ON dbo.CLEAR.ID = dbo.CLEAR_AUX.ID
WHERE (NOT (dbo.CLEAR.DESCRP LIKE N'%----%'))
ORDER BY dbo.CLEAR.DESCRP;

  The issue is the CL.CLEAR.ID and CL.CLEAR>DESCRP column names in the SELECT list.  They should be CL.ID and CL.DESCRP respectively.

• justin.lamantia

  SSC Veteran

  Points: 220

  More actions

  July 12, 2018 at 12:02 pm

  #1997368

  

  This is what i have in View...
  But the ID isnt Binary data...its a bunch of 0x004 blah blah....I need the 0x004 there for this Query..
  
SELECT TOP (100) PERCENT dbo.CLEAR.ID, dbo.CLEAR.DESCRP, dbo.CLEAR_AUX.OWNER, dbo.CLEAR_AUX.EMAIL
FROM  dbo.CLEAR INNER JOIN
     dbo.CLEAR_AUX ON dbo.CLEAR.ID = dbo.CLEAR_AUX.ID
WHERE (NOT (dbo.CLEAR.DESCRP LIKE N'%----%'))
ORDER BY dbo.CLEAR.DESCRP


• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 12, 2018 at 12:04 pm

  #1997371

  justin.lamantia - Thursday, July 12, 2018 12:02 PM

  

  This is what i have in View...
  But the ID isnt Binary data...its a bunch of 0x004 blah blah....I need the 0x004 there for this Query..
  
SELECT TOP (100) PERCENT dbo.CLEAR.ID, dbo.CLEAR.DESCRP, dbo.CLEAR_AUX.OWNER, dbo.CLEAR_AUX.EMAIL
FROM  dbo.CLEAR INNER JOIN
     dbo.CLEAR_AUX ON dbo.CLEAR.ID = dbo.CLEAR_AUX.ID
WHERE (NOT (dbo.CLEAR.DESCRP LIKE N'%----%'))
ORDER BY dbo.CLEAR.DESCRP


  DDL and sample data for the tables so we have something to work with, please.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 12, 2018 at 12:09 pm

  #1997374

  justin.lamantia - Thursday, July 12, 2018 12:02 PM

  

  This is what i have in View...
  But the ID isnt Binary data...its a bunch of 0x004 blah blah....I need the 0x004 there for this Query..
  
SELECT TOP (100) PERCENT dbo.CLEAR.ID, dbo.CLEAR.DESCRP, dbo.CLEAR_AUX.OWNER, dbo.CLEAR_AUX.EMAIL
FROM  dbo.CLEAR INNER JOIN
     dbo.CLEAR_AUX ON dbo.CLEAR.ID = dbo.CLEAR_AUX.ID
WHERE (NOT (dbo.CLEAR.DESCRP LIKE N'%----%'))
ORDER BY dbo.CLEAR.DESCRP


  Also, three (and four) part naming has been deprecated.  You should use table aliases on the tables in the FROM clause and use those aliases on the column names, like this:
  
SELECT  --TOP  (100) PERCENT -- Not needed as you are returning all rows that meet the WHERE criteria
  [cl].[ID]
  , [cl].[DESCRP]
  , [ca].[OWNER]
  , [ca].
FROM
  [dbo].[CLEAR] AS [cl]
  INNER JOIN [dbo].[CLEAR_AUX] AS [ca]
    ON [cl].[ID] = [ca].[ID]
WHERE
  (NOT ([cl].[DESCRP] LIKE N'%----%'))
ORDER BY
  [cl].[DESCRP];


• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 12, 2018 at 12:12 pm

  #1997375

  Also, try running the code for used in the view in a SSMS Query window instead of the designer.  That is why you are seeing <binary data>.