August 7, 2002 at 4:04 pm
I've a stored proc that is generating dynamic SQL. The actual code is far too daunting to read--much less post--but this line displays tho problem:
print convert(nvarchar (250),0x0000119300001F48000000270000188A00001B6400000000)
This displays the message "The command(s) completed successfully". It lies. The following shows the problem better:
declare @sQry as varchar(250)
declare @vbId as varbinary(250)
set @vbId = 0x0000119300001F48000000270000188A00001B6400000000
set @sQry = 'print ''Test: '''
-- The following has a plus sign after
-- "set @sQry = @sQry", and after
-- "(250), '''", and before the final
-- triple quote, but they're not
-- up in the Prewiew page. <sigh>
set @sQry = @sQry + ' + convert(varbinary(250), ''' + convert(varchar(250), @vbId) + ''')'
print @sQry
exec (@sQry)
This results in the following message:
print 'Test: ' + convert(varbinary(250), '
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Š'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ')'.
The problem lies in trying to concatenate a varbinary into a string. If the varbinary is not converted, the + operator tries to do addition on a binary number. If I try to concatenate the varbinary, I get problems like the above.
Steve
Steve Miller
August 7, 2002 at 6:29 pm
I remember having problems converting varbinary strings myself (a Citrix Metaframe XP datastore has a few columns which are binary) but eventually plugged through with the help of a function. With that said, what version of SQL Server are you running on?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
August 8, 2002 at 9:59 am
>> what version of SQL Server are you running on?
SQL Server 2000 MSDE
>> eventually plugged through with the help of a function.
I tried this, but I'm not getting anywhere with it:
create function fnVarbinary2Varchar (@vb varbinary(250))
returns varchar(250)
as
begin
return convert(varchar(250), @vb)
end
go
--
print 'Test: ' + dbo.fnvarbinary2varchar(0x0000119300001F48000000270000188A00001B6400000000)
Returns:
Test:
Steve Miller
August 8, 2002 at 10:45 am
There's a system function which does the conversion in SQL2K. Take a look at dbo.fn_varbintohextstr and see if it fits your needs.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
August 8, 2002 at 11:11 am
I think that did it. Thanks. Now all I have to do is test it in the real code.
print 'Test: ' + master.dbo.fn_varbintohexstr(0x0000119300001F48000000270000188A00001B6400000000)
Output:
Test: 0x0000119300001f48000000270000188a00001b6400000000
Steve
Steve Miller
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply