Monday, August 14, 2017

Is SVG a part of HTML5?

SVG is variously described as  'part of HTML5 specification' or an HTML5 extension. In either case, I am very happy as I am a fan of SVG.

In HTML documents you could embed an in-line SVG code.
SVG in some cases appear superior to Canvas in HTML. SVG creates prefabricated graphics that is part of the documnet model which can be accessed by the ID for any event based action, where as the graphics for use in CANVAS needs to be created and rendered during page loading.

Read my SVG related posts here:

Saturday, August 12, 2017

How do you enable database mail in SQL Server 2016?

For security reasons this feature is turned off and you must enable it.

In a newly installed server if you run stored procedure in the context of msdb

EXEC sp_send_dbmail

You get the following message:

'Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0 [Batch Start Line 9]
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs'
because this component is turned off as part of the security configuration for this server.
A system administrator can enable the use of 'Database Mail XPs' by using sp_configure.
For more information about enabling 'Database Mail XPs', search for 'Database Mail XPs' in
SQL Server Books Online.


This is how you enable Database Mail extended Procedure:
sp_configure 'show advanced options', 1;
sp_configure 'Database Mail XPs', 1;



Now run this statement.

EXECUTE msdb.dbo.sysmail_help_status_sp

You get this response:


You will find the dbo.sp_send_dbmail stored procedure in the msdb database.


You will find all these objects in the msdb database in the Stored Procedures and Views nodes.


Thursday, August 10, 2017

How do I keep my email addresses and passwords encrypted using SQL Server?

You could keep your email addresses and passwords in SQL Server database table. You could get a free copy of your SQL Server from a Microsoft download site.

While the email addresses can be kept un-encrypted, you may want to keep your passwords in an encrypted column of a table. If want to keep everything encrypted you can do it also.

With this table set up correctly you may need to remember just one phrase which can be of your choosing.

Here are steps to create such a table:

1. Create a table in one of your existing databases. I am creating this table in a database called 'Aloha' which I use for teaching.

Here is the statement to create the table:

USE Aloha
Create Table Encrpt(
emailID int Primary Key not null,
emailAdd nvarchar(25) null,
emailPswd varbinary(250) null

This creates the table ENCRPT.

The email password (emailPswd) is stored in a special column(varbinary data type) as shown above.

Now keep a list of emails and the passwords ready to populate the table.

Populate the table you created by inserting values using the following code:

INSERT INTO ENCRPT(emailID, EmailAdd, emailPswd)
values (1, '', EncryptByPassPhrase('Happy birthday to you','xyzpo')),
(2, '',EncryptByPassPhrase('Happy birthday to you ','ZZZ12$' )),
(3, '',EncryptByPassPhrase('Happy birthday to you','$$$11X' ))

I have inserted only three values but you could add more. While the email addresses shown are my real addresses, the passwords are not.

Now when you run a SELECT statement to display all the columns, this is what you see.

Here is a screen shot of all code:

I have used the same PassPhrase for all data, you could have different on, the onus of remembering phrases is on you.

The fourth row was filled using a different PassPhrase ('Happy Anniversary).
Read more here:

Tuesday, August 8, 2017

How do you launch Excel from 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.

Tuesday, August 1, 2017

What is the difference between dbo and db_owner?

This question pops up now and then.

dbo is a user and db_owner is a database role. They both are in the Security node in the Object Browser. The SQL Server Management Studio (SSMS) makes it abundantly clear.





It will be instructive to review their properties as shown here.

dbo - Properties


db_owner - Properties


Saturday, July 29, 2017

What is two digit year cutoff in SQL Server?

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 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:

What is mssql-scripter?

In your SSMS on your computer you can use the Generate Scripts drop-down
menu to create scripts for database objects as shown here for Northwind database on SQL Server 2016 SP1.

However, the above is for SQL Server on Windows Platform. However, mssql-scripter tool provide the same functionality as Generate Scripts wizard on SQL Servers on Linux and macOS. Of course mssql-scripter would work on Windows as well.

Using mssql-scripter based on Python you can generate T-SQL scripts for objects on SQL Servers, Azure SQL database and Azure SQL Data Warehouse.

The generated script is saved to a .sql file or, on Linux can be piped to standard Unix utilities (sed, awk and grep ). The scripts can be checked into source control systems as well.

The source code for mssq-scripter is found here: