Error when using function: exec master..fn_varbintohexstr

Last Post 15 Oct 2009 07:47 PM by busby35242. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
busby35242
New Member
New Member

--
15 Oct 2009 08:51 AM
Hi all,

I am trying to execute the following sctipt below. It is past of an original DTS package file from a SQL database migration tool. In the package, I had a workflow call “Add Photo Command”…which works fine with SQL 2000. In the SQL 2000 script, I was executing the Stored Procedure in the master database named SP_VARBINTOHEXSTR that converts a binary value into a string, in order to create the badge photo file name. However, since SQL 2005 no longer includes VARBINTOHEXSTR as a stored procedure, we created our own as a function, which I am now trying to run agains SQL 2005 run, but keep getting the following error below.

--Here is the old scritpt that I was using with SQL 2000
DECLARE
@BADGENO int,
@micid varbinary(18),
@textmicid varchar(40)

DECLARE id_CURS CURSOR FOR
SELECT recordid FROM cardholder

OPEN ID_CURS
FETCH ID_CURS INTO @BADGENO
WHILE @@FETCH_STATUS = 0
BEGIN

select @micid = micid from cardholder where recordid=@badgeno
exec master..sp_varbintohexstr @micid, @textmicid out
--The followwing line will need to be edit for the proper path of the WinPak Photos and Badging directory--
update cardholder set command=(select ('copy C:\UserImage\'+cast(recordid as varchar (64))+'-1.jpg '+'C:\Badges\'+ @textmicid +'.jpg') as cmd from cardholder where recordid=@badgeno)
where recordid=@badgeno
FETCH ID_CURS INTO @BADGENO
END
DEALLOCATE ID_CURS
go

--Here is the NEW scritpt that I am now trying to use with SQL 2005
DECLARE
@BADGENO int,
@micid varbinary(18),
@textmicid varchar(40)

DECLARE id_CURS CURSOR FOR
SELECT recordid FROM cardholder

OPEN ID_CURS
FETCH ID_CURS INTO @BADGENO
WHILE @@FETCH_STATUS = 0
BEGIN

select @micid = micid from cardholder where recordid=@badgeno
exec master..fn_varbintohexstr @micid, @textmicid out
--The followwing line will need to be edit for the proper path of the WinPak Photos and Badging directory--
update cardholder set command=(select ('copy C:\UserImage\'+cast(recordid as varchar (64))+'-1.jpg '+'C:\Badges\'+ @textmicid +'.jpg') as cmd from cardholder where recordid=@badgeno)
where recordid=@badgeno
FETCH ID_CURS INTO @BADGENO
END
DEALLOCATE ID_CURS
go

--Here is the error I am getting when I execute the NEW script agsint SQL 2005

Msg 8144, Level 16, State 2, Procedure fn_varbintohexstr, Line 0
Procedure or function fn_varbintohexstr has too many arguments specified.


Can anyone provide any suggestions?

Thank you all in advance,

Tracy
busby35242
New Member
New Member

--
15 Oct 2009 01:55 PM
Thank you for the quick response Russell.

Basically, this is what I was told by one of my counterparts to use since sp_varbintohexstr is no longer available in in SQL 2005. Would you recommend a different SP to use?

Tracy
busby35242
New Member
New Member

--
15 Oct 2009 07:47 PM
Thank you so very much Russell. Believe it or not, I got it working a few hours ago after some messing around. Here is what I come up with and seems to do the job okay. LMK if you see anything wrong with it.

--This statement will add the photo command
DECLARE
@BADGENO int,
@micid varbinary(18),
@textmicid varchar(40)

DECLARE id_CURS CURSOR FOR
SELECT cardholderid FROM cardholders

OPEN ID_CURS
FETCH ID_CURS INTO @BADGENO
WHILE @@FETCH_STATUS = 0
BEGIN

select @micid = micid from cardholders where cardholderid=@badgeno
set @textmicid = sys.fn_varbintohexstr (@micid)
--The followwing line will need to be edited for the proper path of the EBI Photos and Badging directory !
update cardholders set command=(select ('copy C:\UserImages\'+cast(cardholderid as varchar (64))+'-1.jpg '+'C:\Badges\'+ @textmicid +'.jpg') as cmd from cardholders where cardholderid=@badgeno)
where cardholderid=@badgeno
FETCH ID_CURS INTO @BADGENO
END
DEALLOCATE ID_CURS
go

Tracy


Acceptable Use Policy
---