Loading raw text data into SQL Server can be done using Flat File import or Data Imports, but I find this is always problematic as it does row scans to predict data types, but invariably fails on large data sets due to bad data types further down the file, or column sizes being predicted too small. Using BULK INSERT also fails in a similar fashion, and requires setting up column mappings.
I found a great way to do this dynamically using the XML data type - this was not my idea, but unfortunately I lost the link of the page where I found the technique.
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'MyTable'))
BEGIN
DROP TABLE MyTable
END
GO
IF (OBJECT_ID('tempdb..#data') IS NOT NULL) DROP TABLE #data
CREATE TABLE #data (data VARCHAR(MAX))
BULK INSERT #data FROM 'C:\temp\MyDataFile.txt' WITH (FIRSTROW = 2, ROWTERMINATOR = '\n')
IF (OBJECT_ID('tempdb..#dataXml') IS NOT NULL) DROP TABLE #dataXml
CREATE TABLE #dataXml (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, data XML)
INSERT #dataXml (data)
SELECT CAST('<r><d>' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(data, 0x1F, ''), '<', '<'), '>', '>'), '&', '&'), '|', '</d><d>') + '</d></r>' AS XML)
FROM #data
SELECT d.data.value('(/r//d)[1]', 'varchar(max)') AS [Column 1],
d.data.value('(/r//d)[2]', 'varchar(max)') AS [Column 2],
d.data.value('(/r//d)[3]', 'datetime2') AS [Another Date Column] INTO MyTable
FROM #dataXml d
DROP TABLE #data
DROP TABLE #dataXml
SELECT TOP 100 * from MyTable
SELECT COUNT(*) AS [RowCount] FROM MyTable
This allows you to create a completely dynamic table, reading from a pipe-delimited text file. You can tweak the data types declared when you read out of the XML as you learn the data in the file.
This article is my personal summary (for future reference) of Robert Martin's book Clean Code (most of it, anyway).
Foreword
Making your code readable is as important as making it executable.
Chapter 1 – Clean Code
Chapter 2 – Meaningful Names
Chapter 3 – Functions
Chapter 4 – Comments
Chapter 5 – Formatting
Chapter 6 – Objects and Data Structures
Chapter 7 – Exception Handling
Chapter 8 – Boundaries
Chapter 9 – Unit Tests
Chapter 10 – Classes
Chapter 11 – Systems
Chapter 12 – Emergence
Kent Beck's rules of simple design:
Chapter 13 – Concurrency
I use mRemoteNG on a daily basis, but every now and again it crashes, prompting you for a password to load your connections. This is as a result of a corrupt connections files.
Fortunately, the application creates backups of your connections when they change. To fix, navigate to the folder in the error message, in my case this folder is C:\Users\matt\AppData\Roaming\mRemoteNG
In that folder will be a confCons.xml file. This file can be deleted or backed up, and then the last backup can be renamed to confCons.xml. In my case, my last backup file was "confCons.xml.20211006-1534460806.backup" . Restart mRemoteNG, and you should be good to go.
Chrome stores SSL certificate state per host in the browser history, which can become an issue when you generate a new self-signed certifcate and need to refresh it. To clear the browser history:
Clear data
You always want to log errors on your live systems, but sometimes there are errors that occur that you are not interested in and don't want to code around. For example, a client disconnects in the middle of a web request. NLog allows you to add filters to ignore these errors at a logger level. So, you could log all errors to a database AND to email, but specific errors would not get emailed and fill up your mailbox!
<rules>
<logger name="*" minlevel="Error" writeTo="Database" />
<logger name="*" minlevel="Error" writeTo="email">
<filters>
<when condition="contains('${aspnet-request:serverVariable=HTTP_URL}','Token') and contains('${exception:format=Message}','task was canceled')" action="Ignore" />
</filters>
</logger>
At IUA we use Jenkins for CI, and we use Powershell for automation of our builds and deployments. We've always invoked Powershell using batch commands, but I recent implemented the Powershell plugin which makes life a little easier. Some tips on implementation:
Include a file
Prefix the file name with a ". "
. C:\Temp\test.ps1
Use environment variables
When running in the context of Jenkins, you can get and set environment variables. This can be useful when wanting to access a common function used across multiple scripts. For example, if you have many files wanting to use a version number, but the generation of that version number is complex enough to put it in a function, you could move that function to a common file and then store the generated value in an environment variable:
. C:\Temp\version.ps1 $env:version = GetVersion Write-Host $env:version
Run jobs via the Jenkins API
I got this code from Stack overflow (https://stackoverflow.com/questions/46131903/why-is-powershell-not-able-to-send-proper-crumb), but adapted it to include build parameters:
function RunJenkinsJob { param( [string]$userName, [string]$password, [string]$apiUrl, [string]$apiPort, [string]$jobName, [string]$jobParams # url-encoded string of parameters e.g. myparam1=val1&myparam2=anotherval ) $h = @{} $h.Add('Authorization', 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::UTF8.GetBytes("$(${userName}):$(${password})"))) $params = @{uri = "${apiUrl}:${apiPort}/crumbIssuer/api/json"; Method = 'Get'; Headers = $h;} $apiCrumb = Invoke-RestMethod @params $uri = "${apiUrl}:${apiPort}/job/${jobName}" if ([String]::IsNullOrWhiteSpace($jobParams)) { $uri = "$uri/build" } else { $uri = "$uri/buildWithParameters?$jobParams" } $h.Add('Jenkins-Crumb', $apiCrumb.crumb) $params['uri'] = $uri $params['Method'] = 'Post' $params['Headers'] = $h Invoke-RestMethod @params } RunJenkinsJob -userName "jenkins" -password "mypassword" -apiUrl "http://buildserver" -apiPort "8080" -jobName "JobWithParams" -jobParams "PARAM1=value1&PARAM2=value2" RunJenkinsJob -userName "jenkins" -password "mypassword" -apiUrl "http://buildserver" -apiPort "8080" -jobName "JobWithoutParams"
For every web site created in IIS, a new W3SVCX folder is created in the logging folder configured for IIS. Knowing which folder applies to which site is not obvious, particularly when you have multiple sites serving the same content and it's not easy to determine this information from inspecting the log files.
The configuration can be worked out by opening the applicationHost.Config
file in the %WinDir%\System32\Inetsrv\Config
folder.
Under the sites
element, each site is then listed with a unique id, for example:
<site name="mysite.mydomain.co.za" id="6" serverAutoStart="true">
In this case, the log files for this site will be stored in the W3SVC6 folder.
I've been meaning to move to CloudFlare for a while, and thanks to this amazingly helpful page created by Troy Hunt, I've taken the leap.
I've been battling for time, and this really took all the research requirements right out of it, and I'm up and running with SSL even though my host doesn't support it on the cheap hosted package I'm using.
I've been using MediaWiki for ages for technical documentation, and I always forget how to implement custom CSS in both screen and print, so I'm adding this as a permanent reminder for myself.
To edit the CSS for the site (screen version), navigate to "http://YOURMEDIAWIKISITE/index.php?title=MediaWiki:Common.css" - and edit the CSS declared on that page
To edit the CSS for printing, navigate to "http://YOURMEDIAWIKISITE/index.php?title=MediaWiki:Print.css"
Easy!
I needed to move a WordPress site to HTTPS. The certificate was installed, and the site was being correctly served over HTTPS, but I was getting mixed content warnings, as many of the images were being loaded over http. There are many sites that give the full rundown, but nothing worked for me for the Enfold theme.
This site: https://managewp.com/blog/wordpress-ssl-settings-and-how-to-resolve-mixed-content-warnings - can be used to set up HTTPS from start to finish
This site: https://isabelcastillo.com/mysql-wordpress-http-to-https - led me to the solution
Once the site was set up for https, I had to run the following four queries against the WordPress database:
UPDATE wp_posts
SET post_content = ( Replace (post_content, 'src="http://', 'src="//') )
WHERE Instr(post_content, 'jpeg') > 0
OR Instr(post_content, 'jpg') > 0
OR Instr(post_content, 'gif') > 0
OR Instr(post_content, 'png') > 0;
UPDATE wp_posts
SET post_content = ( Replace (post_content, "src='http://", "src='//") )
WHERE Instr(post_content, 'jpeg') > 0
OR Instr(post_content, 'jpg') > 0
OR Instr(post_content, 'gif') > 0
OR Instr(post_content, 'png') > 0;
UPDATE wp_postmeta
SET meta_value = ( Replace (meta_value, 'src="http://', 'src="//') )
WHERE Instr(meta_value, 'jpeg') > 0
OR Instr(meta_value, 'jpg') > 0
OR Instr(meta_value, 'gif') > 0
OR Instr(meta_value, 'png') > 0;
UPDATE wp_postmeta
SET meta_value = ( Replace (meta_value, "src='http://", "src='//") )
WHERE Instr(meta_value, 'jpeg') > 0
OR Instr(meta_value, 'jpg') > 0
OR Instr(meta_value, 'gif') > 0
OR Instr(meta_value, 'png') > 0;