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!

• Christian Clark: I made some further testing and this does not seems to be related to the instance's version numnber.
• Christian Clark: I tried this on a 8.00.2305 and works OK, then went back to the instance where the error is manifesting and upgraded it to 2305 and after upgrading the error persists.
• Mickael: is there a lot of logins ? Maybe you should do this in an not automated way
• Christian Clark: Sadly there a few hundreds of them... but the most important thing is that I need to preserve all passwords.
• Christian Clark: I'm now looking into github.com/sqlcollaborative/dbatools with a bit of luck I may be able to use the Export-SQLLogins function.

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 :

enter image description here

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

enter image description here

• Christian Clark: Yep... this is the same result that I'm getting from the servers in which the script executes properly.