kevin

SQL Server 2022 Transaction Log Autogrowth Setting

This should be set to 64MB, ref: link

“In general, transaction log files cannot benefit from instant file initialization (IFI). Starting with SQL Server 2022 (16.x) (all editions) and in Azure SQL Database, instant file initialization can benefit transaction log growth events up to 64 MB. The default auto growth size increment for new databases is 64 MB. Transaction log file autogrowth events larger than 64 MB cannot benefit from instant file initialization.“

Email blocked due to attachment content or type

If you find that an email attachment is blocked due to its content or type, which is very common when attempting to send some code or a zip file, the following PowerShell may help:

#create file to send $source_zip = 'C:\temp\yourfile.zip' $file_to_send = 'C:\temp\yourfile.txt' [convert]::ToBase64String((Get-Content -path $source_zip -Encoding byte)) | Out-File -FilePath $file_to_send #extract zip $file_received = 'C:\temp\yourfile.txt' $extract_zip = 'C:\temp\yourfile.zip' [System.Convert]::FromBase64String((Get-Content $file_received)) | Set-Content $extract_zip -Encoding Byte

Extracting Data Between Characters

In these examples I’ll use \ as the character.

/*test table*/
DECLARE @t1 TABLE (c1 nvarchar(MAX));
INSERT INTO @t1(c1) VALUES ('D:\MSSQL15.MSSQLSERVER\MSSQL\DATA\tempdb.mdf');

/*data after first \ */
SELECT RIGHT(c1, LEN(c1) - 
       CHARINDEX('\',(c1))
	   ) AS after_first
FROM @t1;

/*data after second \ */
SELECT RIGHT(c1, LEN(c1) - 
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1, 1)+1)
	 )
	 ) AS after_second
FROM @t1;

/*data after third \ */
SELECT RIGHT(c1, LEN(c1) - 
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1))
	 )
	 ) AS after_third
FROM @t1;

/*data after fourth \ */
SELECT RIGHT(c1, LEN(c1) - 
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1)))
	 )
	 ) AS after_fourth
FROM @t1;

/*data after last \ */
SELECT RIGHT(c1, CHARINDEX('\',REVERSE(c1))-1) AS after_last
FROM @t1;

/*data before last \ */
SELECT LEFT(c1, LEN(c1) - CHARINDEX('\',REVERSE(c1))) AS before_last
FROM @t1;

/*data before second last \ */
SELECT LEFT(c1,LEN(c1) - CHARINDEX('\', REVERSE(c1), CHARINDEX('\', REVERSE(c1), 0)+1)) AS before_second_last
FROM @t1;

/* data before fourth \ */
SELECT LEFT(c1,  
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1)))
	 ) -1
	 ) AS before_fourth
FROM @t1;

/*data before third \ */
SELECT LEFT(c1, 
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1))
	 ) -1
	 ) AS before_third
FROM @t1;

/*data before second \ */
SELECT LEFT(c1,
     (CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))
	 +CHARINDEX('\', c1, 1)+1)
	 ) -1
	 ) AS before_second
FROM @t1;

/*data before first \ */
SELECT LEFT(c1, 
       CHARINDEX('\',(c1)) -1
	   ) AS before_first 
FROM @t1;

/*between first and second*/
SELECT SUBSTRING(c1
,CHARINDEX('\',(c1))+1
,(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1, 1)) -1) -
CHARINDEX('\',(c1))
) AS between_first_and_second
FROM @t1;

/*between second and third*/
SELECT SUBSTRING(c1
,(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1, 1)) +1)
,(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1)) - 
(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1, 1)) +1))
) AS between_second_and_third
FROM @t1;

