Tag Archives: lesson

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 write T-SQL Geography data to a table

Database Administrator, dba, How To, IT, sql, SQL Server, T-SQL, tsql, , , , , , , , , , , , , ,

Below is some example code for writing the SQL Server geography data type to a table. Note by default geography data is stored in a binary format but it can be converted to a string to make it human readable.

Note: Pass in Longitude and Latitude values in that order.

/*Demo of geo data*/
DECLARE @g GEOGRAPHY;

SET @g = GEOGRAPHY::STPointFromText('POINT(53.578741 -6.611670)', 4326);

/*Geography data is in binary format*/
SELECT @g AS 'GeoBinaryFormat';

/*Convert binary data to a string*/
SELECT @g.ToString() AS 'ConvertingDataToString';


/*Inserting geo data into Table*/
CREATE TABLE #GeoTest ([CoordinateLocation] [geography] NULL);

INSERT INTO #GeoTest (CoordinateLocation)
SELECT GEOGRAPHY::STPointFromText('POINT(53.578741 -6.611670)', 4326);

SELECT *
FROM #GeoTest;

DROP TABLE #GeoTest;

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.

An icon depicting a calendar and clock

How to format SQL Server datetime as dd/mm/yyyy hh:mm:ss

Coding, Data, Database Administrator, Development, How To, sql, SQL Server, T-SQL, , , , , , , , , ,

If you are exporting the results of a SQL Server query to excel typically the recipient of the file wants the dates referenced in the format “dd/mm/yyyy hh:mm:ss” or “dd/mm/yyyy” not in the usual database format yyyy-mm-dd.

The below query formats the datetime as desired. Note that the letter m representing month is capitalised. If they are not the engine will interpret the lowercase letter m as minute so you will end up with days, minutes, years.

Also not that the letter h representing the hours is also capitalised. Capitalising the h makes the time output with the 24 hour format. Lowercase h will be 12 hour format. It is highly recommended not to use the lowercase h.

SELECT FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss', 'en-us')

If you only want the date and not time just remove the relevant text, i.e. just date dd/MM/yyyy or datetime without second dd/MM/yyyy HH:mm.

Icon for Raspberry Pi

How to remote into a Raspberry Pi running Raspbian OS from Windows

Raspberry Pi, , , , , , , , , , , , , , , , ,

There are a lot of tutorials out there explaining how to remote into a Raspberry Pi. Unfortunately a lot of them ignore that this functionality comes baked into even the slimmed downed version of Raspbian. Worse still a lot of them just plain do not work! This article demonstrates how to actually remote into Raspian from Windows and you do not need to write a single line of code as everything can be done through the UI. That is of course assuming you are not a masochist and actually installed the UI.

(FYI these instructions are for connecting over the same network)

Virtual Network Computing

You will gain remote access and control of the Pi using technology called VNC. In computing, Virtual Network Computing is a graphical desktop-sharing system that uses the Remote Frame Buffer protocol to remotely control another computer.

To do this you will need to install a VNC viewer software on your Windows 10 PC. Microsoft’s own Remote Desktop Connection software can be a bit temperamental so it is recommended you use the free and very light software “VNC Viewer”. You will not need to install a VNC Server on the Pi as it is already preinstalled.

Setting up the Windows PC

VNC Viewer can be downloaded for free from RealVNC at the following link, or you can search for it in your web browser of choice if you would prefer. Once you have it installed you will need to set up the Pi to receive VNC connections.

Setting up the Pi

To enable the VNC connections follow these steps.

On the Pi go to the Application Menu, the Raspberry Icon to the top left of Home screen.

Preferences > Raspberry Pi Configuration > Interfaces > then enable the VNC option.

Next you will need to enable the VNC Server to display in the Application Menu which you can do by follow these steps.

From Preferences in the Application Menu go:

Preferences > Main Menu Editor > Other > then enable VNC Server.

Then click okay to apply the setting.

Now when you go to the Application Menu (Raspberry Icon on Home screen) you will be able to access the VNC Server application via the category “Other”.

Remoting into the Pi from Windows

After opening the application you should see under “Connectivity” the IP address that can be used for other computers on the same network to connect to the Raspberry Pi. (If an IP address is not displayed make sure your WiFi is on)

Jump over to your Windows 10 PC and open the VNC Viewer app.

Type the IP address displayed by the VNC Server into the Address bar.

When prompted enter the username and password you use to access the Raspberry Pi (possibly pi and raspberry). You should now have remote access to the Pi.

The best part is on reboot the VNC Server should start automatically.

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

An icon of a headset with a spanner meaning sound settings

How to adjust your Windows 10 volume settings so virtual meeting participants are at the same volume level

System, Windows Administration, , , , , , , , , , , , , , , , ,

You have probably experienced a virtual meeting or online lecture were you could barely hear a person when they spoke. It might have been down to their audio setup, e.g. they were not using a headset and were relying on their laptop built-in microphone, or they might just be especially quiet when they talk. So in an effort to hear them better you maxed out the volume on your PC only for someone else in the chat to chime in with an excessively loud question or comment. If you were wearing a headset in that situation you might have ended up with burst eardrums. Thankfully there is something you can do on your end to normalize the volume of the meeting.

(FYI it is not recommended you test this for the first time during an important business meeting. Sound card drivers etc. can be a bit unpredictable and can even behave differently depending on what other applications you may have open at the time.)

What is Audio Normalization

Audio normalization is a process that increases the audio level by a constant amount so that it reaches a target or norm. Normalization applies the same level increase for the entire duration of the audio stream.

You are probably thinking at this point that if normalization brings up the volume of the quiet person wouldn’t it also make the loud person louder?

That is not the case. It averages out the loudness by leveling the audio output. So when the louder person interjects they should be at a similar volume to the person speaking quietly.

Normalizing the meeting volume

This is achieved through Windows 10 Sound settings and a feature called “Loudness equalization” and there are two ways to turn this feature on.

The first way is via the search bar at the bottom of your display.

Type “Sound settings”

Click on the option that appears.

When the Sound settings window opens look for “Device Properties” under “Output”, i.e. speakers/headphones etc.

In “Device Properties” click on “Additional device properties” to the right hand side of the window.

Open the “Enhancements” tab.

Make sure “Immediate mode” is ticked then scroll down to “Loudness equalization”.

Once the “Loudness equalization” box has been ticked you should hear that the volume of people speaking quietly has been increased. This means you will be able to lower down the overall system volume and when other people speak you should be able to hear them also without it being deafening.

The other way to get to this feature is the old fashion way via Control Panel.

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

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