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 ...

A very simple way to select top X random rows from a table/query or select rows in a truly random order. The key is using a GUID. SELECT TOP X * FROM SomeTable ORDER B Y NewID () That Simple!

Hi All! Hope you had a great summer! I was looking up the 'internets' for something and came across many people looking for a way to retrieve SQL Error Message based on an Error ID. Here's a quick solution - nothing outstanding; but good-to-know script! DECLARE @Error int DECLARE @ErrorMessage varchar(800) SET @Error = @@Error IF @Error 0 SET @ErrorMessage = isnull((select [text] from sys.messages where message_id = @Error),'Error. Details not found.') SELECT @Error as [Error ID], @ErrorMessage as [Error Message] GO Go Go Go. Execute. Regards! Please consider the environment before printing this blog! Go Green!

Your music will be missed. Rest in Peace MJ. (Image Source: http://www.mediabistro.com/agencyspy/original/Michael_jackson_bad_cd_cover_1987_cdda.jpg)

A friend of mine wrote this script for some reason and forwarded to me for the heck of it! I have no use for it but figured somebody else might be looking for it! So here it is... If you have any questions, feel free to post 'em and I will run it by Narayan. -- ============================== =============== -- Author: -- Create date: -- Description: -- ============================== =============== CREATE FUNCTION [dbo].[ udfLatLonRadiusDistance] ( @lat1Degrees decimal(15,12), @lon1Degrees decimal(15,12), @lat2Degrees decimal(15,12), @lon2Degrees decimal(15,12) ) RETURNS decimal(9,2) AS BEGIN DECLARE @ earthSphereRadiusNauticalMiles as decimal(10,6) DECLARE @ nauticalMileConversionToMilesF actor as decimal(7,6) SELECT @ earthSphereRadiusNauticalMiles = 6366.707019 SELECT @ nauticalMileConversionToMilesF actor = .621371 -- convert degrees to radians DECLARE @lat1Radians decimal(15,12) DECLARE @lon1Radians decimal(15,12) DECLARE @lat2Radians decimal(15,12) DECL...

I had a tough time to apply alternate row colors to a SSRS Matrix and finally figured out! Without further ado, here it is... Assume you have a matrix with more than 1, lets say 2 row groupings; RG1 and RG2. 1. Right-click on RG2 (innermost row group), and select "Insert Group"; for simplicity call it "RowColorGroup" 2. In the "Group On" section, add some constant value - for example ="" or ="ankeet" etc... you get the idea! 3. Select the newly created group "RowColorGroup" and enter the following in its "Value" property: =iif(RunningValue(Fields!RG1.Value & Fields!RG2.Value,CountDistinct,Nothing) Mod 2, "LightSteelBlue", "White") 4. Select the "BackgroundColor" property of "RowColorGroup" and enter "=Value" 5. Set the width of "RowColorGroup" to 0pt and "CanGrow" to false 6. Select the data cell(s) and set their "BackgroundColor" pro...