Creating UTC function

UTC dates are used across the board for the applications need to be run on different time zones.

Recently I needed to migrate data from one source to different instances of databases that will be used in different countries (time zones). I expected to see a predefined SQL function that gets the datetime value along with the Time zone and returns the datetime in UTC format, but could not find such a function which was kind ofsurprising to me!

Based on my research, it can be achieved using a CLR function. Let's follow the steps below:

Step 1: We need to create the assembly CLR function which does the conversion. The CLR function can be written in .net and gives us the assembly (dll file) to be used in SQL Server. If you are not sure how to create a CLR function, refer to the link below:

http://blogpad-online.blogspot.com.au/2012/06/create-assembly-clr-function.html

Step 2: When the dll file is created, we can create an assembly to call it. For example, we name the dll file ‘TzConverter’ and copied it to the folder below:

'C:\Users\Dev\UTC\TzConverter.dll'

In creating the assembly to call CLR function, it is important to set the TRUSTWORTHY ON and create the assembly with UNSAFE permission set:

USE testDB
GO

ALTER DATABASE testDB
SET TRUSTWORTHY ON;
GO

CREATE ASSEMBLY tzconverter
FROM 'C:\Users\Dev\UTC\TzConverter.dll'
WITH PERMISSION_SET = UNSAFE
GO

Step 3: Now that the assembly has been created, we need to create the SQL function. It enables us to call the CLR function in SQL Server:

CREATE FUNCTION [dbo].[ToUtc](@dt [datetime], @timeZone NVARCHAR(100))

RETURNS [datetime] WITH EXECUTE
AS CALLER AS
EXTERNAL NAME tzconverter.[UserDefinedFunctions].ToUtc
GO

sp_configure'clr enabled', 1;
GO

RECONFIGURE;

GO

Ok, we are done. Call the function and get the UTC datetime!

--- Examples : It shows the current date time in AustralianEastern standard time format
SELECT GETDATE(), [dbo].ToUtc(GetDate(), 'AUS Eastern Standard Time')

--- Examples : It shows the current date time in New Zealandstandard time format

SELECT GETDATE(), [dbo].ToUtc(GetDate(), 'New Zealand Standard Time')

Opening a database only using the mdf file

I love to improve my knowledge and skills, especially in the profession I work in. In every day work, we come across to some problems and solve them. Therefore, I thought it would be worthwhile to share my experiences with you, hope you find them helpful.
This is my first post. I’m going to talk about attaching a database using only the .mdf file.

Attach mdf file without ldf file in Database
When you have both mdf and ldf files of a database, attaching the database would be very easy, but if we have only the mdf file and the log file is missing, it would not be as straight forward as using the SQL Server management studio visual interface. You need to write SQL script to do that.
First let see what would happen if we try to attach the database by mdf file from SQL Server Management studio:

After selecting the file, it opens two entries in the grid below, one for mdf file and the other ldf file that is missing:



After clicking ok, the error below would be displayed:



By clicking on the referred hyperlink the error details shows the issue as expected. The ldf file is missing:


Solution:
The script below will do the job easily, only you need to copy the mdf file to the DATA folder that the other SQL Server db files are stored. In my case the database name AdventureWorks2008R2 and it is stored in the following folder:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

We use CREATE DATABASE syntax using “FOR ATTACH_REBUILD_LOG” option. This command will create the database based on the mdf file and will rebuild the missing ldf file.


CREATE DATABASE AdventureWorks2008R2 ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2.mdf')
FOR ATTACH_REBUILD_LOG
GO


Pressing F5 and the database will be created easily :)