July, 2020

In-Place Upgrade from SQL Server 2014 to 2016

How to Upgrade SQL 2014 to SQL 2016 Mount the SQL 2016 ISO or media on your current SQL 2014 server. Select “Upgrade from a previous version of SQL Server” SSRS 2016 Part of SQL Server 2016 install. Select “Reporting Services – Native” from Instance Features https://social.technet.microsoft.com/wiki/contents/articles/50902.sql-server-2016-install-and-configure-ssrs.aspx https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/upgrade-and-migrate-reporting-services?view=sql-server-ver15 Notes: Backup all databases in case the... » read more

Drop and Recreate database from Always On High Availability From Script

If you need to re-create the database, you have to remove the database from the Always On High Availability Group first before you can drop the database. To recreate the Always On Availability, create a backup of the database on the primary server and then restore the backup to the replica server. Drop Database from... » read more

Always On and TDE Example

Enabling TDE in AlwaysOn setup Create MS SQL master key on primary server and replica server. Password can be the same or different. Create MS SQL certificate on primary server and copy over to replica server. On Replica, restore the certificate. Use script to enable TDE for each database. Make sure the database is fully... » read more

Copy Table using SELECT INTO

Example: Use the following to create a copy of the original table data in the database.

Import Data from Excel

Objective: Import the content of the excel to a table in the production database. First, import the content of the excel file to a staging database, massage the data, and then export the data as a data script. Steps 1. Create new excel file and copy the content to the new excel file. Original excel... » read more

Email SQL Query Result

Enable Failure Email for SQL Agent Jobs

How to Enable Failure Emails for SQL Agent Jobs Expand SQL Server Agent. Expand Jobs. Right click on a Job > Properties. Go to the Notifications tab. Click to enable Email. Select the correct Operator. Choose “When the job fails” Hit OK. Note: Make sure the SQL Server Mail Account and Profile has been setup correctly.

Linked server was unable to begin a distributed transaction.

Error: The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “xxxx” was unable to begin a distributed transaction. Fix: The default settings were not configured correctly to allow communication with the other servers.  These configuration settings are found under the Component Services snapin which can be launched by typing dcomcnfg.msc at a run/command... » read more

Unable to send email from SQL Server

Error: Message The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2020-07-23T17:27:13). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay). ) Fix: Make sure the SQL Server Mail Account and Profile are setup correctly.

Linked Server Login Failed

Error: Msg 18456, Level 14, State 1, Line 3 Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Fix: Change the login to be… “Be made using this security content:“ Remote login: xxxxxx With password: xxxxx