renu.iitkgp (5/8/2014)

---

Hi,

I had to convert huge volumes with datetime fields (given timezone info) to UTC.

Since Sql does not have a native way to do this, I chose to do it with the CLR-SQL way of hosting a .NET assembly in Sql environment.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Linq;

namespace SqlClr

{

public partial class UserDefinedFunctions

{

[SqlFunction(Name = "GetUTCfromTimeZone")]

public static SqlString GetUTCfromTimeZone(SqlString _dt, SqlString _timeZoneInfo)

{

if (_dt.IsNull || _timeZoneInfo.IsNull)

return _dt;

DateTime dt;

if (!DateTime.TryParse(_dt.Value, out dt))

{

return _dt;

}

// default behaviour

TimeZoneInfo tz = TimeZoneInfo.GetSystemTimeZones().Single(z => z.Id.Equals(_timeZoneInfo.Value, StringComparison.CurrentCultureIgnoreCase));

var utc = new DateTimeOffset(dt, tz.GetUtcOffset(dt));

// Sql-Server DateTime type is implemented based on the Gregorian calendar. It can not handle dates earlier than 01/01/1753.

if (utc.UtcDateTime.Year < 1753)

{

return new SqlString("01/01/1753 00:00:00");

}

return new SqlString(utc.UtcDateTime.ToString("MM/dd/yyyy HH:mm:ss"));

}

}

}

I created a Sql assembly pointing to the CLR dll file, with permission set = unsafe (somehow permission set = safe does not work).

Then, created a user defined function, which points to a method in the CLR-SQL assembly, which takes a datetime string representation, timezone string representation and returns the UTC datetime string representation.

sp_Configure 'CLR Enabled', 1 Reconfigure With Override

go

Alter Database GP_TPS_350 SET TRUSTWORTHY ON

Exec dbo.sp_changedbowner @loginame = N'sa', @map = false

go

If OBJECT_ID('dbo.GetUTCfromTimeZone') Is Not Null

Drop Function GetUTCfromTimeZone

go

If exists (select * from sys.assemblies where name = 'ClrFunctions')

drop assembly ClrFunctions

/*Create assembly from the CLR built dll*/

Create Assembly ClrFunctions From 'C:\SqlClr.dll'

WITH PERMISSION_SET = UNSAFE;

go

/*Create the scalar valued function from the CLR assembly*/

Create Function dbo.GetUTCfromTimeZone

(

@_dt As nvarchar(256),

@_timeZoneInfo As nvarchar(100)

)

Returns nvarchar(256)

As External Name [ClrFunctions].[SqlClr.UserDefinedFunctions].[GetUTCfromTimeZone]

go

Now, using that user defined function in an update statements like:

UPDATE t1

SET

t1.SOME_DATE = dbo.GetUTCfromTimeZone(t1.SOME_DATE, t2.TIMEZONE),

t1.SOME_OTHER_DATE = dbo.GetUTCfromTimeZone(t1.SOME_OTHER_DATE , t2.TIMEZONE)

FROM dbo.SOME_TABLE AS t1

INNER JOIN dbo.USER_INFO AS t2 ON t1.LAST_UPBY_USER_KEY = t2.USER_KEY

on about 50+ tables some of which have over 5 million records, the memory usage goes up to more than 90%.. and the execution takes a more than an hour sometimes..

The execution time is acceptable, but the memory wouldn't clear up until I restart the Sql Server process.

I know, the reason be: because permission set unsafe on the sql assembly, the memory allocation and disposal is not managed by the sql environment..

But when I set the permission set = safe, it yells "Permission set = safe" "trust worthy database" etc when the user defined function is invoked..

Any suggestions would be really appreciated.

Thanks

RPS.