Dzięki SQL Server 2008 życie administratora stało się prostszym, jeżeli kiedyś miałeś okazję przeprowadzać jakieś zabawy z zapisem binarnym to wiesz o co mi chodzi. W wersjach niższych niż 2008 funkcja convert przy zamianie typu varbinary na varchar dawała mylące wyniki
DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @bin= CONVERT(VARBINARY(100),'super')
SET @char=@bin
SELECT @char,@bin
Zapytanie bez jawnej konwersji zwraca
--------------------- -------------------
super 0x7375706572
Dodanie convert niewiele zmienia
DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @bin= CONVERT(VARBINARY(100),'super')
SET @char=CONVERT (VARCHAR(100),@bin)
SELECT @char,@bin
--------------------- -------------------
super 0x7375706572
Sam Microsoft zalecał dodanie procedury sp_hexadecimal przy używaniu procedury do przenoszenia loginów http://support.microsoft.com/kb/246133
Jest sporo metod obejścia tego problemu np.
DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @bin= CONVERT(VARBINARY(100),'super')
SET @char= '0x' + CAST('' as xml).value(
'xs:hexBinary(sql:variable("@bin"))',
'VARCHAR(MAX)')
SELECT @char,@bin
--------------------- -------------------
0x7375706572 0x7375706572
Są systemowe funkcje
DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @bin= CONVERT(VARBINARY(100),'super')
SET @char=sys.fn_varbintohexstr(@bin)
SELECT @char,@bin
--------------------- -------------------
0x7375706572 0x7375706572
DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @bin= CONVERT(VARBINARY(100),'super')
SET @char=sys.fn_varbintohexsubstring(1,@bin,1,0)
SELECT @char,@bin
--------------------- -------------------
0x7375706572 0x7375706572
Podobnie jest w druga stronę, przy zamianie varchar na varbinary funkcja convert traktowała wsad jako znaki ASCII i konwertowała je jeszcze raz
DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @char= '0x7375706572'
SET @bin=@char
SELECT @char,@bin
-----------------
Msg 257, Level 16, State 3, Line 4
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.
Niejawna konwersja nie udawała się, dodanie convert mogło sporo namieszać
DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @char= '0x7375706572'
SET @bin=CONVERT(VARBINARY(100),@char)
SELECT @char,@bin
----------------- ----------------------------------
0x7375706572 0x307837333735373036353732
Można to było obejść
DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @char= '0x7375706572'
SELECT @bin=CAST('' as xml).value(
'xs:hexBinary(substring(sql:variable("@char"),
sql:column("A.position")))',
'VARBINARY(MAX)')
FROM (SELECT CASE SUBSTRING(@char, 1, 2)
WHEN '0x' THEN 3
ELSE 0
END) AS A(position)
SELECT @char,@bin
--------------------- -------------------
0x7375706572 0x7375706572
Lub funkcją systemowa
DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @char= '0x7375706572'
SET @bin=sys.fn_cdc_hexstrtobin(@char)
SELECT @char,@bin
--------------------- -------------------
0x7375706572 0x7375706572
W SQL Server 2008 doszedł styl konwersji który pozwala otrzymywać poprawne wyniki
DECLARE @char VARCHAR(100)
DECLARE @char2 VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @bin= CONVERT(VARBINARY(100),'super')
SET @char=CONVERT (VARCHAR(100),@bin,1)
SET @char2=CONVERT (VARCHAR(100),@bin,2)
SELECT @char,@char2,@bin
-------------- ------------ --------------
0x7375706572 7375706572 0x7375706572
DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @char= '0x7375706572'
SET @bin=CONVERT(VARBINARY(100),@char,1)
SELECT @char,@bin
--------------------- -------------------
0x7375706572 0x7375706572
Brak komentarzy:
Prześlij komentarz