Tag Archives: How To

How to Sort Files into Year-Month-Day Folders

Batch Script, PowerShell, , , , , , , , , , , , , , , ,

Below is example PowerShell code to create a directory structure from file creation dates.

A use case for this code might be a need to organize a collection of files stored on an SFTP server or NAS. The code can be modified to loop through the files putting each one into sub directories corresponding to nested folders organized into Year\Month\Day folders.

To use the logic first create a test folder on your computer.
Then copy the logic below to a text file editor and find and replace the following with reference to your test folder location:

C:\YourTestDirectory

Then save the code below as a PowerShell file called “DirFromDate.ps1” into your test folder.

To then run the file open up PowerShell and paste the following line:

Set-ExecutionPolicy RemoteSigned

Hit return and then enter the letter A if requested.

Then enter the following replacing “YourTestDirectory” with your actual test directory location:

& “C:\YourTestDirectory\DirFromDate.ps1”

Hit return.

<#
.SYNOPSIS
   <A brief description of the script>
.DESCRIPTION
   <A detailed description of the script>
.PARAMETER <paramName>
   <Description of script parameter>
.EXAMPLE
   <An example of using the script>
#>

#FIND AND REPLACE THE FOLLOWING WITH A DIRECTORY
C:\YourTestDirectory

#How Run In Powershell
#Open Powershell terminal and run the folling line:
#Set-ExecutionPolicy RemoteSigned
#Type A and press Enter (if applicable).
#Run the below including "&" at start of line
#& "C:\YourTestDirectory\DirFromDate.ps1"

#VARIABLES
#Define folder variables
$workingFolder = "C:\YourTestDirectory\"
#Define file variable
$testFile = "\ThisIsATestFile.txt"
$testFile = $workingFolder + $testFile


###############################################
#(!!DO NOT ALTER SCRIPT PASSED THIS POINT!!)
###############################################
#Write variables to terminal
Write-Host "Filepath: $workingFolder";

#Create test File

New-Item $testFile

#Define source directory variable
$files = Get-ChildItem -Path $workingFolder 

#3 steps: move raw files to azure, zip file, move zipped file to azure
foreach ($file in $files)
{
		#FileName
		Write-Host "File: $file";
		#Get file creation date from file name
		$fileDate = $file.CreationTime.ToString("ddMMyyyy")
		#Get file creation date (last time data was written to file) from file name
		#$fileDate = $file.LastWriteTime.ToString("ddMMyyyy")
		Write-Host "Filedate: $fileDate";
		$fileDate = $fileDate.Substring($fileDate.Length - 4) + "\" + $fileDate.Substring(3, 2) + "\" + $fileDate.Substring(0, 2)
		Write-Host "Filedate: $fileDate";
		$DirectoryStructure = $workingFolder + "\" + $fileDate
		Write-Host "DirStructure: $DirectoryStructure";

}
md $DirectoryStructure

Move-Item -Path $testFile -Destination $DirectoryStructure

How to check SQL Server schema user permissions

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , ,

The script below will, providing the login you are using has adequate permissions, return the schema permissions a user has.

Commented out at the end of the script are examples of the types of permission you can assign, again providing the login you are using has adequate permissions.

SELECT ClassDescription
	,StateDescription
	,PermissionName
	,SchemaName
	,UserName
FROM (
	SELECT class_desc AS ClassDescription
		,state_desc AS StateDescription
		,permission_name AS PermissionName
		,SCHEMA_NAME(major_id) AS SchemaName
		,USER_NAME(grantee_principal_id) AS UserName
	FROM sys.database_permissions AS PERM
	JOIN sys.database_principals AS Prin ON PERM.major_ID = Prin.principal_id
		AND class_desc = 'SCHEMA'
	) AS schemaPermissions
WHERE 1=1 
/*Uncomment below to check permissions on a specific schema and/or specific user*/
--	AND SchemaName = 'dbo'
--	AND UserName = 'SomeGuy'
ORDER BY UserName ASC
,SchemaName ASC
GO



/*
--Grant schema permission examples
GRANT SELECT ON SCHEMA::dbo TO SomeGuy;
GRANT UPDATE ON SCHEMA::dbo TO SomeGuy;
GRANT ALTER ON SCHEMA::dbo TO SomeGuy;
GRANT DELETE ON SCHEMA::dbo TO SomeGuy;
*/

How to update/replace or remove an email address from all SSRS subscriptions with T-SQL

Database Administrator, dba, SQL Server, SSRS, SSRS Reporting, T-SQL, tsql, , , , , , , , , , ,

Updating/replacing or removing an email address from SSRS subscriptions manually is far too time consuming and tedious. Use the below script instead to either update/replace an email address with a new one across all subscriptions or remove the email address from all subscriptions.

To exclude subscriptions, i.e. keep the email address active for a particular subscription, find the subscription Id for that subscription and include it in the WHERE clause. Remember to uncomment that line in order for the clause to be active.

