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
shwetha onkarappaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.