Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
AskSQLTeam
Ask SQLTeam Question
0 Posts |
Posted - 2005-03-30 : 07:55:05
|
Jeffrey writes "Interest in converting BINARY to VARCHAR in MS SQL. /* CONCEPT IS TO TRANSLATE THIS SQL STATEMENTS INTO SPROC. HOWEVER INTERESTED IN SHOWING BEFORE AND AFTER OF CONVERTING NVARCHAR TO BINARY AND THEN BINARY BACK TO NVARCHAR. NEED TO DETERMINE BEST METHOD TO CONVERT BINARY BACK TO NVARCHAR.*/DECLARE @staff_sys INT,@uid nvarchar(15),@password nvarchar(30),@new_password BINARY(20),@active INT-- the uid and password will be providedSET @uid='test'SET @password='ChangeMePlease' -- enter desired password hereSELECT @staff_sys=staff_sys,@active=active FROM staff NOLOCK WHERE uid LIKE @uid+'%'IF EXISTS(SELECT * FROM tempdb..sysobjects NOLOCK WHERE name LIKE '#temp_uid_pswd%')BEGIN DROP TABLE #temp_uid_pswdENDCREATE TABLE #temp_staff( staff_sys INT, -- unique user identifier uid nvarchar(15), -- user id name active INT, -- is user active old_password BINARY(20), -- current password new_password BINARY(20) -- new password)IF @active<1 BEGIN PRINT 'The userid: '+@uid+' is not active - contact Systems Administrator - step failed'ENDELSEBEGIN-- SELECT CAST(CAST(password AS nvarchar) AS varbinary) FROM staff NOLOCK WHERE staff_sys=@staff_sys-- SELECT CAST(CAST(CAST(password AS nvarchar) AS varbinary) AS nvarchar) FROM staff NOLOCK WHERE staff_sys=@staff_sys INSERT INTO #temp_uid_pswd SELECT staff_sys,uid,active,password,NULL FROM staff NOLOCK WHERE uid LIKE @uid+'%' AND staff_sys=@staff_sys SELECT * FROM #temp_uid_pswd SET @new_password=CONVERT(BINARY(20),@password) UPDATE staff SET password=@password WHERE staff_sys=@staff_sys PRINT 'The userid: '+@uid+' password has been changed successfully to '+@password+' (i.e. case sensitive).' UPDATE #temp_uid_pswd SET new_password=@new_password WHERE uid LIKE @uid+'%' AND staff_sys=@staff_sys CAST(password AS nvarchar) FROM #temp_uid_pswdENDDROP TABLE #temp_uid_pswdROLLBACK TRAN" |
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-03-30 : 10:23:47
|
Ummmhhh, encrypt the passwords???try the UNCOMPRESS command (not documented)SELECT CONVERT(BINARY(20),'the text to convert') AS bin ,UNCOMPRESS(CONVERT(BINARY(20),'the text to convert')) AS txt rockmoose |
|
|
|
|
|
|
|