Converting UTC to local time

Problem

Values in the database are stored as UTC date and time in a simple datetime format. You want to see data time values in the local time.

Solution

Use the following code to convert UTC date/time to local:

CREATE TABLE #table (date_UTC datetime)

/* Populate with UTC date time */

Insert #table Select GETUTCDATE()

Select

/* UTC value */

date_UTC

/* convert UTC to local time */

,DATEADD(MILLISECOND,DATEDIFF(MILLISECOND,getutcdate(),GETDATE()),date_UTC) as date_In_LocalTime

From

table

Use sp_replrestart to fix “The process could not execute ‘sp_repldone/sp_replcounters’” error

This error is generally raised by the log reader agent while running transactional replications. The error indicates that distributor and subscribe databases has data that may be more recent than data in the publisher database (the agent compares latest LSN in transaction logs of Publisher database and latest LSN recorded in the distributer).

This problem usually occurs after publisher database has been restored from a backup. 

To resolve the issue, run sp_replrestart stored procedure in the publisher database and then reinitialize the subscribers. Also, consider running DBCC CHECKDB on publisher database to validate consistency.

How to renew “MCSE:Business Intelligence” credentials

The other day a colleague of mine asked if I am preparing for a recertification exam for my “MCSE: Business Intelligence” certificate. His certificate was close to the expiration date and he wanted to see what resources I will be using.

That caught me by complete surprise! I totally forgot about the 3 year status of that certification. I quickly checking my profile at http://mcp.microsoft.com and there it was - my MCSE certificate was no longer active.

It turns out that Microsoft has tried to warn me, but I didn’t get those emails as they were going to an email account I don’t actively use.

At first I thought that since I missed the deadline my status has been cancelled and I would need to re-take all original exams in order to get it back. However, I decided to email Microsoft Support (certquest@microsoft.com) first and ask them about my options. Next day I received a response from them saying that my status hasn’t actually been cancelled, but was rather deactivated and that it will be re-activated as soon as I reconfirm my status by completing one of the two possible options:

• Option 1: Take re-certification exam 470
• Option 2: Recertify through Microsoft Virtual Academy

The second option required that I review and complete assessments for the following 9 MS Virtual Academy courses:

1. Database Fundamentals
2. Design and Implement Cloud Data Platform Solutions
3. Data Storage and Processing in the Cloud Demystified
4. Querying with Transact-SQL
5. Faster Insights to Data with Power BI Jump Start
6. Implementing a Data Warehouse with SQL Server Jump Start
7. Updating Your Database Management Skills to SQL Server 2014
8. Implementing Data Models & Reports with Microsoft SQL Server
9. Designing BI Solutions with Microsoft SQL Server

I chose the second option because I thought it would be a great refresher and also offered convenient free online access. It took me about 1 week to watch the videos and complete quick assessment. I really enjoyed the presentation and material (yes, you can skip forward on the videos). 

Once finished I emailed my certificate of completion to certquest@microsoft.com with a request to reactivate my status which was done 2 days after.

For details on Microsoft Virtual Academy see the following link: https://www.microsoft.com/en-us/learning/recertification-virtual-academy.aspx