Azure SQL Automation stored procedures

Question

I am migrating on premises SQL server database to Azure PaaS SQL database using Data Migration Tool. I am not using Data Migration Service Instance mechanism to migrate the database as it would require VPN on the corporate network and I do not see any benefit doing that way in my case. There is one error which I am stuck on and a bit confused. When migrating a stored procedure which use automation stored procedures, I get the following error:

When I run Alter procedure script directly on Azure SQL database , I get this error; Reference to database and/or server name in 'MASTER..sp_OACreate' is not supported in this version of SQL Server.A few answers to similar question on stackoverflow suggest that Azure SQL does not support automation stored procedures but the Microsoft link has a green tick against Azure SQL which suggest it does; https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/ole-automation-sample-script?view=sql-server-2017. A snippet of the sp is:

Is it true that automation stored procedures are not supported in Azure SQL and if thats hte case then what is hte workaround apart from rewriting the sp?

if you check this documentation, you can see that sp_OACreate does not exist in Azure SQL: learn.microsoft.com/en-us/sql/relational-databases/…. Don't know what this is for but you will have to do some rewrite — Thomas
– Thomas, Commented Jan 7, 2019 at 20:10

Alberto Morillo · Accepted Answer · 2019-01-07 20:32:03Z

3

SQL Azure prevents you from calling server resources. sp_OACreate attempts to create a reference to an OLE object on the server; that's simply not allowed on Azure SQL Database. So you will need to redesign your solution to move this type of logic in a middle-tier of some kind, such as a Web Role in Windows Azure.

answered Jan 7, 2019 at 20:32

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Azure SQL Automation stored procedures

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related