CODEWITHSUNDEEP
sql-serverazure-sql-database
Greg Gum
Greg Gum

Reputation: 38139

Can I call a stored procedure in an Azure database through a linked server?

I have two SQL Server databases, one in Azure (Azure SqlDb) which is used for the front end and one on-premise (the backend).

In Azure, there are no jobs save Elastic Database Jobs. But I need a maintenance stored procedure to run every 20 minutes.

The Azure Db is already set it up as a linked server, so it seems to me it would be easy to set up a job in the on-premise db, and then the job just calls the stored procedure on the Azure Db.

Is that possible? It seems to me that it should be, but I have not done this before.

That seems much simpler than setting up an Elastic Database Job to run it.

Upvotes: 0

Views: 718

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89424

Sure. Make sure you enable RPC OUT on the linked server, then you can run arbitrary TSQL batches like this:

EXEC sp_dropserver @server=N'MyAzureSqlDB', @droplogins='droplogins'
GO

EXEC sp_addlinkedserver @server = N'MyAzureSqlDB', 
                        @srvproduct=N'', 
                        @provider=N'SQLNCLI', 
                        @datasrc=N'xxxxxxxx.database.windows.net', 
                        @catalog=N'MyDatabase'

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'MyAzureSqlDB',
                          @useself=N'False',
                          @locallogin=NULL,
                          @rmtuser=N'MyUserName',
                          @rmtpassword='xxxxxxxx'
GO

EXEC sp_serveroption @server=N'MyAzureSqlDB', @optname=N'collation compatible', @optvalue=N'true'
EXEC sp_serveroption @server=N'MyAzureSqlDB', @optname=N'data access', @optvalue=N'true'
EXEC sp_serveroption @server=N'MyAzureSqlDB', @optname=N'rpc out', @optvalue=N'true'
EXEC sp_serveroption @server=N'MyAzureSqlDB', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO

exec ('exec SomeProc') at MyAzureSqlDb

Upvotes: 2

Related Questions