My notes, things I have learned, and documentation I have created.

Install the Microsoft repository into your yum configuration:

# curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

Disable the repository:

vi /etc/yum.repos.d/mssql-release.repo
…
enabled=0
…

Remove the unixODBC packages if applicable:

# yum remove unixODBC-utf16 unixODBC-utf16-devel

Install the driver and command line tools (if wanted):

# yum –enablerepo packages-microsoft-com-prod install msodbcsql17
# yum –enablerepo packages-microsoft-com-prod install mssql-tools

Add the tools directory to your PATH variable as required:

vi ~/.bash_profile ~/.bashrc
…
export PATH=”$PATH:/opt/mssql-tools/bin”
..

Add the tools to your current session:

export PATH=”$PATH:/opt/mssql-tools/bin”

Test with sqlcmd:

sqlcmd -U username -P password -S server -d database

I did a little exercise to figure out a way to decode the encoded subject of some email messages.

Here is the decoded subject I was using:

Alaska is cool, go whale watching and glacier gazing (from 75%-off)

Here is the fully encoded subject of that message:

=?UTF-8?B?QWxhc2thIGlzIGNvb2wsIGdvIHdoYWxlIHdhdGNoaW5nIGFuZCBnbGFjaWVyIGdhemluZyAoZnJvbSA3NSUtb2ZmKQ==?=

Here is the command I used to decode the string:

$ echo QWxhc2thIGlzIGNvb2wsIGdvIHdoYWxlIHdhdGNoaW5nIGFuZCBnbGFjaWVyIGdhemluZyAoZnJvbSA3NSUtb2ZmKQ== | base64 -d
Alaska is cool, go whale watching and glacier gazing (from 75%-off)

Note: the leading and trailing did need to be stripped off for this to work successfully. It is perfectly within the standards to encode the email subjects like this. Unfortunately, spammers have known this for many years.

To determine a view definition:

SELECT DEFINITION FROM sys.sql_modules WHERE object_id = OBJECT_ID(‘viewschema.viewname‘)

To list all the tables in a database:

SELECT * FROM INFORMATION_SCHEMA.TABLES

To list all the columns and tables in a database:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

OUTER JOIN example:

SELECT xx.column1, yy.column2
FROM table1 xx
LEFT OUTER JOIN DB2.schema1.table2 yy ON xx.column1 = yy.column2

Change the time format:
Some CONVERT statements to the time format from DATETIME to something else:

DECLARE @SOMETIME DATETIME
SET @SOMETIME = ‘2020-01-31 11:00:00 AM’
SELECT CONVERT(VARCHAR(20), @SOMETIME, 22)
01/31/20 11:00:00 AM

This one has the four digit year:

DECLARE @SOMETIME DATETIME
SET @SOMETIME = ‘2020-01-31 11:00:00 AM’
SELECT CONVERT(VARCHAR(20), DATEADD(Hour, -8, @RECEIPTDATE), 101) + ‘ ‘ + CONVERT(VARCHAR(20), DATEADD(Hour, -8, @RECEIPTDATE), 8)
01/31/2020 11:00:00

Selecting records based on time example:
This will returns records (in this case just a bunch of time variables), if the the @SOMEDATETIME variable is between now and one hour ago:

DECLARE @SOMEDATETIME DATETIME
DECLARE @CURRENTDATETIME DATETIME
DECLARE @CURRENTDATETIMEMINUS DATETIME
SET @SOMEDATETIME = ‘2020-01-31 15:30:00.000’
SET @CURRENTDATETIME = CONVERT(VARCHAR(20), GETDATE(), 101) + ‘ ‘ + CONVERT(VARCHAR(20), GETDATE(), 8)
SET @CURRENTDATETIMEMINUS = CONVERT(VARCHAR(20), DATEADD(Hour, -1, @CURRENTDATETIME), 101) + ‘ ‘ + CONVERT(VARCHAR(20), DATEADD(Hour, -1, @CURRENTDATETIME), 8)

SELECT @SOMEDATETIME AS SOMEDATETIME
, @CURRENTDATETIMEMINUS AS CURRENTDATETIMEMINUS
, @CURRENTDATETIME AS CURRENTDATETIME
WHERE @SOMEDATETIME
BETWEEN @CURRENTDATETIMEMINUS AND @CURRENTDATETIME