Tag Archives: TSQL

Checking Time Zones in SQL Server

Posted on by
4

I was building some basic queries around time zone manipulation and I am happy to say that I enjoyed myself as I found a way to get a time based on a geographic region.

This new feature is available to you from SQL 2016 which obviously means SQL database too (Azure). It is called AT TIME ZONE.

AT TIME ZONE implementation relies on following registry hive where time zones are installed: KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones. This is exposed via the following query


SELECT * FROM sys.time_zone_info

Looking at books on line via https://msdn.microsoft.com/en-us/library/mt612790.aspx it is wrong.

Look at the screen shot, it states it is supported from SQL 2008 – well it is not, it only works on 2016.

times

Running it on a non SQL 2016 server you will get the following message:

Msg 208, Level 16, State 1, Line 1 Invalid object name ‘sys.time_zone_info’.

On a correct SQL version it will return the following. (Snippet)

times2

This information is key as we will use it to build the time zone queries (which is very basic).

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'GMT Standard Time' AS [My Location]
GO
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'US Mountain Standard Time' AS [US Mountain Standard Time]
GO
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Central Asia Standard Time' AS [Central Asia Standard Time]
GO
SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Central Standard Time' AS [AUS Central Standard Time]

Based on the zone offset you will get the relevant correct time.

times3

 

SQL Server tracing

Posted on by
Reply

Have you ever wanted to start a SQL trace (server-side trace!) from SQL agent and let it stop after X minutes and write the TRC to a location? Well this is how you would do it:

 ALTER PROCEDURE [dbo].[ScriptTrace] @RunTime INT,

 @FileName NVARCHAR(100) 

AS 

SET NOCOUNT ON 

DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT -- Specify Max File Size
DECLARE @Now DATETIME
DECLARE @StopTime DATETIME
DECLARE @FileStamp NVARCHAR(25)
DECLARE @COMMAND NVARCHAR(125) 

SET @Now = GETDATE() 

SET @StopTime = DATEADD (Mi , @RunTime , @Now ) 

SET @FileName = @FileName+'_' SET @FileStamp = CAST(DATEPART(YEAR, GETDATE()) AS NVARCHAR) + CAST(DATEPART(MONTH, GETDATE()) AS NVARCHAR) + CAST(DATEPART(DAY, GETDATE()) AS NVARCHAR) + CAST(DATEPART(HOUR, GETDATE()) AS NVARCHAR) + CAST(DATEPART(MINUTE, GETDATE()) AS NVARCHAR) 

SET @FileName = @FileName + @FileStamp SET @maxfilesize = 18000 

EXEC @rc = sp_trace_create @TraceID OUTPUT, 0,
@FQFileName, @maxfilesize, @StopTime 

IF (@rc != 0) GOTO error 

SET @COMMAND = 'SQL Trace Log @' + @FQFileName + ' activated for ' + CAST(@RunTime AS VARCHAR) + ' minute(s)' PRINT @COMMAND 

--Set the events for example

DECLARE @on BIT SET @on = 1 

EXEC sp_trace_setevent @TraceID, 14, 1, @on
EXEC sp_trace_setevent @TraceID, 14, 9, @on
EXEC sp_trace_setevent @TraceID, 14, 6, @on
EXEC sp_trace_setevent @TraceID, 14, 10, @on
EXEC sp_trace_setevent @TraceID, 14, 14, @on
EXEC sp_trace_setevent @TraceID, 14, 11, @on
EXEC sp_trace_setevent @TraceID, 15, 10, @on
EXEC sp_trace_setevent @TraceID, 15, 14, @on
EXEC sp_trace_setevent @TraceID, 15, 18, @on
EXEC sp_trace_setevent @TraceID, 15, 11, @on
EXEC sp_trace_setevent @TraceID, 15, 12, @on 

--Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT 

-- filter 1 include databaseId 5
EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 5 

-- filter 2
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler' EXEC sp_trace_setstatus @TraceID, 1 

SELECT TraceID=@TraceID
GOTO finish ERROR:
SELECT ErrorCode=@rc FINISH:

--Now call it
EXEC dbo.ScriptTrace '1','G:LogsTrace'

output example: SQL Trace Log @G:LogsTrace_20153101419 activated for 1 minute(s)

Timed SQL Server tracing

Posted on by
Reply

Have you ever wanted to start a SQL trace (server-side trace!) from SQL agent and let it stop after X minutes and write the TRC to a location? Well this is how you would do it:

 ALTER PROCEDURE [dbo].[ScriptTrace] @RunTime INT,

 @FileName NVARCHAR(100) 

AS 

SET NOCOUNT ON 

DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT -- Specify Max File Size
DECLARE @Now DATETIME
DECLARE @StopTime DATETIME
DECLARE @FileStamp NVARCHAR(25)
DECLARE @COMMAND NVARCHAR(125) 

SET @Now = GETDATE() 

SET @StopTime = DATEADD (Mi , @RunTime , @Now ) 

SET @FileName = @FileName+'_' SET @FileStamp = CAST(DATEPART(YEAR, GETDATE()) AS NVARCHAR) + CAST(DATEPART(MONTH, GETDATE()) AS NVARCHAR) + CAST(DATEPART(DAY, GETDATE()) AS NVARCHAR) + CAST(DATEPART(HOUR, GETDATE()) AS NVARCHAR) + CAST(DATEPART(MINUTE, GETDATE()) AS NVARCHAR) 

SET @FileName = @FileName + @FileStamp SET @maxfilesize = 18000 

EXEC @rc = sp_trace_create @TraceID OUTPUT, 0,
@FQFileName, @maxfilesize, @StopTime 

IF (@rc != 0) GOTO error 

SET @COMMAND = 'SQL Trace Log @' + @FQFileName + ' activated for ' + CAST(@RunTime AS VARCHAR) + ' minute(s)' PRINT @COMMAND 

--Set the events for example

DECLARE @on BIT SET @on = 1 

EXEC sp_trace_setevent @TraceID, 14, 1, @on
EXEC sp_trace_setevent @TraceID, 14, 9, @on
EXEC sp_trace_setevent @TraceID, 14, 6, @on
EXEC sp_trace_setevent @TraceID, 14, 10, @on
EXEC sp_trace_setevent @TraceID, 14, 14, @on
EXEC sp_trace_setevent @TraceID, 14, 11, @on
EXEC sp_trace_setevent @TraceID, 15, 10, @on
EXEC sp_trace_setevent @TraceID, 15, 14, @on
EXEC sp_trace_setevent @TraceID, 15, 18, @on
EXEC sp_trace_setevent @TraceID, 15, 11, @on
EXEC sp_trace_setevent @TraceID, 15, 12, @on 

--Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT 

-- filter 1 include databaseId 5
EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 5 

-- filter 2
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler' EXEC sp_trace_setstatus @TraceID, 1 

SELECT TraceID=@TraceID
GOTO finish ERROR:
SELECT ErrorCode=@rc FINISH:

--Now call it
EXEC dbo.ScriptTrace '1','G:\Logs\Trace'

output example: SQL Trace Log @G:\Logs\Trace_20153101419 activated for 1 minute(s)