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

Question

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?

Why do you use EXEC insted of SELECT? select master.dbo.fn_varbintohexstr(123) — sepupic, Oct 16 '17 at 13:45
@sepupic, please, read carefully. Example with exec is only used to get more proper error message. Select is not working either. — Vladimir Glushkov, Oct 16 '17 at 13:47
https://stackoverflow.com/questions/11284998/cant-query-between-databases-in-sql-azure — sepupic, Oct 16 '17 at 13:53
@sepupic thanks for pointing this out, gonna read through the MS article mentioned on your link — Vladimir Glushkov, Oct 16 '17 at 14:04

score 3 · Accepted Answer · answered Oct 16 '17 at 14:24

3

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.

answered Oct 16 '17 at 14:24

Alberto Morillo

13,893
2
24
30

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

1 Answers1