January 5, 2015 at 8:33 am
Hi guys, I read this link and I have exactly the same problem:
http://www.sqlservercentral.com/Forums/Topic613703-338-1.aspx
But the solution doesn't work for me! I got a type as table:
CREATE TYPE [dbo].[User_Ven_Att] AS TABLE(
[userName] [bigint] NULL,
[idNasVendor] [bigint] NULL,
[srvAtt] [varbinary](max) NULL
)
I fill it but see a different result (I used the solution in the above link):
DECLARE @TBL AS User_Ven_Att
INSERT INTO @TBL
SELECT 1000006409, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000006409, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000115634, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000115634, 1, 0x1A0900000009FC03FF
select A.userName, cast(cast((select cast(srvAtt as varchar(max))
from @TBL B
where B.userName = A.userName
for xml path('')) as varchar) as varbinary) as all_data
from (select distinct userName from @TBL) as A
The output is something wrong!
10000064090x26237831413B0926237830303B26237830303B26237830303B09FC262378
10001156340x26237831413B0926237830303B26237830303B26237830303B09FC262378
Why? What should I do?
Any idea I appreciate in advance.
January 5, 2015 at 9:31 am
salardx (1/5/2015)
Hi guys, I read this link and I have exactly the same problem:http://www.sqlservercentral.com/Forums/Topic613703-338-1.aspx
But the solution doesn't work for me! I got a type as table:
CREATE TYPE [dbo].[User_Ven_Att] AS TABLE(
[userName] [bigint] NULL,
[idNasVendor] [bigint] NULL,
[srvAtt] [varbinary](max) NULL
)
I fill it but see a different result (I used the solution in the above link):
DECLARE @TBL AS User_Ven_Att
INSERT INTO @TBL
SELECT 1000006409, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000006409, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000115634, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000115634, 1, 0x1A0900000009FC03FF
select A.userName, cast(cast((select cast(srvAtt as varchar(max))
from @TBL B
where B.userName = A.userName
for xml path('')) as varchar) as varbinary) as all_data
from (select distinct userName from @TBL) as A
The output is something wrong!
10000064090x26237831413B0926237830303B26237830303B26237830303B09FC262378
10001156340x26237831413B0926237830303B26237830303B26237830303B09FC262378
Why? What should I do?
Any idea I appreciate in advance.
Quick suggestion, use CONVERT instead of CAST
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TBL TABLE(
[userName] [bigint] NULL,
[idNasVendor] [bigint] NULL,
[srvAtt] [varbinary](max) NULL
);
INSERT INTO @TBL
SELECT 1000006409, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000006409, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000115634, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000115634, 1, 0x1A0900000009FC03FF ;
SELECT DISTINCT
TX.userName
,CONVERT(VARBINARY(MAX),(SELECT CONVERT(VARCHAR(MAX),T.srvAtt,2)
FROM @TBL T
WHERE T.userName = TX.userName
FOR XML PATH('')),2) AS ALL_DATA
FROM @TBL TX;
Results
userName ALL_DATA
----------- ---------------------------------------
1000006409 0x1A0900000009FC03FF1A0900000009FC03FF
1000115634 0x1A0900000009FC03FF1A0900000009FC03FF
January 5, 2015 at 9:52 am
Eirikur Eiriksson (1/5/2015)
salardx (1/5/2015)
Hi guys, I read this link and I have exactly the same problem:http://www.sqlservercentral.com/Forums/Topic613703-338-1.aspx
But the solution doesn't work for me! I got a type as table:
CREATE TYPE [dbo].[User_Ven_Att] AS TABLE(
[userName] [bigint] NULL,
[idNasVendor] [bigint] NULL,
[srvAtt] [varbinary](max) NULL
)
I fill it but see a different result (I used the solution in the above link):
DECLARE @TBL AS User_Ven_Att
INSERT INTO @TBL
SELECT 1000006409, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000006409, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000115634, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000115634, 1, 0x1A0900000009FC03FF
select A.userName, cast(cast((select cast(srvAtt as varchar(max))
from @TBL B
where B.userName = A.userName
for xml path('')) as varchar) as varbinary) as all_data
from (select distinct userName from @TBL) as A
The output is something wrong!
10000064090x26237831413B0926237830303B26237830303B26237830303B09FC262378
10001156340x26237831413B0926237830303B26237830303B26237830303B09FC262378
Why? What should I do?
Any idea I appreciate in advance.
Quick suggestion, use CONVERT instead of CAST
😎
USE tempdb;GO
SET NOCOUNT ON;
DECLARE @TBL TABLE(
[userName] [bigint] NULL,
[idNasVendor] [bigint] NULL,
[srvAtt] [varbinary](max) NULL
);
INSERT INTO @TBL
SELECT 1000006409, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000006409, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000115634, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000115634, 1, 0x1A0900000009FC03FF ;
SELECT DISTINCT
TX.userName
,CONVERT(VARBINARY(MAX),(SELECT CONVERT(VARCHAR(MAX),T.srvAtt,2)
FROM @TBL T
WHERE T.userName = TX.userName
FOR XML PATH('')),2) AS ALL_DATA
FROM @TBL TX;
Results
userName ALL_DATA----------- ---------------------------------------
1000006409 0x1A0900000009FC03FF1A0900000009FC03FF
1000115634 0x1A0900000009FC03FF1A0900000009FC03FF
No real difference between CAST and CONVERT unless you need to use a style code for the conversion. Also, if you listen to Mr. Celko, CAST is a standard SQL function where as CONVERT is not.
January 5, 2015 at 11:12 am
Lynn Pettis (1/5/2015)
No real difference between CAST and CONVERT unless you need to use a style code for the conversion. Also, if you listen to Mr. Celko, CAST is a standard SQL function where as CONVERT is not.
This is one of the cases where that difference matter, consider this code. Cast does the same as CONVERT with style = 0, translates the binary 1:1 to ASCII which when converted back to binary will give the wrong results. Using CONVERT with the style flag 2 gives the hexadecimal character representation of the binary values, hence it can be concatenated correctly. In fact I recommend not using the undocumented function sys.fn_varbintohexstr but CONVERT instead.
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TBL TABLE(
[userName] [bigint] NULL,
[idNasVendor] [bigint] NULL,
[srvAtt] [varbinary](max) NULL
);
INSERT INTO @TBL
SELECT 1000006409, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000006409, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000115634, 1, 0x1A0900000009FC03FF UNION ALL
SELECT 1000115634, 1, 0x1A0900000009FC03FF ;
SELECT DISTINCT
TX.userName
,CONVERT(VARBINARY(MAX),(SELECT CONVERT(VARCHAR(MAX),T.srvAtt,2)
FROM @TBL T
WHERE T.userName = TX.userName
FOR XML PATH('')),2) AS ALL_DATA
FROM @TBL TX;
SELECT
T.userName
,T.srvAtt
,CONVERT(VARCHAR(36),T.srvAtt,0) AS CNV_0
,CONVERT(VARCHAR(36),T.srvAtt,1) AS CNV_1
,CONVERT(VARCHAR(36),T.srvAtt,2) AS CNV_2
,CAST(T.srvAtt AS VARCHAR(36)) AS CST
FROM @TBL T;
Results (not all characters are visible/printable)
userName srvAtt CNV_0 CNV_1 CNV_2 CST
------------ --------------------- --------- --------------------- ------------------- ---------
1000006409 0x1A0900000009FC03FF üÿ 0x1A0900000009FC03FF 1A0900000009FC03FF üÿ
1000006409 0x1A0900000009FC03FF üÿ 0x1A0900000009FC03FF 1A0900000009FC03FF üÿ
1000115634 0x1A0900000009FC03FF üÿ 0x1A0900000009FC03FF 1A0900000009FC03FF üÿ
1000115634 0x1A0900000009FC03FF üÿ 0x1A0900000009FC03FF 1A0900000009FC03FF üÿ
January 5, 2015 at 11:15 am
Lynn Pettis (1/5/2015)
Also, if you listen to Mr. Celko...
Always do listen to JC, don't always agree though.;-)
😎
January 5, 2015 at 11:25 am
Missed you using a style code. I guess I should clean this Afghan dust off my screen.
January 5, 2015 at 12:03 pm
Lynn Pettis (1/5/2015)
Missed you using a style code. I guess I should clean this Afghan dust off my screen.
Foggy glasses are more the problem on this end:smooooth:
For completeness, the default style for binary conversion with CONVERT is 0, gives the same results as CAST.
😎
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply