• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 82
  • Last Modified:

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

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

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
0
shwetha onkarappa
Asked:
shwetha onkarappa
2 Solutions
 
Prasanna ECommented:
0
 
ste5anSenior DeveloperCommented:
First of all: These stored procedures are undocumented, thus officially not supported.

On SQL Azure you don't have the permissions on master, obviously.

The error itself is a compile time error. Thus: you need to split your code and separate them. E.g.

CREATE PROCEDURE p_ExtractTimeZone
    (
        @TimeZone VARCHAR(50) OUT
    )
AS
    SET NOCOUNT ON;

    EXEC master.sys.xp_regread 'HKEY_LOCAL_MACHINE' ,
                               'SYSTEM\CurrentControlSet\Control\TimeZoneInformation' ,
                               'TimeZoneKeyName' ,
                               @TimeZone OUT;
    SET @TimeZone = '''' + @TimeZone + '''';
GO

DECLARE @TimeZone VARCHAR(50) = '''GMT Standard Time''';
DECLARE @Version VARCHAR(50) = @@VERSION;

IF ( @Version LIKE 'Microsoft SQL Server %' )
    BEGIN
        EXECUTE p_ExtractTimeZone @TimeZone OUT;
    END;

Open in new window


I would consider even doing a conditional deployment. Thus on Azure a different sproc:

CREATE PROCEDURE p_ExtractTimeZone
    (
        @TimeZone VARCHAR(50) OUT
    )
AS
    SET NOCOUNT ON;

    SET @TimeZone =  '''GMT Standard Time''';
GO

DECLARE @TimeZone VARCHAR(50);
DECLARE @Version VARCHAR(50) = @@VERSION;

EXECUTE p_ExtractTimeZone @TimeZone OUT;

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
The other "gotcha" is you cannot run across DB's in Azure. You would have to prepare what is known as an Elastic Query

But Azure is not like a local machine. Generally speaking forget registry settings. In fact,  One area of the registry that is currently not supported for either read or write access is the HKEY_CURRENT_USER hive. And dare say you will find problems in other areas...

Besides which Azure uses UTC timezone. But you can do
CREATE FUNCTION dReturnDate( @dFecha as datetime)

returns DATETIME

as

begin

     DECLARE @D AS datetimeoffset

     SET @D = CONVERT(datetimeoffset, @Dfecha) AT TIME ZONE 'W. Europe Standard Time'

     RETURN CONVERT(datetime, @D);

end

Open in new window

https://blogs.msdn.microsoft.com/azuresqldbsupport/2016/07/27/lesson-learned-4-modifying-the-default-time-zone-for-your-local-time-zone/

For elastic queries please read : https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-overview
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now