SQL Server Innovations

   Command Prompt Script to Append Flat Files There was a requirement to merge \ append multiple flat files within one folder and in addition - it should also be able to read header file from 1st file but then remove header row from other subsequent flat files  Below Command Prompt script  will be able to resolve this issue: @echo off set "skip=" >summary.txt (   for %%F in (*.csv) do if defined skip (     more +1 "%%F"   ) else (     more "%%F"     set skip=1   ) )

 Powershell Script to Append Flat Files There was a requirement to merge \ append multiple flat files within one folder and in addition - it should also be able to read header file from 1st file but then remove header row from other subsequent flat files  Below Power shell script  will be able to resolve this issue: $getFirstLine = $true get-childItem "C:\temp\Mergefiles\LS_CTD_PYMTHIST_NF*.txt" | foreach {     $filePath = $_     $lines =  $lines = Get-Content $filePath       $linesToWrite = switch($getFirstLine) {            $true  {$lines}            $false {$lines | Select -Skip 1}     }     $getFirstLine = $false     Add-Content "LS_CTD_PYMTHIST_NF.txt" $linesToWrite     }

How to Transform String in SQL Server Say for example if you have column which is returning values such as 401*1 and you want to extract number from string T-SQL Code:  Select '401*1' AS ALTERNATE_ID, REVERSE(PARSENAME(REPLACE(REVERSE('401*1'), '*', '.'), 1)) As TransformedAlternateID

There was a requirement to stop a SQL Server Agent job if any of the Staging Tables are not loaded in to Data warehouse And to keep it simple and perform this action - I have added a step in SQL Server Agent Job to check Audit table to ensure all the Staging tables are loaded before loading them in to Data warehouse   DECLARE @dbState INT; SELECT @dbState = RowCountCheck FROM [StagingDatabase].[vwEtlRowCountCheck]  WHERE RowCountCheck = 0 --AND TableName NOT IN ('Audit_Log', 'Config','CHARGES_DUE_PER_ASSET') ; IF @dbState = 0 BEGIN   RAISERROR('All Staging tables are not loaded correctly', 11, 1); END

  How to Extract Gmail Data Apart from using the third party paid tools, there are few other freely available tools such as Google Takeout and Microsoft Outlook that will help to extract Gmail messages and attachments as below: 1. You can use Google Takeout which can allow you to export all of your Gmail emails, zip them up and it can even split into required files so you can easily copy and archive them Google Takeout is google product and freely available.  Below is URL for Google Takeout https://takeout.google.com/ After selecting required archive, select Next and you would be able to select Frequency, file type and zip size so you can download to your local drive  2.  Configuring Outlook to use Gmail account and then you can easily copy-paste to respective folder.  

How to export / store your Gmail Account emails I am using my Gmail account for the last 20 years (since the year 2000) and since then always used my Gmail account as my primary account for communication. Hence there were lots of important emails collected throughout the years.   And currently, I am at the edge of losing my free 15 GB of Google storage space that I want to archive.   So I was looking for some tools which can help me to export Gmail emails and labels to PDF and in addition, it should also be able to store attachments with the email   And I found Cloud HQ, is one of the best cloud application which allows me to easily save emails or labels to PDF directly from Gmail™ in one click.    This the extension lets you easily save Gmail™ emails as a PDF document on your the computer's hard drive / Google Drive or even Microsoft One Drive, just in 1 click including all the attachments with emails.     It also gives the option, to save as separate emails or you can