Error while trying to export logins from SQL Server 2000 to SQL Server 2005

Question

I'm trying to migrate logins from an instance in SQL 2000 to a destination instance in SQL 2005.

I've used revlogin before as explained here:

https://support.microsoft.com/en-us/kb/246133

(I'm using the "Method 2" as explained in the article)

The problem is that while trying to create the sp_hexadecimal stored procedure (used by sp_help_revlogin_2000_to_2005) I get the following error:

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 


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 is super weird since the same SP works in other instances.

I'm leaning to believe that this is related to the SQL version I'm working on:

Microsoft SQL Server  2000 - 8.00.2301
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 

The script works well in a:

Microsoft SQL Server  2000 - 8.00.2066 (Intel X86)
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 

Has anyone encountered this issue before?

Is there a workaround?

I found some references to this issue on the Web but they are all inconclusive.

http://www.sqlservercentral.com/Forums/Topic309704-5-1.aspx https://social.msdn.microsoft.com/Forums/en-US/55b9a6c6-2e03-42dd-bcfc-31f487171b73/variable-already-declared-error-when-adding-stored-procedure?forum=sqldatabaseengine

Thanks!

Answer 1

After dealing with this error quite a lot I was not able to find a way to make it work, since support for SQL2000 is no longer available I ended up using dbatools (an awesome PowerShell module): github.com/sqlcollaborative/dbatools

dbatools has a function called Export-SqlLogin which does exactly what I needed: script all logins with sid, password, server role, default db, etc.

I highly recommend looking into this PowerShell module.

Answer 2

Not very usefull, but I have tested it on my sql server 2000 test instance ; version is :

Then copy and start all the code from "Method2" (https://support.microsoft.com/en-us/kb/246133), Everything ran fine :