If you’re worried about messing anything up then back up the table before running the script!

Create backup:

SELECT *
INTO [dbo].[Subscriptions_bk]
FROM [dbo].[Subscriptions]

Update/replace or remove an email address:

/*
To replace an email address with another email address:
Find and replace the following email addresses (Ctrl+H) with the new email address*/
/*
Email address to update/replace:
replaceSomeGuy@someCompany.com

Email address replacement:
newGuy@someCompany.com
*/
UPDATE [dbo].[Subscriptions]
SET ExtensionSettings = REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'replaceSomeGuy@someCompany.com', 'newGuy@someCompany.com')
WHERE CHARINDEX('replaceSomeGuy@someCompany.com', ExtensionSettings) <> 0
--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
;

/*
To remove an email address:
NOTE:Run both of the following scripts as an email address may or may not end with ";"

Find and replace the following email address (Ctrl+H)
*/
/*
Email address to remove
removeSomeGuy@otherCompany.com
*/
UPDATE [dbo].[Subscriptions]
SET ExtensionSettings = REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'removeSomeGuy@otherCompany.com;', '')
WHERE CHARINDEX('removeSomeGuy@otherCompany.com;', ExtensionSettings) <> 0
--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
;


UPDATE [dbo].[Subscriptions]
SET ExtensionSettings = REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'removeSomeGuy@otherCompany.com', '')
WHERE CHARINDEX('removeSomeGuy@otherCompany.com', ExtensionSettings) <> 0
--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
;

How to fix a date field with a format of dd/MM/yy or dd-MM-yy with a Azure Data Factory expression

Azure, Data Factory, , , , , , , , , , ,

Some systems never heard of Y2K.

Many programs in operation today, terrible programs written by lazy developers, still represent four-digit years with only the final two digits, making the year 2000 indistinguishable from 1900.

If you’re consuming data from a source system using an incomplete date format, and you’re doing your job properly, you’ll want to correct for that.

Below is an ADF expression example that will correct date field values that relate to the year 2000 onward by prefixing 20 to the year, e.g. 21 becomes 2021. 

iif(
    like(YOUR_DATE_FIELD, "%-%")==true()
    , toDate(concat(left(CHAR_TRX_DATE, 5), '-20', right(CHAR_TRX_DATE, 2)), 'dd-MM-yyyy')
    , toDate(concat(left(CHAR_TRX_DATE, 5), '/20', right(CHAR_TRX_DATE, 2)), 'dd/MM/yyyy')
	)

How to fix an Azure Data Factory Pull Request Merge Conflict

Azure, , , , , , , , , , , , ,

Typically most pipeline development use cases can be handled directly within Data Factory through the Azure Web Portal. However where the line can get blurred sometimes between working in the cloud and working locally is with DevOps GIT.

If a GIT based deployment gets tangled there is an expectation you will be able to work through the desktop interface for GIT or worse fall back to using command line.

This is necessary because before a GIT pull request can complete, any conflicts with the target branch must be resolved and this usually involves issuing a few commands to put the matter right. The options for resolving conflicts through the web portal by default are limited to nonexistent which is at odds with the very high level, low code approach of developing pipelines in Data Factory.

Luckily if a merge conflict occurs there is an extension you can try.

https://marketplace.visualstudio.com/items?itemName=ms-devlabs.conflicts-tab

A conflict might occur because the master branch is no longer in sync with the development branch for example i.e. the master branch was changed after a development branch was created from it. When a pull request is created this may throw a merge conflict error blocking the merge from proceeding. Without resorting to code the extension above will allow you to choose between the source and target branch and specify which has the correct file version.

How to sum time with T-SQL

sql, SQL Server, T-SQL, tsql, , , , , , , , , , ,

Time cannot be summed directly in T-SQL. In order to sum two times they first need to be assigned a date. When a time data type is cast as a datetime data type, as it does not have a date element, the value defaults to the date of 1900-01-01.

As T-SQL does have the functionality to sum datetime and as the date element will be the same only the time value will be summed. This functionality allows us to sum time.

Below is example T-SQL:

IF OBJECT_ID('tempdb..#TimeTable', 'U') IS NOT NULL
BEGIN
DROP TABLE #TimeTable
END

CREATE TABLE #TimeTable(
	id INT
	,TimeRecord TIME(0)
	);

INSERT INTO #TimeTable
VALUES (
	1
	,'00:00:10'
	);

INSERT INTO #TimeTable
VALUES (
	1
	,'00:14:00'
	);

INSERT INTO #TimeTable
VALUES (
	2
	,'00:00:10'
	);

INSERT INTO #TimeTable
VALUES (
	2
	,'00:35:10'
	);

SELECT id
,TimeRecord
FROM #TimeTable;

/*demo of time converted to datetime*/
SELECT CAST(TimeRecord AS DATETIME) AS DateTimeRecord
FROM #TimeTable

