July 12, 2018 at 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?
July 12, 2018 at 10:25 am
justin.lamantia - Thursday, July 12, 2018 8:35 AMHello all!When I run this Query as an Import:
SELECT ID, OWNER, EMAIL
FROM dbo.CLEAR_AUXi get...
<Binary data> as ID
Barry Bonds as OWNER
abbonds@gmail.com as EMAILI need it to Read...
0x004700DD24EBF06E46E0A707561C0BE83EC0 as ID
Barry Bonds as Owner
abbonds@gmail.com as EMAILFor 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.
😎
July 12, 2018 at 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
July 12, 2018 at 10:38 am
justin.lamantia - Thursday, July 12, 2018 10:30 AMThe 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.
July 12, 2018 at 10:50 am
how is it done in SQL?
July 12, 2018 at 10:51 am
I created the Query with view...How do i convert the ID in VIEW?
July 12, 2018 at 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
July 12, 2018 at 11:06 am
justin.lamantia - Thursday, July 12, 2018 10:53 AMHere 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.DESCRPHow 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;
July 12, 2018 at 11:18 am
July 12, 2018 at 11:19 am
July 12, 2018 at 11:53 am
Eirikur Eiriksson - Thursday, July 12, 2018 11:06 AMjustin.lamantia - Thursday, July 12, 2018 10:53 AMHere 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.DESCRPHow 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.
July 12, 2018 at 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
July 12, 2018 at 12:04 pm
justin.lamantia - Thursday, July 12, 2018 12:02 PMThis 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.
July 12, 2018 at 12:09 pm
justin.lamantia - Thursday, July 12, 2018 12:02 PMThis 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];
July 12, 2018 at 12:12 pm
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>.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply