SQL, VB, MicroStrategy & Other Topics

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE PROCEDURE [dbo].[sp_fixusers] AS BEGIN DECLARE @username varchar(25) DECLARE fixusers CURSOR FOR SELECT UserName = name FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null ORDER BY name OPEN fixusers FETCH NEXT FROM fixusers INTO @username WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_change_users_login 'update_one', @username, @username FETCH NEXT FROM fixusers INTO @username END CLOSE fixusers DEALLOCATE fixusers END Please consider the environment before printing this blog! Go Green!

Few years ago, a friend of mine working for Ramco Systems 'taught' me a logic of incrementally and conditionally updating a table with help of a few variables. I had to use that logic this AM and it required some brush-up, so now I am going to post the logic and sample code right here for all other SQL programmers... create table #temp(num int , runningSum int ) INSERT INTO #TEMP VALUES (1, NULL) INSERT INTO #TEMP VALUES (2, NULL) INSERT INTO #TEMP VALUES (3, NULL) INSERT INTO #TEMP VALUES (4, NULL) INSERT INTO #TEMP VALUES (5, NULL) INSERT INTO #TEMP VALUES (6, NULL) declare @RunningSum int SET @RunningSum = 0 UPDATE #TEMP SET RunningSum = @RunningSum, @RunningSum = @RunningSum + num SELECT * FROM #TEMP DROP TABLE #Temp Results: num         runningSum ----------- ----------- 1           1 2           3 3 ...