CODEWITHSUNDEEP
sqlsql-serverazureazure-sql-databaseazure-sql-server
Vladimir Glushkov
Vladimir Glushkov

Reputation: 521

Calling functions of master DB from other DB on the same Azure SQL Server

I have Azure SQL DB Server + one Azure SQL database on it. Within this DB I have functions which call some functions of master DB as a part of their logic.

Example:

CREATE FUNCTION [dbo].[EncryptByKey]
(
    @EncryptionKeyId nvarchar(1024),
    @ValueToEncrypt varchar(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN master.dbo.fn_varbintohexstr(ENCRYPTBYKEY(Key_GUID(@EncryptionKeyId), @ValueToEncrypt))
END

Gives me an error:

Cannot find either column "master" or the user-defined function or aggregate "master.dbo.fn_varbintohexstr", or the name is ambiguous.

If instead I try:

exec master.dbo.fn_varbintohexstr(123)

The error I get is:

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

Are there any solutions on how to use master DB functions from user's DBs on Azure SQL server?

Upvotes: 1

Views: 1078

Answers (1)

Alberto Morillo
Alberto Morillo

Reputation: 15698

You cannot use distributed database queries using three or four part names on SQL Azure.

For queries that span multiple databases in SQL Azure, you need to use elastic queries. For more information, please visit this article.

Upvotes: 3

Related Questions