/*between third and fourth*/
SELECT SUBSTRING(c1
,(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+1)) +1)
,(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1,(CHARINDEX('\', c1, 1)))))-2) -
(CHARINDEX('\', c1,(CHARINDEX('\', c1, 1))+CHARINDEX('\', c1,(CHARINDEX('\', c1, 1)))))
) AS between_third_and_fourth
FROM @t1;

Power BI Conditional Formatting Greyed Out

If like me you find that the default conditional formatting options are greyed out, you can try resolve as follows:

You Should Be Able To Change & Delete Any Remaining Unused Rules.

Power BI Desktop PDF Export Notes

Exporting to a PDF can be a bit tricky when using Power BI Desktop. The following may make your task a bit easier:

The resolution of exported report pages is 1280 pixels x 720 pixels ref: https://docs.microsoft.com/en-us/power-bi/consumer/end-user-pdf?tabs=powerbi-desktop
Most important: Ensure all pages are using the Page view mode of “Fit to page” before you attempt an export.

Obfuscate File Data

If you need to copy a file but don’t want the data contained to be easily read and encryption/3rd party applications aren’t available, this PowerShell solution may help.

Here I have a file with some data I would like to obfuscate.

Below is the PowerShell steps which:

Prepares some variable to point to the 1.txt file and other files which do not exist
Here we simply convert the text file to Base64 so it is unreadable to humans and create a file called obfuscatedwithaddeddata
We add a salt of 6 (this could be anything and anywhere in the file, but used here for simplicity) to the end of the file text. This essentially corrupts the file.
Here is an attempt to try convert from Base64 to text and it will fail
Here we remove the 6 (salt removed) and save it as a file called obfuscatedwithoutaddeddata
Finally we can convert the file obfuscatedwithoutaddeddata to file called 2.txt

All these files were created here so you can review the contents through the process.

#1. files
$source_file = “C:\temp\obfuscate\1.txt”
$obfuscated_file = “C:\temp\obfuscate\obfuscatedwithaddeddata”
$unobfuscated_file = “C:\temp\obfuscate\obfuscatedwithoutaddeddata”
$dest_file = “C:\temp\obfuscate\2.txt”

#2. text -> Base64
[io.file]::WriteAllText($obfuscated_file, [Convert]::ToBase64String([io.file]::ReadAllBytes($source_file)))

#3. obfuscate by adding 6 to end of file
Add-Content -Path $obfuscated_file -Value 6

#4. attempt to reverse
# Base64 -> text
[io.file]::WriteAllBytes($dest_file, [System.Convert]::FromBase64String([io.file]::ReadAllText($obfuscated_file)))

#5. remove 6
$unobfuscated_data = Get-Content $obfuscated_file
$unobfuscated_data = $unobfuscated_data.Substring(0,$unobfuscated_data.Length-1)
Set-Content -Path $unobfuscated_file -Value $unobfuscated_data

#6. Base64 -> text
[io.file]::WriteAllBytes($dest_file, [System.Convert]::FromBase64String([io.file]::ReadAllText($unobfuscated_file)))

Generate QR code for Website

The Edge browser has a nice feature where you can create a QR code for any website.
Simply navigate to the page -> select the URL area -> select the shown Icon.

GitLab Default Branch Missing

If you create a GitLab project and it has no branches, you can try use the below to add one.

Notes:

This is run from a Windows cmd prompt.
The default branch preference is to now call it main rather than master.

c:
cd \
md temp
cd temp
md gitlab
cd gitlab
git clone https://..../yourgitlabproject
cd yourgitlabproject
echo readme > README.md
git add .
git checkout -b main
git update-ref HEAD main
git push --set-upstream origin main

Replacement for sys.fn_varbintohexstr

If you are on SQL Server 2005, probably just continue to use it.

If you are on a newer version of SQL Server you can use CONVERT(VARCHAR(MAX), [binarydata], 1) e.g.

SELECT
 sys.fn_varbintohexstr([sid]) AS old_way
,CONVERT(VARCHAR(MAX), [sid], 1) AS new_way
FROM master.sys.sql_logins;

Stop Visio Page Moving

If you find that when you use the arrow keys in Visio the whole page moves; typically switching off Scroll Lock usually resolves the issue:

AKAWN

Author: kevin