SQL, VB, MicroStrategy & Other Topics

Use the following code to validate Social Security Number (USA). The rules used for validation are based on SSA website, Wikipedia and HowStuffWorks.com. There may be more rules to the validation. If you find any, keep me posted! 'njoy Validating! SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Ankeet -- Create date: 6/19/2008 -- Description: Validates SSN -- ============================================= ALTER FUNCTION ValidateSSN ( @SSN varchar (11) ) RETURNS varchar (255) AS BEGIN /* USAGE: SELECT DBO.ValidateSSN(777992322) BASED ON: http://people.howstuffworks.com/social-security-number2.htm http://en.wikipedia.org/wiki/Social_Security_number http://www.socialsecurity.gov/employer/stateweb.htm http://en.wikipedia.org/wiki/Individual_Taxpayer_Identification_Number The Administration publishes the last group number used for each area number.[17]. Since group numbers are allocated in a regular (if unusual) pattern, it i...

CHECKSUM will calculate a hash value for the provided function parameters. CHECKSUM function when run multiple times for the same parameters, will result in same hash value. But it is not guaranteed that supplying different parameters will always result in a different hash. Therefore using CHECKSUM to uniquely identify each column of a table is not recommended (even with primary keys implemented on the table). I use this function to see if there were changes to any columns in a given row. Each of my tables have an additional column called ChecksumValue with INT datatype. Periodically (nightly in my case) I calculate Checksum for each row and compare it with the ChecksumValue column and if they turn out to be different, take appropriate action (send out email / restore original value etc). CREATE TABLE Example( FName varchar (50), LName varchar (50), ChecksumValue int ) -- Initially: UPDATE Example SET ChecksumValue = CHECKSUM (FName, LName) -- or CHECKSUM(FName) to track changes to ...

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

The title may not exactly explain with this post is about. I had the same problem while trying to search for a solution to this puzzle. Background: I work in debt collection industry. The collectors call debtors and collect money on the debts and based on the amount of money that they collect in a given month, they earn their commission. Sometimes towards the EoM, collectors are notoriously well known to be collecting any (good/bad) payments that they can, so to inflate their monthly numbers. And many a times the bad payment check will bounce as an NSF. But each of these NSFs cost big bucks to the Collection Agencies and the Debtor. Puzzle: One of the new analysis required was to find out which collector(s) had more than 10 NSF payments within any 5 day range. Example, between the range of April 1 and April 5, find all collectors that had more than 10 NSF payments or between the range of April 2 and April 6, find all collectors that had more than 10 NSF payments and so on. Solution: He...

Since last couple years we have been using Crystal Reports 9 as our reporting tool with applications developed in VB6. Recently we deployed a new Staging server just to find out that we couldn't make CR 9 work on it. No matter how many DLLs we installed/uninstalled, the crystal report viewer would not load. I called up Business Objects Support, once again just to find out that they do not support CR 9 anymore. I finally made the call to upgrade our reporting tool to Crystal Reports XI version. To all of you out there wanting to upgrade your CR9, I would suggest you upgrade to CR XI and CR 10 as I have heard (rumors) that support to CR 10 ends December 2007. I had to go around in a circle to get somebody to tell me a short and simple process to upgrade from CR 9 to CR XI. I got it and found it very useful so here I share it with you! If you find it useful let me know! What you will need: Crystal Reports XI, Developer Edition (to develop and integrate with VB6 / .NET application) VB6...

Earlier this morning, I was creating a DTS package to get data from a particular query and inserting in to an Excel File. The query worked just fine in Query Analyzer and the Excel table (worksheet) column definition also exactly matched the query output. But, as it always happens - an error keeps on popping up when you are in time crunch, I kept on receiving 'Invalid pointer' error. I tried changing the variable names, data types etc and nothing worked. Finally I realized that the cause of failure was some sort of unwanted output from executing the query. I fixed this issue by adding SET NOCOUNT ON towards the top and SET NOCOUNT OFF at the bottom of my query! Hope this post will be useful to you at some point in time!

I have been employed as a Systems Developer since last four years and during these four years many a times I have sought help from various blogs. I believe now that I have enough experience to blog about SQL and VB6 and thereby share my experiences with others in the community. Welcome to my blog - 'SQL, VB etc'!