SQL, VB, MicroStrategy & Other Topics

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!

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

Here is a function that will easily let you convert from a CSV (you can modify it to be ;sv or -sv... you get the idea!) to a Table using SQL's Table-valued Functions. SQL Function: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE FUNCTION [dbo].[charlist_to_table] (@list ntext, @delimiter char(1) = N',') RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, str varchar(4000), nstr varchar(2000)) AS BEGIN DECLARE @pos int, @textpos int, @chunklen smallint, @tmpstr varchar(4000), @leftover varchar(4000), @tmpval varchar(4000) SET @textpos = 1 SET @leftover = '' WHILE @textpos BEGIN SET @chunklen = 4000 - datalength(@leftover) / 2 SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) SET @textpos = @textpos + @chunklen SET @pos = charindex(@delimiter, @tmpstr) WHILE @pos > 0 BEGIN SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) SET @pos = c...

Being used to DTS 2000, I was very thrilled to see Foreach Loop Container in action with SSIS 2005. It took me some time to figure out how to make this correctly work for my scenario, but once done it worked like a charm! Mission: To be able to import all .csv files from a particular location to a SQL table, move the file to an archive folder. All of these files had same file structure, but different file names. Plan: I am assuming that you are familiar with creating and opening an SSIS Package. 1. Drag and drop the Foreach Loop Container (Found under Control Flow Items in Toolbox) in ControlFlow area. 2. Add 5 new variables in the Variables pane (Right click anywhere in Control Flow area and click on Variables) Name / Data Type varFileName / String varSourceFolder / String varFilePath / String varArchiveFolder / String varArchivePath / String 3. Now its time to change the variables' properties in the Properties pane. Change the EvaluateAsExpression property to True for varFilePat...