Enabling FILESTREAM on an instance of SQL Server Database Engine

You could configure FILESTREAM at installation time as shown here while configuring the Database engine for a SQL Server 2012 Express named instance.

FileStream_00

At the highlevel you need the Transact-SQL Access and if you need the file I/O access you need to enable it and provide a Windows share name as shown. If remote client access to FileSTREAM data is required you need to check this as well.

You could check whether FILESTREAM is enabled or not, by reviewing the instance properties in SQL Server Configuration Manager on the SQL Server Instance as shown for the instance OHANA.

The file share location is accessed by the UNC name as shown:

If FILESTREAM is not enabled you can enable in the Configuration Manager.

Decrypting cypherText encrypted using EncryptByPassPhrase funtion

We saw in the previous post how to encrypt using the EncryptByPassPhrase() function.

We will use the same encrypted text and decrypt it using the same PassPhrase used to create it.

We use the reverse function DecryptByPassPhrase() which takes two arguments, the first is the PassPhrase and the second is the encrypted value (cyphertext). The PassPhrase generates the key for decryption.

MSSS2017\ENCRPTPSWD_04.pn

Hands-on learning in Honolulu: COM2037 - Introduction to Structured Query Language (SQL)

The next session will be starting October 12, 2017. Register early.

Explore funding opportunity here:

Encrypting email password in SQL Server

There is a simple but not most secure way of encrypting a password in SQL Server. It uses the T-SQL function EncryptByPassPhrase().

This function takes two arguments: PassPhrase and ClearText. You provide both the PassPhrase and the ClearText(this is your email password that needs to be encrypted using this function). The PassPhrase generates a TripleDes algorithm with a 128 Key bit length.

ClearText can be a variable of the following types:
nvarchar,
char,
varchar,
binary,
varbinary,
or nchar

PassPhrase can be of the following types:
nvarchar,
char,
varchar,
binary,
varbinary,
or nchar

The following code uses the following variables:
PassPhrase -> "Happy birthday to you"
ClearText->   "$#MyPassword#$"
---------
CREATE TABLE ENCRPTPSWD (EncryptedText varbinary(200))
go
declare @pswrd nvarchar(15)
set @pswrd = '$#MyPassword#$'--ClearText
INSERT INTO ENCRPTPSWD (EncryptedText)
values (EncryptByPassPhrase('Happy birthday to you', @pswrd))
go

The column ENCRPTPSWD contains the result of encrypting and is therefore of type varbinary. The phrase to de-encrypt is the phrase 'Happy birth day to you'.

Now if we access that column we see the encrypted value as shown.

SQL Server and two-digit year cut-off

Remember the millenium bug fiasco. It happenned in Year 2000.
This is what chronicled in Wikipedia:

"The Year 2000 problem is also known as the Y2K problem, the Millennium bug, the Y2K bug, or Y2K. Problems resulted because people, including programmers, reduced the four-digit year to two digits. This made the year 2000 indistinguishable from 1900"

Now we have a better interpretation for people using two digit years as far as SQL Server is concerned.

The default time span for two digit years in SQL Server is 1950-2049.

Two-digit year 49 is 2049, but two digit year 50 is back to 1950.

Probably you will start seeing it in your credit cards (may be?)

You may configure it in SQL Server 2017 using,

Exec Sp_Configure 'two digit year cutoff', 2038

In SQL Server 2012 Express it is enabled by default:

Read more here:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-two-digit-year-cutoff-server-configuration-option

Adding Excel Application to SQL Server Management Studio

I am using SQL Server Management Studio Version 17.1. You may user another version.

Launch SSMS 17.1

Click Tools | External Tools...

External Tools window is displayed as shown.

Replace the Title with a name of your own, herein Excel Application. The menu contents also
changes to the one you provided, herein Excel Application.

You need to provide the location of the executable in the Command box. In the present case the
executable is here:

"C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE"

Now you External Tools window should appear as shown.

Click OK.

Now you will find 'Excel Application' as shown in the Tools menu.

Now when you click this menu item, Excel Application is launched.

Usage of math operator Modulo in SQL Server

x%y is defined as the remainder of the division x/y. You can use modulo to find the minutes and hours in so many minutes.

Let us say we have a cconversion exercise to convert 123490 minutes into minutes and hours.
By long hand we would do this:

123490/60 and find the remainder
We get 2058.1666666667 with 0.1666666667 hours converts to 10
That is 2058 hours and 10 minutes.

We can do the same using modulo as shown.

OR

You can use a much shorter code as shown here: (note input data has changed to 1234901)