Error when creating sp_hexadecial and sp_help_revlogin
-
I need to move some users from one server to another. I done this many times using sp_help_revlogin. I grabbed the script form the stored proc I have and I am getting an error when running the script. Also getting a variable declaration in sp_help_revlogin. All your help is very much appreciated. Thank you
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--Error message reads:
Server: Msg 134, Level 16, State 2, Procedure sp_hexadecimal, Line 26
The variable name '@binvalue' has already been declared.
Variable names must be unique within a query batch or stored procedure.
This worked fine in all toher, but two of the servers, Any ideas?
Just in caase here is the script I copied from Microsoft believing mine were not correct.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
----- Begin Script, Create sp_help_revlogin procedure -----
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-
Sorry but that works fine for me.
What do those servers have in common? (SP, verision)??
-
Most likely not the problem but you need a new version of the procedure if your working with SQL Server 2005.
-
Why would that be, the 2 tests I did with it were fine on 2005 (grant it 9 and 10 are not the hardest convert tests
). -
Thanks, All servers are running in Windows 2003 Enterprise and all my sql servers are 2000 sp4. Why it works on almost all of them and not these two???? Who knows, that is why I went to Microsoft to grab the most current copy of these two stored procs. I will keep loking. I can run a DTS too, but these works pretty well before for me.
-
It looks the same on my servers. If I was to try something it looks like you are missing OUTPUT on your first @binvalue. You have one for your @hexvalue but not for @binvalue:
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
Maybe change it to :
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256) OUTPUT,
@hexvalue varchar(256) OUTPUT
AS
I tried the new syntax and it works as well.
-
As far as I understand, the Bin value doesn't need to be returned. It needs to be converted to HEX, nothing more, nothing less.
-
In looking at it further, I believe you may be correct. I think I was looking at it too long.
-
too much coffee this morning ?? 
-
Probably not enough. 
-
In any event, the error I am getting back is that @binvalue has been declared already. The script was taking directly from Microsoft as well as from the stored procedures working in the other servers. Which makes this declare issue very weird to me. Thank you
-
Well I'm out of ideas. Please tell us when you find something.
-
I've got the same issue. Works on one box, not another.
-
I'm running into the same problem:
Server: Msg 134, Level 16, State 2, Procedure sp_hexadecimal, Line 26
The variable name '@binvalue' has already been declared. Variable names must be unique within a query batch or stored procedure.
Server: Msg 134, Level 16, State 2, Procedure sp_help_revlogin, Line 84
The variable name '@login_name' has already been declared. Variable names must be unique within a query batch or stored procedure.
Changing:
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
to:
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256) OUTPUT,
@hexvalue varchar(256) OUTPUT
AS
doesn't make a difference for me. I get the same error. I copied the code directly from Microsoft's KB article. I'm running SQLServer 2000 on Win 03. Any ideas what the problem is? This worked on a different server for me so I don't know what the difference is.
-
I discovered one difference. The box is works on is running SQLServer 2000, SP4, Developer Edition. The box its not working on is running SQLServer 2000, SP4, Enterprise Edition.
I ran this to determine the version:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply