locked
Reference to database and/or server name in 'MASTER.dbo.xp_regread' is not supported in this version of SQL Server RRS feed

  • Question

  • Hi,

    I am running below query to get the time zone of the Microsoft SQL Azure server

    DECLARE @version VARCHAR(50);
    select @version = @@VERSION

    if (@version like 'Microsoft SQL Server %')
    begin
    DECLARE @TimeZone VARCHAR(50)
    EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
    'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
    'TimeZoneKeyName',@TimeZone OUT
    set @TimeZone = ''''+@TimeZone+''''
    end
    else if (@version like 'Microsoft SQL Azure %')
    begin
    set @TimeZone = '''GMT Standard Time'''
    end

    EXEC dba_UpdateColumn 'WAREHOUSE','TIME_ZONE','N','varchar(50)',@TimeZone,'N','N'

    the below error is logged when we run the query.

    "Reference to database and/or server name in 'MASTER.dbo.xp_regread' is not supported in this version of SQL Server"

    can anyone help me to find the name of the timezone which server is in?

    Thanks,

    Shwetha Onkarappa



    Monday, March 19, 2018 7:22 AM

All replies

  • Hello,

    Azure PaaS databases always use UTC as the time zone. There is no need to look for that information.


    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com



    Monday, March 19, 2018 11:30 AM
  • hi,

    I need the sql script which returns timezone name of the server, which should execute both in Azure sql and Sql server.(the script which i have given in question will work only for sql server, not for azure sql).

    Thanks,

    shwetha onkarappa


    Shweta Onkarappa

    Tuesday, March 20, 2018 7:17 AM
  • That is BY Design - there is NO Time Zone information in Azure SQL DB because as Alberto has pointed out it is ALWAYS UTC.

    You cannot change this - it is simply the way that Microsoft has built it.


    Martin Cairney SQL Server MVP

    Tuesday, March 20, 2018 8:01 AM
  • Hello,

    Using SELECT @@version you can determine if your script is running on Azure SQL Database or not.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Tuesday, March 20, 2018 7:21 PM
  • https://blogs.msdn.microsoft.com/azuresqldbsupport/2016/07/27/lesson-learned-4-modifying-the-default-time-zone-for-your-local-time-zone/

    This is the answer you are looking for.


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    Wednesday, March 21, 2018 9:49 AM