SELECT id
	,CAST(DATEADD(MILLISECOND, SUM(DATEDIFF(MILLISECOND, 0, CAST(TimeRecord AS DATETIME))), 0) AS TIME(0)) AS SummedTime
FROM #TimeTable
GROUP BY id;

How to get a substring between two characters with T-SQL

Data, sql, SQL Server, T-SQL, tsql, , , , , , , , ,

This is a very common activity in the data world, i.e. there’s some data in a text string you need and the rest of the data in the string is just in your way. Some use cases might be you have a reference in a filename you need to extract, or you may need a snippet of data to create a composite key, or there’s an order number surrounded by other data that is not relevant to your needs etc.

The following is some simple T-SQL that will extract the data you want from a text string providing the data has specific delimiting characters on each side of it.

/*Delimiter variables, first and second position*/
DECLARE @dfp AS CHAR(1);
DECLARE @dsp AS CHAR(1);
DECLARE @text VARCHAR(MAX);

SET @dfp = ';';
SET @dsp = '@';
SET @text = 'I want you to ;Extract this@ substring for me please.';

SELECT SUBSTRING(@text, (CHARINDEX(@dfp, @text) + 1), (CHARINDEX(@dsp, @text) - 2) - CHARINDEX(@dfp, @text) + Len(@dsp))

How to pick random numbers between two numbers with T-SQL

tsql, , , , , , , , , , , ,

Below is a T-SQL example that will pick a random number between 1 and 50. 

SELECT CAST(RAND() * (51 - 1) + 1 AS INT) AS Random#

That’s a bit boring though.
What about parameters?
What about a use case?
Where’s the familiar glamour of coding with T-SQL?

I hear ya.

Below is a T-SQL example that could make you a multimillionaire!

This T-SQL code will pick random numbers for the Euromillions lottery.

Good luck.

DECLARE @a INT;
DECLARE @b INT;
DECLARE @count INT;
DECLARE @pick INT;

DROP TABLE IF EXISTS #Num;

CREATE TABLE #Num (
	Number INT
	,NumberType VARCHAR(255)
	);

SET @a = 1
SET @b = 51
SET @count = 1

WHILE @count < 6
BEGIN
	SET @pick = CAST(RAND() * (@b - @a) + @a AS INT)

	IF (
			SELECT Number
			FROM #Num
			WHERE Number = @pick
			AND NumberType = 'Main'
			) IS NULL
	BEGIN
		INSERT INTO #Num (
			Number
			,NumberType
			)
		SELECT @pick
			,'Main';

		SET @count = @count + 1
	END
END

SET @a = 1
SET @b = 13
SET @count = 1

WHILE @count < 3
BEGIN
	SET @pick = CAST(RAND() * (@b - @a) + @a AS INT)

	IF (
			SELECT Number
			FROM #Num
			WHERE Number = @pick
			AND NumberType = 'Lucky'
			) IS NULL
	BEGIN
		INSERT INTO #Num (
			Number
			,NumberType
			)
		SELECT @pick
			,'Lucky';

		SET @count = @count + 1
	END
END

SELECT Number
	,NumberType
FROM #Num
ORDER BY NumberType DESC
,Number ASC;

If you found this post helpful please like/share/subscribe.


How to fix Azure Get Metadata Child items missing from Field List

Azure, , , , , , , , , ,

If you cannot find Child items as an option in the Field list for Azure’s Data Factory Get Metadata check the source Dataset.

Using dynamic content for the file path might resolve this problem.

Keeping the parent directory as a static hard coded string will work but the second and third option boxes should contain:

@dataset().SourceFolder
@dataset().FileName

SourceFolder and FileName will need to be dataset parameters of type string.

How to fix an audio echo problem with bluetooth headphones on Windows 10

Audio, Hardware, IT, OS, Windows Administration, , , , , , , , ,

I recently got shipped a new bluetooth headset for work. The headset in question was the MPOW HC5 headset. They are very comfortable and have good audio quality but when they first arrived there was a problem. The audio had a persistent echo.

The echo was isolated to the headset as there was no echo when the audio was coming through the laptop speakers.

The echo was also isolated to Windows 10 as there was no echo when the headset was plugged into an Android device.

At first I thought the problem was caused by the Windows 10 audio enhancements settings. These settings include an option for echo. However in this instance this was not the cause of the problem.

To rule out Windows 10 audio enhancements as the cause of the problem do the following:

Control Panel > Hardware and Sound > Sound > Left click on Speakers (or another output device of your choosing) > Properties > Enhancements > Check “Disable all sound effects”.

In this particular case the echo problem for my headset was that playback was happening twice for the same device albeit slightly out of sync thus creating an echo. The problem was identifiable as when I traversed to the option path below I could see that the headset was registered with the OS twice under two slightly different names.

Options path:

Control Panel > Hardware and Sound > Sound > Playback

I tested playback for both device names, by right clicking the device and clicking test, and found that one of the devices had a audio glitch. When this instance of the device was disabled the headphones worked without any echo.

If you found this post helpful please like/share/subscribe.